Validation of Partitioned table Performance

Why would you expect performance to be different with partitioning? Partitioning improves manageability but performance depends greatly on the workload and queries.  For example, if you have a useful index on the DateColumn, you will likely get the same performance with and without partitioning.  With no index on the column, performance may be better with partitioning as long as only the needed partitions are scanned due to elimination.  But if an index can prevent the scan, that would provide the greatest performance benefit.


Oracle pivot on a column with delimited data

Using the code from this link as a starting point, this should give you the result without the need for a DISTINCT:

select t.keycol,
    regexp_substr ( t.col1, '[^;]+', 1, n) as col1,
    t.col2,
    t.col3
from yt t
cross join
(
    select level n
    from
    (
        select max(length(col1) - length(replace(col1, ';'))) as max_semi
        from yt
    )
    connect by level <= 1 + max_semi
) c
where c.n <= 1 + length(col1) - length(replace(col1, ';'))
order by t.keycol, col1;

See SQL Fiddle with Demo


SQL statement slower on as400 console than from remote odbc

The problem was something simple yet hard to find.

The indexes' sort sequence was wrong.

The clients were wrongly configured to use language preference for sort sequence.

So when the indexes were created they had language specification for sorting.

The server when a job was submitted, was looking for indexes with *HEX specification so he was ignoring the existing indexes.


Let's just look at TableOne, TableTwo, and TableFour
Most of the condition in the "in where" in are already covered in the join
And suggest you use join on for all

Select TableTwo.Column1, DatabaseTwo..
from TableOne
inner join TableTwo  on TableOne.Column4 = TableTwo.Column4
inner join TableFour on TableOne.Column6 = TableFour.Column6
where 1 = 1
--- Section 2
and TableOne.Column6 in  (Select TableOne.Column6
                            from TableOne, TableTwo, TableFour
                            Where TableOne.Column4 = TableTwo.Column4  -- covered in base join
                            and TableOne.Column6 = TableFour.Column6   -- covered in base join
                            and TableFour.Column16 >= @StartDate
                            and TableFour.Column16 <  @EndDate )

Pretty sure this is the same query
Not sure if it is faster but I doubt is is slower
The query optimizer has a better chance of filtering early

Select TableTwo.Column1, DatabaseTwo..
from TableOne
inner join TableTwo  on  TableOne.Column4 = TableTwo.Column4
inner join TableFour on  TableOne.Column6 = TableFour.Column6
                     and TableFour.Column16 >= @StartDate
                     and TableFour.Column16 <  @EndDate

as for

and TableFour.Column6 In (Select TableFour.Column6
                                from TableFour,TableEightteen,TableNineTeen
                                Where TableFour.Column7 = TableNineTeen.Column7
                                and TableEightteen.Column17 = TableNineTeen.Column17
                                and TableFour.Column10 = TableNineTeen.Column10
                                and TableEightteen.Column18 > TableFour.Column16
                                )

pretty sure that can be

join TableEightteen on  TableEightteen.Column18 > TableFour.Column16
join TableNineTeen  on  TableFour.Column7  = TableNineTeen.Column7
                    and TableFour.Column10 = TableNineTeen.Column10
                    and TableEightteen.Column17 = TableNineTeen.Column17


SQL Server 2016 on Microsoft Failover cluster

No. You can setup a two node failover cluster with the standard edition of SQL Server. Any more than that and you need the enterprise edition.

https://www.microsoft.com/en-us/cloud-platform/sql-server-editions


To be honest, with only 2 levels of data, it could be simply achieved with something like this:

drop table t1 purge;

create table t1
(
  id number,
  disable number,
  name varchar2(10),
  param number,
  parent number
);

insert into t1 values (1,  1,    'qwer', 11, null);
insert into t1 values (2,  null, 'qwer', 12, null);
insert into t1 values (3,  null, 'tyui', 3,  1   );
insert into t1 values (4,  null, 'wert', 4,  1   );
insert into t1 values (5,  null, 'erty', 5,  1   );
insert into t1 values (6,  1,    'asdf', 21, null);
insert into t1 values (7,  1,    'asdf', 22, null);
insert into t1 values (8,  null, 'ghjk', 6,  7   );
insert into t1 values (9,  null, 'hjkl', 7,  7   );
insert into t1 values (10, null, 'jklp', 8,  7  );
insert into t1 values (11, null, 'asdf', 23, null);
commit;

I added another row, so 'asdf' has a third entry and we expect parent 7 to turn in to parent 11.

SQL> select * from t1;

        ID    DISABLE NAME            PARAM     PARENT
---------- ---------- ---------- ---------- ----------
         1          1 qwer               11
         2            qwer               12
         3            tyui                3          1
         4            wert                4          1
         5            erty                5          1
         6          1 asdf               21
         7          1 asdf               22
         8            ghjk                6          7
         9            hjkl                7          7
        10            jklp                8          7
        11            asdf               23

And the update:

update t1 set parent =
(
  select new_parent_id from
  (
    select id, name, disable, parent, lead(id) over (partition by name order by id) as new_parent_id , row_number() over (partition by name order by id desc ) as r from t1
  ) new_parent
  where disable = 1 and parent is null and r = 2 and new_parent.id = t1.parent
)
where t1.parent in (select id from t1 where disable = 1);

6 rows updated.

The result:

SQL> select * from t1;

        ID    DISABLE NAME            PARAM     PARENT
---------- ---------- ---------- ---------- ----------
         1          1 qwer               11
         2            qwer               12
         3            tyui                3          2
         4            wert                4          2
         5            erty                5          2
         6          1 asdf               21
         7          1 asdf               22
         8            ghjk                6         11
         9            hjkl                7         11
        10            jklp                8         11
        11            asdf               23

The above statement updates rows with disabled parents, and finds the new parent value within the subquery. Notice how 6 rows were updated, but the subquery was executed only twice, once per parent value, not for every row.

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |      |      1 |        |      0 |00:00:00.01 |      38 |       |       |          |
|   1 |  UPDATE                   | T1   |      1 |        |      0 |00:00:00.01 |      38 |       |       |          |
|*  2 |   HASH JOIN SEMI          |      |      1 |      6 |      6 |00:00:00.01 |      10 |  1344K|  1344K|  788K (0)|
|   3 |    TABLE ACCESS FULL      | T1   |      1 |     11 |     11 |00:00:00.01 |       7 |       |       |          |
|*  4 |    TABLE ACCESS FULL      | T1   |      1 |      3 |      3 |00:00:00.01 |       3 |       |       |          |
|*  5 |   VIEW                    |      |      2 |     11 |      2 |00:00:00.01 |      14 |       |       |          |
|*  6 |    WINDOW SORT PUSHED RANK|      |      2 |     11 |     20 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   7 |     WINDOW SORT           |      |      2 |     11 |     22 |00:00:00.01 |      14 |  2048 |  2048 | 2048  (0)|
|   8 |      TABLE ACCESS FULL    | T1   |      2 |     11 |     22 |00:00:00.01 |      14 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PARENT"="ID")
   4 - filter("DISABLE"=1)
   5 - filter(("DISABLE"=1 AND "PARENT" IS NULL AND "R"=2 AND "NEW_PARENT"."ID"=:B1))
   6 - filter(ROW_NUMBER() OVER ( PARTITION BY "NAME" ORDER BY INTERNAL_FUNCTION("ID") DESC )<=2)


Best way to re-import large amount of data with minimal downtime

A solution I've used in the past (and have recommended here and on StackOverflow before) is to create two additional schemas:

CREATE SCHEMA shadow AUTHORIZATION dbo;
CREATE SCHEMA cache  AUTHORIZATION dbo;

Now create a mimic of your table in the cache schema:

CREATE TABLE cache.IPLookup(...columns...);

Now when you are doing your switch operation:

TRUNCATE TABLE cache.IPLookup;
BULK INSERT cache.IPLookup FROM ...;

-- the nice thing about the above is that it doesn't really
-- matter if it takes one minute or ten - you're not messing
-- with a table that anyone is using, so you aren't going to
-- interfere with active users.

-- this is a metadata operation so extremely fast - it will wait
-- for existing locks to be released, but won't block new locks
-- for very long at all:

BEGIN TRANSACTION;
  ALTER SCHEMA shadow TRANSFER    dbo.IPLookup;
  ALTER SCHEMA dbo    TRANSFER  cache.IPLookup;
COMMIT TRANSACTION;

-- now let's move the shadow table back over to
-- the cache schema so it's ready for next load:

ALTER SCHEMA cache TRANSFER shadow.IPLookup;
TRUNCATE TABLE cache.IPLookup;

-- truncate is optional - I usually keep the data
-- around for debugging, but that's probably not
-- necessary in this case.

This will be more cumbersome if you have foreign keys and other dependencies (since you may have to drop those and re-create them), and of course it completely invalidates statistics etc. and this, in turn, can affect plans, but if the most important thing is getting accurate data in front of your users with minimal interruption, this can be an approach to consider.


How to determine MAXDOP setting when using DTU in Azure SQL

DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O…. This means that there is no definitive measure of equating x DTU = x CPU, X Memory etc.

Since, you have Premium P11 tier - this means that you have 1750 DTUs. Based on this blog post - By Andy Mallon, below is the CPU-to-DTU-to-Service Tier mapping :

+--------------+------+---------------+
| Number Cores | DTUs | Service Tier  |
+--------------+------+---------------+
| 1            |  100 | Standard – S3 |
| 2-4          |  500 | Premium – P4  |
| 5-8          | 1000 | Premium – P6  |
| 9-13         | 1750 | Premium – P11 |
| 14-16        | 4000 | Premium – P15 |
+--------------+------+---------------+

You should use Query Performance Insight for Azure SQL Database to review and adjust the db workload settings. Since you have db scoped configuration as non default (0 is default) - set to 1, you will need to review it and potentially change it so that you can utilize all the cores.


MySQL Workbench - specify Server/User/Pass on command line?

You can use the query parameter and give it the name of one of the connections you have defined (similar for the admin parameter). This will open a SQL IDE tab with that connection.

Instead of a predefined connection you can also use the usual command line params -h127.0.0.1 -uroot -P3306 in conjunction with the query and admin parameters.

For details how this is parsed look in wb_utils_grt.py (connectionFromString(connstr)).

Update:

The documentation says:

  --query [connection|connection_string]:
     Empty: Open a query tab and prompts for a connection.

  Connection: Open a named connection.

  Connection_string: Create a connection based on the entered connection string, which should be in the form $USER@$HOST:$PORT.


How to make distributor highly available in Always On Configuration

Put the distributor in a virtual machine, and use the hypervisor's high availability tools. This lets you survive host hardware failure. OS problems will still cause issues, though, so for an additional level of protection, you can run clusters inside virtualization.


How can I email query results formatted in html from MSSQL 2000 server?

It looks like you're missing an apostrophe from the end of the @body_text parameter.  This fixes that problem:

@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered
DESC';

exec master.dbo.xp_smtp_sendmail
    @server = 'server',
    @from = 'administrator@mydomain.com',
    @to = 'me@mydomain.com',
    @subject = 'HTML Testing...',
    @type = 'text/html',
    @message = @body_text;

Also, You might consider using xp_sendmail from SQL Server 2000.  The syntax is:

EXEC xp_sendmail @recipients = 'recipients'
      , @message = 'message'
      , @query = 'query'
      , @attachments = 'attachments  ;...n '
      , @copy_recipients = 'copy_recipients  ;...n '
      , @blind_copy_recipients = 'blind_copy_recipients  ;...n '
      , @subject = 'subject'
      , @type = 'type'
      , @attach_results = 'attach_value'
      , @no_output = 'output_value'
      , @no_header = 'header_value'
      , @width = width
      , @separator = 'separator'
      , @echo_error = 'echo_value'
      , @set_user = 'user'
      , @dbuse = 'database'

Of course, xp_sendmail requires an Outlook profile be setup on the server, which might be prohibitive.


How to determine column character set used on creation?

This shows the collation and character set for one table:

SELECT column_name,
       character_set_name,
       collation_name
FROM information_schema.columns
WHERE table_name = 'your_table_name'


How can I set up a read only odbc connection?

Create a user in SQL Server with only the perms you want them to have.
Set up the user ODBC string to use this credential
Profit :)


string search in table with partial text and without all field names

Build a string consisting of what you want to match against, and test against that. It could be as simple as casting the whole row to text:
testdb=# create table t(userid integer, uname text, native text, place text);
CREATE TABLE
testdb=# insert into t select 1, 'Richard', 'Italy', 'San Francisco';
INSERT 0 1
testdb=# insert into t select 2, 'Elon', 'Europe', 'New York';
INSERT 0 1
testdb=# insert into t select 3, 'John', 'Vietnam', 'Italy';
INSERT 0 1
testdb=# select t::text from t;
                 t
-----------------------------------
 (1,Richard,Italy,"San Francisco")
 (2,Elon,Europe,"New York")
 (3,John,Vietnam,Italy)
(3 rows)

testdb=# select * from t where t::text ~ 'It';
 userid |  uname  | native  |     place
--------+---------+---------+---------------
      1 | Richard | Italy   | San Francisco
      3 | John    | Vietnam | Italy
(2 rows)

Or, if you want to ensure that commas in the search string don't cause it to match more than one column, or if you need to omit any of the columns, use concat() to separate the values with unprintable chars instead.
testdb=# select concat(uname, e'\x01', native, e'\x01', place) from t;
              concat
-----------------------------------
 Richard\x01Italy\x01San Francisco
 Elon\x01Europe\x01New York
 John\x01Vietnam\x01Italy
(3 rows)

testdb=# select * from t where t::text ~ 'rd,It';
 userid |  uname  | native |     place
--------+---------+--------+---------------
      1 | Richard | Italy  | San Francisco
(1 row)

testdb=# select * from t where concat(uname, e'\x01', native, e'\x01', place) ~ 'rd,It';
 userid | uname | native | place
--------+-------+--------+-------
(0 rows)


Display a one-to-many relationship in one row

I could achieve your query in three steps. Assuming your table values are like the values listed below:

Step 1: In SQL View paste this query and save it as part1, This is for package_id transpose

TRANSFORM First([packing].package_id)
SELECT [packing].[product_id]
FROM packing
GROUP BY [packing].[product_id]
PIVOT [packing].package_id;

Step 2: In SQL View paste this query and save it as part2, This is for package_desc transpose

TRANSFORM First(packing.package_desc)
SELECT packing.product_id
FROM packing
GROUP BY packing.product_id
PIVOT packing.package_id;

Step 3: Join above two queries based on product_id to get your result


Should i place Registered-Users Info in every table? to Restrict user access to his Own Data only

For table-level access restrictions:

GRANT SELECT ON tbl1 TO 'user'@'...';
GRANT SELECT ON tbl3 TO 'user'@'...';

Repeat as needed for each combination of user and table.

If you have a newer version of MySQL, see if "Roles" provides a simplification.

For row-level access restrictions, do not let the users query the tables.  Instead:

Provide an app that they contact; it checks permissions before preforming (or not) the desired query.
Use Stored Procedures.  See also the function CURRENT_USER()
Use a VIEW -- then grant access to the view but not the table; have the view check whether the user is allowed to see the row.  See OP's comment.


The usual reason for sharding is that your workload has exceeded the resources of a single server, so the expectation is that you would not be running mongodump via mongos for a full sharded cluster backup. Backups are instead done by stopping the balancer and then backing up a config server as well as a mongod from each shard.

As at MongoDB 3.0, mongodump does not provide an option to specify read preferences or tags. If you have specific secondaries in your DR data centre for backup purposes, you can use these mongod nodes in your backup procedure.

Recommended backup strategies are included in the MongoDB manual:

Stop the balancer and backup all components of the sharded cluster with mongodump
Stop the balancer and backup all components of the sharded cluster with file system snapshots

Backups with filesystem snapshots are faster to complete (and to restore) because they include all data and indexes. Backups using mongodump export the data and index definitions, but indexes will have to be rebuilt as part of the mongorestore procedure.

FYI, there is an open feature request you can watch/upvote in the MongoDB issue tracker: TOOLS-630: Allow specifying readPreference (including tags).


postgresql replication - pg_stat_replication is showing empty columns

From the prompt, it looks like you are not connecting as a superuser. To view the address information about the connection, you must be connected as superuser.


SSAS Cube - Create measure based on the a dimension attribute

You can try:

create a Calculated Member (clic on Calculation tab and then on
New Calculated Member
define it with the MDX expression you want

In your case, it could look like that:

A more detailed example here.


Can I change which index a primary key uses?

There are some limitations/restrictions when modifying existing Primary Keys (PK), Clustered Indexes (CI) and Foreign Keys (FK).

General Information

When a PK is created and no CI exists, the PK will create a CI if no other index is referenced in the constraint definition.

  The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

Reference: Creating and Modifying PRIMARY KEY Constraints (Technet)

You can't drop/modify an existing PK if it is being referenced by a FK.

  A PRIMARY KEY constraint cannot be deleted if the following exist:

  If it is referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must be deleted first.
  The table has a PRIMARY XML index applied on it.

Reference: Creating and Modifying PRIMARY KEY Constraints (Technet)

Solution

You will have to drop the existing constraints (FK and PK) and/or indexes to create the solution you are aiming for.

This can be observed by reading the following articles:

How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005 (Stackoverflow)
How to change the primary key to be non-clustered? (Stackoverflow)
Modify Foreign Key Relationships (Microsoft Docs)
Creating and Modifying PRIMARY KEY Constraints (Technet)


You can use prepare statement for this and also need a delimiter for the procedure something as

delimiter //

create procedure table_create (in tablename varchar(100))
begin
 set @table_ = concat(tablename, '_meta');
 set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;");
 prepare stmt from @qry;
 execute stmt;
end;//

delimiter ;

Here is a test case in mysql

mysql> delimiter //
mysql>
mysql> create procedure table_create (in tablename varchar(100))
    -> begin
    ->  set @table_ = concat(tablename, '_meta');
    ->  set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;");
    ->  prepare stmt from @qry;
    ->  execute stmt;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> call table_create('test');
Query OK, 0 rows affected (0.08 sec)

mysql> describe test_meta ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| value | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


Spatial query to get enclosing radius/polygon of result set

Yes, using SQL Server's

"Static Aggregate Geometry Methods"
"Static Aggregate Geography Methods"

You essentially have three options

ConvexHull: returns a polygon that essentially wraps the input with a rubber band,
EnvelopeAggregate: returns a bounding box (polygon) that encapsulates the input (box = lines in a cartesian project, arc segments in a 3d/spheroid/geographic projection)
UnionAggregate: takes all of the inputs and composes them into a single complex-polygon

Note: you mention radius, a radius is a buffer over an input in GIS.

If you need more features, you should check out the free PostgreSQL and PostGIS which supports even more GIS functionality.


In generally, when you have a many to many relationship (one "thing" can be in many categories, one category can have many "things" in it), you have to set up a linking table to tie the two things together.

Each row in the linking table would have the ID of one row in the "thing" table, and of one row in the category table. So, many rows could be used to tie a given "thing" to its categories, and many rows could tie different "things" to a given category.

The thing_id and category_id columns in the linking table would be foreign keys to the "thing" and category tables (respectively).


How to confirm that chunk transfer is completed on a DB

To ensure there are no migrations in progress while taking your backup, you should stop the balancer via sh.stopBalancer(). Any in-progress migrations will be completed before the balancer is disabled.

If you want to ensure the balancer is stopped before taking your backup, you can run the following in the mongo shell, which should return true:

 !sh.getBalancerState() && !sh.isBalancerRunning()

After taking your backup, you need to remember to start the balancer again.


Suspended status doesn't tell you much; the wait type tells you the story. I would;

Download and Install sp_whoIsActive execute the below command while the processes are suspended to get the leading blocker, it's wait type along the lock graph. This should show the lock areas. (please upload)

exec sp_WhoIsActive
  @show_own_spid=1,
  @show_sleeping_spids=1,
  @get_task_info =2,
  @get_locks=1,
  @get_additional_info=1,
  @find_block_leaders=1,
  @format_output = 0

Setup a SQL blocking report trace. Here is the link with scripts to set up the blocked report SQL trace. Set the blocked process threshold to 20 secs or higher

Please upload these results for review


Database Schema design of server running status log

Time without date and timezone is pretty useless.

CREATE TABLE logz (
  server_inet inet,
  server_name text,
  app_name    text,
  is_running  bool,
  ts          timestamp with time zone
);

CREATE INDEX ON logz(server_name,ts);
CREATE INDEX ON logz(server_inet,ts);

Then to find the status of the server for each app.

CREATE VIEW running_status_by_name
AS
  SELECT DISTINCT ON (server_name,app_name) server_name, server_inet, app_name, is_running, ts
  FROM logz
  ORDER BY server_name, app_name, ts DESC;

CREATE VIEW running_status_by_inet
AS
  SELECT DISTINCT ON (server_inet,app_name) server_inet, server_name, app_name, is_running, ts
  FROM logz
  ORDER BY server_inet, app_name, ts DESC;

You could also use a pkid and easily do a CROSS JOIN LATERAL (OFFSET 0 LIMIT 1).


Based on what you've got here, it looks like you should be able to JOIN Matrix and Updates together based-on the "Name" field.  If you wanted to see all servers from Matrix that had updates, you could to something like this:

SELECT m.Name, m.Description, u.*
FROM Matrix m
INNER JOIN Updates u ON u.Name = m.Name;

That's based-on a few assumptions:

"Name" is your primary key in both tables.
"Name" is unique in the Updates table (no server would have more than one entry).

"What would you do?"

If it were me, I would create an Updates table that looked something like this:

table: Updates
Name varchar(16)
TimeFrame varchar(3)
Status varchar(10)

Row data would look like:

name     TimeFrame Status
Server01 Q1        Done
Server01 Q2        Incomplete
Server01 Q3        Incomplete

It would have a composite (primary) key of Name and TimeFrame to ensure uniqueness (because in this scenario, "Name" will be repeated).

The advantage here, is that if your time frame for updates is altered/increased at any point (ex: if you start doing updates every 2 months, instead of quarterly) you have the flexibility of adding more updates without having to add a column to the Updates table.  Of course, I'm saying that without knowing what the rest of the fields on Updates are, so maybe that wouldn't make sense.  Or there could be other opportunities for normalization/optimization as well.


How to distinguish free-standing composite types from table composite types in pg_type?

i believe i've found the answer - pg_class (which can be joined to pg_type ON pg_type.typrelid = pg_class.oid) has a column called relkind which provides:

r = ordinary table
i = index
S = sequence
v = view
c = composite type *** identifies free-standing composite type
t = TOAST table

This query identifies custom (free-standing) types:

select t.typname, t.typtype, t.typcategory, t.typrelid, c.relname, c.relkind
from pg_type t
left join pg_class c on c.oid = t.typrelid
where t.typtype = 'c' and c.relkind = 'c';


No

You can get a list of reserved words by typing help reserved words, but that's about it.


Cannot rename database (the user in --username is the owner)

You cannot rename a database you're connected to. I did not know that point or even considered it.


How can the size of the notification queue be configured?

I found this in async.c
 298 #define QUEUE_PAGESIZE              BLCKSZ
     ...
 300
 301 /*
     ...
 310  * The most data we can have in the queue at a time is QUEUE_MAX_PAGE/2
 311  * pages, because more than that would confuse slru.c into thinking there
 312  * was a wraparound condition.  With the default BLCKSZ this means there
 313  * can be up to 8GB of queued-and-not-read data.

This supports the notion that the queue size is not configurable without changes to the source and recompilation, as a_horse_with_no_name commented.


sp_Blitz error: "File '' either does not exist.."

There's a weird bug that only pops up on some instances of SQL Server when the Trace File path has problems. I can't diagnose those, but we took some steps to avoid that bug which are in the latest version of sp_Blitz.

Please head over to firstresponderkit.org to grab the newest version, and file an issue at the GitHub repo if you run into anything else.

Thanks!


Ypu can use hack attach method described by Paul Randal here:
hack-attach a damaged database

  To attach the database you need to fool SQL Server into thinking it’s
  already attached. The set of steps to go through are as follows:

  This only works if the server instance you’re using is the same
  version as the database you’re trying to attach

  Create a dummy database, with the same name, the same number of data    and log files, with the exact same file names (extremely
  important!)    and same file IDs (make sure you have instant file
  initialization    enabled so the file creations don’t take ages – see
  this blog post).    This can be tricky to do if you had added or
  removed files to the    database before it was corrupted, but you need
  to make sure the file    IDs are exactly the same.
  Set the dummy database offline (ALTER DATABASE RealDatabaseName SET    OFFLINE) or shut the server down
  Delete all the data and log files from the dummy database
  Drop in as many of the data and log files from the damaged database    as possible
  Set the dummy database online (ALTER DATABASE    RealDatabaseName SET    ONLINE) or start the server if you shut it down    for step 2

In your case you should delete both mdf and ldf files of newly created database and copy only your mdf to the file location.

Your database will be attached but cannot run recovery, so it will be marked as SUSPECT
Then you can try to put it into EMERGECY mode and extract as much data as possible.

Seee here an example: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database


How to execute this procedure in PL/SQL?

You are missing a semicolon:

VARIABLE x REFCURSOR
BEGIN
   dynamic_pivot_po(:x); -- here
END; -- and here
/


Can I determine the service pack for all my SQL Servers using SCCM?

Very quick and easy solution would be to setup a CMS (Central Management Server)
This enables you to run queries on groups of servers.
So if you make a production group, you can run a query on all of them at the same time
Using
SELECT @@VERSION

Would return you all the versions of the group.
If you're managing a bunch of different servers, a CMS is always a very helpful tool.


(summarizing my comments and putting as answer)

A query rewrite will solve the issue of getting low row estimates.  As Joe Chang explains in his blog post  Query Optimizer Gone Wild - Full-Text

  CONTAINS is "a predicte used in a WHERE clause" per Microsoft documentation, while CONTAINSTABLE acts as a table.

You get a much better plan (merge join) using CONTAINSTABLE vs the actual plan using contains uses a nested loop join with low row estimates.

You can rewrite the query as :

SELECT TOP 30 p.PersonId,
              p.PersonParentId,
              p.PersonName,
              p.PersonPostCode
FROM dbo.People p
left join containstable (ContactFullText, '"mr" AND "ch*"') cf on cf.[yourKey] = p.PersonId
WHERE p.PersonDeletionDate IS NULL
      AND p.PersonCustomerId = 24
      --AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
      AND p.PersonGroupId IN(197, 206, 186, 198)
      AND [RANK] > 0
ORDER BY p.PersonParentId,
         p.PersonName;


column auto updated after 24 hours in mysql

CREATE EVENT reset
    ON SCHEDULE
      EVERY 1 HOUR
        DO
update T1
set state=1
where time < date_sub(now(),interval 24 hour)
  and (state=0 or state=2) ;

it will run in every hour


How to determine if a mysql binary log file is empty?

When it comes to an empty binary log, it is version specific:

120 in MySQL 5.6
107 in MySQL 5.5
106 in MySQL 5.1
98 in MySQL 4.1/5.0

I have discussed this many times in the DBA StackExchange

Nov 19, 2014 : Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB
Jun 26, 2013 : Is it possible to convert mysql binlog from statement format to row format?
Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage
Feb 04, 2011 : MySQL master binlog corruption
All my posts where I discuss this

In your case, you need to run just one command

SHOW MASTER STATUS;

This tells you the current binary log mysqld is writing to and the postition. It just so happens, the log position is also the filesize.

Here is an example how to check for an empty binlog before launching a backup

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Find out the MySQL Version
#
SQL="SELECT VERSION()"
VERS=`mysql ${MYSQL_CONN} -ANe$"${SQL}"`
MYSQL_VERSION=`echo "${VERS}" | sed 's/\./ /g' | sed 's/ /./' | awk '{print $1}'`
case "${MYSQL_VERSION}" in
    "5.0") EMPTY_BINLOG_SIZE=98  ;;
    "5.1") EMPTY_BINLOG_SIZE=106 ;;
    "5.5") EMPTY_BINLOG_SIZE=107 ;;
    "5.6") EMPTY_BINLOG_SIZE=120 ;;
esac
#
# Get the Current Master Log's Position
#
SQL="SHOW MASTER STATUS"
LOGPOS=`mysql ${MYSQL_CONN} -ANe$"${SQL}" | awk '{print $2}'`
#
# Compare log position to an empty log size
#
OK_TO_BACKUP=0
if [ ${LOGPOS} -eq ${EMPTY_BINLOG_SIZE} ]; then OK_TO_BACKUP=1 ; fi
if [ ${OK_TO_BACKUP} -eq 1 ]; then
   ...
fi

GIVE IT A TRY !!!

SUGGESTION

If all your tables are using the InnoDB Storage Engine, you can dump the data at a point-in-time, capture the log file and position, and still allow INSERTs, UPDATEs, and DELETEs all at the same time (please do not do any DDL during the backup). When you use mysqldump with the options --single-transaction and --master-data=2, it will write the log file/position on line 23.

I just mentioned this 6 days ago : Master/Master/Slave Replication

That way, there is no need to wait for an empty binary log with no application downtime.


Is it possible to make an index for an OR query?

You could try splitting it into a union and indexing the two individual columns.

Your query would be

SELECT  *
FROM    Things
WHERE   person_1_id = 123
UNION
SELECT  *
FROM    Things
WHERE   person_2_id = 123

and index person_1_id AND person_2_id

Note we use a UNION rather than a UNION ALL so we don't get superflous records

Disclaimer:

I'm more experienced with SQL Server than any other platform, the above method can work in SQL Server but YMMV on other platforms


MySQL database column to Store Phone numberss

I would recommend the 3 column solution with area code optional. It will be able to handle cases of countries where mobile numbers do have area code (like the U. S.) and will be much easier to maintain and service.


How to get the daily amount of committed transactions on a given MSSQL database?

1) Below is a great script from Paul's blog  Script: open transactions with text and planswhich should get you going:

SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] ASC;
GO

You can schedule it accordingly via job to send you the results as per you're needs:

2) Also, you can refer to Adam Mechanic's WHO_IS_ACTIVE to get the details for same.

To gather the data from who_is_active you can log that to a table for later review as explained here How to Log Activity Using sp_whoisactive in a Loop


The materialized view is already optimal and cannot be tuned any further

If a materialized view is already fast refreshable, the DBMS_ADVISOR.TUNE_MVIEW procedure will return a message saying "the materialized view is already optimal and cannot be further tuned"

So there is nothing to do.


Case of irrecoverable error in transaction

Case 3 is the normal case for most reads in the database - think about it, the only data you're supposed to find in a database is committed data.

Case 4 is a problem because the transaction A (the first one) is reading data written by transaction B (the second), but B has not committed yet. If A acts on that data (and eventually commits), but B rolls back (or does another update on that data before committing), A will have "acted on" data that was never committed, i.e. never existed.

Scenario: Alice has $300 in her checking account, Bob has $250, negative balances are not allowed.

Transaction A: transfer $700 from Alice to Peter
Transaction B: transfer $500 from Bob to Alice

TB starts, update's Alice's balance with +$500 (then goes off to sleep). TA starts.

In case 3, TA only reads committed data. So it sees Alice's starting balance of $300, and will roll back when it realizes that Alice's account would be overdrawn. All good. You can run each step of those transactions in the order you want if you read only committed data, neither transfer will post.

In case 4, TA would read the uncommitted balance $800 from TB, do the transfer and commit - it believes Alice has the funds. System state is corrupt at that point. TB can't commit without breaking the "no negative balances allowed" rules. It can't rollback without also breaking that rule.


Convert rows into columns MySQL DataBase

SELECT  Table1.*,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ALTA') AS Name1,
    (SELECT Table2.Columna2 WHERE Table2.Columna4='ATENCION') AS Name2
    (SELECT Table2.Columna2 WHERE Table2.Columna4='CLIENTE') AS Name3
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FK_ID

is what you want.

But there are other ways to achieve this.


how to enalbe MySQL 8 innodb_dedicated_server option

(I wrote a bug report about that doc page.)

You do not have any other apps (Apache, Java, etc) running on the machine, correct?

Plan A:  Manually with innodb_buffer_pool_size = 1200M, I think 2G is too big for 4G of RAM.

Plan B:  Write a bug report.  In it provide your entire my.cnf and the exact version you are using (bugs.mysql.com).  Then abandon innodb_dedicated_server = ON.


Convert sql_variant to varchar to select left 2

Try explicitly converting the sql_variant column to varchar before applying the LEFT function.  The following simple example worked for me.

Declare @V sql_variant
set @v = '13.0.5026.0'
select left(convert(varchar(max),@v),2)

| (No column name) |
|------------------|
| 13               |


import csv file into mysql with custom data change

What's below works for your data - be careful of any variation in the field width - that's why delimited files are better - csv, or better still, if you can get it, pipe-delimited (|). It also successfully deals with the header line.

I did the following:

My code will do for both MD5 and SHA2 passwords - uncomment the relevant bits.

CREATE TABLE my_user
(
  user_id int not null auto_increment primary key,
  username varchar(11),
  p_word varchar(64), -- VARCHAR(32) for MD5
  e_mail varchar(75)
);

using a sample based on your data (users.csv)

$> more users.csv;
Username   Password   Email
test123    123        test@foobar.com
test456    567        sdfsd@blah.com
$>

And then after some Googling, I came up with this

LOAD DATA
INFILE '/mysql/mdb5.5/inst/users.csv'
INTO TABLE my_user
IGNORE 1 LINES
(@row)
SET username = TRIM(SUBSTR(@row,  1, 11)),
    -- p_word   = MD5(TRIM(SUBSTR(@row, 12, 22))),
    p_word = SHA2(TRIM(SUBSTR(@row, 12, 11)), 256),
    e_mail  = TRIM(SUBSTR(@row, 23, 80))
;

And voilà!

MariaDB [test]> select * from my_user;
+---------+----------+------------------------------------------------------------------+-----------------+
| user_id | username | p_word                                                           | e_mail          |
+---------+----------+------------------------------------------------------------------+-----------------+
|       1 | test123  | a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3 | test@foobar.com |
|       2 | test456  | 97a6d21df7c51e8289ac1a8c026aaac143e15aa1957f54f42e30d8f8a85c3a55 | sdfsd@blah.com  |
+---------+----------+------------------------------------------------------------------+-----------------+
2 rows in set (0.01 sec)


Commentary on this question:  Looking over this post I observe that your job was originally running as 'sa'.  It appears that the service account for your SQL Server was not given rights to the necessary file shares.

This is apparently what led to the job looking as if it were "Running" forever. Of course, nothing was actually happening.

It is a best practice to withhold giving the SQL Server service account rights to any non-essential folders. This helps keep the SQL Server environment from being exploited for unsafe activities.  (Much the same reason that the xp_cmdshell stored procedure is disabled by default.)

When you switched from sa to an account that had the needed rights to the file system everything worked.  Which was, of course, the right thing to do.

Scheduled SQL Agent jobs do sometimes hang (but look like they are still 'running') for a long time. Likely this is usually due to external issues, such as not getting access to file system.

As long as the SQL Agent believes the job is "running", it will not try to start the job again.

Simple lessons:

Think of 'sa' as ruling the SQL Server, but must beg for rights elsewhere.
When reviewing SQL Agent job history, be alert to jobs that have been running much too long. That usually means that the SQL Agent does not realize that the process has died.
Always plan to use a proxy account for SQL Agent jobs that need to access data or objects outside of the SQL Server. And make sure that the rights are granted to the Credential that the Proxy is using.

And, of course, every rule has exceptions.


Following discussions in chat, we've established:

32bit Enterprise SQL Server.
3.5GB of memory in use (no AWE/PAE).
2.1GB database with 1.3GB buffer pool.
Page life expectancy of 3 seconds.

Suggested fixes are:

AWE/PAE as a quick fix so the database fits in memory.
Investigate the frequently called, highest IO queries identified.
Consider rebuild to 64bit.


If you are using MySQL 5.5 or 5.6, you can accomplish this with SIGNAL.

Simplified example, inside the body of the procedure...

IF NOT EXISTS (SELECT * FROM users WHERE username = input_username AND password = input_password) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'authentication failed.';
ELSE
  -- normal code to select and return the username other info to the caller, etc.
END IF;

This will throw an exception:

ERROR 1644 (45000): authentication failed.

If you wanted to be more specific, you'd need to declare a variable to use a the MESSAGE_TEXT and populate it accordingly before calling SIGNAL because SIGNAL only accepts a variable -- not an expression -- as an argument:

DECLARE custom_error TEXT DEFAULT NULL;
...
SET custom_error = LEFT(CONCAT('authentication failed for user ',IFNULL(CONCAT("'",input_username,"'"),'NULL')),128);
...
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = custom_error;

Your code would probably take a different approach than IF ... EXISTS such as selecting INTO a variable and then testing that variable for null and then either returning it to the called with an unbounded SELECT or throwing the exception.

The reason for the LEFT( ...,128) is that this is the maximum length for an argument to MESSAGE_TEXT.  Overrunning it won't hurt anything but you'll end up throwing an error about the invalid argument instead of the error you intended.

For simplicity, the example assumes that the password is in cleartext which obviously wouldn't be the case.


Best practice to query administrative views for instance level elements

This is the classic question / fight over whether monitoring should have a monitoring agent installed on each server, or if it should be "agentless".

With an agent installed on each server, a process/script/etc. wakes up every so often, collects data, and reports it back to a central location (i.e. monitoring server).

With an agentless solution, the central server polls each monitored server/database/etc on a schedule to collect the information.  Cacti and Nagios typically use this solution.

There are pros and cons to each method.  There is not necessarily a best practice for which method to use, discussion usually just results in a holy war (similar to Emacs vs. vi, DB2 vs Oracle, ...).


Are you shrinking before backup in the hope that it will reduce your backup time?  If so that will not make any difference. But if you are doing it so it allocates less space on your target (when you restore) then it will help.

I did not understand if you are doing this every time before backup. If yes you want to resolve this permanently and not do this.

Now to answering your question.

Is the results of the shrink sitting out in tempdb somewhere ready to go?

It does not have to keep the result. It will check everything again once you restart.

To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea?

No, because it does 32 pages at one time and commit. See below.

Reference: https://blogs.msdn.microsoft.com/psssql/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000/

  Shrink works in transaction batches of ~32 pages.   Once 32 pages are
  moved below the truncation point the transaction is committed and a
  new transaction started.   This avoids an older issue with shrink*
  that kept a transaction open for a long time and caused the log files
  to grow because you can’t truncate past an active transaction.   It
  also makes shrink* restart capable.    If you cancel/kill a shrink*
  operation only the current active transaction is rolled back.   This
  means you could schedule a shrink* operation during a maintenance
  window and limit its runtime to that maintenance window.    You can
  restart it during the next maintenance window and it will pick up from
  where it left off.   The only disadvantage to this is that allocations
  could occur after the target once the shrink is terminated.   This may
  require shrink to do additional work for newly allocated space but
  this should be reasonably limited.

On a side note backing up 2TB should not take a full day. There are switches in native backup, can speed up/optimize your backup duration.


Seemingly preferred approach

I was under the impression that the following had been tested already by others, especially based on some of the comments. But my testing shows that these two methods do indeed work at the DB level, even when connecting via .NET SqlClient. These have been tested and verified by others.

Server-wide

You can set the user options server configuration setting to be whatever it is currently bit-wise ORed with 64 (the value for ARITHABORT). If you do not use the bit-wise OR (|) but instead do a straight assignment (=) then you will wipe out any other existing options already enabled.

DECLARE @Value INT;

SELECT @Value = CONVERT(INT, [value_in_use]) --[config_value] | 64
FROM   sys.configurations sc
WHERE  sc.[name] = N'user options';

IF ((@Value & 64) <> 64)
BEGIN
  PRINT 'Enabling ARITHABORT...';
  SET @Value = (@Value | 64);

  EXEC sp_configure N'user options', @Value;
  RECONFIGURE;
END;

EXEC sp_configure N'user options'; -- verify current state

Database-level

This can be set per-database via ALTER DATABASE SET:

USE [master];

IF (EXISTS(
     SELECT *
     FROM   sys.databases db
     WHERE  db.[name] = N'{database_name}'
     AND    db.[is_arithabort_on] = 0
   ))
BEGIN
  PRINT 'Enabling ARITHABORT...';

  ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;
END;

Alternate approaches

The not-so-good news is that I have done a lot of searching on this topic, only to find that over the years a lot of others have done a lot of searching on this topic, and there is no way to configure the behavior of SqlClient. Some MSDN documentation implies that it can be done via a ConnectionString, but there are no Keywords that would allow for altering these settings. Another document implies it can be changed via Client Network Configuration / Configuration Manager, but that does not seem possible either. Hence, and rather unfortunately, you will need to execute SET ARITHABORT ON; manually. Here are some ways to consider:

IF you are using Entity Framework 6 (or newer), you can try either:

Use Database.ExecuteSqlCommand: context.Database.ExecuteSqlCommand("SET ARITHABORT ON;");
Ideally this would be executed once, after opening the DB connection, and not per each query.
Create an interceptor via either:

DbConfiguration.AddInterceptor
DbInterception.Add

This will allow you to modify the SQL before it is executed, in which case you can simply prefix it with: SET ARITHABORT ON;. The downside here is that it will be per each query, unless you store a local variable to capture the state of whether or not it has been executed and test for that each time (which really isn't that much extra work, but using ExecuteSqlCommand is probably easier).

Either of those will allow you to handle this in one spot without changing any existing code.

ELSE, you could create a wrapper method that does this, similar to:

public static SqlDataReader ExecuteReaderWithSetting(SqlCommand CommandToExec)
{
  CommandToExec.CommandText = "SET ARITHABORT ON;\n" + CommandToExec.CommandText;

  return CommandToExec.ExecuteReader();
}

and then just change the current _Reader = _Command.ExecuteReader(); references to be _Reader = ExecuteReaderWithSetting(_Command);.

Doing this also allows for the setting to be handled in a single location while requiring only minimal and simplistic code changes that can be mostly done via Find & Replace.

Better yet (Else Part 2), since this is a connection level setting, it doesn't need to be executed per each SqlCommand.Execute__() call. So instead of creating a wrapper for ExecuteReader(), create a wrapper for Connection.Open():

public static void OpenAndSetArithAbort(SqlConnection MyConnection)
{
  using (SqlCommand _Command = MyConnection.CreateCommand())
  {
    _Command.CommandType = CommandType.Text;
    _Command.CommandText = "SET ARITHABORT ON;";

    MyConnection.Open();

    _Command.ExecuteNonQuery();
  }

  return;
}

And then just replace the existing _Connection.Open(); references to be OpenAndSetArithAbort(_Connection);.

Both of the above ideas can be implemented in more OO style by creating a Class that extends either SqlCommand or SqlConnection.

Or Better yet (Else Part 3), you can create an event handler for the Connection StateChange and have it set the property when the connection changes from Closed to Open as follows:

protected static void OnStateChange(object sender, StateChangeEventArgs args)
{
    if (args.OriginalState == ConnectionState.Closed
        && args.CurrentState == ConnectionState.Open)
    {
        using (SqlCommand _Command = ((SqlConnection)sender).CreateCommand())
        {
            _Command.CommandType = CommandType.Text;
            _Command.CommandText = "SET ARITHABORT ON;";

            _Command.ExecuteNonQuery();
        }
    }
}

With that in place, you only need to add the following to each place where you create a SqlConnection instance:

_Connection.StateChange += new StateChangeEventHandler(OnStateChange);

No changes to existing code are needed. I have just tried this method in a small console app, testing by printing the result of SELECT SESSIONPROPERTY('ARITHABORT');. It returns 1, but if I disable the Event Handler, it returns 0.

For the sake of completeness, here are some things that do not work (either at all or not as effectively):

Logon Triggers: Triggers, even while running in the same session, and even if running within an explicitly started transaction, is still a sub-process and hence its settings (SET commands, local temporary tables, etc) are local to it and do not survive the end of that sub-process.
Adding SET ARITHABORT ON; to the beginning of each stored procedure:

this requires a lot of work for existing projects, especially as the number of stored procedures increases
this does not help ad hoc queries


Help with CTE Aggregating Children Recursively

Unroll the hierarchy into a temporary table first (note the computed column):

CREATE TABLE #Items
(
    Id integer PRIMARY KEY,
    MPath varchar(255) NOT NULL,
    DisplayText varchar(30) NOT NULL,
    OnHandQty integer NOT NULL,
    [Level] integer NOT NULL,
    ParentId integer NOT NULL,
    MaxCanMake integer NULL,
    AvailToSell AS OnHandQty + MaxCanMake
);

WITH Items AS
(
    SELECT
        I.Id,
        MPath = CONVERT(varchar(255), I.DisplaySeq),
        I.DisplayText,
        I.OnHandQty,
        0 AS [Level],
        0 AS ParentId
    FROM dbo.Item AS I
    WHERE I.ParentId IS NULL

    UNION ALL

    SELECT
        I.Id,
        CONVERT(varchar(255), Parent.MPath + '.' + CONVERT(varchar(11), I.DisplaySeq)),
        I.DisplayText,
        I.OnHandQty,
        Parent.[Level] + 1,
        I.ParentId
    FROM Items AS Parent
    JOIN dbo.Item AS I WITH (FORCESEEK)
        ON I.ParentId = Parent.Id
)
INSERT #Items
    (Id, MPath, DisplayText, OnHandQty, [Level], ParentId)
SELECT
    I.Id, I.MPath, I.DisplayText, I.OnHandQty, I.[Level], I.ParentId
FROM Items AS I
OPTION (MAXRECURSION 0);

-- Useful index
CREATE INDEX i
ON #Items (ParentId, AvailToSell);

That gives us:

╔════╦════════╦══════════════════╦═══════════╦═══════╦══════════╦════════════╦═════════════╗
║ Id ║ MPath  ║   DisplayText    ║ OnHandQty ║ Level ║ ParentId ║ MaxCanMake ║ AvailToSell ║
╠════╬════════╬══════════════════╬═══════════╬═══════╬══════════╬════════════╬═════════════╣
║  9 ║ 0      ║ Laptop & Bag Kit ║         3 ║     0 ║        0 ║ NULL       ║ NULL        ║
║ 26 ║ 0.20   ║ Bag              ║        23 ║     1 ║        9 ║ NULL       ║ NULL        ║
║  8 ║ 0.5    ║ Laptop Kit       ║        11 ║     1 ║        9 ║ NULL       ║ NULL        ║
║  7 ║ 0.5.10 ║ Laptop           ║         5 ║     2 ║        8 ║ NULL       ║ NULL        ║
║  6 ║ 0.5.15 ║ Power Supply     ║         4 ║     2 ║        8 ║ NULL       ║ NULL        ║
╚════╩════════╩══════════════════╩═══════════╩═══════╩══════════╩════════════╩═════════════╝

Now compute MaxCanMake per level, starting with the deepest:

DECLARE @Level integer =
(
    SELECT MAX(I.[Level])
    FROM #Items AS I
);

WHILE @Level >= 0
BEGIN
    UPDATE I
    SET I.MaxCanMake =
        ISNULL
        (
            (
                SELECT TOP (1)
                    I2.AvailToSell
                FROM #Items AS I2
                WHERE I2.ParentId = I.Id
                ORDER BY
                    I2.AvailToSell ASC
            ),
            0
        )
    FROM #Items AS I
    WHERE I.[Level] = @Level;

    SET @Level -= 1;
END;

The computed column in the temporary table automatically reflects the change in AvailToSell.

The final display query is then:

SELECT
    DisplayText = REPLICATE('---', I.[Level]) + I.DisplayText,
    I.OnHandQty,
    I.MaxCanMake,
    I.AvailToSell
FROM #Items AS I
ORDER BY
    I.MPath;

╔════════════════════╦═══════════╦════════════╦═════════════╗
║    DisplayText     ║ OnHandQty ║ MaxCanMake ║ AvailToSell ║
╠════════════════════╬═══════════╬════════════╬═════════════╣
║ Laptop & Bag Kit   ║         3 ║         15 ║          18 ║
║ ---Bag             ║        23 ║          0 ║          23 ║
║ ---Laptop Kit      ║        11 ║          4 ║          15 ║
║ ------Laptop       ║         5 ║          0 ║           5 ║
║ ------Power Supply ║         4 ║          0 ║           4 ║
╚════════════════════╩═══════════╩════════════╩═════════════╝

dbfiddle


Syntax error when using exception inside for loop

As stated above in the comment, what I was missing was another BEGIN enclosing the exception.

Updated code:

CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT * FROM some_table LOOP
        BEGIN
        -- Statement 1..2..3..4
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm;
                ROLLBACK;
        END;
        COMMIT;
    END LOOP;

END;
$$;


In MySQL Workbench, insert a new column at the "top" of a table?

OK, I have found! It is so easy I am ashamed I didn't find earlier...

Plain old drag and drop.

Just add the column at the bottom, then move it with your mouse to the position you want.


MySQL - Merge or split datetime intervals (start date to end date)

First due you need to generate a series of dates, I'd suggest to use a calendar table.

CREATE TABLE if not exists calendar (
    mdate date PRIMARY KEY NOT NULL
);

INSERT INTO calendar values
('20170403'),('20170404'),('20170405'),('20170406'),('20170407'),('20170408');

How do they do it

Just to get overlapped activities I've used the query that you provided on your question.

create view overlaped_activities
as
SELECT name, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time
FROM (
    SELECT x.name, x.start, min(y.end) AS end
    FROM activities AS x
    JOIN activities AS y
        ON x.name = y.name
       AND x.start <= y.end
       AND NOT EXISTS (
           SELECT 1
           FROM activities AS z
           WHERE y.name = z.name
             AND y.end >= z.start
             AND y.end < z.end
       )
    WHERE NOT EXISTS (
        SELECT 1
        FROM activities AS u
        WHERE x.name = u.name
          AND x.start > u.start
          AND x.start <= u.start
    )
    GROUP BY x.name, x.start
) AS v GROUP BY name, end;

First I calculate minutes from start date till midnight:

if(date(start) = date(end),
  time_to_sec(timediff(end, start)) / 60,
  (1440 - time_to_sec(time(start)) / 60)) mstart

Then, if start <> end, I calculate minutes from midnight till end date:

if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) mend

This returns a table like this:

| start               | end                 |     mdiff |  mstart |    mend |
|---------------------|---------------------|----------:|--------:|--------:|
| 03.04.2017 11:00:00 | 03.04.2017 11:30:00 |   30,0000 | 30,0000 |       0 |
| 03.04.2017 23:45:00 | 04.04.2017 00:15:00 |   30,0000 | 15,0000 | 15,0000 |
| 04.04.2017 10:00:00 | 04.04.2017 11:30:00 |   90,0000 | 90,0000 |       0 |
| 05.04.2017 23:00:00 | 07.04.2017 00:45:00 | 1545,0000 | 60,0000 | 45,0000 |

That's nice, but there is another problem here:

| 05.04.2017 23:00:00 | 07.04.2017 00:45:00 | 1545,0000 | 60,0000 | 45,0000 |

Of course: 1545 <> 60 + 45

We need to generate a series of dates between start and end date, and add 1440 minutes to each day.

We can get it using the calendar table:

   select   name,
            mdate date_activity,
            sum(1440) minutes
   from     calendar
   join     overlaped_activities
   on       calendar.mdate > date(start)
   and      calendar.mdate < date(end)
   where    datediff(end, start) > 1
   group by name, mdate

Ok, we got all ingredients, it's time to cook the recipe:

select name, date_activity, sum(minutes) min_activity
from (
       select name,
              date(start) date_activity,
              if(date(start) = date(end), time_to_sec(timediff(end, start)) / 60, (1440 - time_to_sec(time(start)) / 60)) minutes
       from overlaped_activities

       UNION ALL

       select name,
              date(end) date_activity,
              if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) minutes
       from overlaped_activities

       UNION ALL

       select   name,
                mdate date_activity,
                sum(1440) minutes
       from     calendar
       join     overlaped_activities
       on       calendar.mdate > date(start)
       and      calendar.mdate < date(end)
       where    datediff(end, start) > 1
       group by name, mdate
    ) act
group by name, date_activity;

Final result:

| name |       date_activity | min_activity |
|------|--------------------:|-------------:|
| me   | 03.04.2017 00:00:00 |      45,0000 |
| me   | 04.04.2017 00:00:00 |     105,0000 |
| me   | 05.04.2017 00:00:00 |      60,0000 |
| me   | 06.04.2017 00:00:00 |    1440,0000 |
| me   | 07.04.2017 00:00:00 |      45,0000 |

Almost forget it, the recipe: http://rextester.com/EIJOI20983


How should I set up these tables in my relational database?

Looks pretty good to me.

I'd also put unique index on qOrder, and all the *Name fields.

http://dev.mysql.com/doc/refman/4.1/en/constraint-primary-key.html
http://dev.mysql.com/doc/refman/5.6/en/create-index.html


Prostrct create causes windows batch file to exit

prostrct is a batch file, so calling it from a batch file will terminate the current batch file.

To get around that you need to call it with a "call" command, like so:

call prostrct -list dbname

or whatever command you want to use.


You may check for NULL and nodes absence (exist method of xml type):

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0

Your XPath can be more specific, if necessary:

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('/BulletPoint/NoteTypeCode/text()') = 0


SQL Server Point in Time Restore Scenario

If the log has never been backed up, then it could have been shrunk or truncated at some point. otherwise, it would have just grown. It's also possible that it has changed to SIMPLE and back to FULL.

You have one option: try it...


Why aggregate operator used after unique index scan

This is a known SQL Server query optimizer limitation. It has been reported to Microsoft, but the Connect item (no longer available) was closed Won't Fix.

There are additional consequences of this limitation, including some that I wrote about in Optimizer Limitations with Filtered Indexes, the summary is quoted below:

  This post highlights two important optimizer limitations with filtered indexes:

  Redundant join predicates can be necessary to match filtered indexes
  Filtered unique indexes do not provide uniqueness information to the optimizer

  In some cases it may be practical to simply add the redundant predicates to every query. The alternative is to encapsulate the desired implied predicates in an unindexed view. The hash match plan in this post was much better than the default plan, even though the optimizer ought to be able to find the slightly better merge join plan. Sometimes, you may need to index the view and use NOEXPAND hints (required anyway for Standard Edition instances). In still other circumstances, none of these approaches will be suitable.


Create trigger to insert rows from one table to another with sum operation (group by)

First observation, FROM is usually before WHERE, i.e.

car_single = SELECT SUM(car_single) FROM shift WHERE ...

Second observation, INSERTnormally have the form:

INSERT INTO daily (day, month, ... )
VALUES (...)

These are the syntactical errors I find in your trigger. Logically I think there is more to do. Your trigger will insert a new row into daily every time a new row is inserted into shift, from your description that does not sound right. Here's what I would try to do (only a sketch).

CREATE TRIGGER `test`.`after_insert_shift`
AFTER INSERT ON `test`.`shift`
FOR EACH ROW
BEGIN
    UPDATE daily SET (car_single, car_return) = (
        SELECT SUM(car_single), SUM(car_return)
        FROM shift
        WHERE day = new.day
          AND daily.month = new.month
          AND daily.year = new.year
          AND ...
    )
    WHERE daily.month = new.month
      AND daily.year = new.year
      AND ...

    -- Check if number of rows updated = 0, if so insert a new row
    SELECT ROW_COUNT() INTO N;
    IF N = 0 THEN
        INSERT INTO daily (c1, c2, c3, ...)
        SELECT ...

Don't know if that's your problem, but I would start there.


How to speed up rebuilding a database from scratch/scripts

Applying schema scrips ends up being a lot of small transactions, and that can cause lots of log file waits.  You can try running the scripts in a transaction, but not all changes are compatible with transactions.

So an easy way make the scripts never wait on log flushes is to to turn on Delayed Durability while running the scripts.

EG

ALTER DATABASE current SET DELAYED_DURABILITY = disabled;

go

declare @i int = 0
while @i < 10000
begin

    exec ('
    drop table if exists mt;
    create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
    ')
    set @i += 1;

end

go

ALTER DATABASE current SET DELAYED_DURABILITY = forced;

go

declare @i int = 0
while @i < 10000
begin

    exec ('
    drop table if exists mt;
    create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
    ')
    set @i += 1;

end


Restoring a backup gives "Failed exit code 1"

This just means that the owner of the table public.auth_group_id_seq was set to the default owner in your RDS instance. This likely happened because you did not have that owner pre-created in the RDS instance prior to migrating so it utilized the default. This isn't particularly an issue unless you had specific security policies set in your PostgreSQL instance dependent on that owner that you need to replicate in your RDS instance.


How to perform FullText search on numeric column in MySQL to meet user requirements

When searching for a numeric string in the middle, FULLTEXT is the wrong approach.  LIKE is the only approach, and it requires a full scan of the plates.  Live with it.

But... How often do you need to search for the middle of a string?  Not often.  If you have the left part of a string, LIKE '234%' works quite efficiently; it is a "range".  But then you need a regular INDEX, not FULLTEXT.

FULLTEXT is aimed at words, not substrings, not full strings.  Don't use it for licence plates.

Don't use INT for a plate number.  It is not a "number" on which you perform arithmetic; that is what the MySQL Doc is talking about.  The plate number is merely a string that often contains only digits, so use a VARCHAR with the minimal collation.  In the 'west', plates are numbers and capital letters.  So, COLLATION ascii_bin is optimal.  However, that means fold to upper case both the plate numbers as they are stored.  Also fold any query strings.  At the same time, remove any punctuation (dash, heart, star, etc) that is not actually important to the comparison.

I cannot address the collation specifics for Bangladesh, but I suspect that utf8_bin would suffice.


Implementation of Rollback in PostgreSQL

Apart from some side tasks like releasing locks, deleting files and similar, the main activity is to set the two bits that correspond to the transaction in the commit log (in pg_xact) to “aborted” and sync the file to disk.

A rollback does not touch the table at all, and it should always be fast.
The newly created row version simply remain in the table (to be cleaned up later by autovacuum), they just become invisible because the transaction that created them has been aborted. Similar with deleted data: the marker that the row was deleted by the transaction simply becomes invalid.


How do I rescue SQL Agent jobs from a set of detached master, model, mdb files?

You will have to attach the MSDB DB under a different name like 'jobsdb' and pull the jobs out of jobsdb. The jobs will be stored under jobsdb.dbo.sysjobs and the job steps will be under jobsdb.dbo.sysjobsteps. They can be joined by the job_id column.

The schedules can be found under jobsdb.dbo.sysjobschedules joined with jobsdb.dbo.sysschedules on schedule_id.

The rows can be added into your current msdb using a insert-select, or by using the stored procedures: sp_add_job, sp_add_jobstep, sp_add_jobschedule, sp_add_jobserver.


Impact of adjusting Time of Mongodb server?

The impact of time adjustments depends on how significant the clock drift is between members of your replica set. Generally the most impactful outcome will be some confusing diagnostic information: operations during the affected period may appear to have improbably large (or small) durations.

If you are running any multiple server deployment you should always use a clock sync service (eg. NTP) to avoid potential clock drift. Generally services that make small, incremental adjustments to time (eg. ntpd) are preferable to utilities that make large adjustments (eg. ntpdate).

For more information on the potential outcomes of clock skew with MongoDB, see: MongoDB and Leap Seconds.


How to add multiple query results into one csv file?

I started by extracting the SELECT query into a separate SSMS window.  I replaced double-ticks (quotes) with single-ticks and added into dbo.TableForEmailingLogBackups that would allow me to create a real table to hold all of the results.

Note:  I had to change a part of your main query from WHERE sysdb.name = DB_NAME() to AND sysdb.name = DB_NAME() to get the query to work.  This is what that looked like:

SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as 'Size Meg'
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig'
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + ' minutes, '
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 'Yes' ELSE 'No' END AS isOlderThan24Hours
into dbo.TableForEmailingLogBackups
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = 'L'
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;

Then, I used the following code to truncate dbo.TableForEmailingLogBackups, insert the results of the stored procedure call EXEC master.dbo.sp_ineachdb and send the email. Note that you'll need to change the email part to send it to the appropriate email address

truncate table dbo.TableForEmailingLogBackups

insert into dbo.TableForEmailingLogBackups
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command =
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg''
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = ''L''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SqlServerEmailProfile',
    @recipients = 'recipients@.test.com;',
    @subject = 'Transaction log backup report',
    @query = N'set nocount on;select * from dbo.TableForEmailingLogBackups',
    @attach_query_result_as_file = 1,
    @query_result_header= 1,
    @query_attachment_filename = 'test.csv',
    @importance = 'High',
    @query_result_no_padding = 1,
    @query_result_separator = ','

A minute later, I received the email with the attachment.


Log file increases while differential backup is in progress

Now, the problem which started recently is whenever our scheduled History database's differential backup is progress log file of History database increases to 10+ GB and as soon as it completes everything gets normal.

So was transaction log backup also running before the differential backup job? Starting from SQL Server 2005 both Differential backup and log backup can be started simultaneously on SQL Server but transaction log truncation would be delayed until the diff backup completes that is why till differential backup is running the log file grows to write transaction after it completes the logs are truncated and space is reused. In your case Log backup would have run while differential backup was running and would not have been able to truncate logs but as soon as it finished the logs were truncated

Full backup and differential backup can delay log truncation because both needs to read some part of log to create consistent backup and if log backup was allowed to truncate the log file this would result inconsistent full and differential backup

This is similar SE thread I answered before.


Set Backup Compression on SQL Server 2008R2 on by default

Technet Online says:

  To view or configure the backup compression default option

  In Object Explorer, right-click a server and select Properties.

  Click the Database settings node.

  Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:

  If the Compress backup box is blank, new backups are uncompressed by default.

  If the Compress backup box is checked, new backups are compressed by default.

  If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.

from: http://msdn.microsoft.com/en-us/library/bb933863.aspx


What determines whether `pg_dump` includes a varchar length?

From the docco here, it says that

  The notations varchar(n) and char(n) are aliases for character
  varying(n) and character(n), respectively. character without length
  specifier is equivalent to character(1). If character varying is
  used without length specifier, the type accepts strings of any size.
  The latter is a PostgreSQL extension.

Note the bit in bold. Your email fields with CHARACTER VARYING without a length are like TEXT fields. This is not good practice - you really only should set fields to this if you really don't know the longest possible value. From the correct answer to this thread, it's 254 characters. This area is treated well in this thread (also check out the link within the correct answer).

[EDIT]

Of course, as @DanielVérité points out, these are not the same fields from the same table - my answer was predicated on the first being the email in table_x and the second being the email in table_y. If they're meant to be the same field in the same table, someone has made a mistake, perhaps not huge, but there's a problem.

It's better to have CHARACTER VARYING(254) or VARCHAR(254) for all email fields. That way, if you attempt to put more than 254 characters into the email field, PostgreSQL will throw an error:

test=# create table x(y varchar(4));
CREATE TABLE
test=# insert into x values('sadfsadfa');
ERROR:  value too long for type character varying(4)
test=# select * from x;
 y
---
(0 rows)

test=#


Because no sample data was given, the next query is untested:

SELECT
    ID_log,
    fecha_gestion,
    nitsec
FROM table t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.nitsec = t1.nitsec and t2.fecha_gestion < t1.fecha_gestion)


PostgreSQL replication with fsync disabled?

Turning fsync off does not prevent writes to WAL, it only prevents those writes from being explicitly synced.  So you can still use this to feed hot standby.  But if your database is so busy you need to do this, I wonder how well the hot standby can keep up with it.

Also, turning synchronous_commit off will get you much of the benefit without the corruption risk of turning fsync off.

Finally, anyone running such busy system should have the budget for some kind of nonvolatile write buffer, which would render fsyncs much less of a performance problem.


Index creation with multiple columns - should I combine as many as possible?

Based on those 3 specific recommendations, I would use

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [ClientId], [DateNextDeadline],[StringID])

The equality/inequality columns are the same, which is the bigger part.  SQL Server should use this for any queries that have all or a subset of those Included cols.

And of course, you run this through a rigorous set of test queries to verify :)


Your bcp format file has six columns defined, but the csv dataset and table have only five columns.

I was able to import your sample data successfully using the format file:

14.0
5
1       SQLBIGINT       0       8       ",\""     1     transaction_id      ""
2       SQLINT          0       4       "\",\""   2     transaction_no      ""
3       SQLINT          0       4       "\",\""   3     store_no            ""
4       SQLINT          0       4       "\",\""   4     register_no         ""
5       SQLDATETIME     0       8       "\"\r\n"  5     transaction_date    ""

This works regardless of whether the table columns are NULL or NOT NULL, though it is good practice to always specify this in your CREATE TABLE statements to avoid ambiguity.

That said, importing csv data prior to SQL Server 2017 can be difficult, or even impossible. From 2017 onward, you can use the RFC 4180 compliant:

BULK INSERT dbo.my_test
FROM 'C:\Temp\my_test.csv'
WITH (FORMAT = 'CSV');

See Using the Bulk-Load Tools in SQL Server by Erland Sommarskog for a comprehensive description of the topic, including the special problems posed by csv data.


SQL Server process suspended while executing simple SQL (one row deletes and selects)

Even though you're deleting one row, do you know the code that is being executed?  Could still be locking the tables you are deleting from.  Triggers that contain other triggers are notorious for this.

Cursors go row by row but they will put locks as well I believe so it might mean that tweaking the code is in order so that it's more set-based deletes and not row-based.


Logging in to phppgadmin remotely Ubuntu 14.04 Amazon EC2

So I received help on this issue from a colleague who is an experienced programmer.

In order to add a password to the page:

sudo nano /etc/apache2/conf-enabled/phppgadmin.conf

Then, modify the .conf file to include the following text within the  block (I placed it underneath the 'allow from all' line that I uncommented previously):

AuthUserFile /usr/share/phppgadmin/.htpasswd
AuthName "Restricted Area"
AuthType Basic
require valid-user

Save this file and exit.

Change directory to phppgadmin:

cd /usr/share/phppgadmin

Create password file (change 'your username' and get rid of triangle brackets):

sudo htpasswd -c .htpasswd <your username>

Enter password twice when prompted.

Restart the apache service and the page will now be password protected.

In order to add a superuser to access phppgadmin(again, (change 'your username' and get rid of triangle brackets):

sudo su - postgres
createuser -P -s <your username>

Again, enter password twice when prompted. This will now allow you to login to to phppgadmin.

Erwin's answer is also helpful too, it just seems as if he takes a different approach to solving the issue.  Although I did not use Erwin's method, it should accomplish the same thing.  However, I did not need to adjust listen_address in postgresql.conf in order to make it work.


You connect to each database in turn and query pg_extension.


How to copy data from table to another and automatically change the values to match the other's data type msql

Based on the information and SQL supplied, you would need to amend the statement as follows:

insert into table2 (
        name, state, dob, gender)
select
    name, state, dob,
    case gender
        when 'Male' then 1
        when 'Female' then 2
     end
from table1

Remember that NULL values will obviously be ignored.

MySQL CASE syntax

Since you will always only have one of two values in this query, you can also of course use the IF operator, depending of course on how you want to handle NULL values.

select name, state, dob, if(gender='Male',1,2)
from table1

or

select name, state, dob, if(gender='Male',1,if(gender='Female',2))
from table1


Mirroring after restoring the database on a new machine

Certificate based authentication of Database Mirroring is explained in How Does Certificate based Authentication Work. After your server rebuild you should had open the master database database master key (hic!) using OPEN MASTER KEY ENCRYPTION BY PASSWORD=... and then add the new server service master key encryption to it using ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. Then DBM would had access to the endpoint certificate private key and would successfully authenticate with the partner.

If you do not have the original master database master key password (shame!) then you need to force a new one, which may loose the DBM endpoint certificate private key encryption. If you also have encrypted the DBM endpoint certificate with a password then you can open it using that password and then add the new master key encryption to it, afterwards the DBM will successfully connect.

If you cannot recover the original DBM endpoint certificate private key then you need to replace it. You do not have to drop the endpoint, nor the mirroring related users and certificates. You can simply create a new certificate, then associate the new certificate with the existing endpoint using ALTER ENDPOINT ... FOR DATBASE_MIRRORING (CERTIFICATE = ...). On the partner(s) (including the Witness) you need to copy the public key of the certificate, create a new certificate for it in master and have it be owned by the user associated with the previous server certificate/user/login. This is the least disruptive way.

What you did is ultimately correct, but kinda of using the sledgehammer. Given the complexity of all the steps, I don't blame you for redoing everything from scratch. See also Replacing Endpoint Certificates that are near expiration.


MySQL is particular about the order in which you do things in a CREATE PROCEDURE/FUNCTION statement. I'll be burned if I can find it in the docs right now, beyond a cursory mention under DECLARE Syntax, but the order MUST BE

Header
Declare variables
Declare cursors
Declare handlers
Do actual stuff

With your INSERT ... SELECT in place, you're attempting to declare variables after you've begun "doing stuff", which isn't allowed. Move the INSERT ... SELECT after the last of your DECLAREs, and things should work much better.


Impact of using mirroring to migrate between SQL Server 2005 and SQL Server 2008 R2

Everything will move over just fine.  Just make sure that you don't fail over by accident ahead of schedule (I've seen someone do that) as there's no way to fail back.


Using Central Management Server to provide convincing performance statistics

How can I, only using Central Management Server, provide convincing
  performance related information for the individual servers managed
  with it?

Unless I've missed a key feature somewhere along the line, you can't. What does it give you?

Logical organisation of your servers and instances.
Policy Based Management can be applied to server groups.
Multi-server queries.

For performance data you'll want to look at installing the Management Data Warehouse alongside your Central Management Server. It isn't as useful as the various 3rd party tools for collecting SQL performance data but it's an improvement on roll-your-own Perfmon/SSRS/Excel solutions.

  I would like to use Central Management Server to go one step further
  by going to our customers and saying, if you did "this" then your
  website or application would be faster times X.

If only it were that simple. You're going to be on shaky ground if you offer this sort of advice to customers, especially if you're sending them a bill for the proposed upgrades. You can certainly offer some insight but not guarantees:

ServerX has a low page life expectancy, it would benefit from additional RAM.
ServerY has increased from an average 50% to 70% CPU in the past month. Has anything changed or have you seen an increase in visitors/customers? Perhaps you should plan for an upgrade.
ServerZ used an average 500IOPS at the start of the year, it's now reached 750IOPS.


Many NoSQL products have sharding built-in.  The DBMS itself looks after storing a particular key range on a certain server and keeping redundant copies for high availability.  Client connections are routed within the DBMS rather than in the application.  Multi-server scale out becomes easy, at the cost of CAP compromises.


How to JOIN each row ON NOT IN ( SELECT ... )?

You can use not exists clause to do this.

SELECT
    u. ID AS user_id,
    s. ID AS song_id
FROM
    songs s,
    users u
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            user_songs us
        WHERE
            us.user_id = u. ID
        AND us.song_id = s. ID
    )
ORDER BY
    1,
    2


What does "ALTER DATABASE ... SET NEW_BROKER" do? Is there anything to worry about?

Only if your database participates in distributed conversations. NEW_BROKER changes your service_broker_id. If you created any route with service name and service instance then it will become invalid as the route service instance value no longer reflect the destination service_broker_guid. It goes w/o saying that doing NEW_BROKER on a database that does have running conversations that span isntances (or even DBs in the same conversation), these conversations will have one half 'nuked' and the other half will be left stranded, unable to make any progress (will have to be cleaned up with END ... WITH CLEANUP or the other database will also have to be NEW_BROKER-ed).

Be aware that NEW_BROKER affects hidden conversations like the ones created by Event Notifications.

If everything you have is local, in the same db, then you have basically nothing to worry about.


SQL Server Backup - move to new server - Media Failure

As Andrew Loree suggested, this might be due to a 3rd party SQL backup product being installed on the source instance.

In order to find out what software was used to take the backup, you can run a RESTORE HEADERONLY on the .bak file from your SQL2012 instance. That should give you some information about how the backup was created.

RESTORE HEADERONLY FROM DISK='X:\file.bak'

If the above also fails, then there would be corruption, but you've already ruled out corruption via SHA1 checksums.


Is the chunk size a limit to the max data size that can be associated with a shard key value?

can a shard key span more than one chunk?

No. A chunk in MongoDB is a logical contiguous range of one or more shard key values. A unique shard key value cannot exist in more than a single chunk range, but many documents may have the same shard key. The cardinality for your chosen shard key determines the granularity of documents that can be represented in a chunk range (with the lowest granularity being a chunk that represents a single shard key value).

The relationship between shard key and chunks is illustrated in the following diagram from the MongoDB documentation explaining Choosing a Shard Key:

  Is the chunk size a limit to the max data size that can be associated with a shard key value

No. The chunk size determines the approximate size of documents expected to be represented by a chunk range (by default, 64MB). If a chunk range is observed to be approaching (or possibly exceeding) the configured chunk size, MongoDB will attempt to split that single chunk into multiple chunks representing smaller contiguous ranges of the shard key. Chunk splits update the sharded cluster metadata but do not involve copying documents between shards. MongoDB's sharded cluster balancer uses migration thresholds to determine when it is appropriate to migrate chunks and associated documents between available shards.

If MongoDB cannot split a chunk that exceeds the configured chunk size (for example, due to a low cardinality shard key), that chunk will be marked as jumbo. Jumbo chunks are problematic as they will lead to an imbalance of data distribution within your sharded cluster: the balancer will no longer attempt to split or migrate jumbo chunks.

The default chunk size works well for most deployments, however it is possible to modify the chunk size if there is a more appropriate setting for your deployment or use case. A smaller chunk size will lead to more frequent chunk split and migration activity; a larger chunk size will lead to more I/O for individual chunk operations such as migrations. The documentation for modifying the chunk size notes several considerations which are worth reviewing before changing this setting.


During data insertion, if MongoDB failed, can I try multiple retries? I have read somewhere it will retry only one time.

The Retryable Writes feature in MongoDB 3.6+ allows compatible drivers to automatically retry certain write operations that may be affected by a transient interruption like a network error or replica set election.

Drivers currently only make one retry attempt if the  retryWrites option is true. This feature is not designed for persistent network errors where multiple retry attempts are likely to be unsuccessful. The Retryable Writes specification addresses this question: Why are write operations only retried once?.

  What will happen if retry fail?

The driver will return a write exception which your application can handle. For example, you could choose to make several retry attempts. For more info on retry strategies, see: How To Write Resilient MongoDB Applications.

  Can I keep the failed record somewhere in server memory?

This is part of the exception handling logic in your application. A write resulting in an exception has not been successfully acknowledged by your MongoDB server deployment, so your application will have to decide how to handle appropriately for your use case. The original data/objects will still be available in your application context (subject to the normal scoping/lifecycle rules of your programming language).


What is the correct method to pass range of dates to a SQL Server 2016 temporal table query where you need a set returned for each day?

While SYSTEM_TIME AS OF allows the use of literals and variables, it apparently does not allow you to use a correlated query to pass in different dates from something like a date dimension table.

With that in mind, here is a solution that uses a multi-statement Table-Valued User-Defined Function (TVF).  It takes in a StartDate and EndDate and uses a while loop to populate the return table.  In general, multi-statement TVF's usually don't perform as well as inline TVF's, but since you said you could schedule this query and not have to run on demand, the performance may still be acceptable.

Here, we create a temporal table called Customer.  We load 1 row and then have a series of update statements spaced 2 seconds apart to reflect some history

set nocount on
IF OBJECT_ID('dbo.Customer') IS NOT NULL
BEGIN
    ALTER TABLE dbo.Customer
        SET (SYSTEM_VERSIONING = OFF);
    DROP TABLE DBO.Customer
    DROP TABLE DBO.CustomerHistory
END
GO

CREATE TABLE dbo.Customer (
    Id INT NOT NULL PRIMARY KEY CLUSTERED
    ,NAME NVARCHAR(100) NOT NULL
    ,StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    ,EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    ,PERIOD FOR SYSTEM_TIME(StartTime, EndTime)
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory))
GO

INSERT INTO dbo.Customer VALUES (1,'Bill_1')
GO
WAITFOR DELAY '00:00:02'
GO
UPDATE dbo.Customer SET Name = 'Bill_2'
GO
WAITFOR DELAY '00:00:02'
GO
UPDATE dbo.Customer SET Name = 'Bill_3'
GO
WAITFOR DELAY '00:00:02'
GO
UPDATE dbo.Customer SET Name = 'Bill_4'
GO

Now, we define the multi-statement TVF

CREATE FUNCTION dbo.TemporalDataAsOf (
    @StartDate DATETIME
    ,@EndDate DATETIME
    )
RETURNS @Table_Var TABLE (
    NAME VARCHAR(100)
    ,StartDate DATETIME
    ,EndDate DATETIME
    )
AS
BEGIN
    DECLARE @DateWork DATETIME = @StartDate

    WHILE @DateWork <= @EndDate
    BEGIN
        INSERT INTO @Table_Var (
            [Name]
            ,[StartDate]
            ,[EndDate]
            )
        SELECT [Name]
            ,[StartTime]
            ,[EndTime]
        FROM [Customer]
        FOR SYSTEM_TIME AS OF @DateWork

        SET @DateWork = DATEADD(Second, 1, @DateWork)
    END

    RETURN
END
GO

Notice that the WHILE loop is incrementing the @DateWork variable by 1 second (easier to demo), but you can defined the granularity as a day.

Also, remember that temporal Start and End dates are UTC, so you'll need to make adjustments for that.

I ran my example at 7:00am Eastern Time (United States) which converts to UTC time of 11:00am

SELECT * FROM dbo.TemporalDataAsOf('2017-10-24 11:00:00', '2017-10-24
11:00:20') ORDER BY StartDate

| NAME   | StartDate               | EndDate                 |
|--------|-------------------------|-------------------------|
| Bill_1 | 2017-10-24 11:00:04.290 | 2017-10-24 11:00:06.320 |
| Bill_1 | 2017-10-24 11:00:04.290 | 2017-10-24 11:00:06.320 |
| Bill_2 | 2017-10-24 11:00:06.320 | 2017-10-24 11:00:08.347 |
| Bill_2 | 2017-10-24 11:00:06.320 | 2017-10-24 11:00:08.347 |
| Bill_3 | 2017-10-24 11:00:08.347 | 2017-10-24 11:00:10.373 |
| Bill_3 | 2017-10-24 11:00:08.347 | 2017-10-24 11:00:10.373 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |
| Bill_4 | 2017-10-24 11:00:10.373 | 9999-12-31 23:59:59.997 |


How to reinstall a legacy DB2 Server 7.x for Linux?

DB2 certification guides, like this one on Amazon, often contain CDs with trial versions of the DB2 server. If you can get hold of one, install it on a fittingly ancient operating system, and roll back the clock to before the trial expiration threshold, you might have a shot at restoring your old backup.


SQL Server 2014 Availability Group failed

Ok I found what I was missed and it was the cluster because there is only two nodes in the cluster so I add third node and it is working now.


Count violated unique constraint caused by INSERT statement

Insert not needed for to count:

WITH
cte1 AS ( SELECT COUNT(*) cnt FROM table1 ),
cte2 AS ( SELECT COUNT(*) cnt FROM table2 ),
cte3 AS ( SELECT COUNT(*) cnt FROM table1 JOIN table2 USING (pkey) )
SELECT cte1.cnt "Records in table1",
       cte2.cnt "Records in table2",
       cte3.cnt "Conflicts count",
       cte2.cnt - cte3.cnt "Potential inserts count"
FROM cte1, cte2, cte3;

And insert only non-conflicted records - it will be less expensive.


MongoDB secondaries become unresponsive during replication

What you are observing is an expected effect of replication batches being applied on your MongoDB 3.4 secondaries. The original design intent of replica sets was to provide data redundancy and failover, so efficient application of replicated writes takes priority over secondary reads. Replicated writes are applied on secondaries in multithreaded batches organised for better write throughput, which means that the order of writes may temporarily diverge from the primary. To avoid out-of-order secondary reads during critical sections of write activity, read operations will periodically have to wait while a replication batch is being applied.

This will have a more noticeable impact during periods of high replication activity or where your secondary is generally having challenges keeping up with write activity.

The items you've highlighted are expected in MongoDB replica sets prior to 4.0:

Intermittent increased latency for  serverStatus commands or read operations on secondaries.
Since mongostat collects serverStatus output on a timed interval, the "no data received" indicates that a result wasn't returned before the next reporting interval.

Improvements in subsequent releases of MongoDB have enabled introduction of non-blocking secondary reads in MongoDB 4.0. If you rely on secondary reads and are impacted by latency under write load, it would definitely be worth planning an upgrade. For more information about the changes in MongoDB 4.0, see: Scaling Your Replica Set: Non-Blocking Secondary Reads in MongoDB 4.0.

For some general caveats on using secondaries for read scaling, see: Can I use more replica nodes to scale?.