Swap Columns on some rows? I have a table of about 106k rows where about 11k rows have the values in 2 columns swapped. I want to run a query to fix it, but I don't think I can do this:

UPDATE `game`
SET `homescore`=`awayscore`, `awayscore`=`homescore`
WHERE (`awayscore`>`homescore` AND `winner`=`hometeam`) OR (`awayscore`<`homescore` AND `winner`=`awayteam`);

Or can I? I'm worried the 2 scores will end up the same value.

Also, please validate that my query will do what I intend to do:

I need to swap the home and away scores on the rows where the recorded winner (which is correct) doesn't match the scores recorded (they were accidentally swapped by a coding mistake now fixed) saying who the winner is (team with more points).

In any decent RDBMS (e.g Postgres, SQL-Server, Oracle, DB2), the simple approach would work fine and swap the values of the two columns:

UPDATE game
SET homescore = awayscore,
    awayscore = homescore
WHERE (awayscore > homescore AND winner = hometeam)
   OR (awayscore < homescore AND winner = awayteam) ;

In MySQL, it will not work as expected! You'll have to use some kind of trick, like this one:

UPDATE game
SET homescore = (@tmp := homescore),
    homescore = awayscore,
    awayscore = @tmp
WHERE (awayscore > homescore AND winner = hometeam)
   OR (awayscore < homescore AND winner = awayteam) ;

You could also add one or two more columns in the table, use them for swapping and then drop them but it won't be very efficient. A better approach would be to use another (temporary) table:

CREATE TABLE temp_swap
( pk INT NOT NULL         -- whatever datatype the Primary Key of your table is
, homescore INT           -- INT or FLOAT or DECIMAL
, awayscore INT           -- whatever the two columns are
, PRIMARY KEY (pk)
) ;

Copy the values to be swapped there:

INSERT INTO temp_swap
  (pk, homescore, awayscore)
SELECT pk, homescore, awayscore
FROM game
WHERE (awayscore > homescore AND winner = hometeam)
   OR (awayscore < homescore AND winner = awayteam) ;

And then back, swapped, into the table:

UPDATE game AS g
  JOIN temp_swap AS t
    ON t.pk = g.pk
SET g.homescore = t.awayscore,
    g.awayscore = t.homescore ;

DROP TABLE temp_swap ;


How to update final associated record I have list of items that are belongs to orders. Each item has fee_percentage field. and the sum of items is total_price in orders table.

What I want to achieve?
I want to update the fee field of last associated items.

_________________________      __________________________________________
| order ID | total_price|      | id | order_id | fee_percentage |  fee  |
-------------------------      ------------------------------------------
|   334    |     425    |      |  1 |   334    |      50        |  212  |
  # The `fee` should be 213 => |  2 |   334    |      50        |  212  |

As you can see the sum of items will be 424. I want to add the remaining fee to the last item fee. (So the fee of item id 2 should be 213).

Any help please?

This is what I have written so far

UPDATE items AS i
LEFT JOIN orders AS o ON o.order_id = b.order_id
LEFT JOIN (
    SELECT order_id, SUM(fee) AS sum_fee
    FROM items
    GROUP BY order_id
    WHERE deleted = 0
) AS b1 ON b1.order_id = b.order_id
SET b.fee = b.fee + (o.total_price - b1.sum_fee)
WHERE b.deleted = 0;

Check it here: http://rextester.com/MGKS16637

I'm getting max(Id) and difference between sum(fee) and total price from items table. If this value is <> 0, then update the item with id=max(id).

update items
       join (select max(i.id) mid, o.totalPrice - sum(i.fee) as diff
           from items i
           inner join orders o on i.order_Id = o.orderId
           group by i.order_id
           order by i.order_id, i.id) v
       on items.id = v.mid
set items.fee = items.fee + v.diff
where v.diff <> 0;


Availability Group Endpoints Windows Authentication Error I have a two-node SQL Server 2016 Standard Always-On environment and when I create a new availability group, I receive the error message below:

  The Endpoints tab lists at least one endpoint that uses only Windows Authentication. However, the server instance might be running under a nondomain account. To use the listed endpoint, change the corresponding SQL Server service account to a domain account. To continue using the nondomain account, alter the endpoint to use a certificate.

  Do you want to use the listed endpoints?

Now there is a fair amount of information to correct this, but I am struggling to find a fix for my scenario.
All im finding, is when you are running SQL Server and the endpoints as a non-domain user, as the message suggests.

I am running both instances as the same domain user, which has admin on both nodes and sysadmin in each instance of SQL Server, and this can be seen on the endpoints tab, both endpoint SQL Service account entries are the Domain\svc_account that is running SQL Server.

The Availability groups get created perfectly, i occasionally have do grant CREATE DB to the secondary node to initialize the seeding, but other than that all is great. Failovers are perfect etc. We do have over 20 AG's. Could that be an issue? I believe AG have been tested successfully at up to 100 groups. Being SQL Server Standard, we can only do 1 DB per group, and most of the DBs are small at <5GB.

So I am assuming its not causing any issues yet, but regardless I would like to know if its something I can ignore, or fix, in my scenario.

The output of Node 1:

Node 2:

Thank you for all your assistance.

So i am assuming its not causing any issues yet, but regardless i would like to know if its something i can ignore in my scenario, or fix.

