Please check the my.cnf in the mysql installment folder.

like this:

datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

it will show you the right folder.


Query product and product variants based on properties

I followed @GerardH.Pille's advice and replaced the describable columns with a product_id and variant_id column. This way the querying is a lot simpler.


Optimizing queries on a range of timestamps (two columns)

For Postgres 9.1 or later:
CREATE INDEX idx_time_limits_ts_inverse
ON time_limits (id_phi, start_date_time, end_date_time DESC);
In most cases the sort order of an index is hardly relevant. Postgres can scan backwards practically as fast. But for range queries on multiple columns it can make a huge difference. Closely related:

PostgreSQL index not used for query on range

Consider your query:
SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    start_date_time <= '2010-08-08 00:00'
AND    end_date_time   >= '2010-08-08 00:05';

Sort order of the first column id_phi in the index is irrelevant. Since it's checked for equality (=), it should come first. You got that right. More in this related answer:

Multicolumn index and performance

Postgres can jump to id_phi = 0 in next to no time and consider the following two columns of the matching index. These are queried with range conditions of inverted sort order (<=, >=). In my index, qualifying rows come first. Should be the fastest possible way with a B-Tree index1:

You want start_date_time <= something: index has the earliest timestamp first.
If it qualifies, also check column 3.
Recurse until the first row fails to qualify (super fast).
You want end_date_time >= something: index has the latest timestamp first.
If it qualifies, keep fetching rows until the first one doesn't (super fast).
Continue with next value for column 2 ..

Postgres can either scan forward or backward. The way you had the index, it has to read all rows matching on the first two columns and then filter on the third. Be sure to read the chapter Indexes and ORDER BY in the manual. It fits your question pretty well.
How many rows match on the first two columns?
Only few with a start_date_time close to the start of the time range of the table. But almost all rows with id_phi = 0 at the chronological end of the table! So performance deteriorates with later start times.
Planner estimates
The planner estimates rows=62682 for your example query. Of those, none qualify (rows=0). You might get better estimates if you increase the statistics target for the table. For 2.000.000 rows ...
ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;

... might pay. Or even higher. More in this related answer:

Check statistics targets in PostgreSQL

I guess you don't need that for id_phi (only few distinct values, evenly distributed), but for the timestamps (lots of distinct values, unevenly distributed).
I also don't think it matters much with the improved index.
CLUSTER / pg_repack / pg_squeeze
If you want it faster, yet, you could streamline the physical order of rows in your table. If you can afford to lock your table exclusively (at off hours for instance), rewrite your table and order rows according to the index with CLUSTER:
CLUSTER time_limits USING idx_time_limits_inversed;

Or consider pg_repack or the later pg_squeeze, which can do the same without exclusive lock on the table.
Either way, the effect is that fewer blocks need to be read from the table and everything is pre-sorted. It's a one-time effect deteriorating over time with writes on the table fragmenting the physical sort order.
GiST index in Postgres 9.2+
1 With pg 9.2+ there is another, possibly faster option: a GiST index for a range column.

There are built-in range types for timestamp and timestamp with time zone: tsrange, tstzrange. A btree index is typically faster for an additional integer column like id_phi. Smaller and cheaper to maintain, too. But the query will probably still be faster overall with the combined index.

Change your table definition or use an expression index.

For the multicolumn GiST index at hand you also need the additional module btree_gist installed (once per database) which provides the operator classes to include an integer.

The trifecta! A multicolumn functional GiST index:
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- if not installed, yet

CREATE INDEX idx_time_limits_funky ON time_limits USING gist
(id_phi, tsrange(start_date_time, end_date_time, '[]'));

Use the "contains range" operator @> in your query now:
SELECT *
FROM   time_limits
WHERE  id_phi = 0
AND    tsrange(start_date_time, end_date_time, '[]')
    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')

SP-GiST index in Postgres 9.3+
An SP-GiST index might be even faster for this kind of query - except that, quoting the manual:

Currently, only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes.

Still true in Postgres 12.
You would have to combine an spgist index on just (tsrange(...)) with a second btree index on (id_phi). With the added overhead, I'm not sure this can compete.
Related answer with a benchmark for just a tsrange column:

Perform this hours of operation query in PostgreSQL


MySQL Workbench model/source synchronize issue

MySql Workbench is powerful, but not perfect.

Some time in similar case, I've done the following to resolve the problem:

create a blank project
require a model update FROM db source
then I'll reapply the mod.

Often this happen if you just change a 'comma' [a small thing] in the db without sync it with the model, and then you change another 'comma' in the model. MySql Workbench goes crazy often as db increases in complexity and table number, but I think it's a very good product.


SQL Server domain account login and user

Unless you're referring to contained databases (which are new in SQL Server 2012), you can't use the second approach. Whenever a person authenticates to SQL Server, they are authenticating against a particular server login (or possibly multiple logins if you're creating logins for domain groups). This server login can then be mapped to one or more database users to allow access to different databases.

Unless you're doing some tricks with EXECUTE AS or using application roles, there's no way for somebody to authenticate directly as a database user without a server login being mapped to it.


How do I separate the values of one column into multiple columns In MS SQL-Server

This sounds like a PIVOT, but those quickly become messy when you're pivoting multiple things conditionally. So I would use MAX/CASE aggregates, like this (the CTE is used to give a deterministic order for numbers of the same type; if you want to change that, just change the ORDER BY inside the OVER() clause):

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
            (PARTITION BY ID, [Type] ORDER BY [Contact No])
  FROM dbo.SourceTable
)
SELECT ID,
  HPhone  = MAX(CASE WHEN [Type] = 'home' THEN [Contact No] END),
  CPhone1 = MAX(CASE WHEN [Type] = 'cell' AND rn = 1 THEN [Contact No] END),
  CPhone2 = MAX(CASE WHEN [Type] = 'cell' AND rn = 2 THEN [Contact No] END),
  WPhone1 = MAX(CASE WHEN [Type] = 'work' AND rn = 1 THEN [Contact No] END),
  Ext1    = MAX(CASE WHEN [Type] = 'work' AND rn = 1 THEN [Ext] END),
  WPhone2 = MAX(CASE WHEN [Type] = 'work' AND rn = 2 THEN [Contact No] END),
  Ext2    = MAX(CASE WHEN [Type] = 'work' AND rn = 2 THEN [Ext] END)
FROM x
GROUP BY ID
ORDER BY ID;


Why insert in with is returning unexpected results?

This is an expected and documented behaviour

Quote from the manual

  Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable

In your second query the database apparently chose to run t2 before t1 and thus it returns 1.

In the third query there have to be two different values, because that's what a sequence guarantees: it never generates the same number twice (unless you have enabled cycle)


Is there a disk space impact when choosing NULL vs empty array {} for the default of an ARRAY[] column?

Why don't you check by yourself?

1 - Create 2 tables:

CREATE TABLE TABLE1 (column1 int not null, column2 int[] default {});
CREATE TABLE TABLE2 (column1 int not null, column2 int[]);

2  - make 1 million inserts for each table with:

insert into TABLE1(colulmn1) SELECT generate_series(1,1000000);
insert into TABLE2(colulmn1) SELECT generate_series(1,1000000);

3  - check table disk usage using this query :
(found in https://wiki.postgresql.org/wiki/Disk_Usage )

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r' and relname in ('TABLE1','TABLE2')
  ) a
) a;

4 - whenever you are convinced just drop the tables:

DROP TABLE TABLE1;
DROP TABLE TABLE2;


The common sense approach would be to cleanse and sanitise the data on import. Creating a view to mask the problem is not fixing the underlying issue, it's just hiding it.

Also, having the views is unlikely to be a one stop fix, you'll likely need to write other code in other places to work around the issue, which will all need to be maintained.

Having code that works around a problem might be the quicker of the options, but in the long run it will create more work and problems than fixing the data as it comes in to the system.


what is "killcursors: found 0 of 1" from mongodb.log with replicaset?

2016-08-05T15:29:36.935+0200 I COMMAND  [conn39339] killcursors: found 0 of 1

As per the info you've found, this is a normal (and benign) message: no administrative action is required. This message may be logged when a client/driver issues the killcursors command (for example, when an operation reaches a cursor timeout or an active cursor is explicitly closed). Normally inactive cursors are automatically cleaned up by the mongod server without a client killcursor command.

If you wanted to investigate further you could ask your application developers if they are using timeouts, closing active cursors before the results are exhausted, or possibly calling the killcursors command explicitly. You could also check on the specific version of the driver they are using if the frequency of this message has changed significantly from past occurrences.

However, given only 4 occurrences in more than a month of log activity (according to your mloginfo output), I would not be concerned by these messages.


Here is an overview of methods to generate random unique ids in mysql, by Rick James: http://forums.mysql.com/read.php?24,425424,425491

The overview is quite comprehensive, gives 5 different strategies, lists pros and cons for every one.


How to calculate MySQL Transactions per second

BEGIN; INSERT...; INSERT...; COMMIT; = 2 inserts, 1 begin, 1 commit.
SET autocommit = ON; INSERT...; = 1 insert, 0 commits.

I don't see any way to get the 'right' answer of '2 transactions'.

However,

if your code always uses BEGIN...COMMIT, then you might use Com_begin as the number of transactions.
if your code never uses BEGIN...COMMIT, then you might use Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi as the number of transactions.

