How to send email from SQL Jobs in SQL Server 2014 How to send email from SQL Server Jobs in SQL Server 2014. I want to implement email notification through stored procedure instead of Windows services.

You need to first configure DB mail in SQL Server in order to utilise this functionality. You can use the below link as a reference for DB mail configuration purposes.
https://www.brentozar.com/blitz/database-mail-configuration/
Once DB mail is set up, you can enable database mail on SQL Server Agent. The same link describes how to do that. Once it is enabled on SQL Server Agent, you need to restart the SQL Server Agent service to make that take effect.
That's it. Now you can use the msdb.dbo.sp_send_dbmail SP to send email using a SQL Server Agent job or a stored procedure.


Postgresql - Recovery using recovery.conf I am trying to recover a database using recovery.conf file for making  point-in-time copy.

On recovery.conf file, I added a recovery target time as below.

recovery_target_time = '2011-06-16 04:10:00 IST'

But in the  logs I can see if it advances the time by 3.5 hours to 07:40 and actual recovery is attempted for this time, not the time I given in recovery_target_time.

2011-06-16 12:53:45 IST LOG:  starting archive recovery
2011-06-16 12:53:45 IST LOG:  restore_command = '/app/postgresinstall/bin/pg_standby -l /dbdata/archive %f %p %r'
2011-06-16 12:53:45 IST LOG:  recovery_target_time = '2011-06-16 07:40:00+05:30'
cp: cannot stat `/dbdata/archive/00000001.history': No such file or directory

Why this difference of 3.5 hours and I need help in figuring out whats going on in deciding the recovery target time?

Update: Here are time details from database

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part
-----------+-----------
         5 |        30
(1 row)

postgres=# select now();
               now
----------------------------------
 2011-08-12 13:08:19.333068+05:30
(1 row)

OS Date/Time:
postgres@xxxxx:~$ date
Fri Aug 12 13:10:19 IST 2011

I faced the same problem when I try to restore from WAL archives on same server and my backup server. Also I can consistently reproduce this problem on both servers.

So if I need to create a near to current time backup, I use a recovery_target_time 3.5 hours behind current time and start the DB with recovery.conf

DISCLAIMER : I am not a PostgreSQL DBA, though I dabble a lot with it.

You probably need to check your timezone in the OS and in psql.

In the OS run this:

[postgres@radarPG-db1 ~]$ date
Fri Jun 17 12:55:37 EDT 2011

In psql, run the following:

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part
-----------+-----------
        -4 |         0

postgres=# select now();
              now
-------------------------------
 2011-06-17 12:54:39.195291-04

Many times, some forget to have postgres default the timezone to that of the OS. If the WAL files also contain the timezone in its internal timestamps, you must align the timezone set in postgres with that of the WAL files.

Something else to consider is the following:

  The default behavior of recovery is to
  recover along the same timeline that
  was current when the base backup was
  taken. If you want to recover into
  some child timeline (that is, you want
  to return to some state that was
  itself generated after a recovery
  attempt), you need to specify the
  target timeline ID in 'recovery.conf'.
  You cannot recover into timelines that
  branched off earlier than the base
  backup.

UPDATE 2011-06-20 15:08 EDT

This is right from the online documentation. Please look this over and see if you missed any WAL files along the way. If you waited too long to setup the WAL files on the new server, postgres may have deleted them on startup. You may need to find those WAL files. See if they reside back in pg_xlog folder from the old server.

Recovering using a Continuous Archive Backup

Stop the server, if it's running.
If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you need at the least to copy the contents of the pg_xlog subdirectory of the cluster data directory, as it might contain logs which were not archived before the system went down.
Clean out all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.
Restore the database files from your base backup. Be careful that they are restored with the right ownership (the database system user, not root!) and with the right permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.
Remove any files present in pg_xlog/; these came from the backup dump and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before.
If you had unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so that you still have the unmodified files if a problem occurs and you have to start over.)
Create a recovery command file recovery.conf in the cluster data directory (see Recovery Settings). You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery has worked.
Start the server. The server will go into recovery mode and proceed to read through the archived WAL files it needs. Should the recovery be terminated because of an external error, the server can simply be restarted and it will continue recovery. Upon completion of the recovery process, the server will rename recovery.conf to recovery.done (to prevent accidentally re-entering recovery mode in case of a crash later) and then commence normal database operations.
Inspect the contents of the database to ensure you have recovered to where you want to be. If not, return to step 1. If all is well, let in your users by restoring pg_hba.conf to normal.


String equality test fails for base64 encoded sha512 digest I have the following table

   Column    |            Type            | Collation | Nullable |
-------------+----------------------------+-----------+----------+-------------------
 name        | text                       |           | not null |
 code        | text                       |           | not null |

I insert new elements using the following INSERT

INSERT INTO table(name, code)
    VALUES('SOMENAME',encode(digest(gen_random_uuid()::text,'sha512'), 'base64')) ;

Now when I try to SELECT against the code field:

SELECT * FROM tabel WHERE code='K5HUaRwxsXzkAl3NaWHbBkvnYEK/...';

It returns an empty set.

----------
(0 rows)

The puzzling part is that it seems to break only for SHA512 with base64 encoding , it works just fine if I use SHA256 or if I change the encoding to HEX.

I have a feeling that it might be a bug, but am I missing something?

With 64 bytes, the sha512 digest is large enough that encoding it in base64 generates a newline character (LF) in the result (at about 76 characters from the start).

Probably what goes wrong is that newline character not being correctly reinjected in the SELECT query, where you truncated the literal with an ellipsis (so we can't actually see it):

SELECT * FROM tabel WHERE code='K5HUaRwxsXzkAl3NaWHbBkvnYEK/...';

To inject a newline, you may use the E'...' syntax and have \n to denote that character.

SELECT * FROM tabel WHERE code= E'K5HUaR.....\n...rest of string';


Adhoc queries memory consumption in SQL Server I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc queries.

Where are these adhoc queries? Are they related to my database? Why are these two parameters separated? How can I find and optimize them?

Any comments would be appreciated.

Could you please run below query and post the result? This would provide the percentage of Adhoc Plan, Total cache plan and percentage of Adhoc:

SELECT AdHoc_Plan_MB, Total_Cache_MB,
        AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc %'
FROM (
SELECT SUM(convert(bigint,(CASE
            WHEN objtype = 'adhoc'
            THEN size_in_bytes
            ELSE 0 END))) / 1048576.0 AdHoc_Plan_MB,
        SUM(convert(bigint, size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans)A

You may check below query also to get level of adhoc queries:

SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
       S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
       CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
       S.Total_MB_1_Use,   S.Total_MB,
       CAST( (S.Total_MB_1_Use   * 1.0 / S.Total_MB  ) as Decimal(18,2) )[Pct_MB_1_Use]
  FROM
  (
    SELECT CP.objtype[CacheType],
           COUNT(*)[Total_Plan],
           SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
           SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
           SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
           CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
           CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
                      / 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
           CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
           CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
                         ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
      FROM sys.dm_exec_cached_plans as CP
     GROUP BY CP.objtype
  ) AS S
 ORDER BY S.CacheType

You may also run below query to check query level details:

  SELECT  DISTINCT
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY MultipleQ.PlanCount DESC

Needless to say, do a bit more search on these topics and you will see tonnes of result.


Implicit conversion in fetch While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint.

Example table and query:

CREATE TABLE checkPagintion
(
    Id INT NOT NULL PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100)
 )

 DECLARE @paramPageNumber AS INT,
         @paramPageSize AS INT;

 SELECT *
 FROM checkPagintion
 ORDER BY Id
 OFFSET @paramPageNumber ROWS
 FETCH NEXT @paramPageSize ROWS ONLY

Execution plan for this query:

My question: should I use bigint for all pagination queries? If I use int will it a be a problem since implict_conversion is known for slow performance?

It's converting your variables, because bigint is what OFFSET ... FETCH is expecting for those values. It probably makes sense to

DECLARE @paramPageNumber AS BIGINT,
         @paramPageSize AS BIGINT;

to avoid any possible performance issues this may cause.

Your id column in your table, however, should still be fine as an int.

From the documentation for TOP (Transact-SQL):

-- Syntax for SQL Server and Azure SQL Database

[
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

  Arguments

  expression
  Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.


Restoring pg_dump hangs Aim: backup a database and restore it in database2

pg_dump

A PG Dump has been created by issuing the following command:

c:\Temp>pg_dump -U postgres postgres > backupTest2.sql

Opening backupTest2.sql shows SQL statements.

restore pg_dump

However, once the backup would like to be restored in another database nothing seems to happen (number of tables in database2 remains 0, command hangs).

c:\Temp>psql -U postgres -d utrechtTest > backupTest2.sql

^C

This:

psql -U postgres -d utrechtTest > backupTest2.sql

runs psql and writes its stdout to backupTest2.sql, which will be overwritten.

You meant:

psql -U postgres -d utrechtTest < backupTest2.sql

which is better written as:

psql -U postgres -d utrechtTest -f backupTest2.sql

because if you use -f then psql can show line numbers.


Which databases support encryption I'm searching for a database that can run on Windows and can be accessed with C#.
The Database also requires that the data can be encrypted (ideally without backdoors).

Can MariaDB serve here?

PostgreSQL comes to my mind. You have pgcrypto library for encrpyting data and there are drivers for .Net


How to preserve sqlplus session in shell script I am trying to insert queries from a sql file in shell script using sqlplus.
Now, some queries can fail because of incorrect schema.
So I want to commit all transactions only if all queries succeed.
The main problem is that, I would like to execute all queries irrespective of any error and rollback all queries if there was at least one error.

The idea behind executing all queries is to find the queries which would error out and create schema fix for the same.

I am using this command:

cat "temp.sql" | sqlplus -s sys/oracle@xe as sysdba

But this will auto-commit once the sqlplus exits.
I cannot use set exitcommit off since I am using 11.1.

Is there any way to do this in shell script?

preliminary note

You issue the statement

 cat "temp.sql" | sqlplus -s sys/oracle@xe as sysdba

You should avoid to pipe into sqlplus. Better you execute

sqlplus -s sys/oracle@xe as sysdba @temp.sql

An advantage is that no prompt will be generated in the output file.
You can better control how the output looks like. Some input will produce
results that make sense when working interactively but not make sense when executing a batch job.
I can't see any advantage in piping commands to sqlplus.

You should not use SYS to do your work with application objects and application data. There are  a lot of security reasons. Besides that  you should be aware that

  Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY

This is not the behavior if you issue DML statements. This can make your data inconsistent.

From the manual:

  You can specify changes you wish to make to the information stored in the database using the
  SQL Database Manipulation Language (DML)
  commands UPDATE, INSERT, and DELETE—which can be used independently or within a PL/SQL block.
  These changes are not made permanent until you enter a SQL COMMIT command or a
  SQL Database Control Language (DCL) or Database Definition Language (DDL) command
  (such as CREATE TABLE), or use the autocommit feature. The SQL*Plus autocommit feature causes
  pending changes to be committed after a specified number of successful SQL DML transactions.
  (A SQL DML transaction is either an UPDATE, INSERT, or DELETE command, or a PL/SQL block.)

So your script  must contain the right statements so that all can be rolled back.

SET EXITCOMMAND was introduced in 11.2 to SQL*Plus. I don't think that it added something really new to SQL*Plus.
You can use the WHENEVER commands to control the SQL*Plus behavior.

  I want to commit all transactions only if all queries succeed

So I assume if the first error occurs you want to rollback and exit.

Therefore you should start your script with the WHENEVER SQLERROR statement

WHENEVER SQLERROR EXIT FAILURE ROLLBACK

If an SQL error occurs the transaction will be rolled back and SQL*Plus will be exited.
The error code that SQL*Plus is an OS-system depended value that signals an error

Maybe a WHENEVER OSERROR command should be executed, too

WHENEVER OSERROR EXIT FAILURE ROLLBACK

Of course AUTOCOMMIT should not be enabled, but

SET AUTOCOMMIT OFF

is the default, so you must not execute this command.

You can add these statements to the site profile or user profile scripts (glogin.sql or login.sql)
so that they will be executed automatically whenever you connect to a database, if this makes sense.

Here  draft how you can run your scrip.

sqlplus script run01.sql

SPOOL run01.log
REM file: run01.sql
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
EXIT COMMIT
REM COMMIT is the default of EXIT

shell script main1.sh

#!/bin/bash
sqlplus $USER/$PASSWORD @run01.sql
if [[ $? = 0 ]]; then
    echo "sql script executed successfully" >&
else
    # alert
    echo "error when executing sql script, check run01.log" >&2
    fi

You added a new requirement:

  I would like to execute all queries irrespective of any error and rollback all queries if there was at least one error.

You can again run the script an rollback end exit if the first error occurs. If that happens you can
run your script again
but now you don't exit in case of error but continue with the execution of the script. At the end
of the script you roll back all transactions using the {EXIT COMMAND]8.

sqlplus script run02.sql

SPOOL run02.log
REM file: run02.sql
WHENEVER SQLERROR CONTINUE NONE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
EXIT ROLLBACK

shell script main2.sh

#!/bin/bash
sqlplus $USER/$PASSWORD @run01.sql
if [[ $? = 0 ]]; then
    echo "sql script executed successfully" >&
else
    sqlplus $USER/$PASSWORD @run02.sql
    # alert
    echo "error when executing sql script, check run02.log" >&2
    fi

This is my preferred solution. The only disadvantage I can see is
that the statements of the temp.sql script
that are executed until the first error occurs are executed two times:
in script run01.sql and in script run02.sql.
In a Linux environment you can try to avoid this by running the whole
script and analyze the logfile with the grep statement
an then exit the session with commit or rollback, depending on the
result of this analysis.

sqlplus script run03.sql

SPOOL run03.log
REM file: run03.sql
WHENEVER SQLERROR CONTINUE NONE
WHENEVER OSERROR EXIT FAILURE ROLLBACK
@temp.sql
SPOOL OFF
HOST ./analyze_logfile.sh
@exit.sql

shell script main3.sh

#!/bin/bash
sqlplus $USER/$PASSWORD @run03.sql
if [[ $? = 0 ]]; then
    echo "sql script executed successfully" >&
else
    # alert
    echo "error when executing sql script, check run03.log" >&2
    fi

shell script analyze_logfile.sh

#/bin/bash
# file: analyze_logfile.sh
if grep "^ORA-" run03.log; then
    echo "EXIT ROLLBACK"
else
    echo "EXIT COMMIT"


Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql? I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache shows the state of all the buffers in shared_buffers. Thus, size of shared buffers = Number of entries in pg_buffercache * Size of one page (8192 bytes). When I run this on my system :

    test=# select count(1) from pg_buffercache;
    count
    -------
    3072
    (1 row)

    test=# show shared_buffers;
    shared_buffers
    ----------------
    24MB
    (1 row)

Calculated size of shared buffers = 3072 * 8192 ~= 25.17 MB.
Actual size of shared buffers = 24 MB.

Why is there a difference between the computed size of shared buffers and the actual size of shared buffers ? Let me know if my understanding is wrong.

Thanks.

Looks like MB vs MiB, ie decimal SI vs binary megabyte:

regress=> SELECT round(25.17*1000*1000 / (1024*1024),1);
 round
-------
  24.0
(1 row)

You've done your conversion into megabytes using decimal SI units (MB) not binary megabytes (MiB). Divide by 1024 not by 1000, or use pg_size_pretty:

regress=> select pg_size_pretty( 3072 * 8192::bigint );
 pg_size_pretty
----------------
 24 MB
(1 row)

Compare:

regress=> select
          pg_size_pretty( 3072 * 8192::bigint ),
          (3072::real * 8192)/(1024*1024) AS binaryMiB,
          (3072::real * 8192)/(1000*1000) AS decimalMB_SI;
 pg_size_pretty | binarymib | decimalmb_si
----------------+-----------+--------------
 24 MB          |        24 |    25.165824
(1 row)

The GUC display code uses kiB / MiB / GiB too, but (IMO) mislabels it kB / MB / GB:

regress=> SELECT 24*1024*1024;
 ?column?
----------
 25165824
(1 row)

regress=> SET work_mem = 25165824;
SET
regress=> SHOW work_mem;
 work_mem
----------
 24GB
(1 row)

(It's GB because work_mem is measured in kb not bytes; ignore the factor-of-1024 difference).


Cannot remove SQL Server 2012 I was in the process of installing SQL Server 2012 x64 Express Management Studio and encountered one heck of an issue.

I was most of the way through the installation process when my computer locked up and I had to do a hard restart.

Now, the computer seems to think that I have components of SQL Server 2012 installed when in reality I do not.

I tried going to the control panel and removing the components but most of the way through the removal process I get the following error:

  "Microsoft SQL Server 2012 Service Pack 1 Setup

  The following error has occurred:

  SQL Server Setup has encountered an error when running a Windows Installer file.

  Windows Installer error message: This installation package could not be opened. Contact the application vendor to verify that this is a valid Windows Installer package.

  Windows Installer file: {A7037EB2-F953-4B12-B843-195F4D988DA1}
  Windows Installer log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log>\20140813_121519\sql_ssms_Cpu64_1.log

  Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.
  For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3128.0&EvtType=0xDC80C325"

I've spent a couple hours on several days looking around for a fix to this but I haven't been able to locate a solution as of yet.

I managed to resolve the issue. I wasn't able to uninstall SQL Server but I downloaded an installer, converted it to a .zip and extracted it and then pointed the setup to that as the install media. Doing this I was able to get Management Studio up and running.

Thanks for all the suggestions!


job send email to developers upon complexion and include DBAs if it fails I have a job that sends an email to an operator (which contains a list of emails within it) upon complexion.

Some of these people that are on that list are the developers who build everything in that job.

I am the DBA but I am also in the list.

QUESTION:

Is there a way to make this job send an email to those guys upon complexion, and to the DBAs only if the job fails?

So, upon complexion of the job, which has 4 steps,
the developers will always get an email, (the job is scheduled to run every day),
and if (by any reason) the job fails, the developers AND  the DBAs will get and email.

What you can do is, add a job step called for example mail dba.

In that job step you put the msdb.dbo.sp_send_dbmail

An example job step

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA',
    @recipients = 'some_email@somedomain.com',
    @body = 'The Job Failed.',
    @subject = 'Automated Error Message' ;

Then you edit every step of the job to go to job step mail dba when it fails.


SQL Server Query Plan XML: QueryPlanHash Length UPDATE: This is definitely a bug. For full details see this Connect Item.

While testing some changes to sp_BlitzCache (full disclosure, I'm one of the authors), I came across what I thought was a bug in our code.

At one point, we're matching the Query Plan Hash to get query cost. We do that about like so:

statement.value('sum(/p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) =
    xs:hexBinary(sql:column("b.QueryHash"))]/@StatementSubTreeCost)', 'float')

This has, as far as I've seen, worked. However, In one weird case, the substring in the XML was throwing a NULL value, and the plan was showing a cost of 0, despite it being rather high.

Digging into the execution plan (full disclosure, I work for the company that hosts Paste The Plan), I noticed that the Query Plan Hash for the one problem hash was 17 characters long, while the rest are 18. Here are examples:

QueryPlanHash="0x4410B0CA640CDA89"
QueryPlanHash="0x2262FEA4CE645569"
QueryPlanHash="0xED4F225CC0E97E5" -- Problem!
QueryPlanHash="0xBF878EEE6DB955EA"
QueryPlanHash="0x263B53BC8C14A452"
QueryPlanHash="0x89F5F146CF4B476F"
QueryPlanHash="0xEF47EA40805C8961"
QueryPlanHash="0xB7BE27D6E43677A5"
QueryPlanHash="0x815C54EC43A6A6E9"

Query Plan Hash is listed as a BINARY 8 -- presumably this should always be the same length, but what does a guy like me know about binary values?

Playing with the XQuery a little bit, I found that by changing the substring to start at the second position, it would come up with a valid (albeit incorrect) hash value.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        **q.n.value('substring(@QueryPlanHash, 2)', 'BINARY(8)')**
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT
        QueryPlanCost = statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float'),
        **q.n.value('substring(@QueryPlanHash, 3)', 'BINARY(8)')**
FROM    #statements s
CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
OPTION(RECOMPILE);

I'm running SQL Server 2016, SP1 (13.0.4001).

Has anyone run into this before?

Is 17 characters a valid length for a BINARY 8 value?

Does this look like a bug that should get a Connect item?

I think this is happening because that one hash is an odd number of characters. A valid VARBINARY is going to need to have an even number of "pairs" to correctly represent the data. So... you should be able to solve this by removing the 0x, putting a '0' at the beginning, grabbing the right 18 characters, and then casting it to VARBINARY.

CONVERT(VARBINARY(MAX), RIGHT('0' + SUBSTRING('0xED4F225CC0E97E5', 3, 20), 18), 2)

If you want something more robust, good luck, because you'd need to divide by 2 as an integer, and get the modulo of 2, and then "do the right thing" to figure out how big your data should be.


Should I convert the frontend generated UUIDv6 to binary(16) for use in SQL Server as the clustered primary key? Background
As suggested by the front-end developer, I looked into using UUID as the primary key for a bunch of tables in our new system. From learning the pros and cons of random vs. sequential UUIDs, to the use of a non-clustered primary key in combination of a clustered index with a sort-able type, my research pointed me to UUIDv6, and an implementation of it.
It is able to generate UUIDs like the below (that is sequential):

UUIDv1                               UUIDv6
------------------------------------ ------------------------------------
5714f720-1268-11e7-a24b-96d95aa38c32 1e712685-714f-6720-a23a-c90103f70be6
68f820c0-1268-11e7-a24b-96d95aa38c32 1e712686-8f82-60c0-ac07-7d6641ed230d
7ada38f0-1268-11e7-a24b-96d95aa38c32 1e712687-ada3-68f0-93f8-c1ebf8e6fc8c
8cc06fd0-1268-11e7-a24b-96d95aa38c32 1e712688-cc06-6fd0-a828-671acd892c6a
9ea6a6b0-1268-11e7-a24b-96d95aa38c32 1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which I thought SQL Server would gladly sort them for me in the clustered primary key (uniqueidentifier) column.
Little did I know how SQL Server would sort an uniqueidentifier column. Here's the ascending sort result:

UUIDv6 uniqueidentifier sorted
------------------------------------
1e712688-cc06-6fd0-a828-671acd892c6a
1e712686-8f82-60c0-ac07-7d6641ed230d
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c
1e712685-714f-6720-a23a-c90103f70be6
1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which is causing fragmentation as if using random UUIDs. This post explains how they were actually sorted.
The real question
Luckily, the system is still in development. Which of these options should I go for next?

reorder the bytes so that the most/least significant bytes are where SQL Server expects them to be

UUIDv6                               UUIDv6 reordered bytes
------------------------------------ ------------------------------------
1e712685-714f-6720-a23a-c90103f70be6 c90103f7-0be6-a23a-6720-1e712685714f
1e712686-8f82-60c0-ac07-7d6641ed230d 7d6641ed-230d-ac07-60c0-1e7126868f82
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c c1ebf8e6-fc8c-93f8-68f0-1e712687ada3
1e712688-cc06-6fd0-a828-671acd892c6a 671acd89-2c6a-a828-6fd0-1e712688cc06
1e712689-ea6a-66b0-910c-dbcdb07df7a4 dbcdb07d-f7a4-910c-66b0-1e712689ea6a

convert the UUIDv6 to binary(16) and use that instead

UUIDv6                               UUIDv6 binary(16)
------------------------------------ --------------------------------
1e712685-714f-6720-a23a-c90103f70be6 1e712685714f6720a23ac90103f70be6
1e712686-8f82-60c0-ac07-7d6641ed230d 1e7126868f8260c0ac077d6641ed230d
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 1e712687ada368f093f8c1ebf8e6fc8c
1e712688-cc06-6fd0-a828-671acd892c6a 1e712688cc066fd0a828671acd892c6a
1e712689-ea6a-66b0-910c-dbcdb07df7a4 1e712689ea6a66b0910cdbcdb07df7a4

Problem with option 1
The UUID standard embeds a 4-bit version field inside the ID. UUIDv6 (still non-standard) also follows that rule. The way I will reorder them is going to break this.
Problem with option 2
I'm not sure. Can hardly find anyone talking about it except this, which is going against the idea. Are there other pitfalls that I should be aware of in using the binary(16) type?
Thanks!

I would consider another option: cluster on a surrogate key (like Id int IDENTITY(1,1) NOT NULL), and make the app-generated UUID a nonclustered primary key.
This avoids the problems you called out with options 1 and 2, because you don't have to worry about the sorting / fragmentation issues in your base table, or potentially esoteric issues with clustering on binary.
It will also save you some space (int is smaller than uniqueidentifier and binary(16)), as the clustering key is included in every nonclustered index (as well as other tables that reference this table via foreign key).


Copying distinct rows from one table to another In SQL Server, I can copy one table into another by doing:

Select * into desttable from sourcetable

However, there are duplicate rows in sourcetable and I would like to just copy distinct rows. How do I do this?

Thanks

If they are true EXACT duplicates, you can do:

SELECT DISTINCT *
INTO NewTable
FROM Sourcetable

If they aren't exact dupes in every single field, then we need more info.


AWS RDS SQL Server extremely slow compared to EC2 with SQL Server Background: I built a tool which grabs a lot of data from multiple third party APIs and stores it in a SQL Server database. Then, I built a visualization to display this data in tabular and chart format.

Problem: I just recently migrated from SQL Server on EC2 (m1.medium) to RDS (db.m3.medium) and used this tool to get all my data into my RDS instance.

My application layer is PHP on EC2. Everything is working perfectly. After migration I was able to enter ~300,000 rows INTO the DB via PHP and ODBC driver. Today I went to run a transform stored procedure which will take the ~300,000 rows of data from landing and move it into dbo. On EC2+SQL Server  this script took at most 15 minutes. Now using RDS this script has taken up to 5 hours.

My transform script simply creates temp tables, moves the data from landing into the temp tables, creates a join between temp table and dbo, and where there is no data in that join inserts the data from temp table into dbo. In short, if the data is not in dbo it inserts it.

Example logic is included below:

SELECT DISTINCT *
INTO ##temptable
FROM lnd.landingtable

INSERT INTO dbo.realtable(
    [DataTable].id
    ,[DataTable].name
    ,[DataTable].createdBy
    ,[DataTable].createdDate
    ,[DataTable].updatedBy
    ,[DataTable].updatedDate)
SELECT
    [Landing].id
    ,[Landing].name
    ,SYSTEM_USER
    ,GETDATE()
    ,SYSTEM_USER
    ,GETDATE()
FROM
    ##temptable [Landing]
    LEFT JOIN dbo.realtable [DataTable] ON [Landing].id = [DataTable].id
WHERE
    [DataTable].id IS NULL

I cannot figure out what I am doing wrong. I did not provision IOPS due to cost however from what I read online this should not be necessary. The difference in performance is so dramatic that I cannot understand why anyone would use RDS. Obviously I am doing something majorly wrong. Any guidelines, links, or comments would be greatly appreciated!!

It seems that the tool I used to migrate my DB from EC2 to RDS added a bunch of weird indexes which caused the performance of the DB to tank.  Once I removed any and all elements of the DB with 'azure' in their name my performance returned to normal!  The tool was extremely useful but if anyone happens upon this question know that you have to remove anything Azure created other than your original data.


Clustering vs. transactional replication vs. availability groups Assuming you need to make sure your application that relies on SQL Server 2012 as its database backend is available around the clock, even if one server machine fails.

As a developer and not a DBA, I am struggling to understand when to use which scenario for my failover / high availability:

Two (or more) servers in a Windows Failover cluster, SQL Server as a clustered instance
Two (or more) SQL Server instances that are kept up to date with transactional replication
Two (or more) SQL Servers in a SQL Server Availability Group, configured in a synchronous commit mode

Which of each of those scenarios works for what kind of workload, and what kind of failure / outage can be handled by those scenarios? Are they even comparable / exchangable?

The way I always like to visualize high availability solutions is the following:

SQL Server Failover Cluster Instance (FCI)

What is highly available?  The entire instance.  That includes all server-objects (logins, SQL Server Agent jobs, etc.).  This also includes databases and their containing entities.  It's a great solution for highly available SQL Server instances, because that is going to be the level of containment with this given solution.

What about reporting?  None, NULL, nonexistent.  A failover cluster instance has an active node delivering the cluster group containing the instance, VNN, etc. and all other nodes are passive, sitting idle (as far as the current cluster group is concerned) and waiting for a failover.

What happens when there is failover?  The downtime for an FCI is going to be determined by the amount of time that the passive node takes to grab the cluster resource and bring the SQL Server instance in a running state.  This is typically minimal in time.

Any client abstraction?  Yes, this is going to be innately built in with the virtual network name for the failover cluster instance.  This will always point to the active node that is currently delivering the SQL Server cluster resource.

AlwaysOn Availability Groups

What is highly available?  An availability group is going to be the logical containment of high availability here, whereas an availability group consists of a number of databases and a virtual network name (the listener, an optional cluster resource).  It is worth noting that server objects such as logins and SQL Server Agent jobs will not be part of the HA solution, and special consideration needs to be taken to ensure that these are properly implemented with an availability group.  Not an overly burdening requirement, but needs to be cared for.

What about reporting? This is a great solution for reporting, although I probably wouldn't use a synchronous replica as my reporting instance.  There are two commit relationships, synchronous and asynchronous.  In my opinion and from what I've seen in practice, is that your synchronous secondary replica is there waiting for a disaster.  Think of it as that replica that's ready to take a no-data-loss failover in the event of an issue.  Then there are asynchronous replicas that can handle that reporting workload.  You aren't using this replica as the aforementioned solution, but moreso for things like reporting.  Reporting workloads can be pointed to this replica (either directly, or indirectly through read-only routing via the listener).

What happens when there is failover?  For a synchronous commit secondary replica that is paired with automatic failover, this will be the replica role state change from SECONDARY_NORMAL to PRIMARY_NORMAL.  In order for there to be automatic failover, you need to have a synchronous secondary replica that is currently synchronized, and what's implemented is the Flexible Failover Policy to determine when in fact this failover should occur.  That policy is indeed configurable.

Any client abstraction?  Yes, you could optionally configure an AlwaysOn Availability Group listener.  This is basically just a virtual network name (can be seen through WSFC as a cluster resource in the AG's cluster group) that points to the current primary replica.  This is a key part of shifting your reporting workload around, as well as setting up a read-only routing list on any servers that you want to redirect ReadOnly traffic (this is set through the connection string, with the .NET Framework Provider for SQL Server, this will be the Application Intent parameter, set to ReadOnly).  You would also need to set a read-only routing URL for each replica that you want to receive this reporting workload while in the secondary replica role.

Transactional Replication

What is highly available?  This is arguable, but I'm going to say nothing.  I don't see replication as a high availability solution whatsoever.  Yes, data modifications are being pushed to subscribers but we're talking at the publication/article level.  This is going to be a subset of the data (could include all the data, but that won't be enforced.  I.e. you create a new table in the publisher database, and that will not automatically be pushed to the subscribers).  As far as HA goes, this is bottom-of-the-barrel and I will not group it in there with a rock-solid HA solution.

What about reporting?  A great solution for reporting on a subset of data, no question about that.  If you have a 1 TB database that is highly transactional and you want to keep that reporting workload off the OLTP database, transactional replication is a great way to push a subset of data to a subscriber (or subscribers) for the reporting workload.  What happens if out of that 1 TB of data your reporting workload is only about 50 GB?  This is a smart solution, and relatively configurable to meet your business needs.

Summary

What it boils down to are a handful of questions that need to be answered (partly by the business):

What needs to be highly available?
What does the SLA dictate for HA/DR?
What kind of reporting will be taking place and what latencies are acceptable?
What do we need to handle with geographically dispersed HA? (storage replication is expensive, but a must with an FCI.  AGs don't require shared storage from standalone instances, and you could use a file share witness for quorum potentially eliminating the need for shared storage)


SQL services wont start due to incorrect drive letter after windows restore SQL services wont start due to incorrect drive letter after windows restore

Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services were already installed.

Only problem the windows drives letters are wrong. So the services are pointed to the incorrect drive letter. I cant change the services in reedit or change the drive letter in disk management. Is there a way to do a repair in SQL add remove programs to associate's the necessary services with the correct drive letter?

Here's a example of the services
Path to executable
I:\Program Files\Microsoft SQL Server\MSSQL.1\

only problem the actual drive letter is E:

Services that wont start
SQL SERVER
SQL SERVER AGENT
SQL Server Analysis
SQL Server Analysis Services
SQL Server Integration Services
SQL Server Reporting Services

I assume you've restored entire partitions, but the partition drive letters are now wrong.

If SQL is looking for I: then you have three choices

a) Fix the drive letter of the partition that's now got the data.  You said this is not an option.  Is that because I: is already taken, or because something else is now looking for this data based on its new drive letter (E:)

b) Add an additional drive letter for the partition - nothing stops you referring to the same partition by more than one drive letter.  This isn't an option if I: is already taken and you can't move that data from I:

c) Create a symbolic link on your new I: drive to point things back to what's now on E:.  Start an elevated command prompt.  Then type

I:
cd "\Program Files\"
mklink /d "Microsoft SQL Server" "E:\Program Files\Microsoft SQL Server"

If you already have a Microsoft SQL Server folder on your new I: then change the mklink command to make the MSSQL.1 folder as the symlink rather than the Microsoft SQL Server folder.


Delete rows while updating references Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance:

CREATE TABLE person (id int PRIMARY KEY);
CREATE TABLE sale (id int PRIMARY KEY,
    person_id int REFERENCES person(id) ON UPDATE CASCADE);

Now, what I am trying to do is: Remove duplicate entries from the person table, fixing the references sale references. For instance, using this data:

INSERT INTO person values (1), (2), (3);
INSERT INTO sale VALUES (11, 1), (12, 2), (13, 3);

Person 1 and 2 are actually the same, so they should be 'merged'. By merging I mean excluding person 2 while updating the references for person 1 in the sale table.

Since there is an ON UPDATE CASCADE on the table that references person, I wonder if there is some way to, without having to manually update the sale table, cause the cascade update while deleting the person 2

What I am trying to achieve here is avoiding a dedicated procedure to update the references since the number of referring tables will most likely increase, requiring maintenance in this procedure (and it is quite big already).

No, there is no built-in feature for merging one key into another, updating all referencing rows automatically. There could be all kinds of complications you need to resolve.

Simple case

You need to run UPDATE on all referencing tables and DELETE on the referenced table. The simple case would look like this:

BEGIN;

--  1. update references
UPDATE sale
SET    person_id = _org_id
WHERE  person_id = _dupe_id;

-- 2. kill dupe
DELETE FROM person
WHERE  id = _dupe_id;

COMMIT;

But there may be all kinds of ...

Complications

Imagine a table person_tag implementing an n:m relationship between person and tag with a unique constraint on (person_id, tag_id). You can't UPDATE rows that would result in duplicate tags. You need to resolve conflicts: only update rows that do not conflict with the unique constraint and delete the rest.

-- example for conflict resolution
UPDATE person_tag pt
SET    person_id = _org_id
WHERE  person_id = _dupe_id
AND NOT EXISTS (
   SELECT 1 FROM person_tag
   WHERE  person_id = _org_id
   AND    tag_id = pt.tag_id
   );

DELETE FROM person_tag
WHERE  person_id = _dupe_id;
-- end person_tag

There may be more sophisticated constellations ...

Full automation

If you have lots of referencing tables that can be treated in the same fashion (or a changing number), you can automate the procedure with dynamic SQL.

For the simple case with a single-column fk constraint and no complications:

CREATE OR REPLACE FUNCTION f_merge_id(_tbl regclass, _col text, _org_id int, _dupe_id int)
  RETURNS void AS
$func$
DECLARE
   rec record;
BEGIN
   FOR rec IN  -- find all referencing columns
      SELECT c.conrelid::regclass AS tbl, quote_ident(a2.attname) AS col
      FROM   pg_catalog.pg_attribute  a1
      JOIN   pg_catalog.pg_constraint c  ON c.confrelid = a1.attrelid
                                        AND c.confkey   = ARRAY[a1.attnum]
      JOIN   pg_catalog.pg_attribute  a2 ON a2.attrelid = c.conrelid
                                        AND a2.attnum   = c.conkey[1]
      WHERE  a1.attrelid = _tbl
      AND    a1.attname  = _col
      AND    c.contype   = 'f'  -- fk constraint
   LOOP        -- Redirect to _org_id all references to dupe_id
      EXECUTE format('
         UPDATE %1$s
         SET    %2$s = $1
         WHERE  %2$s = $2'
        ,rec.tbl, rec.col)
      USING _org_id, _dupe_id;
   END LOOP;

   -- Finally kill (now orphaned) dupe
   EXECUTE format('DELETE FROM %s WHERE %s = $1', _tbl, _col)
   USING _dupe_id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_merge_id('person', 'person_id', _org_id := 1, _dupe_id := 2);

This updates any number of tables that have a foreign key referencing the master table.

Using named parameters in the call, which is not required, but you'll want to make sure  not to confuse original and duplicate here.

SQL Fiddle.

Major points

If you are unfamiliar with the concept of dynamic SQL in plpgsql functions, refer to some of the related questions:

https://dba.stackexchange.com/questions/tagged/postgresql+dynamic-sql+plpgsql

Quote column names correctly to avoid SQL injection. quote_ident() does that for you:

SQL injection in Postgres functions vs prepared queries

If you have to deal with heavy concurrent load, you must be prepared that the final DELETE can fail, which would roll back the whole transaction. Concurrent transactions could enter new rows with _dupe_id. There are various ways to deal with that, which goes beyond the scope of this question.


move data folder and drive mysql centos i need to move the data to a different folder and drive on my cent os db server.

i added new hard drive (since we ran low on space),

i have a 1tb of hard drive and i have a new 2tb i want to put the data on on the same server (dedicated server hosted in a datacenter)

i would like to know what is the easy way to migrate the data without losing any data?

currently i have the data located in: /var/lib/mysql/ on cent os. mysql version is 5.1

Step 1

First setup your drive (assumed it's mounted and setup correctly in the fstab) and create new MySQL directory:

sudo mkdir /path/to/new/mysql

Make sure this folder has the right permissions:

chown -R mysql:mysql /data/mysql

Now here you can shutdown the service while copying data. Copy the data from the current data directory to new data directory, example:.

sudo cp -R /var/lib/mysql/* /path/to/new/mysql/

or you can copy individually each databases if you prefer or if you feel safe, you can move the folder using the mv command.

Step 2

You can change the location of your MySQL data in your my.cnf. If you don't know where this file is, you can run the command:

locate my.cnf

This will give you the path of where the my.cnf is located. Then edit the file:

sudo vi /etc/mysql/my.cnf (of course you have to replace with your path)

Now look for datadir = /var/lib/mysql and update to datadir = /path/to/new/mysql.

Then restart mysql

sudo /etc/init.d/mysqld restart

After few days/weeks, if you feel comfortable you can remove the old folder (/var/lib/mysql) or backup everything inside.


How do I save result of a join to another table? In PostgreSQL I can save result if SELECT to another table with SELECT INTO:

SELECT id INTO new_record_ids FROM new_table
  EXCEPT
    SELECT id FROM old_table
ORDER BY id;

However this doesn't work for for joins:

-- doesn't work:
SELECT * INTO new_records FROM new_table
  JOIN new_record_ids ON new_record_ids.id = new_table.id
ORDER BY new_table.id;

The join itself is correct:

-- works:
SELECT * FROM new_table
  JOIN new_record_ids ON new_record_ids.id = new_table.id
ORDER BY new_table.id;

My question is: how to save joint table to another table?

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like...

INSERT INTO new_records
SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id)
--this assumes the result of the select has the same column layout as new_records...
--which seems unlikely. replace (SELECT *...) with (SELECT column1, column2, etc...) to match the new_records column layout
ORDER BY t.id
--assuming you meant new_table.id instead of new.id although the ORDER BY clause seems extraneous for an insert anyway

If I misinterpreted and you're trying to select into a new table that has not yet been created, check the docs for CREATE TABLE...AS SELECT syntax.

EDIT:

From the comments under your question it sounds like you're trying to populate a brand new table with the query results, in which case...

CREATE TABLE new_records AS
SELECT t.* FROM new_table t JOIN new_record_ids r ON(r.id = t.id);

...should work just fine. As pointed out in comments, the SELECT INTO docs state:

  The PostgreSQL usage of SELECT INTO to represent table creation is
  historical. It is best to use CREATE TABLE AS for this purpose in new
  code.


Update various tables depending on survey answer I've created a survey solution and it work just fine. All questions have multiple options to choose from as answer. What I would like to achieve is when a certain option is saved as an answer the value or values of a another table is to be updated. The table or tables that are to be updated may vary depending on the answer of the user and the data type of column might also vary.

I was thinking about using a metadata table, in this example AffectedData, containing information about the tables, the values and data types. Something like this pseudo code:

CREATE TABLE AffectedData
    ID int
    TableName nvarchar(50) NOT NULL -- table to update
    ColumnName nvarchar(50) NOT NULL -- the col to update
    DataType nvarchar(50) NOT NULL -- the col data type
    DataValue nvarchar(50) NOT NULL -- the value to update with
    Operation int --insert or update

When an answer is inserted a trigger or a procedure might query this table and by using some conditional logic cast the DataValue to the correct data type, using DataType as hint, and insert or update into to the table specified in TableName. In other words I assume I have to use dynamic SQL, using sp_executesql to execute the statement.

Is this a way to go or is there some other wise way of achieving this?

I would say this is a pretty reasonable solution.  You might consider adding an "active" flag or something similar so you can deactivate certain rows from your AffectedData table without deleting them.  Also you will need to make sure that any solution you have takes into account the possibility of multiple entries in the metadata table.  Either by putting a primary key on it that restricts them or by using a loop or batch solution when you are creating your dynamic SQL.


Oracle : Is my DISTINCT function working properly? I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. But I get a doubt if DISTINCT function is working properly for 1 TB full data of alphanumeric values?

First query

SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_1;
SELECT COUNT(DISTINCT(DOC_ID)) FROM TABLE_2;

Difference of count is 3910

Second query

SELECT COUNT(*) DOC_ID FROM
(SELECT DISTINCT(DOC_ID) FROM TABLE_1
MINUS
SELECT DISTINCT(DOC_ID) FROM TABLE_2);

count is 0

Does DISTINCT work properly on alphanumeric DOC_ID, especially if my data is huge?

Your two queries are counting the same thing.

Consider this data:

create table table1 (doc_id varchar(20));
create table table2 (doc_id varchar(20));

insert into table1 values ('foo');
insert into table1 values ('foo');
insert into table1 values ('bar');
insert into table1 values ('bar');

insert into table2 values ('foo');
insert into table2 values ('foo');
insert into table2 values ('bar');
insert into table2 values ('bar');

count(distinct doc_id) returns 2 for both tables.

But the following returns no rows at all:

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;

So the count of that is of course zero.

If you change the sample data your second query still counts something different:

delete from table2;
insert into table2 values ('foo2');
insert into table2 values ('foo2');
insert into table2 values ('bar2');
insert into table2 values ('bar2');

count(distinct doc_id) still returns 2 for both tables, so the difference is still 0 between those two counts

But,

select distinct doc_id
from table1
minus
select distinct doc_id
from table2;

Now only returns foo and bar because foo2 and bar2 are not in table1.

If you add another distinct value to table2:

insert into table2 values ('bar3');

The difference in the distinct count from both tables is 1 (2 distinct values in table1, 3 in table2), but the minus still returns the two distinct values from the first table.


Is a geographically distributed application with SQL Server replication a good idea? We are currently thinking about a target architecture for a geographically distributed application.

This application tracks and schedules the movements of all material units across various production sites.

There will be two logical entities:

The first one is responsible of the operational/local management of a
specific store - there are as many instances of this entity as there
are stores
The second one is responsible of the global management (for example:
units transfers between stores) and therefore communicates with all
operational/local entities

Here is the logical diagram of the application:

The implementation of this application cannot be directly derived from the previous diagram because we have to take into account the following constraints:

Stores are located in different areas (typically a few stores per
area) and if the area is temporarily isolated from the global
network, the system should still be functional for a limited amount
of time.
The operational entity requires the global entity to work

Therefore, I thought about a new architecture where we would create identical instances of the global entity (same executables) with synchronized databases for each area:

I was wondering if anyone already used SQL Server replication to achieve the same kind of goal.

Is it the right way to proceed ? All recommendations/warnings are welcome !

Thanks a lot,
Sébastien.

Replication from one box to area servers, then having those area servers replicate to the store servers within that area is easy.  Setting up the replication to feed from two different area servers to each store server isn't really going to be doable.  You could probably hack it to work, but it wouldn't be all that easy.

You would be better off setting up the area servers with high availability so that the chance of the area server going offline in minimal.


What are all the ways an open transaction will rollback? When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions while the query is in a sleeping status, sometimes for days (showing via sp_WhoIsActive). This leads me to believe that there is an error somewhere in the application that is not allowing the transactions to commit. What are all the ways that these transactions could possibly rollback?

Client-side timeout (if specified)
Client closes or restarts application
Manual kill of the SPID
Anything else?

Should I be doing anything with transactions that have been open for hours or days? If they are going to rollback or timeout at some point anyway, is there any harm in killing the process?

To add to what @RLF provided in his answer; the following will also result in transaction rollback.

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE; will rollback any open transactions.
Disconnection by the client, or server, or any bit of infrastructure in between.
If the underlying disk subsystem goes away, all open connections to the database will be terminated. Uncommitted Transactions will be rolled-back when the database is subsequently opened.
If the server is shutdown, or quits for any reason, uncommitted transactions will be rolled back when it starts up and opens the associated database.
If SET XACT_ABORT ON is set, any T-SQL statement that raises a run-time error will cause an open transaction to roll back.
Batch-scoped transactions, applicable to multiple active result sets (MARS), that have not been committed will roll back when a batch completes.
For completeness, ROLLBACK TRANSACTION will, rather obviously, roll back a transaction.
If the transaction log becomes full while a transaction is open, and the database is subsequently closed for any reason, roll-back will occur when the database is brought back online.
If SET IMPLICIT_TRANSACTIONS ON; is set, including by ANSI default, uncommitted transactions will be automatically rolled back when the client disconnects.


how can I add user who can read all tables in database and have permission to alter only one table I am creating a database using SQL server express 2016 for a college project and
need to learn how to add a user whom can have access to all tables, however, only be able to alter one of the tables, update, delete, etc... I'm using sql server
express 2016 management studio

Add the user to the db_datareader role within the database

sp_addrolemember @rolename = 'db_datareader' @membername '<USERNAME>'

Give the user permission to alter that single table

grant alter on [<TABLE>] to [<USER>]

This will give the permissions you want. You could also create a specific database role with the correct permissions, or grant the user permissions on the schema so you should check the msdn article suggested by @randolph-west


Insert a row into the table omitting all values? Suppose I have a table with a serial as primary key and couple of other columns, that are all nullable. Is there any way to insert a new row in that table by omitting all the columns in the insert clause? Since serial is auto-incremental and can be omitted right? The rest of columns can be null and can therefore be omitted too? So theoretically it should be possible somehow. But is it?

Yes, you can use the default values clause (at least in Postgres):

insert into some_table
default values;

Online example


Upgrade BI server to 2014 and stay on 2008r2 with the database engine server We would be happy to upgrade our SQL Server environments to 2014, but we have a tight budget for next year.

Our BI server (SQL Server 2008 Standard Edition, that is used only for BI purposes) is located separately from the Production Database server (SQL Server 2008 Enterprise Edition). The BI server contains our SSIS and SSRS packages and reports.

I was wandering whether it's possible to have a configuration where the BI server will have SQL Server 2014 (Standard of Business Intelligence) but the database server itself will remain on 2008.

Thanks for your answers,

Roni.

Edit

If your setup involves your BI server being standalone and the report server database is being hosted on the BI server and not the production database then yes upgrade to 2014. You should consider looking at the new deployment methods with SSIS, very cool stuff.

If your setup involves:

your BI server only houses the Integration Services and Report Server binaries
your SSRS report database is being hosted on the production server
your IS packages are file system

Then the below would apply.

With SSRS that would be a supported setup if you installed SQL Server 2014 SSRS on your BI server. You can view the documentation here that shows the supported database instances to house the SSRS databases.

  SQL Server is used to host the report server databases. The SQL Server Database Engine instance can be a local or remote instance. The following are the supported versions of SQL Server Database Engine that can be used to host the report server databases:

SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005

SSIS 2014 is another beast though if you want to actually use the new features that come with it for management: SSISDB Catalog or Project Deployment (which requires the SSIDB Catalog). You would have to have a SQL Server 2014 database instance in order to configure all this. The only thing you would be able to do with this type of setup would be a file system deployment, anything else I think is going to require the database engine to be at the same major version.


Postgres configuration suitable for a single 300 GB dataset (5 billion rows) Our purpose is to use up every bit of resources on 1 machine dedicated to just serve one dataset to 2 data scientists and make it available for slicing, grouping, etc.

Here is our current setup and configuration

Data: 5 billion rows (~300GB) of AWS cloudwatch 5-minute data
Hardware: AWS EC2 t2.2xlarge (8 cores, 32GB RAM, 500GB gp2 disk)
Postgresql version 10
Modified sections from the defaults of /etc/postgresql/10/main/postgresql.conf

work_mem = 25GB
maintenance_work_mem = 25GB

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

With the current configuration, select count(1) from resource_usage8 takes forever to execute.

enel_cloudwatch=# explain select count(1) from resource_usage8;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=19774628.42..19774628.43 rows=1 width=8)
   ->  Gather  (cost=19774628.00..19774628.41 rows=4 width=8)
         Workers Planned: 4
         ->  Partial Aggregate  (cost=19773628.00..19773628.01 rows=1 width=8)
               ->  Parallel Seq Scan on resource_usage8  (cost=0.00..18904390.40 rows=347695040 width=0)
(5 rows)

Here's the table's schema

enel_cloudwatch=# \d resource_usage8
                        Table "public.resource_usage8"
     Column     |            Type             | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
 mtr_resourceid | text                        |           | not null |
 mtr_project    | character varying(30)       |           |          |
 mtr_timestamp  | timestamp without time zone |           |          |
 mtr_metrictype | character varying(30)       |           |          |
 mtr_max        | real                        |           |          |
 mtr_sum        | real                        |           |          |
 mtr_average    | real                        |           |          |

and here is the index I'm creating (which takes several hours to complete)

enel_cloudwatch=# CREATE INDEX ON resource_usage8 USING btree (mtr_resourceid, mtr_metrictype, mtr_timestamp);

Are there any other suggestions that I'm overlooking? Should I just give up on postgresql and resort to another database?

PS: This question was prompted from this SO.

work_mem = 25GB

With only 32GB of RAM, this is much too high.  With two people working independently, and some queries potentially having multiple sort/hash nodes apiece, you can easily oversubscribed memory here.  I wouldn't set this this higher than 4 GB.  And even that is probably aggressive if you expect much parallelism, as each parallel worker takes work_mem independently. So unless you are going to keep a window open running "top" or similar and constantly monitor it, probably 1 GB is a better upper limit.

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

I'd maybe double all of these.  Since you have chosen a burstable instance, I'm assuming there are long periods of idleness.  When it is active, you want to throw all the CPUs you have at "max_parallel_workers_per_gather".  There is no point saving half the CPUs for the benefit of another backend who probably doesn't exist at the moment. On the other hand, what AWS calls a vCPU is only about half a CPU, so maybe the current settings are about correct.

I didn't know what to make of your collation of "C.UTF-8", so I ran a test.  A CPU-bound index build under "C.UTF-8" took about the same amount of time as it did under "en_US", which was about two to three times as long as under "C".  (twice as long in version 12dev, three times as long in version 10.)  So I would recreate your database using "C", not "C.UTF-8", unless you have a librarian or a lawyer telling you that you can't do that.

The first where-less 'select count(*)...' after the data is loaded is going to have do much of the same work as VACUUM does, if the table hasn't already been vacuumed.  This will slow it down a lot, and so will not give you an accurate idea of what the steady state will look like.  So you should either make sure to VACUUM first, or discard the timing of the first 'select count(*)'.  Also, "forever" is not very precise, something like "I killed it after 5 hours" would at least give us a lower bound.

  Should I just give up on postgresql and resort to another database?

I don't know enough about them to recommend them by name, but a database designed specifically for analytics should certainly be better at that tailored task then a general purpose database like PostgreSQL.  There are many tradeoffs to be made, and when it really must choose between them PostgreSQL usually chooses in favor of ACID-compliant transaction processing, rather than batch processing. But squeezing the last ounce of performance out of any product is going to come with a steep learning curve. Flitting from product to product without putting in the time to learn each thoroughly will probably not lead to better results.


How to Drop Tables using a variable in SQL Server? DETAILS:
I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.

WHY?:
In my Access Database I am running a transaction where a bunch of new SQL tables will be created on the fly... but if for some reason one of these tables fails to be created then I need to completely roll back and Drop any of tables that were created in the Back-End.

QUESTION:
Is it possible to create a generic stored procedure to drop any SQL table depending on the name I pass through.

NOTE: I have only just started learning SQL code thus I have minimal understanding of SQL code and syntax so describing in layman's terms would be much appreciated.

SO FAR:

    CREATE PROCEDURE dbo.procdroptable
        @TABLENAME SYSNAME
    AS
    BEGIN
        SET NOCOUNT ON;
    DECLARE @ SQL NVARCHAR(MAX)
    SELECT @ SQL 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC Sp_Executesql @SQL;
    END
    GO

Without questioning your motives, just looking at the stored proc's code, you need to make one or 2 small adjustments as follows:

CREATE PROCEDURE dbo.procdroptable
    @TABLENAME SYSNAME
AS
 BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
    EXEC sp_executesql @SQL;
 END
GO

You can then execute this like so: exec dbo.procdroptable @TABLENAME = 'yourtablename'

Note: this proc also assumes that all your tables will belong to the [dbo] schema.


Is BulkCopy available for SQL-Server 2012 Is BulkCopy still supported by SQL-Server 2012?

Yes, BCP.EXE for SQL Server 2012 works on Windows 10. I have tried it myself several times, though do not use it extensively (so there could be a minor issue with one of the features that I haven't used, but I highly doubt it).

Also, BCP (like the other command-line utilities) is freely available, so you should test it for yourself on your system(s). You can download it from the following page:

Microsoft® SQL Server® 2012 Feature Pack

Expand the "Install Instructions" section and scroll down to (or search for) "Microsoft® SQL Server® 2012 Command Line Utilities".


How to sort query filename output like Windows explorer? i would like to order my query output the way Windows Explorer does.

First special characters like _, then the numbers, and the letters last.

_ttt.bmp
0.gif
bbb.png

Try setting your NLS_SORT to PUNCTUATION or XPUNCTUATION before running the query:

ALTER SESSION SET NLS_SORT = 'XPUNCTUATION';


Make MySQL server terminate when shell exits or on ctrl-c If I run mysqld, ie invoke it directly on the command-line, it survives the shell being closed or hitting ctrl-c.

Is there any way to run it "interactively", ie so that it will shut down if either of those things happen?

(I'd find this useful during development as I have a tmux script to quickly bring up and down various servers.)

You could try --gdb. That will make it respond to SIGINT, though I'm not sure if that has potential to corrupt the database. --one-thread may also be of some help.


Log shipping large DBs - what about the log? I'm currently setting up log shipping of a large DB (Approx 1.5TB) and am wondering what I can do about the log file.

As it stands, I want to do the following steps:

Change DB to FULL recovery
Take FULL backup (5-6 hours) on primary
Restore FULL backup to secondary (leaving in NORECOVERY)
Take DIFF backup on primary
Restore DIFF backup to secondary (still in NORECOVERY)
Initialize log shipping using 'The database is already initialized'

The issue is that while I am taking the full backup, the log file will fill up faster than the backup(s) can complete.

What options do I have to keep the log file from filling up?  Can I just take log backups as normal during the FULL backups as the DIFF restore will cover any transactions that take place during that timeframe?  Has anyone done this before with a DB of this size, any tips/tricks to make it easier?

If I understand you correctly, your major problem is the log files during the several hours of backup. From your opening statement I understand that the 1.5 TB database normally runs in SIMPLE recovery, and thus no log backups to do.

Disclaimer: I have never done log shipping on this scale.

Of course, you should ask whether you can get more space allocated for your log files.  If you can, then great.

However, I think a small modification of your plan, providing that you already run in SIMPLE recovery model and/or the risk of SIMPLE recovery model for a few hours is worth it, would ease some of your worries.

Keep (or set) DB in SIMPLE recovery model.
Take FULL backup (5-6 hours) on primary
Restore FULL backup to secondary (leaving in NORECOVERY)
Set DB in FULL recovery model
Take DIFF backup on primary
Restore DIFF backup to secondary (still in NORECOVERY)
Initialize log shipping using 'The database is already initialized'

The apparent advantages are:

No log files to backup during the large FULL backup.
Switching to FULL before beginning the DIFF backup will give you the needed log to start with and its longest growth is probably during the DIFF backup.

Regarding when a log backup can start:

https://technet.microsoft.com/en-US/library/ms190729(v=SQL.105).aspx

This says: "A new log chain starts either with the first full database backup following database creation or after switching from the simple recovery model to the full or bulk-logged recovery model."

So, I still believe that this will work as outlined. (Not identical, but I have used a differential backup to cover a gap when log files were lost, so as to establish a new origin for the log backups.)

(Remember my disclaimer, of course.)


Does SQL Server prioritize select and update statements? Assume that SQL Server received both select and update statements to the same table at the same time

Do any of them get prioritized? I know that select statements are delayed until update completed. If table lock continue for a long time due to update, select statement gets cancelled with too much waiting error

So what happens when both received at the same time?

There is no priority in SQL Server for commands which ever comes first would be executed first. There would always be nanosecond/microsecond difference, of course you would not be able to notice it.


How to assign CTRL+F1 to sp_whoisactive in ssms 2012? When I go to Tools > Options > Environment > Keyboard and
Type in Query.Sp_whoisactive
Press shortcut keys: Ctrl+F1
Click Assign.

When I press Ctrl+F1, It still calls Help.

Click on Tools > Options.  Then navigate to Environment > Keyboard > Query Shortcuts. In the stored procedure column next to Ctrl+F1, type Sp_whoisactive.

Next navigate to Tools > Options > Environment > Keyboard > Keyboard, and find the command Query.CustomSP1. Assign the shortcut Ctrl+F1. Then search for the command Help.ViewHelp and remove the mapping for Ctrl+F1 there.


How to sum a value in a grouped set of results? SQL Server So i need to sum the value "amount" where where debit_id and transfer_id are equals:
Table Example:

debit_id | transfer_id | amount | debit_type

1184685  |    861288   |  25.5  |     2

1184685  |    861288   |  59.5  |     2

1168516  |    861288   |   23   |     0

Right now i use this query thaht gives me almost the expected values

    WITH ct AS
  ( SELECT *,rn = ROW_NUMBER() OVER (PARTITION BY debit_id, transfer_id
   ORDER BY id DESC)
    FROM transfer_debits
  )
SELECT debit_id, transfer_id, amount, debit_type
FROM ct
WHERE rn = 1 AND transfer_id = 861288
ORDER BY debit_id, transfer_id;

I need this result but with the sum of the field amount grouped by debit_id and transfer_id
Desired result should be:

debit_id | transfer_id | amount | debit_type

1184685  |    861288   |   85   |     2

1168516  |    861288   |   23   |     0

Try something like this:

Select debit_id, transfer_id, sum(amount) as "Amount_Sum"
from Transfer_debits
where transfer_id = 861288
group by  debit_id, transfer_id

For the debit_type, if you want a different sum depending on its value, just add it to the select and the group by.


MariaDB - breaking up a multi join into CTEs So I have the following query:

SELECT
    i.mongodb_id
FROM
    display_list_lists dll
JOIN
    display_lists dl
    ON
        dll.display_list_id = dl._id
JOIN
    list_items li
    ON
    dll.list_id = li.list_id
JOIN
    items i
    ON
    li.item_id = i._id
WHERE
    dl.mongodb_id = ?
    AND
    dl.account_id = ?

since, I only need the display_lists._id in order to then find the other details, would it be better to somehow use CTEs instead?

Broken up, it would look like this:

SELECT
    _id
FROM
    display_lists dl
WHERE
    dl.mongodb_id = ?
    AND
    dl.account_id = ?;

SELECT
    *
FROM
    display_list_lists dll
WHERE
    dll.display_list_id = ?; // previous _id

SELECT
    *
FROM
    list_items li
WHERE
    li.list_id IN (?); // use list ids from previous results

SELECT
    *
FROM
    items i
WHERE
    i._id IN (?); // use item ids from previous results

Your 'join' version is plenty good.  For performance, you need these indexes (or PKs):

dl:  (mongodb_id, account_id,  -- in either order
      id)                      -- third
dll:  (display_list_id, list_id)  -- in this order
li:  (list_id)
i:  (id, mongodb_id)   -- unless (id) is the PRIMARY KEY

Index Cookbook


Best practices for providing PHI/PII data to users in organization I am looking for best practices for how to share data from a SQL Server database that contains PHI/PII to individuals that cannot view PHI/PII. In short, we maintain a SQL server database that contains 30+ columns of PHI/PII. We need to provide datasets for certain individuals that cannot access the PHI/PII columns, but can access the other fields to conduct different types of analyses.
Current structure: The database is 100GBs and is updated 4 times per day. All data resides on Azure SQL Server. The tables should maintain metadata so blob storage is not ideal (unless someone has an idea to maintain metadata in blob storage). The data will be accessed through PowerBI or Azure Databricks.
Several options come to mind:

Create a DB Role and deny access to PHI/PII columns
Create a new SQL server database and an ETL that copies non-PHI/non-PII data from one database to the new database
Create a new schema and then create views of the tables which do not contain PII/PHI columns and then restrict access to users for this schema only
Build an script/ leverage Azure Data Factory to copy data from the database to Azure table.

Security is number one priority. Any advice is much appreciated.

My first choice would be to use the traditional security mechanisms, which has been in place for decades. I.e., only grant permissions on what they should see.
If they are about to write their own queries, they might be annoyed if you grant some columns but not all (1), since SELECT * will choke on that. And, yes, some will do SELECT *, whatever we say. So, (3) gets my vote.
You might run into the Dynamic Data Masking track, and this might be useful if they aren't writing their own queries. Since you say that security is #1 priority, then you probably aren't OK with them being able to deduce info. Basically, the data "is there" through the WHERE clause but not SELECT list. I recently read an article where CROSS APPLY was used as a type of brute force data attack to get the SSN even though it was masked.


Can I see Historical Queries run on a SQL Server database? Someone was running a query on our SQL Server database remotely and their system crashed.

They have no backup of that query and want to see what was run on the server.

Is it possible to find this query in a log or in a history somewhere?

Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here:
Oh **********!

EDIT

To make this a bit more detail of an answer, the referenced linked above Grant provides a query to simply go to the cache on the instance to pull out the query you had just executed (or atleast attempt to):

SELECT  dest.text
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   deqs.last_execution_time > '5/19/2011 11:00'
        AND dest.text LIKE 'WITH%';

A few more options that were noted in the comments of Grant's blog:

Jamie Thomson points where SSMS actually has a "recovery" area under your Windows profile, similar to Word or Excel recovery.
Another individual notes in comments about the SSMS Tools Pack, but this add-on is only free for SQL Server 2008. Starting with SQL Server 2012 it is paid only, but has many features you might find useful.


Create a single-server replica set only for watching on change streams I have a MongoDB database which is of relatively low importance. I shovel data from home automation stuff into it (mostly sensor data, sunlight, switch states and so on) and don't need any replication.

I want to subscribe to change streams on that DB, and for that they must be members of a replica set. Is it OK to just add the --replSet [name] flag to the already existing server and then call rs.initiate() in it without rs.add()-ing any secondary members?

What drawbacks will this have (database size, performance, ...)?

Basically I want devices/software to insert data directly into the database, and then have some middleware watching on change streams to then publish the data via RabbitMQ.

Currently I'm inserting into RabbitMQ and MongoDB in parallel. Inserting into RabbitMQ and having a middleware insert the data into MongoDB is also an option, but I want to check both ways of doing it.

I'm not that much of a friend of replica sets as I have an important Database which holds about 80-90MB of documents (total size when exporting all collections to disc), but each member consumes around 1.5 GB of space (--logpath data excluded).

Is it OK to just add the --replSet [name] flag to the already existing server and then call rs.initiate() in it without rs.add()-ing any secondary members?

Yes, a single node replica set is fine if you don't require data redundancy or fault tolerance.

  What drawbacks will this have (database size, performance, ...)?

A replica set node maintains an operation log (oplog) which has some additional storage overhead. You can change the size of the oplog to suit your use case. Since you only need the oplog for change streams, it can perhaps be smaller than the default to save on disk space.

In MongoDB 3.6+ you can also adjust the size at runtime using the replSetResizeOplog  command. In earlier server versions the resize procedure is manual: Change the size of the oplog (MongoDB 3.4).

  I'm not that much of a friend of replica sets as I have an important Database which holds about 80-90MB of documents (total size when exporting all collections to disc), but each member consumes around 1.5 GB of space (--logpath data excluded).

Clarifying excessive storage usage is really a separate question, but this definitely seems an anomalous ratio.

Some likely causes:

Storage usage in the dbPath includes the replication oplog. The default oplog size is determined based on free disk space and O/S (for example, 5% of free disk space on Linux/Windows with a lower bound of 990MB and upper bound of 50GB).
You were using an older version of MongoDB (likely 3.2 or earlier) with the MMAPv1 storage engine. MMAP does not compress data on disk and some use cases will lead to storage fragmentation over time. Modern versions of MongoDB default to the WiredTiger storage engine which includes data & index compression and significant improvements over MMAPv1.


Unknown returned type in PostgreSQL query The following query works:

SELECT a, b
FROM unnest(ARRAY[(1,2), (3,4)])
AS t(a integer, b integer);

a b
_ _
1 2
3 2

However, I wasn't able to use a different column type such as varchar(255):

SELECT a, b
FROM unnest(ARRAY[(1,'hello'), (3,'world')])
AS t(a integer, b varchar(255));

ERROR:  42804: function return row and query-specified return row do not match
DETAIL:  Returned type unkown at ordinal position 2, but query expects text.

It seems that, in the second case, the column type is inferred as unknown, which is not cast to varchar(255) automatically.

How do I make the second example work and return columns with the right type, if possible without warnings and without modifying the ARRAY[...] definition?

Background: I am trying to improve performance of large bulk insert operations using the psycopg2 Python module, which does not support using multiple rows in VALUES arguments. I stumbled onto the above example while trying out some other methods.

You can do this without generating a warning by creating a type and casting the records to it:

create type t as (a integer, b varchar(255));

select * from unnest(array[(1,'hello'), (3,'world')]::t[]);
┌───┬───────┐
│ a │   b   │
├───┼───────┤
│ 1 │ hello │
│ 3 │ world │
└───┴───────┘

tested on 9.4 and 9.3 (db<>fiddle here)


Can't set root password after fresh installation of mariadb using ansible Problem:
After a fresh installation of mariadb 10.4 the root password could not be set using ansible.
When installing mariadb from the debian packages by mariadb (not the official debian packages), the password of root is empty and a passwordless login is possible from localhost.
Still, ansible mysql_user threw the following error:
failed: [XXX] (item=localhost) => {"ansible_loop_var": "item", "changed": false, "item": "localhost", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}

Solution:
We need to add login_unix_socket: /var/run/mysqld/mysqld.sock to the task:
- name: update mysql root password for root account
  mysql_user:
    name: root
    login_unix_socket: /var/run/mysqld/mysqld.sock
    host: 'localhost'
    password: '{{ mysql_root_password }}'
    priv: "*.*:ALL,GRANT"
    check_implicit_admin: true

Explanation:
The passwordless login is not possible when connecting through the network (this is what ansible does), only when connecting using the unix socket (the mysql CLI does that when connecting to localhost). So we need to force ansible to connect to the socket.
I hope this post saves somebody the time figuring that out.


Take query results and insert into new table So I have the following query:

DECLARE @tbl TABLE(value int)

*--The same function but with the number of the random numbers
;WITH Nbrs (n) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 6 )
INSERT INTO @tbl
(
    value
)
SELECT
    Nbrs.n AS Sequence
FROM Nbrs
;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 480000)
SELECT
    tblOrderRandomNumbers.[1] AS First,
    tblOrderRandomNumbers.[2] AS Second,
    tblOrderRandomNumbers.[3] AS Third,
    tblOrderRandomNumbers.[4] AS Fourth,
    tblOrderRandomNumbers.[5] AS Fifth,
    tblOrderRandomNumbers.[6] AS Sixth
FROM
    Nbrs
    --This cross join. Joins with the declared table
    CROSS JOIN
        (
            SELECT
                [1], [2], [3], [4], [5], [6]
            FROM
            (
            SELECT
                Random,
                ROW_NUMBER() OVER(ORDER BY tblRandom.Random ASC) AS RowNumber
            FROM
                (
                SELECT
                    (ABS(CHECKSUM(NewId())) % 49 + 1) AS Random
                FROM
                    @tbl AS tblNumbers
                ) AS tblRandom
            )AS tblSortedRadom
            --A pivot makes the rows to columns. Using the row index over order of the random number
            PIVOT
            (
                AVG(Random)
            FOR RowNumber IN ([1], [2], [3], [4],[5],[6])
            ) as pivottable
        ) AS tblOrderRandomNumbers
OPTION ( MAXRECURSION 0 )*

I would like to automatically insert these results into a table, anyone have some advice?

If you are trying to insert into a new table that has already been created, you can say:

    ;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 480000)

    INSERT dbo.ExistingTable(First,Second,Third,Fourth,Fifth,Sixth)
----^^^^^^ this line

    SELECT
     tblOrderRandomNumbers.[1] AS First,
     tblOrderRandomNumbers.[2] AS Second,
     tblOrderRandomNumbers.[3] AS Third,
     tblOrderRandomNumbers.[4] AS Fourth,
     tblOrderRandomNumbers.[5] AS Fifth,
     tblOrderRandomNumbers.[6] AS Sixth
    FROM
     Nbrs
    ...

If you trying to create a new table based on the output, you can say:

    ;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 480000)
    SELECT
     tblOrderRandomNumbers.[1] AS First,
     tblOrderRandomNumbers.[2] AS Second,
     tblOrderRandomNumbers.[3] AS Third,
     tblOrderRandomNumbers.[4] AS Fourth,
     tblOrderRandomNumbers.[5] AS Fifth,
     tblOrderRandomNumbers.[6] AS Sixth

    INTO dbo.NewTable
----^^^^^^ this line

    FROM
     Nbrs
    ...


Trouble setting up Postgres for the first time I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself.

Postgres is installed and running. But I cannot connect from a client.

pg_hba.conf looks like this (I added the last line):

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

## Added by me
host    all             all             10.0.1.201/32           trust

Now if I go to the client 10.0.1.201 and try to psql -h server.domain.com I get an error:

psql: could not connect to server: Connection refused
    Is the server running on host "server.domain.com" (10.0.1.38) and accepting
    TCP/IP connections on port 5432?

ssh authorized_keys are setup for this client <-> server connection. There is also Kerberos as an option. Seems I might have too many options here? Not quite sure how to troubleshoot this? I can verify Postgres IS running.

meltemi$ sudo lsof -i -P | grep -i ':5432'
meltemi$

leads me to believe maybe it's not listening?

ideas?

The next thing to check is probably that you have a line in your postgresql.conf that looks like this:

listen_addresses = '*'

You will have to restart after changing that line. Otherwise your pg_hba.conf looks fine.

Good luck! Getting started is a big hurdle, but once you get through it, you will probably find that compared to MySQL things are a lot simpler and more consistent.


Oracle 11gR2 - Limits on Materialised View replication between databases We are about to deploy a 3rd party product running on Oracle 11gR2. The product allows very generic text searches. We'd like to mitigate the performance impact of the searches.

Our current design is to create an OLTP database holding 32 days of data and a archive/reporting one holding up to 7 years. Then, we replicate data between them every 24 hours using Materialized Views without applying deletes. This isolates the impact of searches.

However, our database team has raised concerns that MV's are not the right solution as the 3rd party product creates 4700 tables and we'll be replicating approx. 0.5GB data per day.

Any view on the validity of the concerns?

(we are currently looking at alternatives - no archive, 7 years data in OLTP and partitioning to lessen the impact of search)

Thanks in Advance,
Matt

This sounds like a job for Change Data Capture (CDC), which allows you to (among other possibilities) ship your archivelogs from the OLTP database to the reporting one, mine them for the changes, then query the changes out, ignoring any you don't want (e.g., changes of type 'D' for DELETE), and using whatever process you might devise apply those changes to your reporting tables.

I have no idea how well CDC would do with a ruleset encompassing 4700 source tables from another database. I've never used it for more than about 50 tables myself.

FYI, there are licensing-related limits on CDC. The full feature set is only available on Enterprise Edition.


ssrs how to make the report available to my local machine I have been struggling in Configuring Report Server URLs for a certain reporting services server.

when I am in the server itself, the url works fine as you can see on the picture below.

but trying to access it from my local machine via internet explorer I get the following:

this has all been set up by a consultant and I am not sure if it is all right.

what do I have to do to make these reports on this particular server available to my users?

or at least, is there a default procedure to start troubleshooting?

Below is the view of my reporting services configuration manager.

the service account

web server URL

database

report manager URL

Firstly, the URLs referenced in the screenshots aren't the same: http://server/Reports_GP vs http://server/reportserver. In the comments, you clarified that you've double-checked on URLs, and confirmed that it still isn't working.  On to the next step!

The error indicates that your browser session isn't connecting to the service itself--however, since you're able to run the browser on the server itself and connect successfully, we know the service is listening. If you check the SSRS logs (Depending on your instance root directory, they will be somewhere like: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles), you should be able to confirm that the SSRS service isn't receiving any connection.

This indicates it is likely a networking / firewall / connectivity issue of some type. Is HTTP traffic being allowed through all firewalls (physical & software) between your desktop & the server? Most likely this issue is not a problem with SSRS, but rather a problem reaching SSRS.


Get previous field's value in MySQL trigger I am using a MySQL database and I have this kind of table.

IDRefHandler
1 x    299
2 x    300
3 x    301

But I want to create a trigger (before insert) which will set automaticaly the Handler field. How to select the previous Handler value and just add one with a trigger ?

Is there any better way that storing the result of SELECT Handler FROM table order by Handler DESC LIMIT 1 and storing this in a VAR.
Is'nt it more efficient to use a magic word like OLD ? Or do I have to use a specific trick for this case?

PS : OLD is not usable in this case (source)

You are treating Handler like an AUTO_INCREMENT.  Why not get rid of ID?  Or use it instead of having an extra column?

Edit

Since you want to "reset to 0" the number at the start of the year...  Assuming this is what you want, then converting it to a TRIGGER should work:

START TRANSACTION;  -- To keep other connections from overlapping
SELECT @year := ...,
       @next := MAX(Handler)+1
    FROM ...;
IF (YEAR(now() > @year) THEN
    SET @next := 0;
ENDIF
INSERT ... @next ...;


Postgresql 9.1 PITR file system base backup - do I need to stop database? I am preparing for a migration of a big database to another server and plan to use WAL archiving and Point-In-Time-Recovery to minimize downtime. After reading the documentation I learned I have to do full filesystem-level base backup since the point of WAL archiving started.

In http://www.postgresql.org/docs/9.1/static/backup-file.html, it is stated that:

  The database server must be shut down in order to get a usable backup.
  Half-way measures such as disallowing all connections will not work
  (in part because tar and similar tools do not take an atomic snapshot
  of the state of the file system, but also because of internal
  buffering within the server). Information about stopping the server
  can be found in Section 17.5. Needless to say, you also need to shut
  down the server before restoring the data.

Next, in the PITR section it is said the source server does not have to be shutdown if pg_start_backup() was run before.

http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

  Perform the backup, using any convenient file-system-backup tool such
  as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary
  nor desirable to stop normal operation of the database while you do
  this.

Also this caught my eye:

  Some file system backup tools emit warnings or errors if the files
  they are trying to copy change while the copy proceeds. When taking a
  base backup of an active database, this situation is normal and not an
  error

If the database binary files are changing while performing the backup and this is allowed, how is the file format consistency guaranteed and can I be sure I won't end up with corrupted files?

So my question is, do I or do I not need to stop the source server when performing the full file system backup if steps in PITR section of documentation are followed? And how is it possible the base backup is correct if the files are allowed to change during it? Don't I end up with partial overwrites or something like that? My guess would be that any such damage can be repaired by restoring WAL files? Is this correct?

I am probably just asking what was already said in the documentation, but I need to be sure and I am a bit jumpy about this migration process.

Thank you.

If you have archiving of WAL files already on, there should be no need to restart the primary db server.. If WAL archiving is NOT already turned on, then you might have to restart the server to have the configuration change be active. (Looking at the documentation, I believe issuing a SELECT PG_RELOAD_CONF(); is good enough for archiving .. but the postgresql.conf file may specifically say "changing this setting requires a restart")

The only other requirement is that you issue a pg_start_backup('label') and pg_stop_backup() (neither of which require a db service interruption).

The PITR backup will be valid STARTING from the point of your backup..  (As in, you will not be able to recover to a point BEFORE you issue pg_start_backup unless you have another backup from earlier). Start the backup .. perform a file-level backup of all db files .. stop the backup .. perform a file-level backup of the pg_xlog files ... you're done (with the backup process).

To restore, you need to restore all the files you just backed up (plus any additional WAL files that were generated since you ran the backup) and create a recovery.conf file before starting the server.. I won't go through all the details, as you are obviously already looking through the documentation on it.  :)


How to link books of series together? I have to link all the books in series together but I'm not sure.

I have a SERIES_ID under which there is a name for a series, author, number of books and number book in the series.

I'm not sure how to have a list of all the other books in a series or at least previous and next book.

So if I had a copy of the 2nd book in a trilogy the first and third book would appear.

Any advice?

Your series table is compound. You need to split it up.

Series ( series_id, name )
Series_Book ( book_id, series_id, series_ordinal )

Now you can easily seek to the series_ordinal-1, series_oridnal+1 for the series. Storing the count() as number of books also seems redundant.

Genre and Theme are also pretty nebulous. That method of classification doesn't scale well, or work all that well with humans. Normally you want hierarchical tags, and cloud tags; otherwise, enter "is the Beatles pop or rock?"


SQL Server Transaction Log Initial Data Load What is the standard process when initially populating a database with 5GB+ to avoid a large transaction log? From my understanding the log file would jump to a large size after the initial load and not decrease in disk space from that point on. Regular transaction log backups would free up space within the file, but the file would remain large.

Is it advisable then to set the target database to simple backup, load the data, use CHECKPOINT to truncate log, and then set the database to full backup mode?

Additionally, I know there is a shrink file operation that can be performed on the log, but some say never to use it, others seem to recommend it as a solution. Any thoughts?

Use bulk operations - the bcp command-line utility or BULK INSERT commands, they can generate much smaller transaction logs and they are way way faster. See this for details about the transaction log generation.

(I am answering for Microsoft SQL Server, but equivalent utilities and command are available for all databases)