Suddenly SQL server restarted with pending task count increased

I would start by saying that best person to analyze dump in MS guy or person who knows about it. I would just try to point out some basics from the log you posted

At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4
At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 pending task 165

Please see that taskwait time 256987 and pending task 165. This means the scheduler was hung and 165 tasks were waiting on it to get a scheduler and run. In this case MS SQL Server was not able to get out of this hung scheduler scenario and waited for a while but again it decided that it would be best to restart itself to come out of this hung scheduler scenario and hence it restarted. Now for reason why it hang is beyond my ability to tell with the information you have posted.
Also note a thread is assigned a scheduler and it should run on that scheduler and that is why so many threads were waiting on this hung scheduler.


How do I determine why a MongoDB document is failing validation?

As at MongoDB 3.2, there is no feedback on the reason document validation failed: the overall validation expression currently evaluates as either True ("OK") or False ("Document failed validation"). Validation behaviour can be adjusted with validationAction (error/warn) and validationLevel (strict/moderate/off) configuration options, but this does not provide any further context for validation failures.

If you want to have more detailed feedback, the recommended approach would be to add validation logic into your application rather than relying solely on server-side checks. Even with server-side validation, many checks are best done in application business logic to minimize round trips to the database server and provide more responsive feedback to the end user.

For example, user input for a web app (required fields, field formats, ...) should be validated in the browser before being submitted to your application or attempting to insert/update in the database.

However, it does make sense to validate at multiple levels to ensure data quality and some context to diagnose validation failures would be very useful. There is a relevant open feature request you can watch/up-vote in the MongoDB issue tracker: SERVER-20547: Expose the reason an operation fails document validation.

For more information you may also be interested in Document Validation - Part 1: Adding Just the Right Amount of Control Over Your Documents. This highlights some of the general pros & cons of document validation as at MongoDB 3.2, and includes a reference table for the outcome based on validationAction and validationLevel configuration options.


Calculating storage requirements of individual fields based on actual usage and properties?

Perhaps this is a good start - You'd have to calculate: each data type times expected row count. Of course this is approximate result due to format/and possibly padding overhead, but might be a start.


Automate Backup of SQL Server Database Structure

Since you are using SQL Server 20141 you could look at using DBCC CLONEDATABASE to create a statistics-only copy of the warehouse database.

See support.microsoft.com for details on the command; however it essentially boils down to:

DBCC CLONEDATABASE (warehouse1, warehouse2);

From the page linked above:

  All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail.

You could wrap this in code that drops the current clone, if one exists, then creates the new clone.  This could be added to a SQL Server Agent Job that executes nightly, or weekly, as required.

1 - you'll need service pack 2 or above for SQL Server 2014


How to Identify the user who issued MySQL Shutdown command

Check the secure log in the event it was run via a sudo service call

sudo grep mysql /var/log/secure

Check to see if there's any mysql related stuff going on in cronjobs

sudo grep mysql /var/log/cron

Check shell histories for mysqladmin calls

cd /home; for u in *: do; sudo grep mysql /home/$u/.bash_history; done

Check with people you know that either have sudo or mysql root access on this machine

"Hey, which one of you has been shutting down mysql?"


Store same sequence value in two columns

In order to not rely at all on currval, you might transform the INSERT queries in systematic way following this pattern:

To insert from a select, instead of :

INSERT INTO Test(columns) SELECT columns FROM...;

consider this form:

INSERT INTO Test(test_id, uid, columns)
SELECT pk,pk,columns FROM
(SELECT nextval('test_id_seq') as pk, columns FROM ...) AS alias;

For a single row in a VALUES clause, instead of

INSERT INTO Test(columns) VALUES(...values here...)

consider:

INSERT INTO Test(test_id, uid, columns...)
SELECT pk,pk,columns FROM
 (SELECT nextval('test_id_seq') as pk, ...values here...) AS alias;

for VALUES clauses with multiple rows, instead of:

INSERT INTO Test(columns) VALUES(...values1...),(...values2...)

consider:

 WITH v(columns...) AS ( VALUES(...values1...),(...values2...) )
 INSERT INTO Test(test_id, uid, columns)
 SELECT pk,pk,columns... FROM
  (SELECT nextval('test_id_seq') as pk, v.columns... FROM v) AS alias;

If PostgreSQL had a standard-compliant NEXTVAL, you could do without the subquery and just use something like:

INSERT INTO ... SELECT NEXT VALUE FOR seqname,
                       NEXT VALUE FOR seqname,
                       other columns...

and the NEXT VALUE FOR seqname would evaluate to the same value for both columns of the same output row, as mandated by the standard. But PostgreSQL does not have this construct.


Innodb: Operating System Error number 5 in MySQL

Given the log indication of starting recovery, then getting a failed read, this sounds consistent with a hard drive failure. You can see the same output with that as the diagnosis at http://forum.parallels.com/pda/index.php/t-70181.html and https://serverfault.com/questions/315664/mysql-wont-start, with the first answer in that second link showing a way to recover.


Recover two weeks old stored proc which has changed since

There's no way to recover the code unless you restore a backup of the server taken prior to when you updated the database with your change. But I would just check your source control repository and re-run the prior version.
This is why one generally keeps stored procedures, triggers, table definitions and the like in a source control system, like e.g. Git. That would prevent this sort of situation from happening again and provide many other benefits, such as versioning.


The post you linked to was mine. That was on an array, but the operator is the same for an object (||) concatentation.

Append a new item like {'d' : 4}

SELECT $${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}';

Update the key 'c' with the value 5