Looks like since everything seems to be configured properly (I can't see the domain account information [which is redacted as it should be :) ]) so I wouldn't worry about it. If this happens on multiple servers in the environment then it may require further investigation but overall everything you posted seems in line with a normal configured system.

I traced this down to a call IsValidDomainUserForWinAuthentication used in SSMS which, for whatever reason, is returning false. Looking at the logic, it's checking a few various items, but the one that stands out is the assumption on how the service account name is configured (allowable characters) and that calls to LookupAccountName complete successfully. It would take a time travel trace or full memory dump of SSMS (when the message box is shown) to really investigate further which I don't believe is warranted at this time.


Turn off Error AND Usage Reporting SQL Server Unfortunately, SQL Server usage internet bandwidth and us activity in SQL, for R&D. You can find in your server, this service is called: SQL Server error and usage.

This service is active without any privacy permission, in installation process.

If you don't like this future can be turn off this activity. Like below:

But, I have a problem in SQL Server 2019: we use two SQL instances on one server - both SQL Server 2017 and SQL Server 2019.

I can turn-off SQL Server 2017 error and usage reporting service as shown in the screenshots, but when I open error and usage reporting service for SQL Server 2019 in a cmd window, and then close automatically and don't show anything:

How to turn off error and usage reporting SQL Server 2019 now?

There are registry keys you can edit, see Configure usage and diagnostic data collection for SQL Server (CEIP). Alternatively, there's a thread over on Microsoft's Azure forums that suggests this behavior is caused by some sort of misconfiguration in the C:\Program Files\Microsoft SQL Server\150\Shared\SqlWtsn.exe.config file and either renaming that file to SqlWtsn.exe.config.OLD or editing the contents of the file make fix the issue. I can say that the few times I've encountered this issue, renaming SqlWtsn.exe.config worked for me.


How can I get performance using PostgreSQL CTE recursive? I did a tree structure using id and parent_id in the same table. For query's I'm using CTE provide by PostgreSQL, but it's spend so much time to do the joins with recursive results. For example, by the time I have 100 records on sadt_lot table, and this query is spend 8 seconds to return the results. Someone have a better idea to do it?

Example: Listing all sadt's grouped by root sadt_lot's

EXPLAIN ANALYZE WITH RECURSIVE downlots as (
        SELECT sl1.sadt_lot_id, 0 AS level, sl1.sadt_lot_id as root_id
        FROM sadt_lot sl1
        WHERE sl1.parent_lot_id IS NULL
    UNION
        SELECT sl2.sadt_lot_id, d.level + 1, d.sadt_lot_id as root_id
        FROM sadt_lot sl2
        INNER JOIN downlots d ON d.sadt_lot_id = sl2.parent_lot_id
)
SELECT sl.sadt_lot_id, array_agg(s.sadt_id)
FROM sadt_lot sl
LEFT JOIN sadt s ON s.sadt_lot_id = any(SELECT sadt_lot_id FROM downlots WHERE root_id = sl.sadt_lot_id)
WHERE sl.parent_lot_id IS NULL
group by sl.sadt_lot_id
ORDEr By sl.sadt_lot_id

Query Plan

GroupAggregate  (cost=42.53..15077.74 rows=1 width=36) (actual time=104.090..8436.505 rows=90 loops=1)
  Group Key: sl.sadt_lot_id
  CTE downlots
    ->  Recursive Union  (cost=0.00..42.39 rows=101 width=12) (actual time=0.006..0.104 rows=95 loops=1)
          ->  Seq Scan on sadt_lot sl1  (cost=0.00..2.94 rows=1 width=12) (actual time=0.005..0.019 rows=90 loops=1)
                Filter: (parent_lot_id IS NULL)
                Rows Removed by Filter: 5
          ->  Hash Join  (cost=0.33..3.74 rows=10 width=12) (actual time=0.027..0.028 rows=2 loops=2)
                Hash Cond: (sl2.parent_lot_id = d.sadt_lot_id)
                ->  Seq Scan on sadt_lot sl2  (cost=0.00..2.94 rows=94 width=8) (actual time=0.002..0.008 rows=95 loops=2)
                ->  Hash  (cost=0.20..0.20 rows=10 width=8) (actual time=0.010..0.010 rows=48 loops=2)
                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                      ->  WorkTable Scan on downlots d  (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.004 rows=48 loops=2)
  ->  Nested Loop Left Join  (cost=0.14..15004.14 rows=6242 width=8) (actual time=8.234..8434.229 rows=11345 loops=1)
        Join Filter: (SubPlan 2)
        Rows Removed by Join Filter: 1112125
        ->  Index Only Scan using sadt_lot_sadt_lot_id_parent_lot_id_idx on sadt_lot sl  (cost=0.14..12.86 rows=1 width=4) (actual time=0.011..0.252 rows=90 loops=1)
              Index Cond: (parent_lot_id IS NULL)
              Heap Fetches: 90
        ->  Seq Scan on sadt s  (cost=0.00..635.83 rows=12483 width=8) (actual time=0.002..1.785 rows=12483 loops=90)
        SubPlan 2
          ->  CTE Scan on downlots  (cost=0.00..2.27 rows=1 width=4) (actual time=0.003..0.007 rows=1 loops=1123470)
                Filter: (root_id = sl.sadt_lot_id)
                Rows Removed by Filter: 94
Planning time: 0.203 ms
Execution time: 8436.598 ms

I found the solution. I was using the recursive expression how parameter to join and it was do several lops on the table used on join, a better aprouch is before join with this table(sadt), do the join with recursive expression(downlots "table") and after, using result, do join with the sadt, with that, the query jump from 8sec to 8ms.
Follow the solution:

EXPLAIN ANALYZE SELECT sl.sadt_lot_id, array_agg(s.sadt_id)
FROM sadt_lot sl
LEFT JOIN (WITH RECURSIVE downlots as (
        SELECT sl1.sadt_lot_id, 0 AS level, sl1.sadt_lot_id as root_id
        FROM sadt_lot sl1
        WHERE sl1.parent_lot_id IS NULL
    UNION
        SELECT sl2.sadt_lot_id, d.level + 1, d.sadt_lot_id as root_id
        FROM sadt_lot sl2
        INNER JOIN downlots d ON d.sadt_lot_id = sl2.parent_lot_id
)SELECT * FROM downlots) d ON d.sadt_lot_id = sl.sadt_lot_id
LEFT JOIN sadt s ON s.sadt_lot_id = d.root_id
WHERE sl.parent_lot_id IS NULL
group by sl.sadt_lot_id
ORDEr By sl.sadt_lot_id

Query Plan

Sort  (cost=1935.35..1935.56 rows=82 width=36) (actual time=8.230..8.234 rows=82 loops=1)
Sort Key: sl.sadt_lot_id
Sort Method: quicksort  Memory: 75kB
->  HashAggregate  (cost=1931.72..1932.74 rows=82 width=36) (actual time=8.085..8.197 rows=82 loops=1)
    Group Key: sl.sadt_lot_id
    ->  Hash Right Join  (cost=469.73..1839.25 rows=18493 width=8) (actual time=0.328..6.273 rows=10742 loops=1)
          Hash Cond: (s.sadt_lot_id = downlots.root_id)
          ->  Seq Scan on sadt s  (cost=0.00..645.78 rows=12678 width=8) (actual time=0.007..1.406 rows=12493 loops=1)
          ->  Hash  (cost=465.72..465.72 rows=321 width=8) (actual time=0.242..0.242 rows=82 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 12kB
                ->  Hash Right Join  (cost=432.42..465.72 rows=321 width=8) (actual time=0.049..0.232 rows=82 loops=1)
                      Hash Cond: (downlots.sadt_lot_id = sl.sadt_lot_id)
                      ->  CTE Scan on downlots  (cost=428.41..444.05 rows=782 width=12) (actual time=0.007..0.167 rows=96 loops=1)
                            CTE downlots
                              ->  Recursive Union  (cost=0.00..428.41 rows=782 width=12) (actual time=0.006..0.143 rows=96 loops=1)
                                    ->  Seq Scan on sadt_lot sl1  (cost=0.00..2.99 rows=82 width=12) (actual time=0.004..0.018 rows=82 loops=1)
                                          Filter: (parent_lot_id IS NULL)
                                          Rows Removed by Filter: 14
                                    ->  Hash Join  (cost=4.23..40.98 rows=70 width=12) (actual time=0.030..0.031 rows=5 loops=3)
                                          Hash Cond: (d.sadt_lot_id = sl2.parent_lot_id)
                                          ->  WorkTable Scan on downlots d  (cost=0.00..16.40 rows=820 width=8) (actual time=0.000..0.002 rows=32 loops=3)
                                          ->  Hash  (cost=2.99..2.99 rows=99 width=8) (actual time=0.069..0.069 rows=14 loops=1)
                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                ->  Seq Scan on sadt_lot sl2  (cost=0.00..2.99 rows=99 width=8) (actual time=0.004..0.061 rows=96 loops=1)
                      ->  Hash  (cost=2.99..2.99 rows=82 width=4) (actual time=0.039..0.039 rows=82 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 11kB
                            ->  Seq Scan on sadt_lot sl  (cost=0.00..2.99 rows=82 width=4) (actual time=0.014..0.028 rows=82 loops=1)
                                  Filter: (parent_lot_id IS NULL)
                                  Rows Removed by Filter: 14
Planning time: 0.225 ms
Execution time: 8.300 ms


Left join SQL Select Statement Good day,
Im trying to find a good way on my select statement

Table1
AccountID      AccountName    Rate  OldRate
01             Smith          X     X2
02             Providence     X     X2
03             Jones          X     X3
04             Charity        X2    X
05             Smith          X3    X
06             Providence     X     X2
07             SMith          X     X3
08             Charity        X     X3

Table 2
Rate          Amount
X             500.00
X2            200.00
X3            30.00

Result

AccountID      AccountName    Rate  Amount  OldRate Amount
01             Smith          X     500.00  X2      200.00
02             Providence     X     500.00  X2      200.00
03             Jones          X     500.00  X3       30.00

You can try the following Command and make a left join on rate between both tables to get results as shown in your example

SELECT t1.AccountID, t1.AccountName, t1.Rate, t21.Amount, t1.OldRate, t22.Amount
FROM table1 t1
/* LEFT */ JOIN table2 t21 ON t1.Rate = t21.rate
/* LEFT */ JOIN table2 t22 ON t1.OldRate = t22.rate


SSIS 2008 painfully slow to import CSV I am using SSIS to import a 25,000 row CSV to an empty table.  The table has a primary key that is populated as the data is inserted.  I am using an ADO.Net destination.

What reasons are there why this operation would take around 5 minutes?  At the default value of DefaultBufferMaxRows (10,000), the import fails completely with a timeout after around 5,000 rows.  I dropped the value to 1,000 and now it gets there eventually with no timeout, but takes a full five minutes.

The Flat File data source has explicit column widths set, could that be the issue?

The 'answer' was that the ADO.Net providers in SSIS are basically useless and an embarrassment to the '.Net' moniker.  They flake at more than a few thousand rows and are so hobbled as to be effectively useless.  Basically, just stick to the old OLEDB providers and everything will be fine.


Can I alter an HSTORE column to JSON datatype? Is there a way to modify an existing column of HSTORE datatype (that has data) to JSON datatype in a Postgres database?

You can simple use hstore_to_json function to get a json out of a hstore. To change the table you can use the ALTER TABLE command.

Example:

ALTER TABLE table_name
    ALTER your_column_name TYPE json USING hstore_to_json(your_column_name);

There is hstore_to_jsonb too if you want jsonb instead of json.


Do Triggers in the Active Secondary Database of an AlwaysOn Group Execute? If I have triggers on tables in a database that is a member of an AlwaysOn group, will they fire on the secondary? If they do fire, and they do something different on the secondary will it matter as long as it's not writing to the secondary db? For example, something external to the db, like call a service broker service, or insert into an external db if it and the table exists, will that succeed on the active secondary database?

Damien

The secondary is readonly.  Triggers can't fire on the tables in the secondary because there are no ON SELECT triggers and DML triggers cannot be executed on a readonly database without a successful DML operation (update, insert, delete).

Updates made on the primary and then moved to the secondary do not fire triggers on the secondary.  Instead, data changes made by the triggers on the primary are propagated to the secondary in the same manner as the direct changes made to the table.


mysqlimport imports NULL values unwantedly as int 0 I would like to understand how to create a decent back-up file by using mysql and mysqlimport. I know that I can use mysqldump and mysql, but I'm learning to use the combination: mysql and mysqlimport.

I'm on mysql5.7.21 in Windows 10.
I have test table: aircrafts that looks like this:

I would like to export this table to a textfile trough the mysql client tool.
So I do this:

mysql -h localhost -u root -D julian -p -e "SELECT * FROM `aircrafts` " > aircrafts.txt

This creates the back-up file, and content of it is:

id  countryFactoryID    name    type    price
1   1   Cessna 172 Skyhawk  GA  60000
2   43  Diamond DA40    GA  NULL
3   1   Cirrus SR20 GA  NULL
4   1   Piper PA-28 Cherokee    GA  NULL
5   1   Beechcraft A23 Musketeer    GA  NULL
6   1   Grumman American AA-5 Traveller GA  NULL

What I noticed about the back-up file is that it uses NULL instead of \N.
But that's ok I guess.

Next, I import the back-up file like this:

mysqlimport --local -l -v --ignore-lines=1 -p -u root myDB aircrafts.txt

The output in CLI is:

Connecting to localhost
Selecting database myDB
Locking tables for write
Loading data from LOCAL file: aircrafts.txt into aircrafts
myDB.aircrafts: Records: 6  Deleted: 0  Skipped: 0  Warnings: 6
Disconnecting from localhost

Ok, so far so good.

When I look in phpmyadmin, then I see this:

As you can see, the price is 0, but I would like to have a NULL-value, like in the first image. It's strange because my default value for column price is NULL.

What am I doing wrong?

This issue is caused by the way MySQL LOAD DATA handles NULLs:

  Handling of NULL values varies according to the FIELDS and LINES
  options in use:

  For the default FIELDS and LINES values, NULL is written as a field
  value of \N for output, and a field value of \N is read as NULL for
  input (assuming that the ESCAPED BY character is ).

  If FIELDS ENCLOSED BY is not empty, a field containing the literal
  word NULL as its value is read as a NULL value. This differs from the
  word NULL enclosed within FIELDS ENCLOSED BY characters, which is read
  as the string 'NULL'.

  If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

  With fixed-row format (which is used when FIELDS TERMINATED BY and
  FIELDS ENCLOSED BY are both empty), NULL is written as an empty
  string. This causes both NULL values and empty strings in the table to
  be indistinguishable when written to the file because both are written
  as empty strings. If you need to be able to tell the two apart when
  reading the file back in, you should not use fixed-row format.

In your case NULL is considered as word 'NULL' and if you try to cast 'NULL' to number it will return 0. You need to change NULL to \N or enclose strings using some character.


Use psql --set variable in a function I want to create a script that will create some custom named schema and within it some tables and functions. Like this:

The example.sh file:

#!/bin/bash

# this is the only place I want to set the schema name
SCH="ex"

export SCH

export PGPASSWORD="*******"

PSQL="psql \
  -X \
  -U postgres \
  -h localhost \
  --single-transaction \
  --echo-all \
  --set SCH=$SCH \
  --set ON_ERROR_STOP=on "

eval $PSQL "-f ./example.sql"

The example.sql file:

DROP SCHEMA IF EXISTS :SCH CASCADE;
CREATE SCHEMA :SCH;

CREATE TABLE :SCH.my_table
(
  id SERIAL PRIMARY KEY,
  my_col text
);

INSERT INTO :SCH.my_table (my_col) VALUES ('abc'), ('def');

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

SELECT * FROM :SCH.getLast();

It works fine until the:

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

As the function body is a text constant, the :SCH is not substituted by the schema name and we get an error:

psql:./example.sql:16: ERROR:  syntax error at or near ":"
LINE 3: SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;

Is there a neat way to make it work?

Workaround

This can be done in the following way, but it seems there should be an easier way:

CREATE OR REPLACE FUNCTION :SCH.makeFunction(schema_name text)
RETURNS VOID AS $body$
BEGIN
    EXECUTE format('CREATE OR REPLACE FUNCTION %1$s.getLast()
    RETURNS text AS $$
    SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
    $$ LANGUAGE sql STABLE;', schema_name);
END
$body$ LANGUAGE plpgsql VOLATILE;

SELECT :SCH.makeFunction(:'SCH');

An interesting way of achieving this is using a psql variable to store the whole function body, like

\set body '$$SELECT 1$$'

CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS :c;

SELECT bla();
 bla
─────
   1

Alternatively, you can pass the whole definition into the variable, and then run it:

\set function 'CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS $$SELECT 1;$$;'

:function

SELECT bla();
 bla
─────
   1

So, the question is how to put the body into a suitable variable.

From psql 9.3 above, there is the \gset command, that comes to the rescue.  We build a query that produces the function body as an output, then assign it to a psql variable, and use it like above (with format()):

SELECT format('CREATE OR REPLACE FUNCTION %1$s.getLast()
    RETURNS text AS $$
    SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
    $$ LANGUAGE sql STABLE;', 'test') AS function;

\gset

:function

And done.


Use last inserted ID in another table I am inserting into two tables at the same time, tss_client and tss_details.
First, I insert into tss_details, then using Details_ID from there, I insert into tss_client.

How to I get the most recent ID, from tss_details?
I'm trying LAST_INSERT_ID() but it doest work

 addDetailsQry.prepare("INSERT INTO tss_details(Username, Password, Email, Description, etc..) "
                      "VALUES (:Username, :Password, :Email, :Description, etc..");

 addClientQry.prepare("INSERT INTO tss_client(Client_Name, Website, Contact_Details_ID)"
                     "VALUES (:Client_Name, :Website, :LAST_INSERT_ID()");

addDetailsQry.bindValue(":Username", username);
addDetailsQry.bindValue(":Password", password);
addDetailsQry.bindValue(":Email", email);
addDetailsQry.bindValue(":Description", description);

addClientQry.bindValue(":Client_Name", clientName);
addClientQry.bindValue(":VAT_Number", VATNumber);
addClientQry.bindValue(":Website", website);
addClientQry.bindValue(":Contact_Details_ID", /*what would i put here*/ );

if(addDetailsQry.exec())
{
    qDebug() << "Client Details added!";

    if(addClientQry.exec())
    {
        qDebug() << "Client added!";
    }
}

The ID column in tss_details is an identity.

I'm using ODBC and SQL Server version:

  'Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3  (Build 14393: )

and  I get:

  [Microsoft][ODBC SQL Server Driver][SQL Server]'LAST_INSERT_ID' is not a recognized built-in function name.

Depends on the SQL implementation, I don't know any general solution.

Here someone asked for the implementation of this in a Microsoft SQL Server:
https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id

PostgreSQL:

currval('SEQUENCE_IDENTIFIER'::regclass)

In PostgreSQL, the IDs come from "SEQUENCES", and you could use the following as documented in https://www.postgresql.org/docs/8.2/static/functions-sequence.html . Note that this only works if "nextval" has been called in this session, which is also mentioned in the link.

MySQL: (https://stackoverflow.com/questions/3837990/last-insert-id-mysql)

LAST_INSERT_ID()

Suit yourself.


Download SQL Server Developer Edition I was planning to download a trial of SQL Server Developer today from MSDN Subscriber Downloads. I have two questions:

Next to each download it says: "No product key is required".  I assume this means that a 28 day trial is initiated after download?
What is the difference between SQL Server Developer; SQL Server Web Edition and SQL Server Business Intelligence? I assume that SQL Developer can do everything that SQL Server Business Intelligence and SQL Server Web Edition can do?

I followed these steps:

Went to the appropriate MSDN Subscriber Download page
Selected: SQL Server 2014 with SP1 x64
Logged In
Saved the file locally
During the installation select "Evaluation" rather than the license key.

To my surprise there was already a license key populated.  I have an active Bizspark account (though I did not renew in year three). It must be this account that has populated the license key.  Why is Enterprise Edition evaluation installed rather than developer edition?

Current information
SQL Server Developer Edition is a free download starting with SQL Server 2014.
You can download SQL Server 2017 Developer Edition for Windows, Linux, and Docker from the Microsoft SQL Server downloads page.

SQL Server 2017 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.

Original answer
We can't advise on detailed licensing issues, that is something you should read about on the MSDN Subscriptions site, and contact support if necessary. With that said, I can respond to other points in your question:

Next to each download it says: "No product key is required". I assume this means that a 28 day trial is initiated after download?

You have a choice here. During installation, you are presented with a screen like:

If you choose the pre-populated product key, you are installing Developer Edition (not a trial) under the MSDN Subscriber licence terms. No product key is required for this option, because one is provided for you.

If you choose Evaluation, you are installing a time-limited trial of Enterprise Edition, with different licensing terms. This is just the same as if you had download Trial Edition from anywhere.

What is the difference between SQL Server Developer; SQL Server Web Edition and SQL Server Business Intelligence? I assume that SQL Developer can do everything that SQL Server Business Intelligence and SQL Server Web Edition can do?

SQL Server Developer contains all the features of SQL Server Enterprise Edition, just on different licensing terms. You can compare the various editions here:
SQL Server Editions
You cannot download software from MSDN Subscriber Downloads without a Microsoft account associated with a valid subscription. As it says on the Using Subscriber Downloads help page:

You must be signed in to your Microsoft account that is associated with your subscription in order to download products on Subscriber Downloads. If you are not signed in, you'll be prompted to sign in when you attempt to download a product or view product keys. Everyone, including non-subscribers, can view Subscriber Downloads, but only active subscribers can download products and keys.


Using MySQL binary logs to keep backups between database dumps? I currently take a full database backup using mysqldump once a day. I'd like to make this more granular (e.g. every 15 minutes), but can't just take more full dumps due to size constraints. Can I use mysqlbinlog to only get the changes since the last mysqldump?

How do I only get the binary logs since the exact time of the last mysqldump? If another transaction started before that time and finished after it, will it still be included properly in the log?

Keeping compressed archives of binlogs is a very common way of achieving point in time recovery for your database, assuming you have a full backup from which you can roll forward.

Just copying away the binary logs with a remote transfer procedure (e.g. scp) would be fine- they can just be applied as is; however, it may create inconveniences if the binary logs are in use, being rotated/purged, and above all, being written because they are the last ones. While you could work around that- there is an easier method:

mysqlbinlog is an utility included in the mysql client code which not only allows to transform binlogs into sql, it also allows to remotely stream them by using the mysql protocol (so you don't need to setup additional infrastracture and code). In particular the -R option will allow you to connect to a remote server and stream its binlogs locally. Combined with the --raw and  --stop-never options you will have a built-in option to stream in almost real time the binary logs in the original format, without having to handle the different files and losing events, etc. Although you may still want to wrap that so that you can handle connection errors and other problems that could arise on the remote copy.

See the manual for more details on how it is suggested to proceed with this: https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html
And here is a recipe on Percona XtraBackup website on how to use binlogs to apply incremental backups to an existing backup: https://www.percona.com/doc/percona-xtrabackup/8.0/innobackupex/pit_recovery_ibk.html

  How do I only get the binary logs since the exact time of the last mysqldump?

For that, you need to run mysqldump with --master-data or --dump-slave options (as well as single-transaction, or other way of achieving consistency) and gather the binlog or gtid coordinates. That way you can configure where to start gathering the binlogs (or easier, where to start applying those). A trick to make things even easier is to run FLUSH LOGS before a dump so you have less data to move around when doing a point in time recovery.

  If another transaction started before that time and finished after it, will it still be included properly in the log?

It may or may not be in the log, but its coordinate according to mysqldump will not be included. Transactions are only included in the binlog on commit time. Meanwhile they are cached on memory or disk. While it is possible to split logs as you may be thinking, I would suggest it is easier to keep them as similar as possible to the original format of the msater, and use the coordinates to handle consistency of the different backups you take- for simplicity and so you can reuse the same file for the recovery of similar backups.

  Can I use mysqlbinlog to only get the changes since the last mysqldump?

If still you prefer to keep them separate, use the rotation I suggest above, but you may need to increase the (unnecessary) database locking to make the rotation of the logs and the backups consistent. Or you can just use --start-position and --stop-position mysqlbinlog options for a one-time copy.


Regular Data Space Specification vs Text/Image Filegroup I want to change the FileGroup of a table for FG01 to FG03 . but in properties of table there is the Regular Data Space Specification  and Text/Image Filegroup which have the list of FileGroups, what are the differences?

Regular data is in-row data and it is located on the filegroup listed in Regular Data Space Specification, LOB data is located on a Text/Image Filegroup that can be different from a filegroup where in-row data is located.

You specify these filegroups when create a table that contains LOB data:

CREATE TABLE ...
[ ON { partition_scheme_name ( partition_column_name )
           | filegroup1
           | "default" } ]
    [ TEXTIMAGE_ON { filegroup2 | "default" } ]

Here filegroup1 is for regular data and filegroup2 is for LOB data.

  TEXTIMAGE_ON { filegroup| "default" }

  Indicates that the text, ntext, image, xml, varchar(max),
  nvarchar(max), varbinary(max), and CLR user-defined type columns
  (including geometry and geography) are stored on the specified
  filegroup.

  TEXTIMAGE_ON is not allowed if there are no large value columns in the
  table. TEXTIMAGE_ON cannot be specified if partition_scheme is
  specified. If "default" is specified, or if TEXTIMAGE_ON is not
  specified at all, the large value columns are stored in the default
  filegroup.
  The storage of any large value column data specified in
  CREATE TABLE cannot be subsequently altered.

CREATE TABLE (Transact-SQL)

Note the last cited phrase in bold.
If you have LOB data and think that you can just rebuild your table on another filegroup, your LOB data will not be moved at all.


Microsoft SQL Server local report culture is not supported: 3072 is invalide culture identifer I am using Windows 10, language Serbian, SQL Management Studio 2016 and can't use Database reports because there is error:

  An error occurred during local report processing  Culture is not
  supported  Parameter name: culture 3072 (0x0c00) is invalid culture
  identifier.

I try with different settings for Windows 10 - language (English), try with different settings for SSMS (English) but got same error.

Do you have idea how to solve this?

P.S. My Windows 10 system locale settings:

Get-WinSystemLocale
LCID       Name            DisplayName
----       ----            ----
9242       sr-Latin-RS     Serbian(Latin, Serbia)

Had the same error.

Turns out that it was my setting in Region -> Formats -> Format. When it was on English (World) or English (Europe), I would get this error. Setting it to English (United States) solves it.

I'm guessing that English (World) or English (Europe) were new formats with Windows 10, and SSMS is not prepared for it.


Improve select rows from table1 in table2 but not in table3 I have a SQL Server 2012 database and I'm trying to improve the following sentence:

Select c.CodeId from Code c
where c.AggregationLevelId = 2 and CodeId in
    (select AggregationId from Aggregation where AggregationId not in
    (select AggregationChildrenId from AggregationChildren))

I want to get the rows in Code table where its CodeId is in Aggregation table but it isn't in table AggregationChildren.

It works but it is very slow.

The relationships between the tables are: Aggregation.AggregationId and AggregationChildren.AggregationChildrenId are FK to Code.CodeId.

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    [ ... ]
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [ ... ],
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [ ... ],
    CONSTRAINT [PK_AGGREGATION_CHILDREN] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    [ ... ]
)

Is there a better way to improve that select?

This is the execution plan for the select:

Unless I'm misunderstanding your question, I would think a combination of EXISTS and NOT EXISTS would give you what you want.

--Setup
set nocount on
DECLARE @Code TABLE (CodeId INT)
DECLARE @Aggregation TABLE (AggregationId INT)
DECLARE @AggregationChildren TABLE (AggregationChildrenId INT)

INSERT INTO @Code (CodeId)
VALUES (1),(2)

INSERT INTO @Aggregation (AggregationId)
VALUES (1),(2)

INSERT INTO @AggregationChildren (AggregationChildrenId)
VALUES (1)

--The query
SELECT c.CodeId
FROM @Code c
WHERE EXISTS (
        SELECT *
        FROM @Aggregation
        WHERE AggregationId = c.codeid
        )
    AND NOT EXISTS (
        SELECT *
        FROM @AggregationChildren
        WHERE AggregationChildrenId = c.codeid
        )

| CodeId |
|--------|
| 2      |


Custom sort before ROW_NUMBER() for paging I have the following

SELECT results.*, coinCat.*, cat.*
FROM (SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.CoinId) AS RowNumber
      FROM coins c) AS results
INNER JOIN CoinCategoriesCategories coinCat ON coinCat.CoinId = results.CoinId
INNER JOIN CoinCategories cat ON cat.CategoryId = coinCat.CategoryId
WHERE RowNumber>= @pageNumber * 1 AND RowNumber<@pageNumber *@pageSize
ORDER BY RowNumber

The statement must be nested in order to please Dapper (ORM),

Before applying/paging, I would like my results to be sorted by Coins.Rank but not sure where to stick that part so that my query can run properly and paging works happily

Also would this statement be any different between SQL Server vs PostgresSQL as an example?

instead of ORDER BY c.CoinId i just did ORDER BY c.Rank and that worked

SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.Rank) AS RowNumber FROM coins c


How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file? When I want to write to or alter particular cell of an excel file/table using python I'll use pandas read_csv then alter value in particular cell and write back to file with to_csv. But writing back to file seems to be overwriting the entire file with updated version of the file that differs only in single cell. This is a problem when am altering one or two cells in table of trillion rows and trillion columns.

When we are making write/alter database operations (like in SQL) on single cell out of trillion by trillion table, it seems to be making changes in the disk to only the cells that are modified rather than overwriting entire files.

How do databases facilitate writing/updating only particular cell in a table in the disk rather than overwriting entire file?

BTW, am not using SQL database as my table contains numerical column names and SQL doesn't support that. If you know any SQL/NOSQL database that supports numerical values as column names please let me know.

A very common misconception about Databases can be dispelled with this:

    Database != File

When you update a Row in a database, the underlying data file on disk isn't touched at all - at least not for "some time".  Instead, the database makes a note of the change in its Transaction Log, then updates the value in memory.  "Some time" later, the database might get around to needing that bit of memory for something else and will write the changed value to disk.  How often that happens and how large a chunk of memory get written at a time varies from DBMS to DBMS.

Data storage in databases is measured in Pages, each of which can contain a number of Rows; those things that make up the Tables that you and I play with.  When a Database needs some data, it works out where that data is in its data file(s) and then loads only those Pages that are relevant into its Buffer Cache (memory).  This is why some queries run slowly the first time you run them, but are lightning fast thereafter - serving the same Page over and over from the Cache is way faster than hauling it up from the data file on disk.

  ... I am not using SQL database as my table contains numerical column names ...

Here's another misconception about databases, again easily dispelled:

    Database != SpreadSheet

The way you structure data in Databases can seem quite "alien" when you're starting out; you seem to need to use "complicated", "artificial" constructs instead of just "rows" and "columns" of data.  But, once you gain an understanding of why you need these structures and the power that they give you over your data, you'll get over it pretty quickly.

  ... operations ... on single cell out of trillion by trillion table ...

Do you really have a useful value for every single value in a trillion by trillion table?  Personally, I'd doubt it, unless you work for Google.

I'd suggest what you actually have is a Sparse Array, where you have more "holes" than data.  That's a structure that Relational Tables can support very easily and really quite efficiently.


What are the advantages of going with AWS aurora DB with Mysql backend over AWS aurora DB with Postgresql backend What are the advantages of going with AWS Aurora DB with Mysql backend over AWS Aurora DB with Postgresql backend?
It's great if someone can share your experience or point out a document.

You got it wrong.  Aurora is its own backend, but it comes with MySQL compatibility:

  Amazon Aurora is designed to be compatible with MySQL 5.6, so that existing MySQL applications and tools can run without requiring modification.

The PostgreSQL compatibility is only available as a preview yet, in only one AWS region, after sign-up.


How can we identify AlwaysOn Process? I just wanted to configure "Resource Governor" on one of my production database server, where we have implemented AlwaysOn.

Many applications like SSRS, web App and SSIS connect to the database server.

I'm able to get application name for SSRS, web App and SSIS using APP_NAME() function, but not able to get application name for AlwaysOn processes.

Is there any way we can identify it, so that we can monitor/restrict how much resources being/will be used by AlwaysOn through resource governor.

Is there any way we can identify it, so that we can monitor/restrict how much resources being/will be used by AlwaysOn through resource governor.

I would absolutely not advise trying to do something like this. The point of Always On (I'm assuming you mean availability groups, here) is generally for high availability, disaster recovery, or multiple copies for read heavy workloads without using Replication.

Always On is accomplished through system processes on hidden schedulers (depending on version of SQL Server) and there wouldn't be a straight-forward way to do this with resource governor.

  I'm able to get application name for SSRS, web App and SSIS using APP_NAME() function, but not able to get application name for AlwaysOn processes.

Always On Availability Groups aren't an application but parts of the system. They use all of the normal built in mechanisms to SQL Server with only a few specifically built for it.

For example, part of the process is to write log information to disk. This is no different than writing any other log to disk, when you look at it from the outside. If you were to govern the amount of IOPs that could be done to a database then it would affect the entire database, not just the Availability Group process.


pg_restore fails with drop default on identity column Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase.   See below:

pg_restore --verbose --format=custom --dbname=bosh --clean --if-exists --single-transaction --exit-on-error

pg_restore: dropping TABLE DATA cpi_configs
pg_restore: dropping TABLE DATA configs
pg_restore: dropping TABLE DATA compiled_packages
pg_restore: dropping TABLE DATA cloud_configs
pg_restore: dropping TABLE DATA blobs
pg_restore: dropping TABLE DATA agent_dns_versions
pg_restore: dropping DEFAULT vms id
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2493; 2604 16496 DEFAULT vms id postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column "id" of relation "vms" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.
    Command was: ALTER TABLE IF EXISTS public.vms ALTER COLUMN id DROP DEFAULT;

I'm running this as part of an automation utility where it's not trivial to change the command.  Best I can think of to workaround this is to use -C to drop/create the database with pg_restore, but is this a bug to file or something we can do differently with pg_dump?

That looks like an unfortunate coincidence of --clean and an existing table.

The dump contains a column default definition, so --clean tries to remove the default before restoring it. This does not work for identity columns.

It would be interesting to know how the corresponding column in the database you dumped is defined. Does it have a DEFAULT value?

One could argue that “dropping the default” should include getting rid of identity columns; if you feel that way, you could take up that complaint with the PostgreSQL hackers mailing list. It should be possible to modify pg_restore accordingly, but I wonder if it is worth the effort.


Map functions over array with Postgres Is there any way in Postgres to apply a function over a results of an array (or any sort of collection)?

For example if I have a function create function add1... that increments a number by one, is there any way to do something like:

select map add1 array[1,5,8...];

and getting back

array [2,6,9...]

?

I am not sure if it has any sense over a select as it can map a function on the single result directly:

select add1(x) from X;

Also, is there any concept of laziness here? All the functions will be 'strictly' applied?

If you are merely looking to increment each element of an input array, this simple function is one method of doing it.

CREATE OR REPLACE FUNCTION add1 (int[]) RETURNS int[] AS
$func$
DECLARE
    e       int;
    new_arr int[] := '{}';
BEGIN
    FOREACH e IN ARRAY $1 LOOP
        e := e + 1;
        new_arr := new_arr || e;
    END LOOP;
    RETURN new_arr;
END;
$func$ LANGUAGE plpgsql STABLE;

SELECT add1(array[1,5,8,15]::int[]);
    add1
------------
 {2,6,9,16}

Or queried from your table

select id, array_col, add1(array_col) from yourtable;
 id |      arr      |     add1
----+---------------+---------------
  1 | {1,3,6,9}     | {2,4,7,10}
  2 | {10,20,30,40} | {11,21,31,41}

Plain SQL version of the function:

CREATE OR REPLACE FUNCTION add1(int[]) RETURNS int[] AS
$func$
    SELECT array_agg(vals)
    FROM (select unnest($1) + 1 as vals) AS x;
$func$ LANGUAGE sql STABLE;


MariaDB why do I get error "Unknown table x in field list" when it does exist (yes, another one!) Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.
The important part of the schema shows
/*Database: mamlocal*/
----------------------

/*Table Information*/
---------------------

Name                                  Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time          Check_time  Collation          Checksum  Create_options  Comment                                                        Max_index_length  Temporary
------------------------------------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -------------------  ----------  -----------------  --------  --------------  -----------------------------------------------------------  ------------------  -----------
...
member                                InnoDB       10  Dynamic        706             162       114688                0         49152          0            4528  2020-08-17 14:48:55  2021-04-06 10:27:53  (NULL)      utf8_general_ci      (NULL)                                                                                0  N
...

and the table member is created using...
CREATE TABLE member (
  member_id int(11) NOT NULL AUTO_INCREMENT,
  member_title varchar(10) DEFAULT NULL,
  member_fn_1 varchar(50) DEFAULT NULL,
  member_sn varchar(50) DEFAULT NULL,

  ... other fields

PRIMARY KEY (member_id)
)

These statements each produce the expected results.
SELECT * FROM information_schema.tables WHERE table_schema = 'mamlocal' AND table_name = 'member';
SELECT * FROM information_schema.tables WHERE  table_name = 'member';
SELECT member_id FROM member
SELECT member_id FROM mamlocal.member

but these produce Error Code: 1109 Unknown table 'member' in field list
UPDATE member SET  member_id = member_id ;
UPDATE mamlocal.member SET  member_id = member_id

(The real update does something useful but I simplified it to try to find out where the error was)
Can anyone help me to identify why?
I have looked at these other questions but still cannot see what is wrong
Qn1
Qn2
Qn3
Here is an SQLfiddle showing the same code working correctly in MySQL, as I would expect and I have used similar code in many other places without problems to update table member. Using the same code to update another table works ok.
More experiments showing that table member is there.
This code...
INSERT INTO member () VALUES  ();
UPDATE member SET  member_id = member_id ;

...
gives me the message
2 queries executed, 1 success, 1 errors, 0 warnings

Query: insert into member () values ()

1 row(s) affected

Execution Time : 0.002 sec
Transfer Time  : 0.001 sec
Total Time     : 0.004 sec
-----------------------------------------------------------

Query: UPDATE member SET member_id = member_id

Error Code: 1109
Unknown table 'member' in field list

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec

I think this might be the answer and might help others with this problem.
I had a test trigger on the table member
 CREATE  TRIGGER `test` BEFORE UPDATE ON `member` FOR EACH ROW
    BEGIN
     DECLARE addr VARCHAR(100);
     DECLARE        joint INT;
        SET @joint := is_joint_member(member.member_id);
        SET @addr = 'jgkjgkjg' ;
     CALL dummy2;
    END $$

where
is_joint_member()

is a working UDF that I've used for ages and
dummy2()

is an empty, but working stored procedure just to see if I could call a proc inside a trigger. However the trigger might not be working correctly as if I alter it and comment out
SET @joint := is_joint_member(member.member_id);

then my UPDATE works OK, as it does if I drop the trigger all together.
In other words the error
Error Code: 1109 Unknown table 'member' in field list

is misleading as it does not mean that the table doesn't exist, it means that something has gone wrong with a trigger attached to the update event on that table.
Something to watch out for in future!


Return all rows containing specific value in json array I have the following table:

CREATE TABLE [dbo].[Journals](
    [Id] [nvarchar](450) NOT NULL,
    [CorrelationId] [nvarchar](450) NOT NULL,
    [Version] [int] NOT NULL,
    [Type] [nvarchar](max) NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Journals] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Updated]
GO

One row could look like this:

Id: 1833eac9-3ea7-4613-be26-af3f7589d6f0
CorrelationId: 0HLNKM700ILBN:00000001
Version: 1
Type: Notification
Data: [{"type": "Event_Received","timeStamp": "2019-06-24T12:47:54.3960942Z","data": {"version": 1,"notificationName": "My Notification","receiverName": "John Doe","receiverUserIdentifier": "123456789","receiverEmail": "john.doe@example.org","senderName": "Some Sender","senderEmail": "someone@example.org","contentSubject": "This is my subject","contentBody": "And this is my body","attachmentNames": []},"sensitive": true}, {"type": "Event_ValidationSucceded","timeStamp": "2019-06-24T12:47:54.4167034Z","data": "{}","sensitive": false}, {"type": "Event_PublishStart","timeStamp": "2019-06-24T12:47:54.4691455Z","data": "{}","sensitive": false}, {"type": "Event_PublishSucceeded","timeStamp": "2019-06-24T12:47:54.4740542Z","data": "{}","sensitive": true}]
Created: 2019-06-19 12:29:20.8100000
Updated: 2019-06-19 12:30:51.1123311

I want to fetch all the Ids from the rows that contains array entries where Sensitive = true

So I have tried something like this:

SELECT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1

This works but I get duplicate ids back (since the array contains more than one item where Sensitive = true).

How can I limit the query so that I don't receive duplicate Ids when the array contains more than one item where Sensitive = true?

Wanted output:

1833eac9-3ea7-4613-be26-af3f7589d6f0

Actual output:

1833eac9-3ea7-4613-be26-af3f7589d6f0
1833eac9-3ea7-4613-be26-af3f7589d6f0

I guess that I could add a DISTINCT to SELECT DISTINCT Id but that feels like cheating, or? :D

You could opt to use TOP(1) in the CROSS APPLY

SELECT Id
FROM Journals journals
CROSS APPLY
(
SELECT TOP(1) Sensitive
FROM OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive')
WHERE Sensitive = 1
) as jsonValues;

Or, as you mentioned DISTINCT

SELECT DISTINCT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1;

Main difference between the two

When using the TOP(1) the values will be filtered and the top operator will be applied earlier in the execution plan

When using the DISTINCT solution, the filtering and grouping is only done after the JSON function is (cross) applied

Which is why I would opt for the TOP(1) + CROSS APPLY solution.

As a side note, storing unique identifiers as nvarchar() is not ideal, especially when using a huge data type like nvarchar(450). Consider using the minimal or close to the minimal amount of space needed in your column datatypes & using the uniqueidentifier datatype.


Replication from Enterprise to Standard edition Can I define transactional replication while the Publisher and Distributor are both Enterprise Edition and the Subscriber is Standard Edition?

What do I need to consider in case it is possible (given I already know about partitioned tables)?

Yes, you can replicate from Enterprise edition to Standard edition.

The only caveat is Peer-to-Peer Replication requires all nodes to be Enterprise Edition, and Web and Express editions are Subscriber only.

Features Supported by the Editions of SQL Server - Replication


unable to import the file Gone through the below link also but i am unable to import CSV file in mysql. Even i had tried by using > LOAD DATA LOCAL INFILE but same error persisting.
Please help me in this. queries i tried had appended below

MySQL import csv only gets half the lines

mysql> LOAD DATA INFILE 'home/karthick.g.s/Desktop/karthick.csv'
    -> INTO TABLE test_1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '^'
    -> LINES TERMINATED BY '\r\n';
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/home/karthick.g.s/Desktop/karthick.csv' (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE 'home/karthick.g.s/Desktop/karthick.csv'
    -> INTO TABLE test_1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '^'
    -> LINES TERMINATED BY '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

I am using the version 5.5.37-0ubuntu0.12.04.1

From the manual here, you could try experimenting with your my.cnf settings - I know that I had this problem in the past and I eventually got around this by using [client] setting.

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
loose-local-infile  =   1

local-infile    = 1

Now, it was either local-infile or loose-local-infile which worked eventually. I believe that loose-local-infile is the more permissive setting, so you could try that first - and if you've no security concerns on your server, you could leave it at that.


How to enable all the possible backup options of MySQL I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to enable it? I also want to know what all parameters to include in a command-line mysqldump command to take a perfect guaranteed backup.

Add the following to my.ini

[mysqld]
log-bin=mysql-bin

Then, restart mysql

As for mysqldump, most of the options you need are defaulted already in --opt. Here are my choice parameters:

mysqldump -h... -u... -p... --master-data=2 --routines --triggers --flush-privileges --all-databases > DataDump.sql

Here is an explanation for my choices (including default --opt)

--opt               Same as --add-drop-table, --add-locks, --create-options,
                    --quick, --extended-insert, --lock-tables, --set-charset,
                    and --disable-keys. Enabled by default, disable with
                      --skip-opt.
-R, --routines      Dump stored routines (functions and procedures).
--triggers          Dump triggers for each dumped table.
--flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
                    database.  This option should be used any time the dump
                    contains the mysql database and any other database that
                    depends on the data in the mysql database for proper
                    restore.
--master-data[=#]   This causes the binary log position and filename to be
                    appended to the output. If equal to 1, will print it as a
                    CHANGE MASTER command; if equal to 2, that command will
                    be prefixed with a comment symbol. This option will turn
                    --lock-all-tables on, unless --single-transaction is
                    specified too (in which case a global read lock is only
                    taken a short time at the beginning of the dump; don't
                    forget to read about --single-transaction below). In all
                    cases, any action on logs will happen at the exact moment
                    of the dump. Option automatically turns --lock-tables
                    off.

You will need --master-data if you want to setup the dump to prepare replication slaves. The master log and position at the time of the dump will be recorded as a comment (using --master-data=2) or as a command (using --master-data=1) on line 22 of the dump file.

UPDATE 2011-07-26 15:20 EDT

If you would like to dump separate databases and separate tables, please follow the instructions at this URL.


Is SQL_CALC_FOUND_ROWS Safe and Accurate for a Single DB User Environment? I have a large MySQL web application that uses a single user for all of its database queries. We have multiple databases running off of a master-slave replication setup.

My question is this: We use SQL_CALC_FOUND rows to get the number of records returned from a query as if the query had been run without a LIMIT clause; we do this for pagination on our front-end. My concern is that, if two users of the web application login and run two queries simultaneously (remember that every user is using the same DB user account to run queries), when we query for the number of found rows:

SELECT FOUND() ROWS AS total

There may be a non-zero chance that the number being returned is from a different query that was run at the same time as the first query. Is this a possibility? Could the above query return an inaccurate number or is there some sort of fail-safe in place?

Well, it might not be a complete in depth analysis but, from a developer perspective, I can say it depends on how your server handle the requests and execute the application. To be more clear: synchronously or asynchronously.

If your server handle requests synchronously, than there's nothing to worry. In this case, given two hypothetic users (User A and User B) making requests at the same time, your server will attend only one request first, All the processing for the given hypothetic requests are going to happen in a different moment so there will be no information crossover madness.

But if your application is running in many server instances or in a server that handles requests asynchronously, I wouldn't suggest you to rely the presented method to count rows.

  Even in cases when my assumption is not 100% accurate, for a sake of
  sanity, in your application, I would suggest you to use the good old
  common bulletproof double query solution:

SELECT [fields] FROM [tables] WHERE [filter]

and later our count query...

SELECT count(*) as total FROM [tables] WHERE [filter]

There's no way to go wrong and, as @DTest added, this 2 query approach can be faster in some environments (see the link in his comment).

Oh, and I took a fast read at MySQL documentation related to Information Functions, there it was on a tiny little silly notice:

  Important:
  FOUND_ROWS() is not replicated reliably, and should not be used with
  databases that are to be replicated.

from: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count


How to load 100k + Records into progres database from a CSV file? Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of batches so that unloaded data/batch can be revisited, corrected and loaded again).

Our project involves a Java application and we initially thought of using JDBC, but reading each row and committing to database slows down the application.

Please suggest if you know an approach to get this job done.

You can use the COPY command for this.

See:
http://www.postgresql.org/docs/9.3/static/sql-copy.html
https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table


using triggers to synchronize data i've two database, Database1 and Database2! i'd like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I've used triggers to synchronise data and it works but it duplicates everything say number, name, amount yet i want it to update, say if name jovia with a number 014567 and her previous amount(balance) is 200 in Database2. And if payment is made in Database1, with the same name and number and with 300 as the amount, i'd like the trigger to synchronize the other table by adding up the previous amount and the new amount, it does that but it duplicate the rows.

I've added a LIMIT 1 to avoid duplicate rows but it's the same issue

Database2 cc_phonenumber_table

-------------------------
name  | number | amount
-------------------------
jovia | 014567 | 200
maria | 098765 | 500
jovia | 014567 | 500
muche | 987653 | 245

how can avoid this duplication in the cc_phonenumber_table? please help me below is my trigger i've tried to use;

delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
  FOR EACH ROW
  BEGIN
    UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
    INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount);
       END;
    |

delimiter ;

Well thanks everyone,
but I read somewhere saying that i should include this in the table i want to sync to(to make the table unique) "alter table "YOURTABLE add unique index(row1, row2);"

then i  add ON DUPLICATE KEY UPDATE name = name and number = number;

Here is a final trigger after making the table unique to the one am synchronize to, in this case it's "database2.cc_phonenumber" and everything worked perfectly.

    delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
  FOR EACH ROW
  BEGIN
    UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
    INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount) ON DUPLICATE KEY UPDATE name = name and number = number;
       END;
    |

delimiter ;


Can I use `on delete set null` on a compound foreign key which contains a primary key column? The docs say:

  Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key constraint, the operation will fail.

But it isn't spelled out whether on delete set null will fail if only one of the foreign key columns is nullable, or if it will merely set the nullable part to null, which is it?

You can't use on delete set null if any of the foreign key columns don't accept null:

create schema stack;
set search_path=stack;
--
create table t1( foo integer
               , bar integer
               , primary key(foo,bar) );
--
create table t2( foo integer
               , baz integer
               , bar integer
               , primary key (foo,baz)
               , foreign key (foo,bar) references t1 on delete set null );
--
insert into t1 values (1,1);
insert into t2 values (1,1,1);
delete from t1;
/*
ERROR:  null value in column "foo" violates not-null constraint
DETAIL:  Failing row contains (null, 1, null).
CONTEXT:  SQL statement "UPDATE ONLY "postgres"."t2" SET "foo" = NULL, "bar" = NULL WHERE $1 OPERATOR(pg_catalog.=) "foo" AND $2 OPERATOR(pg_catalog.=) "bar""
*/
--
drop schema stack cascade;

And the same is true for on delete set default. I don't know of any simple way round this, and I'm not sure I agree that it is the most sensible behaviour, I think a better choice for on delete set null would be to set the nullable columns to null if there are both.


Select all rows where a column value occurs more than once I have a following table structure in Postgres:

 id | account_id | plan_id | active
----+------------+---------+--------
  1 |    0cYd7Ak |       1 |      f
  2 |    Uk02q1d |       1 |      t
  3 |    eRlk810 |       2 |      f
  4 |    Uk02q1d |       2 |      t
  5 |    0cYd7Ak |       1 |      t
  6 |    yT3nv3p |       3 |      t

How I can select all row where account_id occurs more than once? For the example above it will return rows 1, 2, 4, 5

I tried this one query, but it does not return what I expect:

select * from table_name t1
where (select count(*) from table_name t2
  where t1.account_id = t2.account_id) > 1
order by t1.account_id;

Your query seems correct (although it may not be the most efficient).

There are many ways to write this type of query. For example, you could use EXISTS to avoid counting in the correlated subquery:

select * from table_name t1
where exists
      (select 1 from table_name t2
       where t1.account_id = t2.account_id and t1.id <> t2.id)
 ;

Another method is to use a subquery or CTE and window aggregate:

select id, account_id, plan_id, active
from
  ( select *,
           count(1) over (partition by account_id) as occurs
    from table_name
  ) AS t
where occurs > 1 ;

Or use a subquery to find the accounts that have more than one occurrences and then join to the table:

select t.*
from
  ( select account_id
    from table_name
    group by account_id
    -- having count(1) > 1
    having min(id) <> max(id)      -- variation with same result
  ) as c
  join table_name as t
  on t.account_id = c.account_id ;


Session ID in SQLCMD running multi-batch script My script contains multiple GO keywords. I know that SQLCMD interprets GO as a batch delimiter, so the code is not run as a single block but instead each part between the delimiters (a batch) is run separately. My question is, are all batches executed in the same session?

I tried testing this using a simple script like this:

SELECT @@SPID
GO

SELECT @@SPID
GO

SELECT @@SPID
GO

I then ran the script from the command line using this command

SQLCMD -S MyServer\Instance -E -i MyScript.sql

and got this output:

------
    62

(1 rows affected)

------
    62

(1 rows affected)

------
    62

(1 rows affected)

It seemed to answer my question in the affirmative, but then I ran the script again and got exactly the same output, i.e. all values were 62 again. Apparently the two separate executions of SQLCMD could not possibly run in the same session, they just happened to receive the same session ID. But that in turn makes me think that the same @@SPID value return by a single run might not necessarily mean it was the same session either. Each batch could be executed in a different session that just happened to receive the same ID, similar to how different executions of SQLCMD were running in different sessions with the same ID.

Therefore, my question still stands: are multiple batches of the same script executed in SQLCMD in the same session or not? Is there a way to determine this?

You should be able to verify that all batches in a single SQLCMD script are run in the same session by creating a local #temp table at the top of the SQLCMD script and trying to reference that temp table in subsequent batches.  Since the scope of a local temp table ends when the session ends, if subsequent batches were indeed running in different sessions (even though the id was the same), you would get table not found errors.

create table #test(TestColumn varchar(50))
insert into #test(TestColumn) values('I''m still here!')
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO


Spread SQL Server data files across devices to improve performance? Would it improve performance of a database in SQL Server if I place the .mdf (rows data) and .ldf (log) files on separate sets of raided disks, as opposed to having both files on the same set of raided disks?

All performance solutions are based on measurements. As the theory goes, SQL Server requires the transactions to flush to durable storage (disk) before acknowledging commit (WAL). This makes the log write throughput critical for high write loads. Separating the LDF file on a separate physical disk allows the log writes to achieve serial streaming access to disk, which is the fastest way a spinning disk can write (~100-150Mb/s). Having the MDFs on the same physical disk introduces random IO patterns that dramatically reduce the throughput (~10-40Mb/s). This is the background of the separate LDF /MDF disk recommendations. The point is, obviously, mute on SSD storage.

However, to improve the server performance you need to identify the bottleneck and address it appropriately. If your bottleneck is poor indexing, lock contention, CPU, small buffer pool or any other of many more possible causes then separating the LDF and MDF will help nothing. So you should first find the bottlenecks, and I recommend following the Waits and Queues methodology. Once you have measurements that prove a bottleneck that causes performance degradation then and only then you can act appropriately.


MySQL Cluster Replication Merge We'd like to have two MySQL cluster groups, and at the same time have replication set up between them.  Is that possible?

Here are two possible setups you could try

TOPOLOGY #1

Data Center #1 with two(2) DB Servers (DB1, DB2)
Data Center #2 with two(2) DB Servers (DB3, DB4)
Setup DRBD with Each Data Center
Setup MySQL Replication Between the DRBD Primaries
Setup DBVIP (Linux Heartbeat/ Pacemaker / ucarp)

What this setup provides

Synchronous Disk-Level Replication Within Each Data Center
Asynchronous Replication Between Data Centers

TOPOLOGY #2

Data Center #1

Three(3) DB Servers (DB1, DB2, DB3)
Percona XtraDB Cluster among the three(3) DB Servers

Data Center #2

Three(3) DB Servers (DB4, DB5, DB6)
Percona XtraDB Cluster among the three(3) DB Servers

MySQL Circular Replication

DB3 used as Master to DB6
DB3 used as Slave to DB6
Use Semisync Replication Between Master and Slave

What this setup provides

Virtually Synchronous Replication Within Each Data Center
Asynchronous Replication Between Data Centers

I have discussed setups like this before

Feb 10, 2014 : MySQL High Availability for InnoDB
Jul 11, 2013 : MySQL replication between datacenters
Dec 26, 2012 : MySQL Replication and High Availability
Oct 25, 2012 : mysql read slave to fetch entire database on start
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Mar 26, 2012 : 3 Databases, Best Replication Setup?
Dec 19, 2011 : Best way to setup master to multi master replication
Mar 29, 2011 : MySQL high availability, failover and replication with Latency

GIVE IT A TRY !!!


Copy SQL Server Maintenance Plan between instances on same server I have multiple SQL Server 2019 instances on the same server. I've created and exported the first instance's maintenance plan, edited its exported DTSX file to change the instance and plan names to avoid name conflicts, and imported the edited DTSX via SSIS (and DTUTIL) on the same server.
Problem: I see the the both the original and imported plan in SSIS under Stored Packages > MSDB > Maintenance Plans, but NOT under the second instance's Management > Maintenance Plans list in SSMS.

Here you go:
I believe only Windows server can have only one copy of SSIS installed. So even if you have seven instances of SQL on the same box, you'll only have SSIS devoted to one of them. SSIS will work for all them but will initially only be aware of one of them. I think the following link still holds the answer by modifying MsDtsSrvr.ini.xml to make SQL Server SSIS aware: https://www.mssqltips.com/sqlservertip/2061/how-to-manage-ssis-packages-stored-in-multiple-sql-server-database-instances/


Composite primary key plus a separate (surrogate) id column for foreign key references I am trying to build a database about locations like so:

Country → Region → Town

Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:

The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.

I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.

You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.


Synchronize MySQL database from remote MySQL database The documentation mentions a sync function, but that function is not available from the Database menu as shown in the screenshots there. Is this functionality available elsewhere? Using reverse engineer makes the option show, but the wizard is unclear..

It would be nice being able to choose specific tables, but have the necessary tables linked through foreign keys be automatically included to avoid synchronization failure, and to do it all from the mysql shell not from an error prone UI.

Seems like the migration wizard (available from menu Database) is a better choice for this scenario.


SQL Question: Selecting different language versions based on availability I have the following situation: I have a table that contains entries with language versions (German and English) as part of the key (the other part being a "name" field). However, not always both versions are available - sometimes, e.g. only a German version exists. I want to build a select statement so that I get all English ones that are available, and all the German ones for the entries that have no English version. The resulting table should not contain any duplicates in the "name" field.

Currently, I've come up with this query:

SELECT * FROM table WHERE lang = 'E'
UNION
SELECT * FROM table WHERE lang = 'D'
                      AND name NOT IN (SELECT name FROM table WHERE lang = 'E')

This works, but it seems very complicated and I'm afraid it doesn't perform very well. Also, if I want additional WHERE clauses on the result I have to build another SELECT * around it, using the above statement as a temp table.

Is there a better way to do this? Thanks for your help in advance!

You can create a query to get the two language as two column, as translation, and use COALESCE to get the value that is present.
If you have one language that have all the data you'll want to use it as the anchor and write a LEFT JOIN (if word is the field with the data)

SELECT COALESCE(e.word, d.word) word
FROM   table D
       LEFT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name

otherwise a FULL JOIN is needed

SELECT COALESCE(e.word, d.word) word
FROM   table D
       FULL JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name

Edit
As ypercube pointed out MySQL don't support the FULL JOIN, but it can be simulated

SELECT COALESCE(e.word, d.word) word
FROM   table D
       LEFT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name
UNION
SELECT COALESCE(e.word, d.word) word
FROM   table D
       RIGHT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name


Error while trying to export logins from SQL Server 2000 to SQL Server 2005 I'm trying to migrate logins from an instance in SQL 2000 to a destination instance in SQL 2005.

I've used revlogin before as explained here:

https://support.microsoft.com/en-us/kb/246133

(I'm using the "Method 2" as explained in the article)

The problem is that while trying to create the sp_hexadecimal stored procedure (used by sp_help_revlogin_2000_to_2005) I get the following error:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

Msg 134, Level 16, State 2, Procedure sp_hexadecimal, Line 26
The variable name '@binvalue' has already been declared. Variable names must be unique within a query batch or stored procedure.

This is super weird since the same SP works in other instances.

I'm leaning to believe that this is related to the SQL version I'm working on:

Microsoft SQL Server  2000 - 8.00.2301
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

The script works well in a:

Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Has anyone encountered this issue before?

Is there a workaround?

I found some references to this issue on the Web but they are all inconclusive.

http://www.sqlservercentral.com/Forums/Topic309704-5-1.aspx
https://social.msdn.microsoft.com/Forums/en-US/55b9a6c6-2e03-42dd-bcfc-31f487171b73/variable-already-declared-error-when-adding-stored-procedure?forum=sqldatabaseengine

Thanks!

After dealing with this error quite a lot I was not able to find a way to make it work, since support for SQL2000 is no longer available I ended up using dbatools (an awesome PowerShell module): github.com/sqlcollaborative/dbatools

dbatools has a function called Export-SqlLogin which does exactly what I needed: script all logins with sid, password, server role, default db, etc.

I highly recommend looking into this PowerShell module.


Merging two 'tables' (function outputs) 1 to 1 Let's say I have two functions. We'll represent them as tables: order_items, order_details.

CREATE TABLE order_items
    ("order_item_id" int, "product_id" int, "quantity" int)
;

INSERT INTO order_items
    ("order_item_id", "product_id", "quantity")
VALUES
    (10, 1, 1),
    (11, 1, 1),
    (12, 1, 1),
    (13, 2, 2)
;

CREATE TABLE order_details
    ("order_details" jsonb, "product_id" int, "quantity" int)
;

INSERT INTO order_details
    ("order_details", "product_id", "quantity")
VALUES
    ('{"1":2,"2":5}'::jsonb, 1, 1),
    ('{"1":2,"2":6}'::jsonb, 1, 1),
    ('{"1":1,"2":4}'::jsonb, 1, 1),
    ('{"1":1,"2":4}'::jsonb, 2, 2)
;

I would like to take their outputs and have an output that looks like this:

("order_details", "product_id", "quantity", "order_item_id")
('{"1":2,"2":5}'::jsonb, 1, 1, 10),
('{"1":2,"2":6}'::jsonb, 1, 1, 11),
('{"1":1,"2":4}'::jsonb, 1, 1, 12),
('{"1":1,"2":4}'::jsonb, 2, 2, 13)

Basically, order_details can be associated with any order_item_id as long as the product_id and quantity match. The output must return 4 rows.

I've been trying to do this in plain SQL, but I am starting to feel like it's not possible. Using plpgsql is fine, but I figure there is a faster way than doing a double nested for-loop.

Just assign a unique index in both tables, then use that for the join:

SELECT order_details, product_id, quantity, order_item_id
FROM (SELECT order_item_id, product_id, quantity,
             row_number() OVER (PARTITION BY product_id, quantity) AS indx
      FROM order_items) AS items
JOIN (SELECT order_details, product_id, quantity,
             row_number() OVER (PARTITION BY product_id, quantity) AS indx
      FROM order_details) AS details
USING (product_id, quantity, indx);


SQL*Plus TNS:protocol adapter error I am using this system on which everything was already configured. I want to connect to local db on SQL Plus. When I enter user-name scott and password tiger, it gives ORA-12560:TNS:protocol adapter error.

Enter user-name: scott
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

When I try with one line connection string scott/tiger@localhost:1521/orcl, it gives ORA-12541: TNS:no listener.

Enter user-name: scott/tiger@localhost:1521/orcl
ERROR:
ORA-12541: TNS:no listener

I am new to this.

Please make sure that oracleserviceSID is running in your services.msc.

Then try restart your listener from services.msc itself or from command prompt(connect to sqlplus sys as sysdba).

(Hope you have configured listener.ora from netca)


Does bool_and short-circuit function calls with arguments? Suppose I have the following query:

SELECT a.*
FROM a
CROSS JOIN b
GROUP BY a.id -- Primary key
HAVING bool_and(expensive_comparison(a, b))

What will PostgreSQL do with this query? Will it process expensive_comparison on all pairs, or will it stop and return immediately for a given for a given a.id?

Is there a more efficient, equivalent way to write this query without resorting to procedural code?

I'm using PostgreSQL 9.3, but if it's changed in any newer versions, that would be relevant info.

It depends on whether the funciton is IMMUTABLE or not. All tests done on PostgreSQL 9.5.

CREATE FUNCTION f_expensive_volatile ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
VOLATILE;

CREATE FUNCTION f_expensive_stable ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
STABLE;

CREATE FUNCTION f_expensive_immutable ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
IMMUTABLE;

Now we create sample data.

CREATE TABLE f AS
SELECT 1 AS id, *
FROM generate_series(1,100) AS gs(x);

Now on the IMMUTABLE one, note Execution time: 0.138 ms,

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_immutable() );

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2.50..2.51 rows=1 width=4) (actual time=0.083..0.083 rows=1 loops=1)
   Group Key: id
   Filter: bool_and(true)
   ->  Seq Scan on f  (cost=0.00..2.00 rows=100 width=4) (actual time=0.015..0.030 rows=100 loops=1)
 Planning time: 1001.328 ms
 Execution time: 0.138 ms
(6 rows)

And on the STABLE one...  note me not waiting around

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_stable() );