(I consider autocommit=OFF...COMMIT to be an abomination.)


Keep rows only if Date exists in both table

If you want to perform a DELETE action:

You'll delete all rows in table1 not present in table2 (by date field) and viceversa

DELETE FROM table1
WHERE NOT EXISTS(
    SELECT 'X'
    FROM table2
    WHERE table1.date = table2.date
)

and

DELETE FROM table2
WHERE NOT EXISTS(
    SELECT 'X'
    FROM table1
    WHERE table2.date = table1.date
)

If you want to perform a SELECT action

You'll show only rows presents in table1 and table2

SELECT * FROM table1
WHERE EXISTS(
    SELECT 'X'
    FROM table2
    WHERE table1.date = table2.date
)
UNION ALL
SELECT * FROM table2
WHERE EXISTS(
    SELECT 'X'
    FROM table1
    WHERE table2.date = table1.date
)


All I can offer you at this point are my deepest sympathies!

Doesn't the person/company who/which is getting you to write a tool under this mind-boggling constraint realise that you'll have to do far more work for a far lesser return than if you used (as you say) Python or Perl or even Brainfuck :-).

Anyway, what you wrote rang a very old bell in my memory cells - I remembered having read about a database system written in shell which I thought might have been interesting for, say, a student project (sort of like those C obfuscation competitions that challenge people to be clever without actually doing anything useful).

I visted Christopher Browne's linux databases site and found what I was looking for here (the kshSQL DBMS link). I was unable to find the source code for that one, but it is based on another programming effort, shql, a DBMS written in bash - it's github, so you have the source code.

The only bright spot on the horizon for you IMHO, is that it was written by Bruce Momjian who is a major player in the PostgreSQL world and knows a thing or two about databases. I've never used it, but there's just a chance that you might be able to do something with this. Good luck, you're going to need it! :-)


How to Get the Sum (total) of a column in a query result set

Use the OVER clause to modify a SUM scope in your query. No GROUP BY needed

SELECT
    MyColumn, OtherColumn,
    SUM(MyColumn) OVER () AS SumTotal
FROM
    ...

Otherwise, SSMS isn't a calculating engine: it display query results so add it to your query...


Yes it is acceptable.  In ER modeling, this is known as a reflexive relationship.  In a relational model, it's implemented by a foreign key that references another row in the same table.

The simplest example is a table of employees, where the PK is EmployeeID (or just ID) and there is an FK called SupervisorID.  This kind of thing is done all the time.


viewing the population? (sql server full text search)

I think you need to be having a look at the Full Text DMVs here: http://msdn.microsoft.com/en-us/library/ms174971%28v=sql.105%29.aspx

Specifiably I think you want something like:

SELECT * FROM sys.dm_fts_index_keywords(db_id('AdventureWorks2012'), object_id('HumanResources.JobCandidate'))
GO

or

SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('AdventureWorks2012'), object_id('HumanResources.JobCandidate'))
GO


Is it common practice to mix InnoDB and MyISAM tables on same server?

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types.

For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be regenerated easily and it's a small table, say for queuing, you might choose Memory. If you have a table that's 90% reads, and the data has got to be there when you look for it, then you'd probably choose a storage engine that supports transactions and configurable atomicity, such as InnoDB. If you want accessibility through the file system w/o damaging data, you might choose CSV.

Nonetheless, you can safely use multiple storage engines within the same schema as well as the physical host.

Let me note though, that your buffers play a role in this whole mess. If you use both MyISAM and InnoDB, you will need to be careful that your key_buffer and innodb_buffer_pool do not contend. This will take careful planning on your part, but that's what we do.


Insert results from a stored procedure into a table variable

To insert the results into a table, you just want INSERT....EXEC... not the VALUES part of the query.

In your case, this would look like the following:

INSERT INTO @DomainHistory(DomainId,HasHistory)
EXEC GetOrSetDomainId 'test', 'test2';


How does one change the default socket used by mysqladmin?

Add a [mysqladmin] section in your /etc/my.cnf or $USER/.my.cnf and set the socket.  This will override the compiled-in socket and the socket defined in the [mysqld] section.

You should be able to figure it out from what's shown below:

VM52-CentOS58[root@ivdb41 ~]# mysqladmin ping

mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql2.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql2.sock' exists!

VM52-CentOS58[root@ivdb41 ~]# grep -A 1 mysqladmin /etc/my.cnf

[mysqladmin]
socket=/var/lib/mysql/mysql2.sock

VM52-CentOS58[root@ivdb41 ~]# mysqladmin ping mysqld is alive

VM52-CentOS58[root@ivdb41 ~]# grep -A 1 mysqladmin /etc/my.cnf

[mysqladmin]
# socket=/var/lib/mysql/mysql2.sock


Execution plan for select with empty value using wildcards

Simple answer LIKE '%%' will always trigger an FULL index scan if the column is indexed.

see http://sqlfiddle.com/#!2/382bca

if not indexed it will always trigger an FULL table scan.

see http://sqlfiddle.com/#!2/a0fd0/1

Note an FULL index scan and FULL table scan is almost the same but an FULL index scan is more likely to be faster if the index size is smaller what is most likely the case..


How to change the Write Ahead Log directory on Windows

From the docs

  WAL logs are stored in the directory pg_wal under the data directory, as a set of segment files [...]

The docs go on to day,

  It is advantageous if the log is located on a different disk from the main database files. This can be achieved by moving the pg_wal directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

So your options are simple on Windows

NTFS Symbolic Link, or ReFS symbolic links.
Change the data_directory


Configuring Availability group between Two SQL Server Failover Cluster Instances

You can try a “distributed availability group”:

  A distributed availability group is a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform - such as between an availability group hosted on Linux and one hosted on Windows. As long as two availability groups can communicate, you can configure a distributed availability group with them.


How to use postgresql variable in where clause

Actually select user_id from user will result in an error, because user is a reserved keyword (function) that returns the current user - and that does not have a column named user_id. See here: https://rextester.com/EPWT79333

The reason why it works in the sub-select of the first query, is that any column not available in the tables of the sub-query will resolve to a column from the outer query.

So the query:

select *
from device
where device.user_id in (select user_id from user);

Is actually the same as:

select *
from device
where device.user_id = user_id;

If you do have a table named user you have to quote it, to make clear that you mean the table, not the built-in function: "user"

So the correct form for the first query is:

select *
from device
where device.user_id in (select user_id from "user");


inserting the table colomn data in multiple tables colomn.(SQL SERVER)

Try this

CREATE TRIGGER Trigger_t1
   ON  T1
   FOR INSERT
AS
BEGIN

    SET NOCOUNT ON;

    insert into T2(C1,C2,C3 )
    select  C1,C2,C3
    from    inserted

    insert into T3(C1,C2,C3 )
    select  C1,C2,C3
    from    inserted

    insert into T4(C1,C2,C3 )
    select  C1,C2,C3
    from    inserted

END
GO

DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables.

                                                                                :MSDN

https://msdn.microsoft.com/en-us/library/ms191300.aspx