SELECT ($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || {"c":5}';

Upsert the already existing key 'd' with the value 6

SELECT (($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || '{"c":5}') || '{"d":6}';

End result,

{"a": 1, "b": 2, "c": 5, "d": 6}


Database design - load multiple excels daily

If the data needs to be clean and relational before being presented in your UI, I'd use SSIS to handle reading, cleaning, inserting your data into SQL Server, handling rejects, and archiving the input files.

If your data does not need to be clean/relational before presenting in the UI, then a NoSQL solution might work for you.

By the way, Excel is horrible to work with as an input file format.  You might want to see if you can receive the data as CSV or some other flat file format.


Restoring a database using backups that cover the same time periods multiple times

DIFFERENTIAL backups are there for one and only one purpose: to speed up restore by allowing you to skip one or more LOG backups. Namely, all LOG backups taken between the last FULL backup and the DIFFERENTIAL in question.

Since, in your example, you have no LOG backups between the FULL and DIFFERENTIAL, then there is no LOG backup to skip so there is no restore speed to gain. Attempting to restore the DIFFERENTIAL is a waste of time.

If, say, you'd modify the sequence like this:

t01 FULL database backup 1
t02 Log backup 1
t03 log backup 2
t04 Log backup 3
t05 log backup 4
t06 Database DIFFERENTIAL backup 1
t07 log backup 5

then it would be a benefit by allowing you to skip the restore of LOG backups 1-4.


how to get ALL the objects in a database that are NOT FOR REPLICATION?

Row Level Security Policies have a NOT FOR REPLICATION property too, allowing the replication agents to bypass the policy.  But I think that's it, at least those are the only objects with is_not_for_replciation column in the catalog.

select object_name(object_id) object_name, c.name column_name
from sys.all_columns c
where c.name like '%replication%'
order by column_name


Users groups/permissions when working with PostgreSQL

There are two basic approaches to users and groups.  I will also describe what approach I favor here and why.

The first approach is to use login roles to describe what software is logging on and restrict rights accordingly.  This has the advantage of being relatively simple to maintain and a number of disadvantages as well, but it is probably the most common approach because of its administrative simplicity.

The second approach, which I favor, is to give every user his or her own role.  DB permissions are then attached to groups (i.e. nologin inherit roles) which are granted to individual users.  This is more complex to administer.  Keep in mind that unlike Linux, PostgreSQL groups can be nested and unlike Windows, there is no deny acl, so permissions are always fully cumulative.  In some cases where someone needs a restricted account we can create a separate user.  This adds complexity to the administration process but it also helps mitigate security issues in the application because something like a sql injection attack is less likely to have privilege escalation possibilities, and it essentially allows outsourcing of authentication to the database, and PostgreSQL can outsource this to a variety of sources (RADIUS, PAM, KRB5, GSSAPI, SSPI, etc).


How to set the default timezone in PostgreSQL on Amazon RDS?

ALTER DATABASE <db_name>
  SET TimeZone = 'UTC';

But you shouldn't do that. Using UTC for database is safest thing possible.


Compilation error on stored procedure using PL/SQL in Oracle?

@kevinsky is correct, you cannot use avg as a variable name in PL/SQL, however that is not the only issue. You also cannot assign values to IN variables like m1, m2, and m3. My best guess is that you meant those to be local variables seeing as the first thing you do with then is SELECT INTO them.

test table:

create table students(
  sid integer
, marks1 integer
, marks2 integer
, marks3 integer
, total integer
, average number
, grade char(1)
);

your procedure errors:

select * from user_errors;

NAME                | TYPE      | SEQUENCE | LINE | POSITION | TEXT                                                                                | ATTRIBUTE | MESSAGE_NUMBER
:------------------ | :-------- | -------: | ---: | -------: | :----------------------------------------------------------------------------------------- | :-------- | -------------:
UPDATESTUDENTRESULT | PROCEDURE |        1 |   17 |        8 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        2 |   20 |       12 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        3 |   23 |       12 | PLS-00103: Encountered the symbol "=" when expecting one of the following:

   (
 | ERROR     |            103
UPDATESTUDENTRESULT | PROCEDURE |        4 |   32 |       12 | PLS-00103: Encountered the symbol "," when expecting one of the following:

   (
 | ERROR     |            103

your procedure errors after fixing use of avg keyword:

select * from user_errors;

NAME                | TYPE      | SEQUENCE | LINE | POSITION | TEXT                                                                                | ATTRIBUTE | MESSAGE_NUMBER
:------------------ | :-------- | -------: | ---: | -------: | :-------------------------------------------------------------------------------------- | :-------- | -------------:
UPDATESTUDENTRESULT | PROCEDURE |        1 |   12 |       34 | PLS-00403: expression 'M1' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        2 |   12 |       37 | PLS-00403: expression 'M2' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        3 |   12 |       40 | PLS-00403: expression 'M3' cannot be used as an INTO-target of a SELECT/FETCH statement | ERROR     |            403
UPDATESTUDENTRESULT | PROCEDURE |        4 |   12 |       43 | PL/SQL: ORA-00904: : invalid identifier                                                 | ERROR     |              0
UPDATESTUDENTRESULT | PROCEDURE |        5 |   12 |        1 | PL/SQL: SQL Statement ignored                                                           | ERROR     |              0

fixed procedure:

create or replace procedure updateStudentResult(id int,
tot out int,
v_avg out float,
grd out char)
as
  m1 int;
  m2 int;
  m3 int;
begin

select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;

tot:=m1+m2+m3;
v_avg:=(m1+m2+m3)/3;

if v_avg >= 85 then
grd:='A';

elsif (v_avg >= 65) then
grd:='B';

elsif (v_avg >= 50) then
grd:='C';

else
grd:='D';

end if;
UPDATE students SET
total=tot,
average=v_avg,
grade=grd
where sid=id;

end;
/

with no errors!:

select count(*) from user_errors;

| COUNT(*) |
| -------: |
|        0 |

dbfiddle here

If the grading rules are set in stone, you may wish to use virtual columns instead of updating them at all:

create table students(
  sid integer
, marks1 integer
, marks2 integer
, marks3 integer
, total integer as (marks1+marks2+marks3)
, average number as ((marks1+marks2+marks3)/3)
, grade char(1) as (case when (marks1+marks2+marks3)/3>=85 then 'A'
                         when (marks1+marks2+marks3)/3>=65 then 'B'
                         when (marks1+marks2+marks3)/3>=50 then 'C'
                         else 'D' end)
);

insert into students (sid,marks1,marks2,marks3) values (1,55,66,77);

select * from students;

SID | MARKS1 | MARKS2 | MARKS3 | TOTAL | AVERAGE | GRADE
--: | -----: | -----: | -----: | ----: | ------: | :----
  1 |     55 |     66 |     77 |   198 |      66 | B

dbfiddle here


How to setup Email alerts for Reporting jobs when it fails?

You are correct that is the best way for individual server.

Sample code for notification when a job fail.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @jobname=N'test',
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @notify_email_operator_name=N'sysadmin'
GO

You can send email for success/failure or both.

  0 Never

  1 On success

  2 On failure

  3 Always

Books online has details.

There are large shops where instead of setting up notification for each SQL Agent jobs, all failed jobs will be collected at xx minute interval and send one email/raise ticket in the form of a report.


Not able to connect to Mongodb vie mongo shell

I suspect Compass is falling back on the system trust store while the command line tool is not.
Capture the certificate presented by the server:
openssl s_client -connect some-url:27020 </dev/null > retrieved_cert.pem

And then validate using the CA certificate:
openssl verify -CAfile CA_INTERMEDIATE retrieved_cert.pem

If validation succeeds, you should see
retrieved_cert.pem: OK

If that doesn't work, take a look at the subject and issuer of each certificate, and ensure that the file passed to --tlsCAFile contains the self-signed root certificate at the head of the chain.


Replication from Azure SQL Database table to On-premise SQL Server possible?

In theory, Azure Data Sync does this, but it's been in "preview" status for years, and there's a lot of gotchas. Read the documentation carefully to see if its limits can work for you, and just know that in a preview program, Microsoft can end up removing support for it later.

A better solution would be something like SSIS jobs to keep the data in sync, but that's obviously not easy, either. As your tables change, you'll need to keep changing the ETL jobs.


On Elasticsearch, what is the required response for a directory URL of a HTTP URL repository?

I also asked this question in Japanese SO and got an answer.

The snapshot, when stored, generates some files which stores the names to the data files. Namely: index, metadata-THESNAPSHOTNAME, snapshot-THESNAPSHOTNAME (note snapshot-THESNAPSHOTNAME files are generated in several directories) . From these files the relative paths of the files are generated; thus no need for ls-ing to the directory URL.

So the answer to my questions would be:

No, my assumption was wrong
No need for any response to the directory URL: URLs are constructed by following the specific file's content.


SQL Server: How to add a full join to two tables but have matches line up?

You can use a FULL OUTER JOIN.

SELECT ISNULL(a.item_id, w.item_id) AS item_id,
    a.alternate_code_ERP, w.web_alternate_code
FROM alt_code_erp a
FULL OUTER JOIN web_alt_code w
    ON a.item_id = w.item_id
    AND a.alternate_code_ERP = w.web_alternate_code
ORDER BY 1, ISNULL(a.alternate_code_ERP, w.web_alternate_code)

I added an ORDER BY in case you need it.  It's still not exactly the same but without doing some string manipulation you aren't going to get an order of item_id like you want.


Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Ensure that the default undo tablespace has enough capacity.

Based on your output, I see 3 tablespaces with 2GB of size.
Perhaps one of them is the default assigned UNDO and auto extend is off.

Another prediction is that the default undo could be another tablespace with a different name, the query executed does not indicate the default undo but rather checks the tablespaces named with 'UNDO'.

You can check the default undo tablespace using the below query

select value from v$parameter where name like 'undo_tablespace';


SQL Server version numbers, when related to hotfixes, are they "inclusive"?

Unless you have access to the log files for every cumulative update install, the only thing you would know is #3:

  Hotfix 9.00.3182 has definitely been applied, there simply isn't enough information available to determine if the other 2 have been applied or not.

Cumulative updates are, well, cumulative. So are the builds within a CU branch. So 9.00.3182 will contain the fixes from 3179, 3178, 3165, 3122, 3068, etc. Microsoft used to release certain critical fixes individually, but now they have almost completely stopped doing that, opting for a ~60 day Cumulative Update delivery model (security fixes excepted).

The challenge comes when you pass a service pack boundary. There are cases where a fix is released in a cumulative update right before a service pack is released. The service pack code path was frozen at least 60 days earlier (since SPs go through more rigorous testing/regression etc). So, the service pack - even though released later - is missing fixes that won't be in that branch until the first cumulative update for that service pack. This is why you will often see a CU released out of band, almost immediately after a service pack is released.

Then, of course, there is the situation where multiple branches are being maintained, and multiple CUs come out for the lower service pack. There will be fixes there which weren't even known issues when the newer service pack was released.

This means that in many cases a newer build (say, 2005 SP4, 9.00.5000) is missing fixes from cumulative updates for SP3 (say, CU15, which was released several months after SP4). All of those fixes are usually also available in subsequent CUs for the later service pack, but just looking at version number alone wouldn't really tell you that unless you knew which fixes you are trying cross-reference. You'd need a database for that, not a numeric/string compare.

You're kind of playing with fire here, anyway. You're running SQL Server 2005, and not even on the most recent service pack. Unless you have gone through extended support, you are completely on your own. In fact 2008 and 2008 R2 both hit end of mainstream support in about 60 days.

My philosophy is to always be on the absolute latest service pack for any version I am running, and unless there is something prohibitive, always be on the latest CU for that service pack as well. So, rather than worry about whether the fixes for 3179 or 3178 were installed independently, I'd focus more on getting onto a more modern build (SP4 + MS12-070, putting you at 9.00.5324), or a more modern version (and one that isn't about to sunset).


how to prevent any update to my database before a exp operation?

From the command line

from the command line enter lsnrctl and then stop (stops the listener, only connections from the server possible)
connect to sqlplus as sysdba
shutdown immediate  ( makes sure all sessions are terminated)
startup restrict  (only allows users with dba privilege to log on)
as Mindaugas Riauba mentions disable all jobs likely to change data during the export
export your data
alter system disable restricted session
back to command line enter lsnrctl and start


Table Design - Identical tables for different statuses

In my experiences with similar things (tables of scheduled jobs and tasks), everything is in one table. There is usually  a status column, but jobs of all statuses are in the same table (sometimes there's an index on status). As for primary key, why not just use a sequential ID? I've seen that work very well. You can also have as combined key of draft number AND actual job number, unless there's some other requirement I'm not understanding.

If you think this will improve performance, make sure you have measurable problems that can only be solved this way before you make any changes. Also try to prove that this method results in actual necessary performance gains.


What is the proper way to assign the privileges when a MySQL instance has multi dba?

Mysql offers different ways of creating user .

For application A you can create a user with specific required roles and its better to give IP from respective DBA operates instead of giving % which allows user to enter in mysql from anywhere. With this user you can check performance of your queries and also explain part.
So you can use :
Grant select, insert, update,delete to user@IP on db_a.* Identified by 'password';

For subset user you need to declare which admin rights you want to grant like version and all. Roles will depend on your requirement.

User can be created with same command as above.
Let me know if more clarity is needed


Tools and techniques for untangling monster views (nested views)

You can enumerate nested views through this query:
SELECT DISTINCT
    sd.referenced_major_id,
    OBJECT_NAME(sd.referenced_major_id) as This_view,
    sd.object_id,
    OBJECT_NAME(sd.object_id) as need_this_view_to_work

FROM
    sys.sql_dependencies sd
WHERE
    EXISTS (SELECT 1 FROM sys.views v WHERE sd.object_id = v.object_id)
    AND EXISTS (SELECT 1 FROM sys.views v WHERE sd.referenced_major_id = v.object_id);


Segregating email ids of each customer (if more than one) by creating the new lines/records for that customer

CREATE TABLE mytable (
    NAME VARCHAR(26) NOT NULL PRIMARY KEY
    ,City VARCHAR(10) NOT NULL
    ,EMail VARCHAR(79) NOT NULL
    ,Contact VARCHAR(33) NOT NULL
    );

GO

INSERT INTO mytable
(NAME, City, EMail, Contact)
VALUES
('EFG', 'Chennai', 'eminent5sales@gmail.com;eminentyusuf@gmail.com', 'Mr. Y'),
('ABC', 'AHMEDABAD', 'priyalcorporation@gmail.com', 'Mr. N'),
('XYZ', 'Ahemadabad', 'Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com', 'Mr. J');
GO

select * from mytable;
GO

NAME | City       | EMail                                                                           | Contact
:--- | :--------- | :------------------------------------------------------------------------------ | :------
ABC  | AHMEDABAD  | priyalcorporation@gmail.com                                                     | Mr. N
EFG  | Chennai    | eminent5sales@gmail.com;eminentyusuf@gmail.com                                  | Mr. Y
XYZ  | Ahemadabad | Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com | Mr. J

 SELECT A.[NAME],A.[City],
     Split.a.value('.', 'VARCHAR(100)') AS [Email],[Contact]
 FROM  (SELECT [NAME],[City],
         CAST ('<M>' + REPLACE([Email], ';', '</M><M>') + '</M>' AS XML) AS [Email],[Contact]
     FROM  mytable) AS A CROSS APPLY Email.nodes ('/M') AS Split(a);
GO

NAME | City       | Email                        | Contact
:--- | :--------- | :--------------------------- | :------
ABC  | AHMEDABAD  | priyalcorporation@gmail.com  | Mr. N
EFG  | Chennai    | eminent5sales@gmail.com      | Mr. Y
EFG  | Chennai    | eminentyusuf@gmail.com       | Mr. Y
XYZ  | Ahemadabad | Dhruv_Patel@milacron.co      | Mr. J
XYZ  | Ahemadabad | mayur_a_patel@milacron.com   | Mr. J
XYZ  | Ahemadabad | Bharat_R_Parekh@milacron.com | Mr. J

dbfiddle here


Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?

There is nothing wrong (in principle) with a three way (or more) intersection table, as long as it properly describes your relation.

However: Your particular table is not in third normal form (3NF).

You should generally normalize to 3NF unless you have good reason not to.

The problem is that some of your data depends on part of the key, instead of on the whole key.  This means your table is only in second normal form (2NF).

Consider this:  Your table would allow a record that says a Prius is a Toyota and another record that says a Prius is a Chrysler.  Obviously that's not good.

You want to remove the hierarchy of make and model into a separate table.

Similarly, the type of a car is dependent on the model, but not on the colour.

So what you need to do is separate these columns too.  What you really need are four tables:

Manufacturers (manufacturer name)
Models (model name, FK to Types and FK to Manufacturers)
Types (type name)
Cars (FK to Models, colour)

This would give you a normalized schema with no redundancy and very little risk of data corruption due to insert, update and delete anomalies.


If you want the same result on both systems, your choices are:

If you always want to know the server-level Windows account in use, change your code to use ORIGINAL_LOGIN() in both places instead of relying on database mapping (or use SUSER_SNAME()).
Add your login to the sysadmin role on the production system.
Create a new login on your test system (or at least be sure you know the sa password), remove this login from the sysadmin role, and make sure they are no longer the database owner (e.g. ALTER AUTHORIZATION ON DATABASE::dbname TO sa;). You may then need to manually add them as a user and add permissions within the database (sorry, this is my least preferred option, and I didn't feel like messing up my own login on my system to test it).


MySQL set column value if another column's new value is different than current one

All expressions in the SET section are evaluated in the order in which they are written. So mycolumn in the second expression is equal to the value set in the first expression. Simply switch expressions - mycolumn_is_new = first.

  Is it possible check the new and old value in a single query?

Yes, using user-defined variable. Save old, assign new, use old from variable and new from field. The simplest task need those method - switch the values of two fields in one UPDATE.


SQL 2016 SQL Server Assertion: File: , line=951 Failed Assertion

Looking at the KB you have a couple of options / workarounds:

Switch to FULL recovery model.  You say "this isn't much of an option for a warehouse" but it's really just a matter of setting up the transaction log backups on a regular basis eg 15 minutes and then disposing of them.  SSIS / Maintenance Plans have stock tasks for doing this.  You will lose bulk-logged transactions, but I've never found these have made a great difference in runtimes, just log size.  You can even backup log to nul which I won't describe here.  If you're not sure what to do, ask you local DBA.  Disk space and transaction log backup retention are easier problems to solve than fatal errors.  When this problem eventually gets solved you can switch back.
The KB mentions "multiple BULK INSERT statements in a single-distributed transaction".  It's not clear from your question how your bulk inserts are set up.  Are you using SSIS to run 'Execute SQL' tasks which use the MERGE command?  What does 'multiple BULK INSERTs' mean here?  Is there a way to convert your approach to single BULK INSERTs, one at a time for example?  In SSIS you can set the 'MaxConcurrentExecutables' to 1 temporarily for example, see if that helps.  Tie it in to a config variable so you can change it back later.  Obviously it will slow things down but you prefer your ETL to finish rather than fail quickly.  Doing things in parallel is a good pattern and a real strength of SSIS, but you can only go as fast as your slowest component; say you have 10 dimensions which take a minute and one fact which takes an hour, your ETL finishes in an hour running parallel or 1 hour 10 minutes running serially.
MERGE is nice but does have a few issues.  You could consider converting back to INSERT/UPDATE.  Also you should be using HOLDLOCK with MERGE according to here.  Do you use that hint?  Does it make any difference to this issue if you do?  We had an issue in an early SQL 2014 build where using MERGE with composable DML (OUTPUT clause) into columnstore caused this kind of assertion - I made them take the columnstore indexes off the dimensions, which they had added without telling me.
What kind of transaction handling are you doing?  Sometimes with ETL the position is repeatable by just re-running.  Sometimes you need it to fail and rollback.  How have you implemented this?  Can be altered so it's not "single distributed transaction"?

Good luck.


Is my database secure? / What does "=Tc/postgres" allow?

=Tc/postgres means that postgres has granted CONNECT and TEMPORARY privileges to PUBLIC. You can revoke these privilege with :

revoke connect on database iproductiondb from public;
revoke temporary on database iproductiondb from public;

In general you should also check pg_hba.conf.


Enable slow_query_log tries to write to slow_log.CSV

I did these steps :

set global slow_query_log=0;
remove the slow log file in system
set global slow_query_log=1;

it works fine, so I tried these steps:

mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.12 sec)

mysql> set global slow_query_log_file='/tmp/slow.log';
Query OK, 0 rows affected (0.10 sec)

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

still works, and new slow log file is generated as /tmp/slow.log.

At last, the value of global variable log_output decides where to generate the general log and slow log's output, not the file format.


You mean like this:
mongodump --archive --db=test --port=27017 | mongorestore --archive --port=27018


Try to escape dash using the backticks like

GRANT ALL PRIVILEGES ON `bivv-acc`.* TO 'bivv-acc'@'localhost';

Check the manual which says:

  If an identifier contains special characters or is a reserved word,
  you must quote it whenever you refer to it.

  ...

  The identifier quote character is the backtick ("`")


Why set `group_concat_max_len` below the maximum?

As per MySQL BOL Here

  The maximum value for group_concat_max_len for 64-bit is
  18446744073709551615

  &

  The maximum value for group_concat_max_len for 32-bit is
  4294967295

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Note: The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.

As MySQL documented blog Here Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).

As MySQL Blog by @Shlomi Noach Here group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using GROUP_CONCAT more and more, recently, to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by max_packet_size. I would suggest, then, that this parameter should be altogether removed, and have the max_packet_size limitation as the only limitation. Otherwise, I'd like it to have a very large default value, in the order of a few MB.

For Further your ref Here & Here


Some of the implementation specifics depend on exactly what type of behaviour you need from this table (e.g. FIFO), but a typical design for a queue table uses DELETE with an OUTPUT clause:

DELETE TOP (n)
FROM QueueTable WITH (ROWLOCK, READPAST)
OUTPUT deleted.RecordID;

Or if ordered removal is required:

DELETE FROM
(
    SELECT TOP (n) RecordID
    FROM QueueTable WITH (ROWLOCK, READPAST)
    ORDER BY RecordID
) AS DF
OUTPUT deleted.RecordID;

If the records should remain in the table after processing, the table would have an extra columns to record the status (e.g. New, Processing, Processed) and probably the process id owning the row (if Processing or Processed). The de-queuing code is then modified to choose only records with the desired status. An alternative design is to DELETE rows and move them to a separate audit/archive table at the same time. As I mentioned, the specific solution depends on the precise requirements.


Replication throw an error after failover mirror partner is down in SQL Server

This should work as it did for me:

Set the -failoverpartner using the mirror name in the sql agents for replication.
Fail mirror over to secondary

Make sure replication continues to send commands to subscriber.

Remove mirroring so primary is no longer connected.

Go to distributor, add an alias (sql server alias)
The alias should be the old principal name pointing to the secondary server.

Replication will continue without errors, however jobs (and replication monitor) will show everything under the primary mirror server.


Difference between commit and checkpoint

A commit is a user initiated action that tells the database (Oracle in this case) that the transaction is completed and that the changes may be committed and any locks/resources released. Normally the changes are committed to in memory data buffers and to the redo log buffer.

A checkpoint though is a database initiated action that writes all of the data to the actual physical disk file based on the changes recorded in the redo log buffer. Some good articles can be found here and here.


How to avoid Repetition in the Database Design?

Track attributes against a product (always) and optionally against a retailer.  If the attibute value is used by all retailers then the retailer foreign key would be NULL.

When you join attributes to a product include an OR clause in either your join or where (depending on how you do your joining) so that you select out attribute values where the retailer FK is as given or where the retailer FK IS NULL.


SQL Server 2016 Standard Version Features Evaluation

Do you have an MSDN License?  Or could you start a 30-day free trial with Azure?  If so, you can easily set up a VM with SQL Server 2016 Standard edition installed:

This will help ensure no surprises with this or other Enterprise features.


Steps to apply windows updates to sql failover cluster?

You do not need to stop any jobs or services to manually failover. Please see the Microsoft Docs - Perform a Forced Manual Failover on manually failing over.
These are the general steps you should follow for applying any updates to your failover cluster instances:

Backup all databases.
Failover all SQL cluster roles to Node A.
Install the patches on Node B (passive).
Restart Node B.
Failover all SQL cluster roles to Node B.
Install the patches on Node A (passive).
Restart Node A

More information on the installing updates process can be found in these Microsoft Docs - Upgrade a failover cluster instance.


How do I return JSON structured as the transpose of json_agg() in PostgreSQL?

This will take care of the f1,f2,f3 name issue, but unfortunately can't think of an easy way for an arbitrary query.

 SELECT row_to_json(r) from
      (SELECT array_to_json(array_agg(count)) As count,
              array_to_json(array_agg(lead_source)) As lead_source
      FROM (
        SELECT count(lead_source), lead_source from leads_table group by lead_source
          ORDER BY count desc
    ) as a) AS r;

This one is not quite as short but will allow you to to put an arbitrary in your a subquery and output the columns. (so solving both issue 1 and issue 2).  It piggy backs on hstore so if you don't have hstore installed, you'll need to do a:
    CREATE EXTENSION hstore;

SELECT  ('{' || string_agg(sv, ',') || '}')::json
 FROM (SELECT ('"' || (kv).key || '"' || ':'
   || array_to_json(array_agg((kv).value) ) )   As sv
FROM (SELECT each(hstore(a)) As kv from
  (
   SELECT count(lead_source), lead_source from leads_table group by lead_source
      ORDER BY count desc
   ) as a) As r
 GROUP By (kv).key) As r2;


Is WITH RECURSIVE the right tool for my task?

Simpler and faster (and correct) with window functions and a subquery:

SELECT id, group_id, count(step) OVER (ORDER BY id) AS i
FROM  (
   SELECT id, group_id
        , lag(group_id, 1, group_id) OVER (ORDER BY id) <> group_id OR NULL AS step
   FROM   users
   ) sub;

id|group_id| i
--+--------+--
 1|       1| 0
 2|       1| 0
 3|       2| 1
 4|       1| 2
 5|       3| 3
 6|       3| 3

It's the same basic logic as discussed and explained in depth here (just simpler, without aggregation of rows):

Grouping or Window

There are also working solutions with a recursive CTE like you tried under the linked question, but those are substantially slower and more complex.

Andthere is also a plpgsql solution. Also more complex, but fastest.
Here is an optimized variant for your simple case without aggregation:

CREATE OR REPLACE FUNCTION f_users_i()
  RETURNS TABLE (id bigint, group_id bigint, i bigint) AS
$func$
DECLARE
   _last_group_id bigint;                 -- aux var to remember last group
BEGIN
   i := 0;                                -- init to 0

   FOR id, group_id IN
      SELECT u.id, u.group_id FROM users u ORDER BY u.id
   LOOP
      IF group_id <> _last_group_id THEN  -- next group starts
         i := i + 1;
      END IF;

      _last_group_id := group_id;         -- remember last group
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_users_i();

Same result.

dbfiddle here (Postgres 9.6).
SQL Fiddle (the same, based on your fiddle on Postgres 9.3).

Aside: I doubt you need bigint for users and groups. Can probably all be just integer.


When do i need to DEALLOCATE a prepared statement in a MySQL stored procedure?

[https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html] says:

  A prepared statement is specific to the session in which it was
  created. If you terminate a session without deallocating a previously
  prepared statement, the server deallocates it automatically.

  A prepared statement is also global to the session. If you create a
  prepared statement within a stored routine, it is not deallocated when
  the stored routine ends.

  To guard against too many prepared statements being created
  simultaneously, set the max_prepared_stmt_count system variable. To
  prevent the use of prepared statements, set the value to 0.

I would note run DEALLOCATE unless I have a large number of prepared statements close to the maximum (by default 16382 in MySQL 8.0).


Building Oracle XE docker image in Windows 10 Pro

Use Wsl Ubuntu Bash set path to Docker executables or simply install Git Bash for Windows from Docker Tools and run the shell script from Git Bash terminal(I prefer Git Bash).Download and Copy Oracle18xe rpm file to 18.40 folder

This is output from Git Bash for windows

 xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 (master)
    $ ls -lrt
    total 2513906
    -rw-r--r-- 1 user 197121         74 Dec 28 13:17 Checksum.xe
    -rw-r--r-- 1 user 197121       2675 Dec 28 13:17 Dockerfile.xe
    -rwxr-xr-x 1 user 197121       1137 Dec 28 13:17 checkDBStatus.sh*
    -rwxr-xr-x 1 user 197121        905 Dec 28 13:17 checkSpace.sh*
    -rw-r--r-- 1 user 197121        533 Dec 28 13:17 oracle-xe-18c.conf
    -rwxr-xr-x 1 user 197121       6132 Dec 28 13:17 runOracle.sh*
    -rwxr-xr-x 1 user 197121        775 Dec 28 13:17 setPassword.sh*
    -rw-r--r-- 1 user 197121 2574155124 Dec 28 13:48 oracle-database-xe-18c-1.0-1.x86_64.rpm

After running shell script successfully

xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 (master)
$ docker image ls
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/database     18.4.0-xe           19b38be42ae6        30 minutes ago      8.44GB
oraclelinux         7-slim              0d49b70bc432        9 days ago          118MB

xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles (master)
$ docker run --name myxedb \
>     -d \
>     -p 51521:1521 \
>     -p 55500:5500 \
>     -e ORACLE_PWD=xxxxxxx \
>     -e ORACLE_CHARACTERSET=AL32UTF8 \
>     oracle/database:18.4.0-xe

xxx@localhost MINGW64 /d/docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 (master)
$ docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                             PORTS                                                        NAMES
afce013adb98        oracle/database:18.4.0-xe   "/bin/sh -c 'exec $O…"   19 seconds ago      Up 16 seconds (health: starting)   8080/tcp, 0.0.0.0:51521->1521/tcp, 0.0.0.0:55500->5500/tcp   myxedb

You can connect to database via sqlplus or login into docker container using Windows Dos

    D:\>docker exec -it --user=oracle myxedb bash

[oracle@afce013adb98 /]$ . oraenv
ORACLE_SID = [XE] ?
The Oracle base remains unchanged with value /opt/oracle

[oracle@afce013adb98 /]$ sqlplus / as sysdba

    SQL*Plus: Release 18.0.0.0.0 - Production on Sun Dec 29 05:04:32 2019
    Version 18.4.0.0.0

    Copyright (c) 1982, 2018, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
    Version 18.4.0.0.0

    SQL> select banner from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

  P.S:-Make sure you logged into docker hub from git bash terminal
  docker login

To use WSL subsystem bash terminal add path and alias to .bashrc

export PATH="$HOME/bin:$HOME/.local/bin:/mnt/c/Program\Files/Docker/Docker/resources/bin:$PATH"
alias docker=docker.exe
alias docker-compose=docker-compose.exe

From WSL Bash terminal

xxxx@localhost:~# docker -v
Docker version 19.03.8, build afacb8b


Unable to remotely connect to Postgresql 9.5 instance

Turns out my hosting provider moved the server behind a managed firewall without notice.


How to determine the time out setting of mirroring?

The best practice is to be 10 seconds or over for timeout because it could trigger a false failure otherwise, the rest depends on your environment and your requirements for disaster recovery and high availability. If you set the number too high, it will take longer for the failover to occur when you do have an issue. The business needs should drive the results, the results should not dictate business needs.

Microsoft notes on the minimum.

SQL Sailor: Best practices and time outs for mirroring.

MSSQLTips: Greg Robidoux: DB Mirroring

SQL Central Discussion: Time outs for mirroring

To address what would be a good failover time, as I mentioned, it should be driven by your business requirements. Here are a few guides to developing those requirements:

Peak10: Carilyn Booker: DR and HA

Redgate: Joshua Feierman: DR and HA

Brent Ozar: DR and HA


On 9.3 you will have to rewrite your jsonb value.  From 9.5 you could use jsonb_set() to simplify the task.


fn_dblog is the way to look backwards as the other commentators have said.

Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This may mean that you'd need to make application changes depending on how they're currently accessing the data. Which may or may not be possible for you.

If you can't do that, then a quick and simple way with SQL 2005 is going to be using a trigger to do some DML auditing at the table level. (SQL Server 2008 onwards have auditing tools built in).

A simple solution for your issue might be:

drop table audit_test
go
create table audit
(
uname varchar(50),
[date] datetime,
what nvarchar(4000),
host varchar(50),
)
go

create trigger ddlcheck on tbl_example
for update, delete
as
declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp

This will fire any time a query attempts to update the table, or delete from the table. It uses DBCC inputbuffer ( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) to get the issued command. This gives you a table populated with all the update and delete statements issued against a particular table. Plus it records who issued statement, and where and when it was.

Now this could be a lot of logging data on a busy table, so this could be restricted to just catch 'bad' queries (say those that update/delete 1000+ rows) by altering the trigger to:

create trigger ddlcheck on tbl_example
for update, delete
as
declare @cnt integer
select @cnt=count(1) from deleted
if @cnt>1000
begin
  declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
  insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
  insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp
end

This logs less data, but the trigger still needs to 'evaluate' for every operation, so may have a performance impact which will need to be measured and tested. This will also miss the actions if the user submits lots of individual statements, ie;

won't catch:

delete from tbl_example where id=1
delete from tbl_example where id=2
.....
delete from tbl_exampe where id=1000

will catch

delete from tbl_example where id>0 and id<1001

Hope this is of some help for the future.


PgAdmin III - How to connect to database when password is empty?

I found this answer elsewhere.

If your DB is on the local host, try leaving the host field blank in the connection as opposed to using “localhost” or “127.0.0.1″. This tells PgAdmin to connect via the local unix socket instead of TCP.


Relations between a imeid, sim chip, and account from a cellphone company?

Here are the layers of abstraction that you need to go through to track who has (pays for) a GSM phone:

Note that this is good for a point in time.  If you need to track changes over time then everything becomes many-to-many with start/end dates in the intersection entities.


Foreign key subsumed by unique constraint in MariaDB/MySQL

I may have stumbled on a solution by trial and error -- correct me if I'm wrong please.

Seems that the FK needs some form of index to function. When creating the unique index, the FK's own index is taken over by the unique one, so that when the unique index is dropped later, the FK would remain "index-less". To prevent that from happening, seems like it is enough to add a normal index before deleting the unique:

ALTER TABLE `actors` ADD index (`user_id`);
alter table `actors` drop index this_subsumes_fk;

At least, with this I don't get an error anymore.


acl to allow nested groups in owner attribute

Almost as soon as I posted the question, I saw the dumb mistake I made -- I forgot to include the permission to be granted in the acl entry.  This entry is tested and works for users as owners, as well as groups and nested groups as owners (with a break-through so non-owners aren't rejected outright):

to dn.children="dc=Groups,dc=example,dc=com" by ssf=128 set="user & this/owner" manage by ssf=128 set="user & this/owner*/member*" manage by * break


Real time or close to real time with MySQL or MSSQL

My opinion is that polling in your case (10 seconds is OK to me) is better than the trigger solution. The trigger is closely coupled with your transaction, and can cause lots of un-expected issues that are hard to trouble-shoot. Personally, I have never used trigger to call external application (what if the application fails, long to start, stuck? )

A better (but more difficult solution) can be using service broker to engage with external application.


Updating multiple rows with different values in one query

UPDATE mytable SET
    fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
    drink = CASE WHEN id=1 THEN 'water'  ELSE 'wine'       END,
    food  = CASE WHEN id=1 THEN 'pizza'  ELSE 'fish'       END
WHERE id IN (1,2);

Personally, using CASE WHEN THEN END looks clumsy.

You could code this using the IF function.

UPDATE mytable SET
    fruit = IF(id=1,'orange','strawberry'),
    drink = IF(id=1,'water','wine'),
    food  = IF(id=1,'pizza','fish')
WHERE id IN (1,2);

Give it a Try !!!

CAVEAT : CASE WHEN THEN END is only handy when dealing with multiple values (more than 2)


The best practice for SQL + NoSQL database design

From comments:

This question is incredibly open-ended and has an entire branch of the industry devoted to it, namely data-architects and program-architects. The best thing to do is to pick ONE concept and stick with it. Thousands of users is a low-threshold for performance, so any solution will work. Are you expected to present a design or an implementation? Any database system can implement almost any sort of ERD, so just focus on a solid ERD.
There are plenty of options on what should happen here, and there are professional patterns that you may miss in your design (knowing how to build appropriate M:N relations based on experience) or things like audit tables that you may not be considering (we do a lot of this sort of thing post-academia, and know a lot of questions to ask stakeholders to ensure we build the right things). Just do the best work you can, and work with your advisor to know if it's enough for your diploma project. - jcolebrand

And one other suggestion from another comment:

You might like to try using PostgreSQL which has both classic RDBMS and a Document Store capability within the same database - indeed, you can join between classic tables and JSONB documents. Just a thought! – Vérace


We need to do encryption in transit in mongodb so which one is recommended ssl or tsl ? and what is the difference between ssl and tls?

TLS (Transport Layer Security) is the successor to the now deprecated SSL (Secure Sockets Layer). Early versions of TLS included some backward compatibility with SSL, but modern security protocols are now fully TLS. Older versions of SSL (and TLS) have known weaknesses and are not recommended (and often no longer supported).

The mongod and mongos configuration files have a net.ssl.disabledProtocols configuration option to disallow usage of some protocol versions for improved security. Starting in version 4.0, MongoDB disables the use of TLS 1.0 if TLS 1.1+ is available on the system.

The two protocols are often described as TLS/SSL since SSL is the older (and typically more familiar) reference.

MongoDB configuration parameters for TLS/SSL in MongoDB 4.0 include SSL in the name for historical reasons, but starting from MongoDB 4.2 there are new TLS options (which are renamed equivalents of the older SSL options) to provide clarity that the protocol is in fact TLS.


Figure out what IP address MySQL resolves a host name to

Alright.. It was wrong TCP port. Sorry :) So I solved my problem with RDS connectivity. But the second part still stands: MySQL server as a "DNS resolver". While dealing with RDS I tried to go with PostgreSQL, because if it were DNS problem it would affect PostgreSQL as well. I figured that I can do it in PostgreSQL with various extensions like plperlu. Unfortunately, all of them are blocked in RDS :)


SQL Server Setup: Report Server and RAM Considerations

Report Server executes the query and stores a copy of it within its cache. This is outside of the SQL Server process and so you will need to account for it when architecting your installation. Generally, the biggest winner I have noticed when I have done installations myself is the number of CPU's. RAM helps of course, but I have found additional CPU's to have the biggest gains - no point having 1TB of RAM if your request is stuck in a queue on a 2 core machine!

There is a very good (but lengthy) white-paper on MS-Docs called Planning for Scalability and Performance with Reporting Services which outlines the difference between different hardware setups, scaling and provides some very good advice on how to architect a SSRS solution. I would highly recommend that you check it out.


Oracle: changing a global temporary table to a normal table and vice versa

No, you cannot change from GTT to a normal table.  But, you could create another table to copy your sessions specific data to in order to preserve the information.

More on GTT's


MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance

By using "UTF8 thingies" I think that the problem here would be concepts. Let's review them:

Encoding (badly called character set in MySQL): utf-8 (utf8 and utf8mb4), utf-16 (utf16), 7-bit ASCII (ascii)... Those are the actual formats in which the characters are stored, transmitted or converted. They can use (in MySQL, from 1 byte to 4, and some are dynamic, while others are fixed in size)
Collation: these are the default algorithms that, when nothing is explicitly said, MySQL will use to compare 2 strings. the _bin one basically compare bit-to-bit, while the _ci and _cs will compare for a particular language, in a case-insensitive or case-sensitive way.

Now we have presented the concepts, let's apply them: First, you should use the most appropriate format for your application. If your application needs to store international names, you definitely want to use a multi-byte encoding. And given that even the most local business are globalised something like utf8 is practically a must. However, there are some cases in which has a lot of sense to use different encodings. Storing something like a car plate, may only require ascii, which not only is cheaper in size, but would prevent from other strange characters to get in. Other examples include Wikipedia, a high-traffic site that has to support perfectly tons of different languages. They decided (I suppose partly to overcome MySQL limitation, partly for efficiency) to store all the pages without encoding, in pure blobs.

Regarding efficiency: having the same encoding in all the tiers of your application is usually the best policy: you avoid unnecessary conversions back and forth. Smaller and fixed size encodings are supposed to be faster but never compromise functionality. Sometimes the bottleneck on encodings can be a slow conversion, sometimes on storing many zeros. Compression can always help with the last one, specially with text.

And now about collation: basically use the one you will need by default- it will affect the string comparisons and order of the sorts. Different collations do not change the bits for each row, but it (may) change the order between them (is 'a' before 'A' or are they equivalent?). Although you can force a different collation for particular queries manually:

mysql> SELECT mycolumn FROM mytable WHERE mycolumn LIKE 'a%' COLLATE utf8_bin;
mysql> SELECT mycolumn FROM mytable ORDER BY mycolumn COLLATE utf8_bin;

Why you may have seem many people saying that the _bin version may be faster? Well, because it only has to compare bits, and not have into account the particularities of each language. If you are not going to ever do comparisons in which a == A and even a == A == á == ä, use the binary collation. But in those cases, you probably do faster by not storing text in char/varchar/text, but in the binary equivalents: varbinary/binary/blob. Of course, if you want to get the advantages of storing characters and not bytes, like getting those comparisons done automatically done for you, use utf8_general_ci or utf8_unicode_ci, which will work for most languages well.

Given that most of your data is ASCII, the size in utf8 shouldn't have changed much. Check your performance- I have suffered some weird utf8 bugs, but usually this shouldn't be a common problem unless having to deal with lots of 4-byte utf-8 characters. Given that most of your data is ascii-like, and that cpu is usually not the bottleneck (and IO will probably won't change), you may not see any change, so use the combination that better fits your needs. But test it before and after to see the change.

Remember that there are several configurations on mysql: the character set for the connections, the one for the table storage and the one in which they are sent back to the client. You can check both the encoding and the collations of a particular session by doing:

mysql> show session variables like '%character\_set\_%';
mysql> show session variables like '%collation\_%';

Some of them are not that important, like the internal format of the identifiers.

Finally, remember that the configuration of the charset and collation for databases and tables are defaults, and that changing those don't modify the actual column encoding. Also, be careful when ALTERing the tables, as you block writes to that table.


Export database in one file with every dataset existing

My guess is you need to specify the type of join.  You really want a "full outer join" which will not exclude records.  A select where you join by keys will default to an inner join.

See https://stackoverflow.com/a/6188334/259538


How can I disable utf8mb4 entirely on MySQL 5.5?

POLITICALLY CORRECT ANSWER
You cannot do MySQL Replication from a New Master to an Old Slave
I have written many posts about this:

Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Dec 22, 2011 : What does the base64 BINLOG statements in mysqlbinlog output mean?
Feb 04, 2011 : MySQL master binlog corruption

In addition, there is the likelihood of dealing with the internal BINLOG magic number being in the wrong place and replication going senile. Here are my posts on the BINLOG magic number:

Oct 28, 2012 : Binlog has bad magic number
Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

Since there is nothing in the meantime you can do, please upgrade your Slave to MySQL 5.5
ALTERNATE PERSPECTIVE
In all fairness, @ChristopherSchultz is correct in that you can implement it. I just yell and scream "RISK !!!" to be more cautious because of the BINLOG magic number's place in the MySQL Replication Universe.


MongoDB use all memory, always! Reason why find increases memory usage (compare to fresh started machine) is that with every find, mongod will fill it's cache more to server clients better. It tries to keep active indexes and data in the memory, if possible.


Moving the data between partitions in a table

Now, what could be the best strategy to switch the data from older
  single partition with boundary value '20190101' to these four
  partitions?

Assuming your partitioned table is aligned, use a similarly partitioned staging table for the partition maintenance. This will avoid the costly data movement and logging when non-empty partitions are split.

Below is an example gleaned from the DDL in your question. Note that your partition scheme DDL is incorrect since the partition function with 2 boundaries creates 3 partitions, with the first being for data prior to year 2018. All 3 partitions must be mapped to a filegroup so I used PRIMARY in this script. Not sure why you chose CHAR(8) instead of DATE for the partitioning column data type since DATE requires only 4 bytes per row compared to 8.

--1) create a staging partition function, scheme, and aligned staging table like the original but with different names:

CREATE PARTITION FUNCTION DateRangePF_Staging (CHAR(8))
    AS RANGE RIGHT FOR VALUES ('20180101','20190101');

CREATE PARTITION SCHEME DateRangePS_Staging
    AS PARTITION DateRangePF_Staging TO ([PRIMARY], [Y2018FG], [Y2019FG]);

 CREATE TABLE YourTable_Staging (
      PartitioningColumn CHAR(8) NOT NULL
    , OtherColumn int NOT NULL
) ON DateRangePS_Staging(PartitioningColumn);

CREATE CLUSTERED INDEX cidx
    ON YourTable_Staging(PartitioningColumn)
    ON DateRangePS_Staging(PartitioningColumn);
CREATE NONCLUSTERED INDEX ncidx
    ON YourTable_Staging(OtherColumn)
    ON DateRangePS_Staging(PartitioningColumn);
GO

--2) `SWITCH` the '20190101' partition into the staging table:

 ALTER TABLE YourTable
    SWITCH PARTITION $PARTITION.DateRangePF('20190101')
    TO YourTable_Staging PARTITION $PARTITION.DateRangePF_Staging('20190101');

--3) split the original partition function to create new boundaries (the split partition is empty after `SWITCH`):

ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20190401');
ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20190701');
ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20191001');
GO