And also this exemplifies me not waiting around...

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_volatile() );

With arguments

CREATE FUNCTION f_expensive_immutable (int)
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
IMMUTABLE;

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_immutable(id) );

Yes, it seems that optimization is lost if you provide arguments


Saving all sql server transactions to txt for a transactional replication to another database I have sql server 2014 running my main database at location A.
I want to know if it would be possible to export all insert,update and delete queries to txt files for me to then run on a off site database ? I need to set up something like sql servers transactional replication but with the queries being sent to txt files.

Is it possible for sql 2014 to log and export queries to txt files ? is there any softwares i could use ?

Due to cost reasons setting up a vpn to the offsite database for transactional replication is unfeasable.

This probably isn't the best idea. The only way that comes to mind to do this is to capture the queries in a profiler or extended events trace and transforming the output to do what you want. That's going to add overhead and ultimately be quite hacky and unreliable.

You'd probably be much better off using something like log shipping, so you can take log backups from your main database, copy them to your off site server, then restore them against the database(s) on that server. Then you get all the changes in a manner you can rely on with minimal overhead.

In answer to follow-up question:
There would be no downtime. Taking transaction log backups (like any other backup) creates some overhead, but it is generally good practice to be doing this regardless of needs to replicate the data.

Log shipping is generally easy to set up:
https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-2017