ORDER BY with IF-ELSE statement (DESC doesn't work)

Welcome.

Please notice that you are in a wrong place.

dba - here you can give your mysql code alone and ask for help.

You might have asked this question in stackoverflow.

Anyway here is what you miss.

if(isset($_GET["sortfield"])){

is right. But

if(isset($_GET["sorttype"]) == "asc"){

is not right as the expression to the left of equal condition operator would evaluate to true or false and if you check the logical value == "asc" it will fail. It should be

if(isset($_GET["sorttype"]) and $_GET["sorttype"]== "asc"){

or simply

if($_GET["sorttype"]== "asc"){

if you are sure that it is already set (as of the first if).

Also, please go through this before asking. https://stackoverflow.com/help/minimal-reproducible-example

If you had gone through the mentioned page, formatted your question clearly and asked at the right place, you would have got your answer on day one itself for sure.


Why is query using Clustered Index when it shouldn't?

The "like '%zzz%'" largely negates the possible benefit of the index since it will need to examine each entry to determine whether it matches; and doing the "SELECT *" means it would have to do a lookup to the clustered index to get all of the column data on the matched records; the optimizer can't estimate how many matches there will be so chooses the clustered index.

Try changing it to "like 'zz%'" and see what you get.


If the column is just a surrogate key and is not used for other purposes (display sorting and so forth) then I would just use your database's AUTOINCREMENT equivalent and ignore the fact that there will be gaps in the IDs, as there point is to be unique rather than serving other purposes. The much recommended "SQL Anti-Patterns" book has a chapter on this entitled "pseudokey neat-freak". This depends on what you are modelling and how of course, if the numbers do carry meaning beyond being an identifier then this point is irrelevant.

The general concept you want to read around (it is worth a little background reading as these problems crop up all over the place so knowing how to spot them and deal with them efficiently or design them out is very useful) is gaps and islands. There are many online references about this (this is the first one a quick search found, it is talking about MSSQL but the concepts are transferable).

As well as the WHERE NOT EXISTS method, which is usually suggested as the way the code reads makes your intentions more obvious, you can also do:

SELECT TOP 1 t1.id-1
FROM     yourtable t1
LEFT OUTER JOIN
         yourtable t2
ON       t2.id=t1.id-1 -- will match if there is a row with the next ID down from the row in t1
WHERE    t2.id IS NULL -- the next ID down not found
AND      t1.id > 0     -- assume 1 is the lowest valid ID
ORDER BY t1.id

(that is MSSQL syntax, you may need to tweak it)
Both variants should produce similar query plans so perform the same, but I've seen the above approach perform better as part of a more complex query so it may be worth trying both in your circumstances and verifying which performs best.

Also not that neither method as currently presented will return the first ID if the table is empty: you'll get no rows back instead (or NULL if you are using this as a sub-query). One method of getting around this is to have an "invalid" row with id=0 but that is quite dirty (you end up having to filter that out of many other queries) so dealing with the nothing/NULL in your logic instead instead is strongly recommended. For tables that will never be empty in production (a users table for instance, even on initial install there will be one record for the initial setup/admin user) this or course is not an issue.

edit: Corrected the query to return the first ID if not found, as ypercube noted the first version would not do

Newer mySQL Versions

Since this answer, mySQL and MariaDB have added support for window functions (in versions 8 and 10.2 respectively) such as LAG() and LEAD(). You can use these to inspect the next (or previous) row and compare the value that is intended to increment and see if there is a gap, meaning you do not need to join the table onto itself or use a sub-query with EXISTS for this comparison - with the right index(es) this can be a much more efficient way to implement some gap/island queries.

All the common SQL database engines (sqlite, postgres, SQL Server, Oracle, ...) now support window functions.


How do SSIS connection strings send passwords?

The Package protection level matters here as a setting of DontSaveSensitive will remove any sensitive data, like a password, when you save the package. You might enter the password, click validate connection and it's good but as soon as you save, it's wiped out and when you re-open, connections are broken because the password is incorrect (blank)

There are challenges with Package Protection Levels other than DontSaveSensitive. You can encrypt all or sensitive data with user key. That is, the author of the package's Active Directory bits are used to transparently encrypt data. Which is great until that person leaves the organization and the AD account is removed. Hard to unlock something when you're a ghost. Encrypt all or sensitive with password is overkill for a single user and a shared secret among all the developers is no secret at all. So, the advice back in the day was Don' Save Sensitive and use some other means of populating sensitive values. Thus...
Check your expressions. If I have an expression on the Password and the expression is the string "1SecretPassword!" don't save sensitive can't wipe the password out because it's an expression, not a stored value. Semantics, but it's true. I advise people to do the expression stuff in SSIS variables and assign them in properties so instead of seeing "1SecretPassword!", in my packages you would see @[User::Password] which has the password in clear text. Yeah, it's a terrible thing for security, so that's why we had the next thing

Configurations! This allowed us to configure a single element per config entity (Environment Variable or Registry key) or point to a cookie jar of settings (SQL Server table or xml based package configuration file). We had a two phase configuration method in place - the Environment Variable would bootstrap the Config connection string which then pointed at a SQL Server table. As long as the xml/table entity exists, the application of configuration values will just consume them all. Which was nice because you could then add/remove config values without changing code.

But sometimes you would get a package through testing and ready for production and realized you forgot to do any of that. You do not want to go back through the whole testing cycle and approval by the high holy SOX compliance review panel that only meets every other week so what do you do, command line overrides when the package executes. This is why I want to see how the job is defined because what you see in the designer/visual studio might be stomped over at run-time by the parameters defined in the job.

There's a fifth package protection level: Server encryption. I honestly forgot how this works but I think when you deploy a package to the MSDB it strips the other protections and replaces it with its own but it's been nearly a decade since I've dealt with package deployment model in capacity.


EAV, multiple left join query optimisation

Please explain the order by - limit - group by.  It sounds kludgy and possibly illegal in future versions.
Store the tail of the filename so you can avoid SUBSTRING (etc) in critical places like WHERE clauses.
Show us SHOW CREATE TABLE.


Unexplained sustained space used in the TempDB log file

Is it possible for tempdb log space to not be reclaimable for hours even if there are no sessions running for anywhere near that long?

While it's probably possible, I would say under normal circumstances the answer is no.  In the absence of long-running transactions or long-lived sessions, the tempdb log should clear pretty quickly.

What caused tempdb log space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and tempdb transaction log is full)?

The most likely explanation is that there was a long-running transaction.  It finally encountered this error and was killed, which allowed the log space to free up.

Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do?

I would expect that to capture it.  One thing that might have caused you to miss it is if the process had executed statements in a transaction and then just stopped doing anything (without committing, rolling back, or disconnecting).
This would be the case if someone ran a query in SSMS that used a ton of tempdb, and then just left that SSMS window open all day.  These queries (that are holding resources, but not doing anything actively) won't show up in sp_WhoIsActive by default.  You need to add the @show_sleeping_spids = 2 option to see those.  Here's an excerpt from the built-in docs:

If the tempdb log space used was actually reclaimable but a glitch in SQL Server prevented it from clearing out, is there a T-SQL command I can run to try and free up available space in the log?

Not that I can think of.  You might try issuing a CHECKPOINT, to force the VLFs to clear:
USE [tempdb];
GO
CHECKPOINT;


Your question has already been answered in multiple comments and I hope you were able to solve them by now.

Let me try to summarize them for you in terms of roles, logins and users.

  Logins and users: Logins and Users are basic security concepts in SQL
  Server. They are often, and incorrectly, considered to be pretty much
  one in the same so it is sometimes confusing to some SQL Server users.
  Another important security concept tied to a login and user in SQL
  Server is Security Identifiers (SID). A login is a security principal
  at the scope of the SQL Server instance, and a SQL Server instance can
  contain numerous databases. There are two main types of logins;
  Windows authenticated login and SQL Server authenticated login. Simply
  stated, a login allows you to connect to a SQL Server instance.

  Once you are connected to SQL Server instance, you will typically need
  access to a particular database. To allow access to a database, this
  login must be mapped to a database user. Internally within SQL Server,
  a login is mapped and identified to a user using security identifier
  (SID).

  Roles: Using SQL Server database roles, is the simplest
  security method to assign and manage user permissions. I think this is
  the most common method that Database Administrators (DBA) use to
  handle permissions using either fixed database roles or creating
  user-defined database roles. This comes from over two decades of doing
  SQL database administration work.

  Traditionally SQL Server provides two types of database-level roles:
  fixed-database roles that are predefined in the database and
  user-defined database roles that you can create. The database roles
  are defined at the database level and exist on each database. When the
  DBA maps the logins to databases, he/she can also create members of
  these database roles that manage the security in the database.

Having above definition, in your case you need to be considerate towards granting role - role can be granted on any objects(tables, views, functions, stored procedure etc) to a login(login is not a user as stated above).

For example - we can grant read permission on table employee in a database called Usersdb to a login called John. This can be done using below commands on database:

USE Usersdb;
go
CREATE USER John FOR LOGIN John
go
grant select on employee to John
go

Above example is based on two consideration that login John is not already a user in database Usersdb and employee table is created under default schema dbo.

Second issue with your command is grant all. As it has already been highlighted in comments that this is deprecated by Microsoft and hence shouldn't be used.

  This option (ALL) is deprecated and maintained only for backward
  compatibility. It does not grant all possible permissions. Granting
  ALL is equivalent to granting the following permissions:

  If the securable is a table, ALL means DELETE, INSERT, REFERENCES,
  SELECT, and UPDATE

So, you need to use below commands collectively instead of grant all

GRANT DELETE ON tablename to login;
GRANT INSERT ON tablename to login;
GRANT UPDATE ON tablename to login;
GRANT SELECT ON tablename to login;
GRANT REFERENCES ON tablename to login;

Many points have been already highlighted in comments by Mr. Randi Vertongen, Ronaldo, McNets.

Hope above helps.


Upgrade Single Config Server to Replica Set

Add a the following line in config file

replication:
   replSetName: csReplSet

restart your instance
login and use admin
initiate to replica
add members
configure priority
check your replica cluster.


TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers

I can't give you answer but I can direct you towards it.

You need to download the public symbols for your installed
version of SQL Server
http://www.sqlskills.com/BLOGS/PAUL/post/How-to-download-a-sqlservrpdb-symbol-file.aspx
You need to able the Trace Flag which shows the public function names for
that Stack Trace using DBCC TRACEON(3656) with reference to this blog
Report the public symbol names for those frames back to us or
Microsoft CSS (Customer Support Services)


Behavior of Select expressions containing select

In SQL, 'single quotes' are used for string values, while "double quotes" are used for table/column names.

SQLite allows all types of quotes for compatibility with other databases, but it uses the correct interpretation first, if possible.

So in the subquery (SELECT "Name"), it will try to use a column named Name, and there is one. In other words, the first query is interpreted as:

SELECT NAMES.Name, (SELECT NAMES.Name) FROM NAMES;

(The subquery just refers to the value in the current row of the outer query; this is a rather pointless instance of a correlated subquery.)

SQLite is designed as an embedded database that is accessed from a 'real'
 programming language, so it has no built-in mechanism for dynamic SQL. You have to do the column name lookup separately:

cursor = db.execute("select fname from fields where fid = ?", [2])
fname = cursor.fetchone()[0]
cursor = db.execute("SELECT name, {} FROM NAMES".format(fname))
for name, othercolumn in cursor:
    ...


How do I create a non localhost connection with MySQL?

If I have understand you, what you want is to connect to your mysql server, from another PC different of which is running your mysql service.
In that case, what you need to enter in the Hostname, is the IP address of your mysql server.


Query last time a view, function or sp was last used/read

Unless you have a server side trace running or XEvent running, there is no guaranteed way of telling a particular object is used or not. Some objects might be referenced by a report that is ran once a year by your CIO or business user. So unless you have a concrete data for your business life cycle, there is always a room for error.

I would suggest that once you identify possible candidates for removal, do not drop them, instead rename them and after a year or so, drop them.

When using DMVs you need to log them to a persisted table as DMV data get flushed out due to instance restart, certain sp_configure changes, etc.

I have answered a similar question here that will get you started.


Compatibility levels and reported engine versions

Yes, you can ignore the reported engine version in Azure SQL Database.  This note was added to the @@VERSION function in March of 2018, so I guess it's not gonna change anytime soon:

We are aware of an issue where the product version reported by @@VERSION is incorrect for Azure SQL Database. The version of the SQL Server database engine run by Azure SQL Database is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in Azure SQL Database.
To programmatically determine the engine edition, use SELECT SERVERPROPERTY('EngineEdition'). This query will return '5' for Azure SQL Database and '8' for Azure SQL Managed Instance.
We will update the documentation once this issue is resolved.


SQL Server Editions: Backup restore of Enterprise Edition 2014 on Standard Edition 2017

I believe it is possible to restore with same old answer that subject to condition you are not using any "enterprise specific" features. This you can get from

select * from sys.dm_db_persisted_sku_features.

If you are using any such feature you would have to stop using it, take fresh backup and then restore. I know its hard to tell because starting from SQL Server 2016 Sp1 standard provides far more features support. So at the last the answer would be you need to restore and test.

Good news is SQL Server 2017 RTM Evaluation Edition is released so you have the Eval edition for your testing.


DB Mirroring in async/High Performance mode: Available in SQL Server 2008 Standard Edition?

In the first document you list, it is clearly stated that Asynchronous Mirroring is only supported in the Enterprise Edition of SQL Server 2008.

Synchronous Mirroring is available in the Standard Edition.


How to name tables such as "Suppliers Categories"

Is it correct (acceptable), to name the table that holds the comments
  made on suppliers: suppliers_comments, both plurals? I could have
  named the table simply comments, but then it would have interfered
  with other tables.

You can, But before RENAME of any tables name stop here & see the MySQL blog documentation here Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table.

MySQL RENAME TABLE examples

First, we create a new database named hr that consists of two tables: employees and departments.

To create the hr database

CREATE DATABASE IF NOT EXISTS hr;

To create departments & employees tables

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    id int AUTO_INCREMENT primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    department_id int not null,
    FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

Second, we insert sample data into both employees and departments tables:

INSERT INTO departments(dept_name)
VALUES('Sales'),('Markting'),('Finance'),('Accounting'),('Warehouses'),('Production');

INSERT INTO employees(first_name,last_name,department_id)
VALUES('John','Doe',1),
 ('Bush','Lily',2),
 ('David','Dave',3),
 ('Mary','Jane',4),
 ('Jonatha','Josh',5),
 ('Mateo','More',1);

Third, we review our data in the departments and employees tables:

SELECT
    department_id, dept_name
FROM
    departments;

SELECT
    id, first_name, last_name, department_id
FROM
    employees;

Renaming a table referenced by a view

If the table that you are going to rename is referenced by a view, the view will become invalid if you rename the table, and you have to adjust the view manually.

For example, we create a view named v_employee_info based on the employees and departments tables as follows:

CREATE VIEW v_employee_info as
    SELECT
        id, first_name, last_name, dept_name
    from
        employees
            inner join
        departments USING (department_id);

The views use the inner join clause to join departments and employees tables.

The following SELECT statement returns all data from the v_employee_info view.

SELECT
    *
FROM
    v_employee_info;

Now we rename the employees to people table and query data from the v_employee_info view again.

RENAME TABLE employees TO people;

SELECT
    *
FROM
    v_employee_info;

MySQL returns the following error message:

  Error Code: 1356. View 'hr.v_employee_info' references invalid
  table(s) or  column(s) or function(s) or definer/invoker of view lack
  rights to use them

We can use the CHECK TABLE statement to check the status of the v_employee_info view as follows:

CHECK TABLE v_employee_info;

We need to manually change the v_employee_info view so that it refers to the people table instead of the employees table.

Renaming a table that referenced by a stored procedure

In case the table that you are going to rename is referenced by a stored procedure, you have to manually adjust it like you did with the view.

Note: Here I have taken all codes & pictures from the mysqltutorial.org to clarify the RENAME of table.

After edit about the Naming Conventions for MySQL

Case sensitivity is set when a server is installed. By default, the
names of database objects are case sensitive on UNIX and not case
sensitive on Windows. For example, the names CUSTOMER and Customer
are different on a case-sensitive server.
Database names must be unique. For each user within a database, names
of database objects must be unique across all users. For example, if
a database contains a department table that User A created, no other
user can create a department table in the same database.
A name cannot be a reserved word in MySQL unless you enclose the name
in quotation marks. <a
href="https://dev.mysql.com/doc/refman/5.7/en/identifiers.html" rel="nofollow noreferrer">See
the MySQL documentation for more information about reserved words.

As Mr. Max Vernon already said that here Database names are completely up to the person creating them. Having said that, it makes sense to use some kind of sane naming convention.

For further your ref here


Installing 10g client next to 11g database on same computer

You could live quiet safety with two ORACLE_HOMEs if you know:

%TNS_ADMIN% is your tnsnames.ora / listener.ora / sqlnet.ora directory for both;
in your %PATH% you should specify the right order for your preferred oracle bin;
there's a Oracle_home_selector installed somehere (I don't remember) in full client install
take a look at Windows Registry (something like HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE)
pay attention to %NLS_LANG%


Creating High Availability Cluster with PostgreSQL

There are many ways to set up an HA cluster for PostgreSQL. Some ways are big and tough.

The easiest method is the following:

sync your db to a standby server
Install heartbeat and do a asymmetric clustering

This method is the easiest method to build a HA cluster with graceful degradation  .


Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.
I once wrote a post about the home position of any empty binary log:

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

Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.
I once put that in an answer (Mar 05, 2013 : MySQL Replication without stopping master). In Step 06 from my answer I wrote this:
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

I also used position 4 in these other posts

Apr 02, 2014 : How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743
Mar 05, 2014 : Risk of changing clocktime on MySQL server host

Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.
Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.
GIVE IT A TRY AT YOUR OWN RISK !!!
UPDATE 2014-11-18 22:32 EST
Just for official reference, this example CHANGE MASTER TO command
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.
Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.
UPDATE 2014-11-19 17:47 EST
Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.


How can I determine the maximum possible number of threads?

The best way to see current threads (including all foreground and background threads) is now with performance_schema.  For example:

mysql 5.7.3> select thread_id, name, type, PROCESSLIST_ID from performance_schema.threads;
+-----------+----------------------------------------+------------+----------------+
| thread_id | name                                   | type       | PROCESSLIST_ID |
+-----------+----------------------------------------+------------+----------------+
|         1 | thread/sql/main                        | BACKGROUND |           NULL |
|         2 | thread/innodb/io_log_thread            | BACKGROUND |           NULL |
|         3 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         4 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         5 | thread/innodb/io_ibuf_thread           | BACKGROUND |           NULL |
|         6 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         7 | thread/innodb/io_read_thread           | BACKGROUND |           NULL |
|         8 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|         9 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        10 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        11 | thread/innodb/io_write_thread          | BACKGROUND |           NULL |
|        14 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |           NULL |
|        15 | thread/innodb/srv_error_monitor_thread | BACKGROUND |           NULL |
|        16 | thread/innodb/srv_monitor_thread       | BACKGROUND |           NULL |
|        17 | thread/innodb/srv_master_thread        | BACKGROUND |           NULL |
|        18 | thread/innodb/srv_purge_thread         | BACKGROUND |           NULL |
|        19 | thread/innodb/page_cleaner_thread      | BACKGROUND |           NULL |
|        20 | thread/sql/signal_handler              | BACKGROUND |           NULL |
|        22 | thread/sql/one_connection              | FOREGROUND |              2 |
+-----------+----------------------------------------+------------+----------------+
19 rows in set (0.00 sec)

I believe the background threads are fixed in number (the actual count will depend on some configuration settings such as innodb_read_io_threads and the number of innodb_purge_threads etc.)  The FOREGROUND threads are one per connection + a potential of thread_cache_size on top of that.


Oracle 11g XE 64bits installation problem on Windows 7

After more than 6 hours to fix this, I found this post which help me to understand the problem.

The problem was that I tried to install Oracle 11g XE with my user account connected on the enterprise domain. The installation process, then, wasn't able to connect to the database. I suspect that it try to connect to it from the enterprise domain, and not from localhost.

To solve this, I had to redo the installation with a local admin account on the computer and not as an admin user on my enterprise domain.

Found another post for reference.

I hope this answer will help other guy which encounter the same problem.


How can I speed-up my query on geo-location processes

You may consider using a GIST index based on using the function ll_to_earth. This index will allow for fast "nearby" searches.

CREATE INDEX
   ON locs USING gist (ll_to_earth(lat, lng));

Once you have this index, your query should be done in a different way.

Your (lat, lng) pairs need to be converted to the earth type, and compared with the indexed values (which are of the same type). Your query will need to have two conditions, one for "approximate" result, and one for the "precise" one. The first one will be able to use the previous index:

SELECT
    *
FROM
    locs
WHERE
    /* First condition allows to search for points at an approximate distance:
       a distance computed using a 'box', instead of a 'circumference'.
       This first condition will use the index.
       (45.1013021, 46.3021011) = (lat, lng) of search center.
       25000 = search radius (in m)
    */
    earth_box(ll_to_earth(45.1013021, 46.3021011), 25000) @> ll_to_earth(lat, lng)

    /* This second condition (which is slower) will "refine"
       the previous search, to include only the points within the
       circumference.
    */
    AND earth_distance(ll_to_earth(45.1013021, 46.3021011),
             ll_to_earth(lat, lng)) < 25000 ;

For using this code, you need two extensions (included in most PostgreSQL distributions):

CREATE EXTENSION IF NOT EXISTS cube ;
CREATE EXTENSION IF NOT EXISTS earthdistance;

This is the documentation for them:

Cube. You should take a look at the description of the @> operator. This module is needed by the next one.
EarthDistance. You will find here information about earth_box and earth_distance. This module assumes that the earth is spherical, which is an approximation good enough for the majority of applications.

A test with a table consisting of 2.2 million rows taken from the Free World Cities Database gives me the following answer to the previous query (which is not exactly the same as yours):

"ru","andra-ata","Andra-Ata","24",,44.9509,46.3327
"ru","andratinskiy","Andratinskiy","24",,44.9509,46.3327
"ru","chernozemelskaya","Chernozemelskaya","24",,44.9821,46.0622
"ru","gayduk","Gayduk","24",,44.9578,46.5244
"ru","imeni beriya","Imeni Beriya","24",,45.0208,46.3906
"ru","imeni kirova","Imeni Kirova","24",,45.2836,46.4847
"ru","kumskiy","Kumskiy","24",,44.9821,46.0622
"ru","kumskoy","Kumskoy","24",,44.9821,46.0622
"ru","lopas","Lopas","17",,44.937,46.1833
"ru","pyatogo dekabrya","Pyatogo Dekabrya","24",,45.1858,46.1656
"ru","svetlyy erek","Svetlyy Erek","24",,45.0079,46.4408
"ru","ulan tuk","Ulan Tuk","24",,45.1542,46.1097

To have an "order of magnitude" idea about timings: pgAdmin III is telling me that the time to get this answer is 22 ms. (PostgreSQL 9.6.1 with "out-of-the-box" parameters, on a Mac with Mac OS 10.12, Core i7, SSD)


How can I copy the definition of a field from one table to another at design-time?

I would like to copy those field definitions to another table, without needing to re-write the whole definitions.

How about this?
create table t2 as
select *
from t1
where 1 = 2 ;

OK, it doesn't copy indexes and such like, but it gets you the basic structure, as requested.

I'm working with the Data Dictionary, who seems not to cover this feature.

Given that the job of a Data Dictionary is to manage the structures of other tables, I'd say that's a pretty big hole in its functionality!


Sql Server Full Backup Duration Increased Dramatically

i found the solution.the network admin defrag Disk that cause Database fragmentation.by defrag databases the problem is gone


Can a database table not have a primary key?

Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform.

Should you create a database table without a primary key? No.

Every table should have some column (or set of columns) that uniquely identifies one and only one row. It makes it much easier to maintain the data.

It's true, without a primary key (or some unique key), you don't have an insertion anomaly if you go to insert the same data multiple times.

What you do have is a user table with 300 separate entries for a user named "bob", many of which have different values in the other columns. And, if you've tried to connect rows in some other table to user without a primary key/foreign key relationship - every row in message marked as sent_by "bob" ties back to every one of those 300 user entries. Oh, and there're 39 unique passwords for "bob" (157 of the rows all show the password "password", of course).

Without primary keys, things can get really messy, really fast.


How can I get the user not to be listed twice?

Try the SQL below. I could only create a few sample records, so maybe I haven't covered everything.

BTW, welcome to the forum. In future, when you are asking questions like this one, it would be great if you could provide DDL in the form of CREATE TABLE MyTable(col1, col1_type, col2...) and some sample DML (INSERT INTO TABLE MyTABLE VALUES(val1, val2...). You can put this up on SQLFiddle for example - or here of course if there's not too much data - you can always provide a downloadable script.

Good questions tend to get better answers - check out this (and links within) to see how to get the best out of this forum - it really is a great resource. As it says, help us to help you!

SELECT
    *
FROM
    racerecords
WHERE
    (holder_regid, rec_time) IN (
        SELECT
            holder_regid,
            MIN(rec_time)
        FROM
            racerecords
        WHERE
            race_id = 17
        GROUP BY
            holder_regid
    )
AND race_id = 17
GROUP BY holder_regid   <<=== added this line
ORDER BY rec_time, holder_regid;


Is there a way to check when a user was mapped as DB Owner?

Unless you have a DDL trigger (or some monitoring software) capturing that, you are out of luck.


Microsoft SQL Server Error: 18456(Administrator is also not able to access)

During the installation there is option to choose what are users to be
  added as instance administrators.

  Your customer should add you or your team Distribution list. You
  probably added the built-in Administrators group and now you're
  running as admin, that's the reason you cannot access the instance.

  If yes, then is simply a mater of running the client process elevated
  ('Run as administrator').

Check it out: Connect to SQL Server When System Administrators Are Locked Out

Otherwise;

try to run SSMS (SQL Server Management Studio) on the server with 'Run As Admin' to check if you added builtin\administrators during install. If you cant find any user with enough rights then you have to add a login to the server and give it sysadmin rights.

Start the server in single user mode by starting cmd.exe on the server with administrative rights and running net stop mssqlserver and then net start mssqlserver /m
then in the same window run SQLCMD.

Use the following:

CREATE LOGIN [DOMAIN\ADMINISTRATOR] FROM WINDOWS;
EXEC SP_ADDSRVROLEMEMBER 'DOMAIN\ADMINISTRATOR', 'SYSADMIN';
GO
EXIT

and then run net stop mssqlserver and net start mssqlserver.

If you are running a named instance the servicename is different -
Check services.msc to see the name and you have to run SQLCMD with -S server\instance -E to connect


Best practices for relating NoSQL data to RDMBS

There are two ways I would approach this.

1.) Dump your json from Mongo into a tmp_retrieval_table in MySQL, then write corresponding db functions to perform the parsing / ETL to the schemata of choice in what ever fashion you see fit.

2.) Utilize MySQL's partitioning functionality. Where you could in deed sort the records by a specific key or timestamp.

MySQL Partitioning


How do I model a many-to-many relationship on two different columns?

You have asked: What kind of relationship is this called, and how do I model it in my database?

Your scenario seems to contain a recursive many-to-many relationship: EACH company1 may have (optional) ONE OR MORE associations with another company2. Each company2 may have one or more associations with a company1.  ("company1" and "company2" are both: companies.  I have only given them these names so that the relationship's "directions" become obvious) The traditional way of "resolving" a many-to-many relationship is: creating an intersection/junction.

ERD (entity relationship diagram)

Intersection in relational model:

Doing a "fast forward" to the actual implementation, maybe the following will be useful for you:

1 - for recording all NODES in your existing model: have one table for recording unique company ids, use PK constraint.

2 - for all EDGES in your existing model: have another table for recording VALID combinations. Use 2 foreign key constraints (only existing company ids are allowed), and a PK constraint that guarantees the uniqueness of the recorded combinations. (You can also use a CHECK constraint, in order to avoid associations of a company with itself.  However, your test data values , line 4: 79735371  79735371, suggest that a node can be associated with itself.)

Example implementation (Oracle 12c):

-- ~ nodes in graph, uniqueness through pk constraint ~
create table companies(
  id number primary key
);

-- insert unique values ~ nodes ~
begin
  insert into companies (id) values (9581091);
  insert into companies (id) values (79735371);
  insert into companies (id) values (53506312);
  insert into companies (id) values (961273620);
  insert into companies (id) values (135962137);
end;

--- ~ edges in graph ~
create table associations(
  co1 number references companies(id)
, co2 number references companies(id)
);

alter table associations
add constraint apk
primary key (co1, co2);

testing

-- use your test data
begin
  insert into associations values (9581091, 53506312);
  insert into associations values (9581091, 961273620);
  insert into associations values (79735371, 53506312);
  insert into associations values (79735371, 79735371);
  insert into associations values (79735371, 135962137);
end;

-- Optional: testing the intersection/junction by inserting all possible combinations:

insert into associations
select
  c1.id
, c2.id
from companies c1, companies c2;


INSERT INTO using the RETURNING values from another INSERT

Use a data modifying CTE:

with t1 (name) as (
  INSERT INTO table1 (name, address) VALUES ('me', 'home')
  returning name
)
insert into table2 (name)
select name
from t1;


Find month to month percentage growth rate

You can use a variable to simulate a LAG() function, as far as I don't know how many records by month I've used a group by just to be sure.

select year, month, entry,
       if(@last_entry = 0, 0, ((entry - @last_entry) / entry) * 100) "growth rate",
       @last_entry := entry
from
      (select @last_entry := 0) x,
      (select year, month, sum(entry) entry
       from   mytable
       group by year, month) y;

This is the result:

| year | month | entry | growth rate      |
|------|-------|-------|------------------|
| 2017 | 3     | 1     | 0                |
| 2017 | 4     | 1     | 0                |
| 2017 | 5     | 2     | 50               |
| 2017 | 6     | 9     | 77,7777777777778 |

rextester here


Is there a way to force one optimization level in SQL Server?

I think this question represents a misunderstanding of the optimisation phases and a willingness to try and override the optimizer ("I know best") when there is really no need to do so, as from my experience the optimizer gets it right most of the time.  For the odd occasion it doesn't there are more appropriate tools for experienced developers to work around, eg query rewrites, different indexing strategies, filtered indexes/stats, hints, plan guides, plan forcing.  Again from my experience on occasions the optimizer isn't quite getting it right it tends to be because of over-complexity, either in the query or schema design, although there are occasional bugs.

Phases search 0 - 2 are also components of Full Optimisation and not all queries are candidates for this.  For example simple queries are much more likely to be optimised as Trivial Plan which is cheaper to run than full cost-based optimisation.  Why force full optimisation for simple queries where there is only one path?  Additionally optimisation can drop out of these phases if a 'good enough' plan is found.

You can influence some control over the optimizer.  Here's a few examples:

give the optimizer as much information as possible so it can make good decisions, eg up-to-date statistics, foreign keys, unique indexes, constraints, appropriate datatypes
to encourage simple plans you could only write simple queries; build up resultsets eg using temp tables
prevent parallel plans - turn off parallelism at server level ( eg with sp_configure 'max degree of parallelism' ), or at query level using OPTION ( MAXDOP 1 ).  This isn't necessarily such a bad idea in an OLTP system for example.
there is a trace flag for encouraging parallel plans, but not generally recommended

So hopefully you can see it doesn't really make sense to force the optimizer to run in only one of these modes.

Do you have some specific examples in mind where you don't feel the optimiser is doing a good job?  Post them here as questions and I'm sure someone will be able to help.


Query optimization for multiple joins postgres

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order CREATE INDEX accountholder_LastModificationTimestamp_idx ON acountholder(LastModificationTimestamp DESC NULLS LAST);

Since you are using top 5000 entries indexing is not actually good thing. And index maintenance is expensive. You will be faster by doing sequential scan. ;) If you use timestamps in your search queries that use index, otherwise not.
Useful article:
PostgreSQL Indexes

And if you often hit the same query without the data being updated you can have the materialized view:
Materialized views in PostgreSQL docs

I would put not changing things into materialized view (you can refresh it when need it).
So you will have less joins, and when playing with it use explain (planning) or explain analyize (planning and executing). Use analyze so you update your statistics.

After looking at the diagram I would do the following:

Extract the changing part of the AcountHolder into separate table with 1:1 relationship, and leave Revision history into separate table (because there is a last modification timestamp and refreshing materialized view is very expensive) and put everything else into materialized view.


difference between root and ALL PRIVILEGES

To answer your question:

  Is the new user able to create other users?

The answer is NO, the new user is not able to create other users nor grant/revoke privileges to other users.

If you want to create a root similar user you must issue the following statement:

GRANT ALL PRIVILEGES ON *.* TO 'non-root'@'localhost' WITH GRANT OPTION;


Do SHOW CREATE TABLE for one of the tables where you need Emoji.  It probably says 'utf8' on the columns and/or default for the table.  The columns must be utf8mb4.
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

Will blindly change all the columns to utf8mb4.  This is probably the action you need.
Also, the connection needs to specify utf8mb4.  Since I don't know what client you are using, I can't provide specific information.  If necessary, run this right after connection: SET NAMES utf8mb4;
Terminology:

"character set", such as utf8 or utf8mb4 is the encoding for the characters.  This is where you are having trouble with storing Emoji.
"collation" is the ordering, such as whether 'a' < 'b' and whether there is case folding to make 'a' = 'A'.  Collation may be important when comparing (WHERE) or sorting (ORDER BY).


User can't execute stored procedure despite in role with permission

this script gives the permissions assigned to a table or stored procedure in a script format.

have a look what it returns for your MyProcedure
that could be a start.

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as +
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE 1=1
    AND o.name IN ('MyProcedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs
ORDER BY dpr.name


Sum values from child tables and ignore equal rows

Use:

SELECT *
 FROM Project AS P
OUTER APPLY
(SELECT SUM(C.project_cost) AS TOTAL_COST
    FROM Cost AS C
    JOIN ProjectBenefitCost AS BC
    ON P.id = BC.project_id
    AND BC.id = C.benefitcost_id) C
 OUTER APPLY
 (SELECT SUM(B.project_benefit) AS TOTAL_BENEFIT
    FROM Benefit AS B
    JOIN ProjectBenefitCost AS BC
    ON P.id = BC.project_id
    AND BC.id = B.benefitcost_id) B
;

You can't have your join make multiple copies of your project row without also making multiple copies of your benefit row. So when you join through to cost and benefit, you need to aggregate the rows there down to a single row per project before the join kicks in.


The C# code you are using to invoke the stored procedure is passing in @order_id as the first parameter.  That parameter is not referenced in the actual stored procedure.  Try removing that parameter from the C# code.


When troubleshooting the error I've searched the internet for possible solutions. The main solution to this error is the 32bit / 64bit excecution of SSIS.

Because I can run other packages without any problems all posible solutions I found regarding configuration of packages, SSIS and Visual Studio didn't apply.

Maybe I did something wrong when I generated the packages with BIML, so I started with an empty package and see if it ran.... Still the same error.

Eventually i changed the name of the package to "crm contact" and it ran. It seems that using blockquotes ( [ ] ) in the name gave the error.


You are using the wrong quotes for the SQL

SUGGESTION #1 : Use Single Quotes for the SQL

sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,'test','gmail.com');"

SUGGESTION #2 : Escape Double Quotes for SQL with Backslash (\)

sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,\"test\",\"gmail.com\");"

The confusion is caused by the double quotes you are using for the sudo and for the SQL

GIVE IT A TRY !!!


It's called Schema migrations and one of hundreds of tools that can manage that is migrate written in GO.

  Database migrations written in Go. Use as CLI or import as library.

I never heard that any of those schema migration tools can do a diff between dev and prd.


Securely generate a UNIQUEIDENTIFIER in SQL Server

SELECT CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER)

Should do the trick I would have thought.

CRYPT_GEN_RANDOM

  Returns a cryptographic random number generated by the Crypto API
  (CAPI).


Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL

OK I found where the problem is
the syntax for calling the procedure is incorrect
I have to call the procedure like this :

begin

   s_print_data(1);
  end;

and everything works fine


How do I assign an entire Active Directory group security access in SQL Server 2008?

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal
Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal"
Add user to role (also a "database level principal")
GRANT permissions to the roles on the tables (a table or proc etc is a "securable")

Sample script

USE master;
GO
CREATE LOGIN [MYDOMAIN\APPLICATION SUPPORT] FROM WINDOWS;
GO
USE mydb;
GO
CREATE USER [MYDOMAIN\APPLICATION SUPPORT] FROM LOGIN [MYDOMAIN\APPLICATION SUPPORT];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO

sp_addrolemember is deprecated starting with SQL Server 2012, where ALTER ROLE should be used instead.


Database Design (how to handle subclass & superclass)

I've just recently answered a similar question here. What you want is a People table containing data common to all people contained in your DB with other subsidiary tables providing the additional data unique to different roles some of those people play in the operation of your service. The linked question and answer should provide the details you need. If not, a couple of my answers to related questions can be found here and here. However, perform a search to get a variety of answers from other contributers. I have found many times that the best answer for a particular situation may be a combination of techniques from several sources.


Postgres INNER JOIN same table failing on CartoDB

The problem may be that you have multiple columns of the same name in the result (2x cp). When using SELECT * you would return both.

The simple fix for the query at hand would be USING in the join clause, which only keeps one instance of the joined column in the result (and seems smarter in this particular case anyway):

SELECT *
FROM   traffic_counts s1
JOIN  (SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2 USING (cp);

Updated SQL Fiddle.

BTW, SQL Fiddle itself was updated only recently to allow such ambiguity. Until some months ago it also raised an error for queries with duplicate names in the result. Quite a few clients cannot cope with that.


Think of the interval between log backups as the maximum amount of data you can afford to lose.

Realize you need to restore a "FULL" backup, plus all the log backups taken since the full backup in order to get back to the most recent point in time.

Also, practice your restore strategy.  This is perhaps the single most important thing you can do vis-a-vis backups.  You can have all the backups in the world, but if you don't practice how to restore them, they won't do you any good in the event of disaster.

Regarding your questions:

  My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

Taking a full backup and a log backup will leave your log in a state where it is pretty close to empty (unless you have something like log shipping, mirroring, always on, etc, that relies on log records).  The log will subsequently continue to grow until the next log backup is taken, at which point SQL Server will begin re-using space in the log file.  For some interesting reading about log growth, please see this question.

  If I take a nightly backup, then take a log backup right after, will that leave a break in consistency if there happens to be transactions going on at that time?

Any transactions that have not been committed at the precise moment a log backup begins will be automatically rolled back during the recovery process.  However, if you have a later log backup where those transactions were committed, they will be committed during recovery once that later log backup is restored.

Typically, a sound production level backup strategy consists of the following minimal steps:

Take a full backup once per day, at some low-activity point.  For instance say 2:00am.
Take log backups every 15 minutes, retaining every log backup taken since the last nightly full backup.
Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

If once-per-day full backups are unworkable, you can instead do:

Once per week, take a full backup.
Once per day, take a differential backup.
Once every 15 minutes take a log backup.  Retain every log backup taken since either the last full backup, or the last differential backup, whichever is most recent.
Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

You may be interested in looking at Ola Hallengren's backup solution which is very highly regarded in the SQL Server community.


Why is log file divided into virtual log files in SQL Server?

I believe that some other DBMSs requires several files, to provide for deletion of old log files as they are no longer needed. SQL Server doesn't need that thanks to the subdivision of one ldf file internally into several virtual log files. One can argue which concept one would prefer (several files, or one file with VLFs), but that decision was made for us about the mid ninties (the first version of SQL Server with the current storage architecture was released 1998).


From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

Picking up on the sqlserver.read_only_route_complete Extended Event mentioned by Kin and Remus, it's a nice Debug event, but it doesn't carry a great deal of information with it - just route_port (eg 1433) and route_server_name (eg sqlserver-0.contoso.com) by default.  This would also only help determine when a read-only intent connection was successful.  There is a read_only_route_fail event but I couldn't get it to fire, maybe if there was a problem with the routing URL, it didn't seem to fire when the secondary instance was unavailable / shutdown as far as I could tell.

I have however had some success joining that up with the sqlserver.login event and causality tracking enabled, along with some actions (like sqlserver.username ) to make it useful.

Steps to Reproduce

Create an Extended Events session to track relevant events, plus useful actions and track causality:

CREATE EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD EVENT sqlserver.login
    ( ACTION ( sqlserver.username ) ),
ADD EVENT sqlserver.read_only_route_complete
    ( ACTION (
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username
        ) ),
ADD EVENT sqlserver.read_only_route_fail
    ( ACTION (
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username
        ) )
ADD TARGET package0.event_file( SET filename = N'xe_watchLoginIntent' )
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,   --<-- relate events
    STARTUP_STATE = ON      --<-- ensure sessions starts after failover
)

Run the XE session (consider sampling as this is a Debug event), and collect some logins:

Note here sqlserver-0 is my readable secondary and sqlserver-1 the primary.  Here I'm using the -K switch of sqlcmd to simulate read-only application intent logins and some SQL logins.  The readonly event fires on a successful read-only intent login.

On pausing or stopping the session I can query it and attempt to link up the two events, eg:

DROP TABLE IF EXISTS #tmp

SELECT IDENTITY( INT, 1, 1 ) rowId, file_offset, CAST( event_data AS XML ) AS event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file( 'xe_watchLoginIntent*.xel', NULL, NULL, NULL )

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( event_data );

-- Pair up the login and read_only_route_complete events via xxx
DROP TABLE IF EXISTS #users

SELECT
    rowId,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #users
FROM #tmp l
WHERE l.event_data.exist('event[@name="login"]') = 1
  AND l.event_data.exist('(event/action[@name="username"]/value/text())[. = "SqlUserShouldBeReadOnly"]') = 1

DROP TABLE IF EXISTS #readonly

SELECT *,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/data[@name="route_port"]/value/text())[1]', 'INT' ) AS route_port,
    event_data.value('(event/data[@name="route_server_name"]/value/text())[1]', 'VARCHAR(100)' ) AS route_server_name,
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="client_app_name"]/value/text())[1]', 'VARCHAR(100)' ) AS client_app_name,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #readonly
FROM #tmp
WHERE event_data.exist('event[@name="read_only_route_complete"]') = 1