--4) repartition the staging table and indexes using the original partition scheme:

CREATE CLUSTERED INDEX cidx
    ON YourTable_Staging(PartitioningColumn)
    WITH(DROP_EXISTING=ON)
    ON DateRangePS(PartitioningColumn);
CREATE NONCLUSTERED INDEX ncidx
    ON YourTable_Staging(OtherColumn)
    WITH(DROP_EXISTING=ON)
    ON DateRangePS(PartitioningColumn);

ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190101')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190101');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190401')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190401');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190701')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190701');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20191001')
    TO YourTable PARTITION $PARTITION.DateRangePF('20191001');
GO

--5) update stats after SWITCH
UPDATE STATISTICS YourTable;
GO

--6) drop staging objects
DROP TABLE YourTable_Staging;
DROP PARTITION SCHEME DateRangePS_Staging;
DROP PARTITION FUNCTION DateRangePF_Staging;
GO


When is SQL Server permission check done?

This is spelled out in some detail here:

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine

But the basic answer is that stored procedures have cached and reused query plans, and permissions checks are not necessary when objects owned by the stored procedure owner are accessed from a stored procedure.  If valid ownership chains exist between the procedure and the object to be accessed, then only EXECUTE permission is checked before the stored procedure is started.  Other permissions checks are skipped.

  Could you come up with a step list about how a query (or SP) is processed after it is submitted?