But you may need to craft your own equivalent if you don't have easy connectivity between the two servers.

Before you start you need to make sure the source database is in FULL recovery model (you can't take transaction log backups otherwise) and restore a copy of it to the destination server using a full backup with the STANDBY option. STANDBY will put the database in the state where it is readable (but not writeable) and transaction log backups can be applied.

Then your log shipping consists of three components:

1) Have a process on the source box (a SQL Agent job usually) that takes log backups on your source database. The more often you take these, the closer to real-time your destination database can be.

2) A process that copies the transaction log backups to your destination server.

3) Another process, this time on the destination box (again usually SQL Agent) your restores the copied logs to your destination server. Again, using the STANDBY option.

Apart from that you just need some housekeeping, e.g. to remove the log backups once they have been restored or after a certain length of time.

When you use the "built-in" version of log shipping you get some extras, such as monitoring and logging of what is going ob, but overall it is a simple process, so not too hard to replicate on your own.


Is it overkill to encrypt an already hashed BCrypt password? I'm using BCrypt to hash my passwords on the server side.  Before I store it in my MySQL database, would it be overkill to encrypt my hashed-BCrypt password or would storing the hash directly in the database suffice?

This website advises to encrypt passwords after hashing them:

  As long as an attacker can use a hash to check whether a password
  guess is right or wrong, they can run a dictionary or brute-force
  attack on the hash. The next step is to add a secret key to the hash
  so that only someone who knows the key can use the hash to validate a
  password. This can be accomplished two ways. Either the hash can be
  encrypted using a cipher like AES, or the secret key can be included
  in the hash using a keyed hash algorithm like HMAC.