SELECT *
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer

SELECT u.username, COUNT(*) AS logins, COUNT( DISTINCT r.rowId ) AS records
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer
GROUP BY u.username

The query should show the logins with and without application read-only intent:

read_only_route_complete is a Debug event so use sparingly.  Consider sampling for example.
the two events together with track causality offer the potential to fulfil  your requirement - further testing necessary on this simple rig
I did notice if the database name was not specified in the connection, things didn't seem to work
I tried to get pair_matching target to work but ran out of time.  There is some potential for development here, something like:

ALTER EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD TARGET package0.pair_matching (
    SET begin_event = N'sqlserver.login',
        begin_matching_actions = N'sqlserver.username',
        end_event = N'sqlserver.read_only_route_complete',
        end_matching_actions = N'sqlserver.username'
    )


SSRS 2008 to 2014 Migration Excel Export error

This appears to be related to some implicit conversions that are occurring in the background when exporting the report to xlsx.  Numeric columns are being sent to excel as if they are text columns.  One report responded to a change to the query using an Explicit cast to Numeric (Cast as opposed to convert to remove any commas or other formatting that might confuse the conversion).  Several other reports did not respond to the Cast but returned correctly after using an expression conversion of VAL().  I still have several reports that are not responding to either.  If anyone out there has a good explanation for this I'd love to hear it.


Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions.