The main thing to remember here is that all the queries in a batch are parsed and compiled before any of them starts to execute.  Permissions are checked during execution.  And it's even possible to reference a table in a query in such a way that permissions on that table are never actually checked.

If you watch an XE session like this:

CREATE EVENT SESSION [compiles] ON SERVER
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[client_app_name]=N'Microsoft SQL Server Management Studio - Query'))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS)

And do something like

use tempdb

create table t(id int)
create table w(id int)

create user fred without login

grant select on w to fred

Then:

dbcc freeproccache

go
execute as user='fred'

go
select * from w
select * from t
go

select 1
where 1 = case when 1=1 then 1 else (select count(*) from t) end

revert

You'll see that the query plans in the first batch are both compiled, then the first query runs, then the second fails.  The third query succeeds without a permissions check.


150 External flat files used to create internal DB

BIML is designed for this kind of semi-repetitive task. One defines a "template" that includes placeholders for the file and table names. The tool will then generate the corresponding 150 SSIS jobs.


#1215 - Cannot add foreign key constraint ERROR even though the columns are exactly the same

If games table is referring google_id from the parent table "players",then use below syntax (unless you are establishing Foreign key with "role" table as per your SQL statement):

ALTER TABLE
games ADD FOREIGN KEY(google_id) REFERENCES players(google_id);