I'm coding in Java.  I'm trying to gauge whether the added layer of protection vs. speed performance of read & retrieval of passwords for user logins is worth it or not.

Adding such an additional secret - and, ideally, storing it securely in an HSM - is indeed a normal practice. It's not common, exactly; many platforms are using hashes alone (and often poorer hashes than bcrypt). But an additional layer definitely makes things harder for the attacker - if done right.

Some further advice: roll as little of the implementation yourself as possible. Instead, find well-established libraries to do as much of the crypto as you can (because it's hard to get right - and getting it wrong can be very bad indeed).

Note that bcrypt has some limitations - notably that the maximum plaintext length allows is 72 bytes. It also is not memory-hard, and therefore is subject to attack with (relatively) lower-cost specialized hardware with many cores in parallel. The modern options - scrypt and the Argon2 family - are resistant to low-memory parallelization. Look for well-baked implementations of those. Also, use bcrypt with as high of a work-factor cost as your users (and projected hardware) can tolerate (cost 12 is often where it starts to get close to the break-even point, but YMMV; test on your hardware).


How to add linked server SQL Server 2008 R2 I have two SQL Server 2008 located in different cities.

I have physical access to one of them and I can exec SQL Server Management Studio there.

For second server I have following parameters:

remote domain name
remote port
login
password

With these parameters I can connect to second server with SQL Server Management Studio.

But now, I have to create cross server queries. It is possible? How? :)