The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the differences between old and new version are a concern on their own and having to deal with plan regression is expected anyway, so I prefer fighting with it with TF4199 enabled.

AS far as existing databases is concerned, there is only one way to know: test it. You can capture a workload on the existing setup and replay it after enabling the flag. RML Utilities can help you automate the process, as described in this answer.

Obviously, the flag affects the whole instance, so you'll have to test all the databases sitting there.


SQL Server Optimization/Configuration for Inflexible Application

We've all been there. Those that haven't are going to run into this situation one day!

There is a text book approach to dealing with third party horror software:

Prove there is a problem. Identify and document crazy queries and data access patterns.
Approach the vendor with your evidence of a problem. If you can show that a re-write of a query shows improvements are possible, it gets harder for the vendor to ignore your concerns.
If your getting no response from your contact at the vendor, escalate. If you can't make enough noise to be heard, maybe your CTO can.
If you've reached step 4 without any success, you're likely to be sacrificing follicles or noticing the telling first signs of grey. This is where a voodoo doll of the third party development lead can ease the stresses of the day. A dartboard featuring the company logo is an acceptable alternative.

Back in the real world, what practical approaches do we have:

Kill it with iron. It always pains me to say it but throwing hardware at the problem can be the fastest, cheapest, lowest risk solution at times.
Understand the root causes. Run the same analysis as you would for a system that was fully under your control. Identify the most frequent queries, those with highest CPU, highest reads, highest duration, look at tempdb usage, analyse the plan cache (single use ad-hoc queries crop up often in these scenarios).
Indexing. The Database Tuning Advisor (DTA) is a useful tool for analysing workloads you have little control over. In particular, it can spot candidates for indexed views and missing multi-column statistics which may not be obvious.
Plan guides. If you spot a query that you can re-work the execution plan for, plan guides give you a way of forcing that plan without modifying the source query.