Also, please check the DDL of role table(if exists)- datatype, width of column google_id and the mysql engine for both the tables.


See if an JSON array in MySQL contains an object whose key holds a specific date

One solution as provided by Morgan Tucker - @morgo is to use json_contains like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}')

For now the answer is OK, but i believe it can have some performance issues and feels a bit hackish to me (see the next query) - but I will deal with those when i get there :)

If I would need to query on a date range (from 2016-04-24 to 2016-04-26) I would need to add an invididual json_contains for each day in the time span like so:

select * from applications where json_contains(`data`, '{"date" : "2016-04-26"}') or json_contains(`data`, '{"date" : "2016-04-25"}') or json_contains(`data`, '{"date" : "2016-04-24"}')

And this would return invalid data if I would have an date key nested somewhere else

So if you have an different solution, I would like to know


Linked server from MSSQL to Oracle Issue

If you're able to select the 'Oracle Provider for OLE DB' option in the "Provider" dropdown, then I'd suggest trying the following (via the GUI):

Product Name: ###.###.#.###:####/SRV1

Data Source: ###.###.#.###:####/SRV1

Where ###.###.#.###:### is your Oracle Instance's IP Address (with standard 1521 or 152# port), and SRV1 is your Service name.

Just make sure the ORACLE user you're using to get in is correct in the "Security" section.