Do I have to add Linked Server? How?

I see only this window, but where do I writer domain/port/login/password info?

Change the server type to SQL server, put the servername,port number in the Linked Server name; on the Security section, change to "Be made using this security context" and enter the user name/password, assuming they are SQL Authentication credentials.


SQL Server Replication tools We're trying to create an exact copy of our prod database to later use it for reporting.

I was told that some of my colleges tried to use the out of the box replication tools that SQL Server provides without any success.

I believe the reason it failed was because of the version of the servers (Runtime Edition if I'm not mistaken).

Are there any good third party tools to replicate data from a SQL Server 2005 to another server?

I don't think it was the run time edition that was the problem, most likely you:
-Were not set to a high enough compatibility level in your DBs
-Some of your tables might have not had primary keys
-End nodes perhaps were not accessible

Replication is great as it gives you a lot of flexibility in what to replicate over and what not to.  It also gives you very near real time reporting.  It does create 6 jobs and requires you to handle schema changes differently based on scenario you use.

Something much easier to manage might be to use async mirroring (if you have enterprise).  With Asynch mirroring you will not slow down commits on the primary site and you can take snapshots every day/12 hours/etc. then do your reporting off that.  Everything, including DLL and DML changes are replicated over and it doesn't need to create 6 jobs.


PostgreSQL ERROR: cannot freeze committed xmax First of all, I am new to PostgreSQL database.

In my PostgreSQL log I can see errors bellow and I don't know what is wrong or what I should change.

2018-03-29 10:54:38 CEST::@:[5059]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:55:38 CEST::@:[5144]: ERROR:  cannot freeze committed xmax 710196
2018-03-29 10:55:38 CEST::@:[5144]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:56:38 CEST::@:[5182]: ERROR:  cannot freeze committed xmax 710196
2018-03-29 10:56:38 CEST::@:[5182]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:57:38 CEST::@:[5208]: ERROR:  cannot freeze committed xmax 710196
2018-03-29 10:57:38 CEST::@:[5208]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"

I use default vacuum settings except this one parameter:

vacuum_cost_limit = 200

We are using PostgreSQL 9.4.16, single node solution on RedHat 7.

Can you help me?

Two leads:

If "jiradb.public.propertyentry" is a materialized view, you might be running into a bug that was fixed with the release of 9.4.17 on 2018-03-01. The release notes:

  Repair pg_upgrade's failure to preserve relfrozenxid for materialized views (Tom Lane, Andres Freund)

  This oversight could lead to data corruption in materialized views
  after an upgrade, manifesting as “could not access status of
  transaction” or “found xmin from before relfrozenxid” errors. The
  problem would be more likely to occur in seldom-refreshed materialized
  views, or ones that were maintained only with REFRESH MATERIALIZED VIEW CONCURRENTLY.

  If such corruption is observed, it can be repaired by refreshing the
  materialized view (without CONCURRENTLY).

The last line also hints how to repair this.

Or, if you upgraded from Postgres 9.2 or older, this other issue resulting in cannot freeze committed xmax <xid> may be yours:

https://www.postgresql.org/message-id/E1erVTy-0005Lp-8p%40gemulon.postgresql.org
https://www.postgresql.org/message-id/CAPYLKR6yxV4=pfW0Gwij7aPNiiPx+3ib4USVYnbuQdUtmkMaEA@mail.gmail.com

Fixed, but not released, yet. Should be in 9.4.18.


Joining two Tables with matching two values I have two tables. One is user. user has two columns.

user_id
user_name

The other table is product. product has two columns.

product_type
seller_id

seller_id is a foreign key of user.user_id.

An example of product_type is "laptop".
I want to list all the names of users who are selling laptops.
(SQL Query)

SELECT user_name FROM user u
WHERE EXISTS (SELECT 1 FROM product p
                WHERE product_type = 'laptop'
                AND p.seller_id = u.user_id)


SSAS cube fact and dimension relationship I have a cube that all the tables has one row per ID and have measure in all of them except for the calendar dimension.

When I view the measures in excel from Table 1 with attributes from table 2, I have no issues, but when I view the measures from Table 2 with attributes from Table 1 it returns the same number for each value of the attributes:

BillingCycle        HCD Accounts For Review
7                     4067
14                    4067
30                    4067

How can I overcome this issue? Does this have anything to do with the direction of the relationship between the tables?

What you are seeing is a typical example of a missing dimension relationship.

Yes it has something to do with the relations in your dsv but easy to solve with dimension relationships.

If you add a dimension to a measure group/cube/fact table the dimension relations are automatically generated based on the relations in the data source view, if a relation isn't present a dimension relation has to be created manually.

So in your example you need to go to the cube where the measure group is residing and select dimension relationships and define the relation between your dimension key and the fact table.

See defining dimension relationships for details on how to do exactly that.

It's actually fairly obvious why SSAS would behave like that. If there is no relation between axis and figures, there is no way a slicer can have impact on the figures.

If you are using a Tabular model, unless it lets you add relations in both directions I fear you'll be out of luck and need to design your data warehouse better using actual fact tables and dimension tables.