Contributions originally left as comments

I typically aggregate dynamic columns into a single JSON value these days rather than creating dynamic columns for each attribute. That might be a challenge though as well, with your outdated (and unsupported) Postgres version - a-horse-with-no-name

The desired output can't be obtained directly in SQL. You need to write a query that generates the query that generates your desired output. Giving up on your desired output and producing JSON instead may also be considered, as it would be simpler. - Daniel Vérité


Select with a preffered where condition, but if missing use another

One way of accomplishing this might be a self-join (assuming (name, lang) uniquely identifies rows):

SELECT e.name, coalesce(s.text, e.text) AS text, coalesce(s.lang, e.lang) AS lang
FROM (
  SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang = 'E') AS e
LEFT JOIN (
  SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang = 'S') AS s
ON e.name = s.name


Can I upgrade SSIS 2008 packages directly to SSIS 2017 SSISDB?

Maybe.

SSIS packages as a general rule are forward compatible - if you run a 2005 package using the SQL Server 2019 binary of dtexec.exe the first step it will do is an in-memory upgrade of the package to a 2019 equivalent version. Once the package exits, the upgraded package disappears (in-memory only) and all is well and good.

My experience is that there are two cases where the auto upgrade fails: third party components and Script Components - typically source components but async transformations are equally likely candidates. 2008 and 2012 had changes in the signature of the pipeline/scripting stuff that the upgrade wizard couldn't always auto figure out. I never figured out precisely what would throw it out of kilter but something would. Generally speaking, opening the package up in the newer version of SSDT and then double clicking the script component would force it fix itself and worse case, it'd tell you what was off once you were in there.