This option allows you to bypass the TNSNames file if you want to confirm that there's a connection (TNSPing is my go to, just like Gerald H. Pille said in the comments above).

Or if you want to go about it via T-SQL:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName'
                             , @srvproduct=N'###.###.#.###:####/SRV1'
                             , @provider=N'OraOLEDB.Oracle'
                             , @datasrc=N'###.###.#.###:####/SRV1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerName'
                                ,@useself=N'False'
                                ,@locallogin=NULL
                                ,@rmtuser=N'OracleUserHere'
                                ,@rmtpassword='passwordhere'
GO

Then go back in and change your options via the GUI (RPC, RPC Out, etc.)


Stored Procedure is not returning complete result

The split function you use truncate the valuelist to varchar(512). There are several split functions to choose from in this answer and here is another.

create function dbo.Split(@sep char(1), @s varchar(max))
returns @T table(s varchar(max))
as
begin
  while len(@s) > 0
  begin
    insert into @T values(left(@s, charindex(@sep, @s+@sep)-1))
    set @s = stuff(@s, 1, charindex(@sep, @s+@sep), '')
  end
  return
end


how to prevent replication to stop if there is no activity for some time?

BOL has it documented :

  Subscriptions can be deactivated or can expire if they are not synchronized within a specified retention period.

  If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.

  If a subscription is not synchronized within the publication retention period (default of 336 hours), the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher. The subscription must be recreated and synchronized.

  Where can I see these replication settings?

You can see them at distribution database by running sp_helpdistributiondb

  what to put in place to prevent this to happen?

Do it programatically as I have described in my answer here.

or

You can adjust the max_distretention value for sp_adddistributiondb which is 72hrs default to a large value depending on your organization's need.


Why does sql server perform better after restart

If you want to know why it's better after you restart, you need to track what was running when all those lines got squiggly.

When you clear the plan cache, it doesn't help queries currently executing, and it doesn't guarantee that the next plan you cache will be good. I'd advise against doing that until you know exactly what's going on.

Take a look at the part of your monitoring tool that tells you which queries were running when you had a problem. If your monitoring tool doesn't tell you that, it's time to find a new one.

Next time it's happening, use a script like sp_WhoIsActive to catch currently executing queries.


Do I need client certs for mysql ssl replication?

No, you don't need client certificates if you only want to encrypt the connection.

But, if you have client certificates, the master can authenticate the slave using the much more secure public/private keys (in addition to the password).

I got pretty frustrated with the other guides, so I put together a very comprehensive step by step guide to setting up encrypted replication.  It shows both what's the minimum required to get encryption running, and how to use certs on both sides to beef up authentication.
http://mysql.wingtiplabs.com/documentation/ssl27yzv/encrypt-replication-traffic


Calculating percentage of a row over total sum

You're looking for the analytical function ratio_to_report

select
  agent,
  round(ratio_to_report(commission) over ()*100) "% Comm."
from
  commissions;


In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

Short Answer: From when the backup was taken

I actually wasn't sure of the answer, so I just made a database, put it in full recovery model, took a full backup, did some work (create a couple tables named after the time I created them) and then started restores. Restored the full and then attempted to apply the log backups.

When I did that I had to specify the time zone from when the changes were made - from when the backup was taken. If I tried to use the new time zone's setting, it errored - bad timing.

So the answer to your question in my experience with SQL Server 2012 and 2008R2 - appears to be "The local time from when the backup was taken"

This backs up my expectation before testing. The way the log records are written and the way the backups are taken - that makes sense.

That said - I can't imagine a ton of situations where the time zone is changing with the need to worry about point in time recovery?


This sounds like all your .ibd file have been detached. Why ?

Please look at the InnoDB Architecture from Percona CTO Vadim Tkachenko

ibdata1 has an internal data dictionary that registers every .ibd by a unique tablespace_id. Somewhere along the line, all the tables in the trouble database have become detach from the data dictionary.

I wrote about this years ago

Apr 23, 2012 : How can extract the table schema from just the .frm file?
Dec 21, 2011 : Table compression in InnoDB?

To rightfully give credit where credit is due, I learned about this concept of detached .ibd files and trying to connect them back to the data dictionary from a blogpost written by Chris Calendar back in Feb 2009. I once helped client recover 30 tables with the information from his post (See my post ERROR Cannot find or open table? on what I did to help that client). Please read his biog and then my posts on what you need to try.

As for the why the table would be come detached, here is an example

Your datadir is /var/lib/mysql
Your have a database called mydb
You have a table called mytable

If you create an InnoDB table called mytable in the mydb database

You get two files

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

.ibd file would be assigned a tablespace_id.

If you drop that table and create it again, the table would have a different tablespace_id.

Now, picture this:

You make a backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table
You drop the table and create it (TRUNCATE TABLE internally does the same thing), ending up with tablespace_id of 28
You restore the backup of the database folder /var/lib/mysql/mydb with the tablespace_id of 27 on that one table

This causes the detachment because table mydb.mytable has the old tablespace_id of 27 while the data dictionary within ibdata1 has 28.

Since you said you didn't move or copy anything, then you may have too many InnoDB tables. Each InnoDB table needs two file handles to be opened and accessed.

I hope I have given your the information you need to help you recover your database.


Data Warehouse design for reporting against data for many time zones