The important thing to know moving the SSIS project from 2008 to 2012+ is that the wizard will encourage you to move to the "new" project deployment model. Currently, you're in the package deployment model. The package deployment model allows all the packages to work at their own pace - no dependencies on any other package (at least enforced in code). You're welcome to use whatever logging you wish, same with your configuration approach and package protection levels.

But, your question specifically asks about moving to SSISDB so yeah, you get to tackle those questions. With the Project deployment model, all the packages in an SSDT project need to be in harmony.

Oh and a while I've not tested since the release candidate era, they introduced  Incremental Package deployment to the SSISDB with 2017.

Fire up ISDeploymentWizard (C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe)
Change the deployment model from Project Deployment to Package Deployment
Select the package(s) of interest
Connect to your server and create the deployment folder and then create a new project to hold them
Click Next, Click Deploy your packages and cross your fingers


Is a query inside mysql IF clause allowed?

You have a syntax error

This is not the right SQL for getting the count

Select COUNT(*) from `user` into c where `mobile_no` = mobileNo;

You do not put into c after the from clause. It goes before it.

Therefore, this is correct SQL syntax

Select COUNT(*) into c from `user` where `mobile_no` = mobileNo;

Give it a Try !!!


mySQL Data loading: Better to column map or INSERT SELECT INTO ETL?

Since you have CSV files, it makes a lot more sense to use LOAD DATA.  Put them into 125 tables.  After that, you can do the massaging that you suggest doing with bash.


import mysql schema data folder instead of from a dump

The file ibdata1 has "tablespace ids" in it that need to correspond to the .ibd files containing the data, etc.  Since these ids are generated by the instance, when you reloaded the dump, they may be different than the ones on the production machine.  Hence, the rsync could be copying wrong ids.

Moral of the story:  Don't mix source dumps (mysqdump, xtrabackup, etc) with file backups (rsync, etc).

Your backup is hosed; you will need to start over.

Some backup techniques (starting with least-invasive):

Zero downtime (after set up):  Master-Slave; the Slave is continually updated as a viable backup.

Near zero downtime:  LVM.  After setting up a snapshot area, you (1) stop mysqld, (2) "snapshot" the filesystem, (3) restart mysqld, and (4) rsync the snapshot to wherever.  Step 4 is the only slow part; it does not impact the running of the db other than chewing up lots of I/O.

incremental backup via binlog

Xtrabackup, with some of the non-trivial features.


Is conditionally SQL Server log backup possible?

My idea is to count(*) the result of this function and if it increased then I'll take a log backup. But I'm not sure whether read will cause log record increase.

A read action, including reading from the system management views/functions should not cause any log activity, so this would work as you intend.

But why do you need to skip the log backups in this case? They will be minimally small as there has been no new activity since the last log backup so won't consume significant resources to take, store, transfer, and (if needed later) restore, and you would be adding an extra complication (so a potential point of failure) into your process.


T-SQL tool that requires a semicolon to end statements

Rather than relying on semicolons (a misplaced semicolon could also cause the problem you are describing), better to rely on transactions.

Set up your query tool to require a commit, and you will have the option rollback if necessary.

In SSMS, you can do this by checking the option SET IMPLICIT TRANSACTIONS under Tools/Options/Query Execution/SQL Server/ANSI. Or, you can turn it on with this command:

SET IMPLICIT_TRANSACTIONS ON

SET IMPLICIT_TRANSACTIONS (Transact-SQL)

  When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit
  transaction mode. When OFF, it returns the connection to autocommit
  transaction mode.

  When a connection is in implicit transaction mode and the connection
  is not currently in a transaction, executing any of the following
  statements starts a transaction:

  ALTER TABLE FETCH REVOKE BEGIN TRANSACTION GRANT SELECT CREATE INSERT
  TRUNCATE TABLE DELETE OPEN UPDATE DROP

  If the connection is already in
  an open transaction, the statements do not start a new transaction.

  Transactions that are automatically opened as the result of this
  setting being ON must be explicitly committed or rolled back by the
  user at the end of the transaction. Otherwise, the transaction and all
  of the data changes it contains are rolled back when the user
  disconnects. After a transaction is committed, executing one of the
  statements above starts a new transaction.


How necessary are primary and foreign keys when I truncate all the tables in my database daily?

PRIMARY KEYs are important; use them.  Since there seems to be a 'natural' PK, use it instead of a surrogate AUTO_INCREMENT.

FOREIGN_KEYs are not a requirement.  They do, however, create helper INDEXes, but you could create those without FKs`.

If the data is changing every day, what would you do if an FK failed?

Using TRUNCATE, then LOAD leaves the table empty for a brief time; if that is a problem, do this:

CREATE TABLE x LIKE real;  -- or use a regular CREATE.
LOAD DATA ... INTO x;
RENAME TABLE real TO old, x to REAL;
DROP TABLE old;


Both posts are wrong syntax.

I've -1 in the first from SO and left a comment on the second.

Create a table

SELECT * INTO PlayerBackups FROM NhlPlayer

Inserts to an existing table

INSERT PlayerBackups SELECT * FROM PlayerBackups


Broken mysql replication on slave server (error 1236)

I believe the log file and position need to be specified with the host.

Try:

CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;


Speed up AWS PostgreSQL insertion via Python script

t2 classes are unsuitable for high work loads, by design.  Once you have used up all your CPU credits, performance is abysmal.  Again, by design.

Don't use t2 (or any t*) instances if you care about performance.

As far as I can tell, there is no way to monitor the CPU credits on RDS (the way you can on EC2)


Triggers on Slave database (in the context of replication) for auditing purpose in MySQL

@parag Yes you can create triggers on the slave which won't be replicated. The only issue I see is if you're Master fails and you promote your slave to master you will need to keep track of what changes need to be applied to the new slave and what changes need to be applied to the newly promoted master.
If I'm not mistaken you can create the triggers on the master so that they can be replicated and then disable them and just enable them on your slave.  That way all your environments will have the triggers but only enabled on one slave.

Or, so you can avoid managing details like that, which can become overwhelming, you can make the slave, the master of another slave which will do the auditing and only have the triggers on that subsystem.

Thanks
John


ms sql query multiple table colums as identical tag

You need to use FOR XML clause at the end. You can specify each row's node with PATH and the root with ROOT. Also the having a column alias with @ at the start with move the value to an attribute.

;WITH YourTable AS
(
    SELECT
        V.ID,
        V.Name
    FROM
        (VALUES
         (1, 'Bert'),
         (2, 'Agnes'),
         (3, 'Diane')
         ) V(ID, Name)
)
SELECT
    [@ID] = T.ID,
    T.Name AS '@Name'
FROM
    YourTable AS T
FOR XML
    PATH('MyTag'),
    ROOT('MyRootElement')

Result:

<MyRootElement>
  <MyTag ID="1" Name="Bert" />
  <MyTag ID="2" Name="Agnes" />
  <MyTag ID="3" Name="Diane" />
</MyRootElement>

You can find all the XML options on the docs. This particular PATH mode is explained here.


how to find the rows that contain byte zero?

SQL server somehow badly understands \x00...

Maybe this trick will help you slightly?

create table test (id int, txt varchar(8));

insert into test values
(1, '123' + char(0) + '456'),                -- in the middle
(2, '123' + char(0)),                        -- at the end
(3, char(0) + '456'),                        -- at the beginning
(4, '12' + char(0) + '34' + char(0) + '56'), -- in the middle
(5, '123456');                               -- absent

select id, pos
from test
cross apply ( select number + 1 pos
              from master..spt_values
              where type='P'
                and number < len(txt) ) numbers
where substring(txt,pos,1) = char(0)
order by id;

Output

id | pos
-: | --:
 1 |   4
 2 |   4
 3 |   1
 4 |   3
 4 |   6


Why does sqlexpress run on .\sqlexpress while mssqlserver runs on .\

"Why is it that sqlexpress runs on .\sqlexpress while mssqlserver just runs on .\?"

Because SQL Server version 7 only supported one instance per server.  And when the ability to run multiple instances was introduced in SQL Server 2000 Microsoft has to figure out some way for clients to distinguish between the instances.

And back at that time connecting to SQL Server using protocols other than TCP/IP was still common, especially Named Pipes, so just using different ports wouldn't have worked.


Game database- many to many relationship

Yes, I believe you are on the right track.  The gameDate and gameScore fields are more a part of the game entity and not the player, so they should be on the Gametable.  Besides, bridge tables should really only contain the related ID fields.

If gameDate and gameScore were on the PlayerPlaysGame table, they would only be dependent on the gameID and not the playerID.  By definition, this would constitute a partial dependency, and would violate second normal form.


Latin1 to UTF8 on large MySQL database, minimal downtime

Promoting a slave would probably be my preferred route.  As you pointing out any selects on MyISAM tables would require table level locks.  There is one tool that might be able to help, pt-table-sync.  It's primary purpose is to find gaps and differences in existing master slave relationships.

A nice thing about it is it does this in nice "chunks".   Think of it kind of like antilock breaks.  The chunk size is configurable but you could, for example go through doing 1000 rows at a time, minimizing lock times and letting things flow through.  I haven't used it to fully repopulate a slave from scratch though although I'd give that a look.  Once you have a full copy, do another run to catch new stuff, updates that have come in.  Do a flush table with read locks, do a final table sync.  run show master status to get the binary log position to start slaving from, unlock tables.

Oh, if you don't have binary logging you'll at least need a master bounce and cnf change unfortunately.

Another approach that might be much simpler depending your write downtime tolerance.  You're all myisam,  you gave the size in rows by what's the disk footprint in MB or GB?   Figure out how long it would take to transfer that size between your machines (hopefully there both in the same local network).  You could do flush table with readlock, again still show master status, then just rsync the .MY* files over to your new DB's datadir.

One final alternative, depending on how you're setup: Can you do an LVM or other kind of filesystem snapshot? This would be the best way to minimize downtime.  You flush tables with readlock, show master status, start snapshot, then unlock the tables to allow full read write activity to flow through.  The difference here is you'll copy the snapshot you started.  You'll just need to feel confident the write activity won't exceed the snapshot size you allocate before the copy finishes.

What ever method, before promotion I would verify the character set conversions went as desired.  They can be a real pain to reverse.

I would also recommend upgrading to innodb if possible to make it possible to use xtrabackup in a non blocking fashion in the future.