I've solved this by having a very simple calendar table - each year has one row per supported time zone, with the standard offset and the start datetime / end datetime of DST and its offset (if that time zone supports it). Then an inline, schema-bound, table-valued function that takes the source time (in UTC of course) and adds/subtracts the offset.

This will obviously never perform extremely well if you are reporting against a large portion of data; partitioning might seem to help, but you will still have cases where the last few hours in one year or the first few hours in the next year actually belong to a different year when converted to a specific time zone - so you can never get true partition isolation, except when your reporting range does not include December 31 or January 1.

There are a couple of weird edge cases you need to consider:

2014-11-02 05:30 UTC and 2014-11-02 06:30 UTC both convert to 01:30 AM in the Eastern time zone, for example (one for the first time 01:30 was hit locally, and then one for the second time when the clocks rolled back from 2:00 AM to 1:00 AM, and another half hour elapsed). So you need to decide how to handle that hour of reporting - according to UTC, you should see double the traffic or volume of whatever you're measuring once those two hours get mapped to a single hour in a time zone that observes DST. This can also play fun games with sequencing of events, since something that logically had to happen after something else could appear to happen before it once the timing is adjusted to a single hour instead of two. An extreme example is a page view that happened at 05:59 UTC, then a click that happened at 06:00 UTC. In UTC time these happened a minute apart, but when converted to Eastern time, the view happened at 1:59 AM, and the click happened an hour earlier.
2014-03-09 02:30 never happens in the USA. This is because at 2:00 AM we roll the clocks forward to 3:00 AM. So likely you will want to raise an error if the user enters such a time and asks you to convert that to UTC, or design your form so that users can't pick such a time.

Even with those edge cases in mind, I still think you have the right approach: store the data in UTC. Much easier to map data to other time zones from UTC than from some time zone to some other time zone, especially when different time zones start / end DST on different dates, and even the same time zone can switch using different rules in different years (for example the U.S. changed the rules 6 years ago or so).

You will want to use a calendar table for all of this, not some gargantuan CASE expression (not statement). I just wrote a three-part series for MSSQLTips.com on this; I think the 3rd part will be the most useful for you:

http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/
http://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server--part-2/
http://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server--part-3/

A real live example, in the meantime

Let's say you have a very simple fact table. The only fact I care about in this case is the event time, but I'll add a meaningless GUID just to make the table wide enough to care about. Again, to be explicit, the fact table stores events in UTC time and UTC time only. I've even suffixed the column with _UTC so there is no confusion.

CREATE TABLE dbo.Fact
(
  EventTime_UTC DATETIME NOT NULL,
  Filler UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
);
GO

CREATE CLUSTERED INDEX x ON dbo.Fact(EventTime_UTC);
GO

Now, let's load our fact table with 10,000,000 rows - representing every 3 seconds (1,200 rows per hour) from 2013-12-30 at midnight UTC until sometime after 5 AM UTC on 2014-12-12. This ensures that the data straddles a year boundary, as well as DST forward and back for multiple time zones. This looks really scary, but took ~9 seconds on my system. Table should end up being about 325 MB.

;WITH x(c) AS
(
  SELECT TOP (10000000) DATEADD(SECOND,
    3*(ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1),
    '20131230')
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
  ORDER BY s1.[object_id]
)
INSERT dbo.Fact WITH (TABLOCKX) (EventTime_UTC)
  SELECT c FROM x;

And just to show what a typical seek query will look like against this 10MM row table, if I run this query:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, EventTime_UTC), 0),
  COUNT(*)
FROM dbo.Fact
WHERE EventTime_UTC >= '20140308'
AND EventTime_UTC < '20140311'
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, EventTime_UTC), 0);

I get this plan, and it returns in 25 milliseconds*, doing 358 reads, to return 72 hourly totals:

* Duration as measured by the free SentryOne Plan Explorer, which discards results, so this does not include network transfer time of the data, rendering, etc.

It takes a little longer, obviously, if I make my range too large - a month of data takes 258ms, two months takes over 500ms, and so on. Parallelism may kick in:

This is where you start thinking about other, better solutions to satisfy reporting queries, and it has nothing to do with what time zone your output will display. I won't get into that, I just want to demonstrate that time zone conversion is not really going to make your reporting queries suck all that much more, and they may already suck if you are getting large ranges that aren't supported by proper indexes. I'm going to stick to small date ranges to show that the logic is correct, and let you worry about making sure your range-based reporting queries perform adequately, with or without time zone conversions.

Okay, now we need tables to store our time zones (with offsets, in minutes, since not everybody is even hours off UTC) and DST change dates for each supported year. For simplicity, I'm only going to enter a few time zones and a single year to match the data above.

CREATE TABLE dbo.TimeZones
(
  TimeZoneID TINYINT    NOT NULL PRIMARY KEY,
  Name       VARCHAR(9) NOT NULL,
  Offset     SMALLINT   NOT NULL, -- minutes
  DSTName    VARCHAR(9) NOT NULL,
  DSTOffset  SMALLINT   NOT NULL  -- minutes
);

Included a few time zones for variety, some with half hour offsets, some that don't observe DST. Note that Australia, in southern hemisphere observes DST during our winter, so their clocks go back in April and forward in October. (The above table flips the names, but I'm not sure how to make this any less confusing for southern hemisphere time zones.)

INSERT dbo.TimeZones VALUES
(1, 'UTC',     0, 'UTC',     0),
(2, 'GMT',     0, 'BST',    60),
     -- London = UTC in winter, +1 in summer
(3, 'EST',  -300, 'EDT',  -240),
     -- East coast US (-5 h in winter, -4 in summer)
(4, 'ACDT',  630, 'ACST',  570),
     -- Adelaide (Australia) +10.5 h Oct - Apr, +9.5 Apr - Oct
(5, 'ACST',  570, 'ACST',  570);
     -- Darwin (Australia) +9.5 h year round

Now, a calendar table to know when TZs change. I'm only going to insert rows of interest (each time zone above, and only DST changes for 2014). For ease of calculations back and forth, I store both the moment in UTC where a time zone changes, and the same moment in local time. For time zones that don't observe DST, it's standard all year long, and DST "starts" on January 1.

CREATE TABLE dbo.Calendar
(
  TimeZoneID    TINYINT NOT NULL FOREIGN KEY
                REFERENCES dbo.TimeZones(TimeZoneID),
  [Year]        SMALLDATETIME NOT NULL,
  UTCDSTStart   SMALLDATETIME NOT NULL,
  UTCDSTEnd     SMALLDATETIME NOT NULL,
  LocalDSTStart SMALLDATETIME NOT NULL,
  LocalDSTEnd   SMALLDATETIME NOT NULL,
  PRIMARY KEY (TimeZoneID, [Year])
);

You can definitely populate this with algorithms (and the upcoming tip series uses some clever set-based techniques, if I do say so myself), rather than loop, populate manually, what have you. For this answer I decided to just manually populate one year for the five time zones, and I'm not going to bother any fancy tricks.

INSERT dbo.Calendar VALUES
(1, '20140101', '20140101 00:00','20150101 00:00','20140101 00:00','20150101 00:00'),
(2, '20140101', '20140330 01:00','20141026 00:00','20140330 02:00','20141026 01:00'),
(3, '20140101', '20140309 07:00','20141102 06:00','20140309 03:00','20141102 01:00'),
(4, '20140101', '20140405 16:30','20141004 16:30','20140406 03:00','20141005 02:00'),
(5, '20140101', '20140101 00:00','20150101 00:00','20140101 00:00','20150101 00:00');

Okay, so we have our fact data, and our "dimension" tables (I cringe when I say that), so what's the logic? Well, I presume you're going to have users select their time zone and enter the date range for the query. I will also assume that the date range will be full days in their own timezone; no partial days, never mind partial hours. So they will pass in a start date, an end date, and a TimeZoneID. From there we will use a scalar function to convert the start/end date from that time zone to UTC, which will allow us to filter the data based on the UTC range. Once we've done that, and performed our aggregations on it, we can then apply the conversion of the grouped times back to the source time zone, before displaying to the user.

The scalar UDF:

CREATE FUNCTION dbo.ConvertToUTC
(
  @Source   SMALLDATETIME,
  @SourceTZ TINYINT
)
RETURNS SMALLDATETIME
WITH SCHEMABINDING
AS
BEGIN
  RETURN
  (
    SELECT DATEADD(MINUTE, -CASE
        WHEN @Source >= src.LocalDSTStart
         AND @Source < src.LocalDSTEnd THEN t.DSTOffset
        WHEN @Source >= DATEADD(HOUR,-1,src.LocalDSTStart)
         AND @Source < src.LocalDSTStart THEN NULL
        ELSE t.Offset END, @Source)
    FROM dbo.Calendar AS src
    INNER JOIN dbo.TimeZones AS t
    ON src.TimeZoneID = t.TimeZoneID
    WHERE src.TimeZoneID = @SourceTZ
      AND t.TimeZoneID = @SourceTZ
      AND DATEADD(MINUTE,t.Offset,@Source) >= src.[Year]
      AND DATEADD(MINUTE,t.Offset,@Source) < DATEADD(YEAR, 1, src.[Year])
  );
END
GO

And the table-valued function:

CREATE FUNCTION dbo.ConvertFromUTC
(
  @Source   SMALLDATETIME,
  @SourceTZ TINYINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN
 (
  SELECT
     [Target] = DATEADD(MINUTE, CASE
       WHEN @Source >= trg.UTCDSTStart
        AND @Source < trg.UTCDSTEnd THEN tz.DSTOffset
       ELSE tz.Offset END, @Source)
  FROM dbo.Calendar AS trg
  INNER JOIN dbo.TimeZones AS tz
  ON trg.TimeZoneID = tz.TimeZoneID
  WHERE trg.TimeZoneID = @SourceTZ
  AND tz.TimeZoneID = @SourceTZ
  AND @Source >= trg.[Year]
  AND @Source < DATEADD(YEAR, 1, trg.[Year])
);

And a procedure that uses it (edit: updated to handle 30-minute offset grouping):

CREATE PROCEDURE dbo.ReportOnDateRange
  @Start      SMALLDATETIME, -- whole dates only please!
  @End        SMALLDATETIME, -- whole dates only please!
  @TimeZoneID TINYINT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT @Start = dbo.ConvertToUTC(@Start, @TimeZoneID),
         @End   = dbo.ConvertToUTC(@End,   @TimeZoneID);

  ;WITH x(t,c) AS
  (
    SELECT DATEDIFF(MINUTE, @Start, EventTime_UTC)/60,
      COUNT(*)
    FROM dbo.Fact
    WHERE EventTime_UTC >= @Start
      AND EventTime_UTC <  DATEADD(DAY, 1, @End)
    GROUP BY DATEDIFF(MINUTE, @Start, EventTime_UTC)/60
  )
  SELECT
    UTC = DATEADD(MINUTE, x.t*60, @Start),
    [Local] = y.[Target],
    [RowCount] = x.c
  FROM x OUTER APPLY
    dbo.ConvertFromUTC(DATEADD(MINUTE, x.t*60, @Start), @TimeZoneID) AS y
  ORDER BY UTC;
END
GO

(You may want to have a go at short circuiting there, or a separate stored procedure, in the event that the user wants reporting in UTC - obviously translating to and from UTC is going to be wasteful busy work.)

Sample call:

EXEC dbo.ReportOnDateRange
  @Start      = '20140308',
  @End        = '20140311',
  @TimeZoneID = 3;

Returns in 41ms*, and generates this plan:

* Again, with discarded results.

For 2 months, it returns in 507ms, and the plan is identical other than rowcounts:

While slightly more complex and increasing run time a little bit, I am fairly confident that this type of approach will work out much, much better than the bridge table approach. And this is an off-the cuff example for a dba.se answer; I'm sure my logic and efficiency could be improved by folks much smarter than me.

You can peruse the data to see the edge cases I talk about - no row of output for the hour where clocks roll forward, two rows for the hour where they rolled back (and that hour happened twice). You can also play with bad values; if you pass in 20140309 02:30 Eastern time, for example, it's not going to work too well.

I might not have all of the assumptions right about how your reporting will work, so you may have to make some adjustments. But I think this covers the basics.


Is a chain of foreign keys better than duplicating data?

I would keep this style of design. The only issue that this could begin to cause is a performance hit on reports when dealing with a lot of data, but you wouldn't start seeing it until the millions of rows range. This would be because any report would have to basically query a half dozen tables to get the information it needs, but in general this would give you a lot of flexibility to create queries and views to sort and filter the rows.
In the real world, you'll likely find that the University table would contain the fields for both Country and City, with them just as commonly being just text fields, instead of having lookup tables. This would improve query performance a bit, especially since the number of universities would not be nearly as large as the number of faculty or students. The cost for this would be an increase in storage requirements, since every row in the University table would have the full city and country names instead of the FKs.


How to upgrade PostgreSQL from version 8.4 to 9.4?

This is how I solved my problem.

Upgrade Postgresql 8.4 to 9.4 in Centos

 1. Yum Install PG9.4
 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
 3. yum install pgdg-redhat94-9.4-1.noarch.rpm
 4. yum install postgresql94-server
 5. service postgresql-9.4 initdb
 6. chkconfig postgresql-9.4 on

Backup Data

 7. su - postgres

 8. pg_dumpall > dump.sql

Restore Data

 9. service postgresql stop

 10. service postgresql-9.4 start

 11. su - postgres

 12. psql < dump.sql

Config Network Access

vi /var/lib/pgsql/9.4/data/postgresql.conf

 1. listen_addresses = '*'
 2. port = 5432

/var/lib/pgsql/9.4/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident
host    all         all         130.51.79.0/24        md5
host    all         all         10.210.29.0/24        md5
# IPv6 local connections:
host    all         all         ::1/128               ident

Remove PG8.4

 1. yum remove postgresql
 2. ln -s /usr/pgsql-9.4/bin/psql /usr/local/bin/psql


What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment?

$ORACLE_HOME/dbs is used to store the spfiles, init files and password files on Linux and Unix systems. But as you pointed out the dbs directory not used on windows servers. So on a Windows servers it is probably not needed.


Minimum value within a set (or window) within a three column table

You could do this without the new (2012+) window function FIRST_VALUE(), using the ROW_NUMBER(). But you could also do it using  OUTER APPLY:

SELECT
    d.CategorieID,
    IDMinOrder = a.ID,
    IDMaxOrder = b.ID
FROM
    ( SELECT DISTINCT CategorieID
      FROM tableX
    ) AS d
  OUTER APPLY
    ( SELECT TOP (1) a.ID
      FROM tableX AS a
      WHERE a.CategorieID = d.CategorieID
      ORDER BY a."Order" ASC
    ) AS a
  OUTER APPLY
    ( SELECT TOP (1) b.ID
      FROM tableX AS b
      WHERE b.CategorieID = d.CategorieID
      ORDER BY b."Order" Desc
    ) AS b ;


You can combine the select and update into a single statement that runs atomically, by doing something like:

CREATE TABLE dbo.Clients
(
    ClientID INT NOT NULL
        CONSTRAINT PK_Clients
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ClientState VARCHAR(255)
);

CREATE TABLE dbo.CallCheckout
(
    ClientID INT NOT NULL
    , CheckedOutAt DATETIME
);

INSERT INTO dbo.Clients(ClientState)
VALUES ('AA')
    , ('AB')
    , ('AC');

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (1, NULL);

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (2, GETDATE());

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (3, NULL);
GO

CREATE PROCEDURE dbo.CheckOut
(
    @state VARCHAR(3)
)
AS
BEGIN
    UPDATE TOP(1) dbo.CallCheckout
    SET CheckedOutAt = getdate()
    OUTPUT inserted.clientid
    FROM dbo.CallCheckout cc
        INNER JOIN dbo.Clients c ON cc.ClientID = c.ClientID
    WHERE cc.CheckedOutAt IS NULL
        AND c.ClientState = @state;
END
GO

EXEC dbo.CheckOut 'AA';
EXEC dbo.CheckOut 'AC';

The results are:

Notice, I've specified the schema in the CREATE PROCEDURE and UPDATE statements.  You should always specify the schema, even if you only ever use dbo, since it prevents the server from having to make that decision, and potentially making the wrong decision.

Also, please do not use reserved keywords for column names.  I'm talking about [state].  Notice, I've named that column CheckoutState which is not a reserved keyword, and makes the column name more meaningful.


SSIS 2008 - Getting Execute SQL Task to update data with parameters

In the ever confusing world of SSIS supporting multiple systems, you have chosen an OLE DB Connection manager (generally a good to the only choice). The downside to using an OLE DB Connection manager with parameters, either in an Execute SQL Task or an OLE DB Source, is that parameters are ordinal based.

Execute SQL Task

In your example SQL, there are 3 parameters

UPDATE [Integration].[Staging].[OrderTracking]
   SET TriggerIntegration = CASE
                              -- Zero-eth ordinal
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
               -- First ordinal
     , Error = @Error
 WHERE SalesforceOpportunityLineItemId = @Id -- second ordinal

In your Parameter Mapping image, you need to list the User::Error twice. The Parameter Name will be 0 and 1 while User::Id has a parameter name of 2

User:Error Input NVARCHAR 0 50
User:Error Input NVARCHAR 1 50
User:Id    Input NVARCHAR 2 50

You have only provided two parameters where three are needed.

Change your result set type to None as the invocation of the above SQL does not generate a result set (or a single row of output)

Data Types

In your original parameter mapping, you indicate that the SSIS Variable User::Id should be Numeric when it lands in your OLE DB Connection but within SSIS you have it defined as a string. Unless you have a reason for data type conversions, you might be better served by keeping your types consistent. Also, is it really numeric and not int/bigint or int32/int64 in SSIS parlance.

Why doesn't the query work as is?

The query is running but no results are found WHERE SalesforceOpportunityLineItemId = @Id is testing where SalesforceOpportunityLineItemId is equal to NULL (since no value is supplied for the parameter @Id). And as we have all painfully discovered at some point in our careers, NULL will never be equal to anything.

Other thoughts

If I have misread all of that and you are trying to generate a result set from  [CEGSalesforce].[dbo].[OpportunityLineItem_OrderTracking] and use the Error/Id combo, then you are likely looking for a different pattern. Google "Shred Recordset SSIS" but the basics will be

Execute SQL Task  (use select query, with full result set to a new Variable of type Object, call it rsUpdates)

Foreach Recordset Enumerator (use an ADO.net Recordset as the source with rsUpdates and assign values to @User::Error and @User::Id)

Execute SQL Task (use the update procedure with two variables)

Note, no data flow is required for this approach

Edit #1

A question via comment "Now that I know I need three parameters, I need to have 3 "?" behind my stored proc, correct?" I can say.. maybe

If the procedure is defined as

CREATE PROCEDURE dbo.uspU_UpdateIntegrationFlag
(
    @error varchar(50)
,   @Id int
)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [Integration].[Staging].[OrderTracking]
    SET TriggerIntegration = CASE
                              -- Zero-eth ordinal
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
               -- First ordinal
     , Error = @Error
    WHERE SalesforceOpportunityLineItemId = @Id -- second ordinal
END

then ordinal based explanation above will apply to the stored procedure's parameters of @error and @id which are denoted correctly with the ?, ? and names of (0,1)


How can I restrict the version of the target DB instance in a SSIS package?

Create an initial Execute SQL Task in the Control Flow and use the following code in it:

IF ((SELECT CAST(SERVERPROPERTY('ProductVersion') as CHAR(2))) = 11)
    BEGIN
        SELECT 'TRUE' As Result
    END
    ELSE
        SELECT 'FALSE' As Result

You can of course do several things with this, one of the easiest and clearest for SSIS developers would be to store the results in a SSIS variable and assign the Precedent Constraint after the SQL Task to Expression and Constraint.

If the Execute SQL Task Succeeds and returns TRUE it will allow the rest of the execution, which could be a single package, part of a larger execution stream, or a master package. If it fails or returns FALSE your package stops... might be worth putting in another path for an alert message or log of this though.

You can also use (SELECT @@VERSION) LIKE 'Microsoft SQL Server 2014%'. I like this as it's a more familiar version nomenclature.