How to query a database for empty tables Due to some 'developers' we had working on our system we have had issues with empty tables.  We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't.

I would like to run a query the system tables to find what user tables are empty.  We are using MS SQL 2008 R2.

Thanks for the help.

Leverage sys.tables and sys.partitions:

select
    t.name table_name,
    s.name schema_name,
    sum(p.rows) total_rows
from
    sys.tables t
    join sys.schemas s on (t.schema_id = s.schema_id)
    join sys.partitions p on (t.object_id = p.object_id)
where p.index_id in (0,1)
group by t.name,s.name
having sum(p.rows) = 0;

Use a sum of rows just to make sure you don't have confusion with partitioned tables.  Index_ID of 0 or 1 means you're only looking at the row counts for your heaps or clustered indexes.


SSRS 2017 reports having issues with IE 11? I'm experiencing symptoms very similar to what is described in this 2015 MSSSQLTips article:

SQL Server Reporting Services (SSRS) is not compatible with Internet Explorer 11

  You upgraded to Internet Explorer 11, now when you preview a SQL Server Reporting Services (SSRS) report you find the print command is not visible in the report viewer. Also, sometimes when you change parameter values IE11 stops responding or it takes a long time to load the report. You may also find that the toolbar that displays the report controls and export features, appears on multiple lines when viewed with IE11. When you preview the same SSRS report in Chrome or Mozilla Firefox, it works fine without any issue, which means it is a browser compatibility issue. How can you make SQL Server Reporting Services (SSRS) compatible with IE11?

Virtually all discussion I can find of this problem refers to this article (or to a now-unavailable MS Connect page).

The problem is that the server-side fix suggested for SSRS 2008 doesn't work for SSRS 2017 (there is no <meta http-equiv="X-UA-Compatible" content="IE=5"> tag in the ReportServer\Pages\reportviewer.aspx file).

Is there an equivalent server-side fix for SSRS 2017? Should I be adding this meta tag to this or a different ASPX file?

Other posts suggest upgrading SSRS, like this question about SSRS 2012, but I'm already on the next-to-last version of SSRS 2017 (14.0.600.689), and the change log for the more recent patch doesn't seem to contain any relevant fixes.

(A client-side fix isn't absolutely out of the question, but would involve a more difficult AD group policy push, so I'd prefer a server-side fix if there is one.)

try add

<meta http-equiv="X-UA-Compatible" content="IE=EDGE">

tag in the ReportServer\Pages\reportviewer.aspx file.

https://stackoverflow.com/questions/6771258/what-does-meta-http-equiv-x-ua-compatible-content-ie-edge-do


How to select redundant rows Suppose a table data recording values of some foo and bar over time:

time | foo | bar
-----+-----+----
1    | a   | a
2    | a   | a
3    | a   | a
4    | B   | a
5    | B   | a
6    | a   | a
7    | a   | a
8    | a   | X
...

We would like to select the duplicate rows in order to get rid of them. Duplicate rows are, in this example, those with times 2, 3, 5 and 7, because the tuple (foo, bar) did not change from the previous time. Also, while the row 6 seems to be duplicate of 1, 2 and 3, there is a change in between, thus 6 is not a duplicate (but 7 is).

My simple solution is this:

SELECT
  *
FROM
  data AS candidate
WHERE
  EXISTS (
  SELECT
    *
  FROM
    data AS original
  WHERE
    ROW(candidate.foo, candidate.bar) IS NOT DISTINCT FROM ROW(original.foo, original.bar)
    AND original.time < candidate.time
    AND NOT EXISTS (
      SELECT
        *
      FROM
        data AS other
      WHERE
        ROW(candidate.foo, candidate.bar) IS DISTINCT FROM ROW(other.foo, other.bar)
        AND original.time < other.time
        AND other.time < candidate.time
    )
  )

In human language: looking for rows where we can find a row above it with the same (foo, bar), but where there is no other row with some other (foo, bar) in between.

Is there some feature of PostgreSQL that could be used for this purpose?

There are many ways for doing this.

When measurements can be trusted to be sequential (time), then one can use a simple self left join, which should perform very fast:

SELECT *
FROM data d
LEFT JOIN data prev ON (prev.time = d.time - 1)
WHERE (d.foo, d.bar) IS DISTINCT FROM (prev.foo, prev.bar)

Another one is using window functions (PG version >= 8.4):

SELECT time, foo, bar
FROM (
  SELECT *, lag(foo) over () AS prev_foo, lag(bar) over () AS prev_bar
  FROM data
  ) d
WHERE (foo, bar) IS DISTINCT FROM (prev_foo, prev_bar)

And another option is using a recursive CTE (PG version >= 8.4):

WITH RECURSIVE d(time, foo, bar) AS (
  SELECT * FROM (SELECT * FROM data ORDER BY time LIMIT 1) a
  UNION ALL
  SELECT *
  FROM (
    SELECT data.*
    FROM data, d
    WHERE data.time > d.time AND (data.foo, data.bar) IS DISTINCT FROM (d.foo, d.bar)
    ORDER BY data.time
    LIMIT 1) a
)
SELECT * FROM d


Who started the SQL Server Agent? How to find out who started the 'SQL Server Agent' in Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64)

Windows Applicationlog ID 102: SQLServerAgent service successfully stopped.
Windows Systemlog ID 7036: SQLServerAgent service successfully stopped.
SQL Server Logs: Event ID: [098] SQLServerAgent service successfully stopped.

It doesn't show who did it. I think you need to configure auditing or tracing for this. For tracing there is a Security Event called Audit Server Starts and Stops.


Use the DATENAME() function and specify the datepart as weekday.

select ID, Name, Salary, Date
from dbo.yourTable
where datename(weekday, Date) in ('Saturday', 'Sunday');

As Aaron pointed out, this relies on the language being set to English.  Likewise, you could use the DATEPART() function with weekday and test for Saturday and Sunday values.


SQL Server Metric Report Information For Management We have a quarterly metric meeting and I report the metrics for all of our production SQL Servers.  The people that are looking at these reports are mostly management (some technical, but others are not so much) and want to see a nice overall picture of the performance of the SQL Servers, but I really don't want to give the generic metrics of CPU, number of db, disk space usage, etc.

Currently, I have some numbers like average batches per database, restore time for databases and I do have the number of databases (even though I said I really didn't want that one).

Any suggestions on other metrics that may be interesting to show managment that would be easy for them to understand and actually show the health of the SQL Servers?

If as you say your management don't give you clear directions, I think it gives you an opportunity to be proactive and show them you're worth more than they're currently paying you. Listen to other people reporting their metrics and figure out what management feels is important from the business perspective.

"X batches per second"? Meh. "My databases can support up to x online orders per second with current hardware; this meets our peak workload of y orders per second on Black Friday". Sounds useful.

"Backup status"? Meh. "If hardware fails, we'll be able to be back online in Z minutes". Sounds useful.


Error migrating a big table from mySQL to SQL Server 2012 I'm trying to convert several mySQL tables to Microsoft SQL Server 2012 tables using the SQL Server Migration Assistant for MySQL.  The small tables get converted but when it comes to the big one (slightly less than 4 GB), after about 1:30 hour of processing, it gives the following error:

  (Elapsed Time = 00:01:24:32:197). Cannot truncate target table.
  Reason: ExecuteReader requires an open and available Connection. The
  connection's current state is closed.

Here is the output:

Migrating data...
Analyzing metadata...
Preparing table `big fmail user`.conversionlog...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 87489673 rows total
Table `big fmail user`.conversionlog data migration: 8760542 rows processed.
Table `big fmail user`.conversionlog data migration: 17510722 rows processed.
Table `big fmail user`.conversionlog data migration: 26249995 rows processed.
Table `big fmail user`.conversionlog data migration: 34998808 rows processed.
Table `big fmail user`.conversionlog data migration: 43757368 rows processed.
Table `big fmail user`.conversionlog data migration: 52494656 rows processed.
Table `big fmail user`.conversionlog data migration: 61254458 rows processed.
Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
 Errors: Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
Table `big fmail user`.conversionlog data migration: 0 rows processed.
Completing migration of table `big fmail user`.conversionlog...
Migration complete for table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 0 rows migrated (Elapsed Time = 00:01:24:32:197).
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Information: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Data migration operation has finished.
    0 table(s) successfully migrated.
    0 table(s) partially migrated.
    1 table(s) failed to migrate.

Any suggestions as to what might cause this error, and how I should resolve it?

I solved the issue. The problem was the value of the Data migration timeout in minutes property.

Go to Tools > Project Settings > General and in the left pane click on Migration and then in the right pane, in the section Misc, change Data migration timeout in minutes property to something larger (the default value was 15; I changed it to 300).


Update fields with multiple values from different tables in MySQL I have 3 tables:

Product_categories (created from a .csv import). The limit number of categories in subcategories is 4.

product_reference | category_id | subcategories

REF.001           | 1           | 1/2

Categories (created from a .csv import)

id | name

1  | example_category_1
2  | example_category_2

Importer* (created to store all the data I need from the previous tables)

sku     | category

REF.001 | null

How could I update Importer to show the following? The limit number of categories in subcategories is 4.

sku     | category

REF.001 | example_category_1>example_category_2

*Data in Importer would be to export in .csv and be able to import in WooComerce, this is why I need to import the categories by this way. https://docs.woocommerce.com/document/product-csv-import-suite-column-header-reference/#section-4

Full example.

1) Create tables and data (code)

CREATE TABLE Product_categories (product_reference VARCHAR(8), category_id INT, subcategories VARCHAR(8));
INSERT INTO Product_categories VALUES
    ('REF.001',1,'1/2'),
    ('REF.002',2,'1/2/3/4');

CREATE TABLE Categories (id INT, name VARCHAR(32));
INSERT INTO Categories VALUES
    (1,'example_category_1'),
    (2,'example_category_2'),
    (3,'example_category_3'),
    (4,'example_category_4');

CREATE TABLE Importer (product_reference VARCHAR(8), category VARCHAR(128));
INSERT INTO Importer (product_reference) VALUES
    ('REF.001'),
    ('REF.002');

2) Check data (console output)

mysql> SELECT * FROM Product_categories;
+-------------------+-------------+---------------+
| product_reference | category_id | subcategories |
+-------------------+-------------+---------------+
| REF.001           |           1 | 1/2           |
| REF.002           |           2 | 1/2/3/4       |
+-------------------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Categories;
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | example_category_1 |
|    2 | example_category_2 |
|    3 | example_category_3 |
|    4 | example_category_4 |
+------+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM Importer;
+-------------------+----------+
| product_reference | category |
+-------------------+----------+
| REF.001           | NULL     |
| REF.002           | NULL     |
+-------------------+----------+
2 rows in set (0.00 sec)

3) Perform update (code)

UPDATE Importer i, (SELECT pc.product_reference,GROUP_CONCAT(c.name ORDER BY n.n SEPARATOR '>') category
                    FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) n
                    JOIN Product_categories pc
                    JOIN Categories c ON SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('',pc.subcategories,'////'),'/',n.n),'/',-1) = c.id
                    GROUP BY pc.product_reference
                   ) d
SET i.category = d.category
WHERE i.product_reference = d.product_reference;

4) Check result (console output)

mysql> SELECT * FROM Importer;
+-------------------+-----------------------------------------------------------------------------+
| product_reference | category                                                                    |
+-------------------+-----------------------------------------------------------------------------+
| REF.001           | example_category_1>example_category_2                                       |
| REF.002           | example_category_1>example_category_2>example_category_3>example_category_4 |
+-------------------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)


Copy Specific tables from one database to another database on the same server I have 2 databases that are identical. I make a backup of DB1 and restore as DB2.
2 days later I want to copy 5 tables from DB1 into DB2. All tables start with 'AG'. Is there a simple way of doing this? I would like to do this in a T-SQL script so I can do this often and easily.

There are a few possible options to this. It kind of depends on your requirements I guess.

First of all it depends on your table definitions, indexes, keys and constraints.

If your table is just a simple heap and you just want to create a copy of the data you could use

drop table mytable;
select * from [databasename].[schemaname].[mytable] into mytable;

If your table is an actual copy including indexes you could use

truncate table mytable;
insert into mytable select * from [databasename].[schemaname].[mytable] ;

That obviously wouldn't work if you have foreign key constraints on mytable and the constraints don't resolve. Then you would need to update all related tables first or disable they constraints and enable/recheck them if you don't have any extra records in the referenced tables.

The import and export wizard suggestion isn't excluded because you can choose to save the result as a package and you could call that package from a script using dtexec.exe


MySQL: Adding Routines to a Slave While setting up the MySQL replication, I took the mysqldump (without --opt --routines options) unaware of the fact that in mysqldump, --routines it is NOT enabled by default.
Questions

Is there any way to just insert the routines into the slave without disturbing the replication?

Do I have to do the whole thing again by taking a new dump with --routines option?

You may be able to stop the slave, create the routines, and restart the slave without a problem.

MySQL replication relies on the master and the slave servers being identical, but it does not matter how they came to be identical.  You could theoretically clone a master server by hand, one table and row at a time -- as long as the slave was identical (structures, data, AUTO_INCREMENT values, etc.) to what appeared on the master at the instant in time represented by the binlog coordinates where you started replication, everything would be fine.  Not practical, but technically no different than what we accomplish with mysqldump.

Now, having said that the servers must be identical, replication will not stop or detect a problem if the servers are not identical as long as none of the non-identical elements are referenced in any way by replication events.

If a table is altered, dropped, etc., on the slave, no replication error will occur as long as no inserts, updates, or deletes to that table are done on the master.

The extension of this concept is that if the routines (which means stored procedures and stored functions -- scheduled events and triggers are not "routines") have not been referenced by replication events, their absence may not yet have caused any problems.

If your master is configured with a binlog_format of MIXED or ROW, then it's entirely possible that no replication events may have been sent across that depended on those routines being present, even if your application uses them extensively.  This is because whenever the master chooses row-based logging for a replication event (as it is very likely to do most of the time in MIXED mode) the procs and functions on the slave don't execute -- instead, the row changes that occurred on the master as a result of the procs and functions are replicated row-by-row to the tables on the slave.  In MIXED mode, this is a decision made by the optimizer on the master when the query is executed.

The same thing is true of triggers.  In my environment, it's extremely rare for a trigger to fire on a slave, since the vast majority of things replicate in ROW format (with configuration set to MIXED).

On the other hand, if a query did get logged in STATEMENT mode that needed one of these routines to be present, replication should have stopped with an error when the procedure or function referenced in the statement was not found.  As long as you haven't done anything like set global sql_slave_skip_counter = 1 or configured your slave to ignore errors (don't), then you should be able to define the routines now, and START SLAVE;.  The failed query should re-execute.

If you have a lot of routines on the master, the closest thing I can think of for getting them would be:

mysqldump --routines --no-data --single-transaction --all-databases --skip-add-drop-table | perl -pe 's/^CREATE\sTABLE\s/CREATE TABLE IF NOT EXISTS /g' > routines.sql

This will generate a dump file with all of your table definitions and routines, but it will not have the DROP TABLE before each CREATE TABLE and the CREATE TABLE statements will be changed to CREATE TABLE IF NOT EXISTS which will skip creating the tables and instead throw warnings that the table was already there... and it will not have row data.  I'm not aware of a way to get mysqldump to skip the tables entirely, though that might be a nice feature (if not a bit ironic).

Open this file with a text editor and review it before applying it to your server.

As with any replication setup, never confuse the lack of errors with total consistency.  Verify the consistency of all your data on an ongoing basis, especially if anyone has write privileges on the slave (which they shouldn't).   A lack of errors only means no replication events have touched anything that resulted in a different outcome than what occurred on the master.


Oracle: DB *partial* constraint? I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation.

I currently have a table at hand that records order transactions. The columns in this table are TRANSACTION_TYPE, ORDER_ID, QUANTITY, DATE, CREATED_BY, CREATION_DATE. The transaction types are 'PURCHASE' and 'REFUND'. Till now we only had full refunds because mostly the items bought were of single quantity. We're hoping to make it multiple in which case we will possibly have to perform multiple refunds.

The tables unique-key constraint is a combination of TRANSACTION_TYPE, ORDER_ID. I can break this constraint to make it possible to do multiple refunds by expanding the combination to TRANSACTION_TYPE, ORDER_ID, TRANSACTION_REFERENCE (this reference comes from the service that does the actual purchase or refund). But there's the thing, this means that I can do multiple refunds on the same order and the same is possible if it is a purchase. I would like to create a constraint such that the combination of 'PURCHASE' + ORDER_ID is unique in the table so that only a single purchase is possible for a given ORDER_ID by constraint.

I tried searching for this possibility and even tried it in this site. I might be using the wrong terms here. But if anyone here has an answer for this, I would LOVE to hear it out.

Any help to even nudge me to the right direction would be much appreciated.

You can create a function-based index.  If the table is named TRANSACTION

CREATE UNIQUE INDEX only_one_purchase
    ON transaction( (case when transaction_type = 'PURCHASE'
                          then order_id
                          else null
                      end) );

This leverages the fact that Oracle doesn't store completely NULL rows in the index so only the order_id values that are part of PURCHASE transactions will be stored in the index.


Why do wildcards in GROUP BY statements not work? I am trying to make the following SQL statement work, but I get a syntax error:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*

Here, A is a wide table with 40 columns and I would like to avoid listing each column name in the GROUP BY clause if possible. I have many such tables over which I have to run a similar query, so I will have to write a Stored Procedure. What's the best way to approach this?

I am using MS SQL Server 2008.

GROUP BY A.* is not allowed in SQL.

You can bypass this by using a subquery where you group by, and then join:

SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
  LEFT JOIN
      ( SELECT FKey, COUNT(foo) AS cnt
        FROM TABLE2
        GROUP BY FKey
      ) AS B
    ON A.PKey = B.FKey ;

There is a feature in SQL-2003 standard to allow in the SELECT list, columns that are not in the GROUP BY list, as long as they are functionally dependent on them. If that feature had been implemented in SQL-Server, your query could have been written as:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.pk                          --- the Primary Key of table A

Unfortunately, this feature has not yet been implemented, not even in SQL-Server 2012 version - and not in any other DBMS as far as I know. Except for MySQL which has it but inadequately (inadequately as: the above query will work but the engine will do no checking for functional dependency and other ill-written queries will show wrong, semi-random results).

As @Mark Byers informed us in a comment, PostgreSQL 9.1 added a new feature designed for this purpose. It is more restrictive than MySQL's implementation.


Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY? MSDN advises that @@FETCH_STATUS be used with care because it's global:

  Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor.

This leads me to two questions:

Is it possible that another (not nested) cursor could run concurrently with mine and affect the value of @@FETCH_STATUS between my FETCH NEXT and WHILE condition? For example, say the other cursor is in a stored procedure that's called on a schedule.
If this is possible, does @@FETCH_STATUS have an equivalent of @@IDENTITY's SCOPE_IDENTITY()?

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server.
There is no equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner loop completes. If you need to stack FETCH NEXT operations or perform a FETCH NEXT before an inner loop containing a FETCH NEXT, you can DECLARE a temporary variable of type int outside the loop, SET the temporary variable equal to @@FETCH_STATUS immediately after your FETCH NEXT statement, and then use the temporary variable as your loop control instead of @@FETCH_STATUS.


Only a sysadmin can specify '@output_file_name' I have developed an async batch system using the Agent Jobs in SQL Server 2017. It is working really well. I have one issue with setting up the operations staff to run this. We get the following error
Only a sysadmin can specify '@output_file_name' parameter for a jobstep
The user has been set up on the msdb with the following Database role membership;
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
What it looks like happening is that the user can create (and delete) agents, but when we try and run the code then error above comes up which seems very odd.
For now I have temporarily granted sysadmin to get him working, but quite keen to revoke those right if we can resolve this.
Any help would be appreciated
Regards Ben

To the best of my knowledge, this is a hard rule in SQL Server. See for instance here: https://docs.microsoft.com/en-us/sql/ssms/agent/job-step-properties-new-job-step-advanced-page.
You can of course wish for a change in Agent security architecture, but I don't hold my breath for that to happen: https://feedback.azure.com/forums/908035-sql-server.
In the end, perhaps you can work ut an interface with wrappers, signed procedures that creates your jobs, so they are owned by sysadmin without making this person a sysadmin. I can't say for sure whether it'll work out for you in the end, though, but perhaps worth looking into. This would be a good start: http://www.sommarskog.se/grantperm.html.


Enforce Data integrity on an Email column in a database I want to put a constraint on an email column like so: xxxx@xxxx.yyy
Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc

This is way too big a task if you wanted to do it properly. If you are able to use SQL CLR though this is how we do it at our company:

using System;
using System.Net.Mail;
using Microsoft.SqlServer.Server;

namespace Functions
{
    public static class Utilities
    {
        [SqlFunction]
        public static bool IsValidEmail(string email)
        {
            if (string.IsNullOrEmpy(email))
                return false;

            bool isValid = false;
            try
            {
                // use the validation provided by the System.Net.Mail.MailAddress class
                var mailAddress = new MailAdress(email);
                isValid = true;
            }
            catch(FormatException)
            {
                isValid = false;
            }
            return isValid;
        }
    }
}

And then assuming you deploy it as dbo.fn_IsValidEmail you can use it in your check constraint like you would any other scalar function like so:

create table dbo.tbl
(
    id int identity not null,
    email nvarchar(256) not null,
    constraint ck_tbl_isValidEmail check (dbo.fn_IsValidEmail (email))
);

If your column allows NULL then you will need to change the first part of the function to this instead:

if (email == null)
    return true;
if (email == string.Empty)
    return false;

Documentation:

System.Net.Mail.MailAddress

Microsoft.SqlServer.Server.SqlFunctionAttribute

CLR Functions


mysql 5 vs 5.5 memory usage I had a less less memory usage in mysql 5 vs 5.5 do they do something drastically different? I went from around 100mb to about 1 gb of memory usage.

Does memory usage in 5.5 also grow over time?

I don't know exactly if 5.5 require much memory than 5.0 but look this example :

In 5.0 MySQL Official Documentation:

innodb_buffer_pool_size : The default value is 8MB.

In 5.5 MySQL Official Documentation:

innodb_buffer_pool_size : The default value is 128MB.

If you paste your both "show global variables" results (5.0 and 5.5) we'll can see exactly what variables have gone up...

Max.


Admin DB replicated in MongoDB replica set I'm setting up a mongodb replica set across 3 different hosts. If you create the admin user on one host and turn on auth and keyfile in the /etc/mongodb.conf, do you have to create that same user on nodes 2/3?  Or can I just add those nodes to the replica set and the admin database will be replicated with the admin user I added from node 1 to the other 2? I'm having some trouble doing that if I bring up the other nodes with auth/keyfile turned on.

There are a couple of separate questions/issues here:

  do you have to create that same user on nodes 2/3?

If they are members of the same replica set, then no.  The users will be written to the primary and then replicated to the secondaries - remember any secondary can become primary in a normal set, so you would have to have all the data necessary to do that, including users.  If the nodes are in the set when they are added, the users will replicate normally.  If you add them later, they will replicate the users as part of the initial sync process.

Note that for nodes that are members of different replica sets (say multiple shards) that is not the case.

  I'm having some trouble doing that if I bring up the other nodes with
  auth/keyfile turned on

Remember that the keyfile must be identical for all nodes in a set.  The keyfile is what the nodes will use to authenticate with each other (for the purposes of initial sync and replication for a start, so it is an absolute must).  If you are having issues when you add the nodes, there will be errors in the logs that will tell you why.  The common reasons would be:

Incorrect config (new nodes not configured with the replica set name)
Different key files (this must be identical on all nodes in the replica set)
Connectivity or hostname lookup issues

If you expand on the difficulties you have when you try to add (how you are adding, what error you get, and preferably the output of rs.status() and a sample config file) I can elaborate further.


Inserting year with zero Is it possible to enter a date with a zero year?
What would the insert clause look like?

I ask, as an application is doing this, see the dump of the date:

dt_data: 05/01/00
dump(dt_data): Typ=12 Len=7: 100,100,5,1,1,1,1
to_char(dt_data, 'dd/MM/yyyy'): 00/00/0000

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0


mySQL Relational Tables Say I have 3 tables Products, Orders and Orders Product

Products Table

ID | StockNum | Description | Price
4  | 452SB    | Black       | 45.65
5  | 752SA    | Green       | 17.12

Orders Table

ID  | OrderNum | Total  |
14  | 12312    | 79.89  |
15  | 12313    | 45.65  |

Orders Product Table

ID  | OrderID  | Product  | Qty   |
45  | 14       | 452SB    | 1     |
46  | 14       | 752SA    | 2     |
47  | 15       | 452SB    | 1     |

If product information changes (Price or Description) this would throw off all previous order information, whats the best solution for this?. Also does it make more sense to be storing the product ID instead of the StockNum?

** Update **
Does This Make More Sense?  Also everything marked with ID at the end is FK

Products Table

ID | StockNum | ProductDetailsID |
4  | 452SB    | 8                |
5  | 752SA    | 9                |

Products Details Table

ID | Description | Price
8  | Black       | 45.65
9  | Green       | 17.12

Orders Table

ID  | OrderNum | Total  |
14  | 12312    | 79.89  |
15  | 12313    | 45.65  |

Orders Product Table

ID  | OrderID  | Product  | Qty   | ProductDetailsID |
45  | 14       | 452SB    | 1     | 8                |
46  | 14       | 752SA    | 2     | 9                |
47  | 15       | 452SB    | 1     | 8                |

You can add the fields Price and Description to the Orders Product table and copy the values when you create the row. This allows you to read the values as they were by the time of the order execution

Orders Product Table

ID  | OrderID  | Product  | Qty   | Description | Price
45  | 14       | 452SB    | 1     | Black       | 45.65
46  | 14       | 752SA    | 2     | Green       | 17.12
47  | 15       | 452SB    | 1     | Black       | 45.65

And yes, using Keys is always preferable

UPDATE

About foreign keys. Display them like this

Products Table

ID | StockNum | ProductDetailsID |
4  | 452SB    | 8                |
5  | 752SA    | 9                |

Products Details Table

ProductDetailsID | Description | Price
8                | Black       | 45.65
9                | Green       | 17.12


Vertica Export configuration I have a DEV Vertica database and need to create a new TST Vertica database.
During the past few months both administrators and users have had pretty-much wide open access to do what ever was needed to get things working. Thus, the internal Vertica configuration/metadata is not well tracked nor understood.
Im looking for a way to export all the internal configuration details and metadata needed to 1) Document what the current configuration is, and 2) Deploy that configuration to a new TST Vertica database.
Some of the things I am looking for are:

ACCESS/Authentication/LDAP config
Profiles/Resource/Roles/Users/Options
Object DDL, Flex tables, Schemas, Sequences
Anything else metadata related to be able to
document/reproduce  the configuration.

You can use Vertica's vbr utility with copycluster task to copy an entire database to another cluster. This copies everything, including the data.
See Copying the Database to Another Cluster

Alternately, you can use the EXPORT_CATALOG function. But it's limited to physical schema only, it doesn't export configurations like users, resource pools, LDAP etc.


SQL Server 2008 R2 copy Schema with all user security permissions I have a prod support request from a high value user to adjust the settings on a company-wide dashboard so that he can manage a section of his workflow currently being excluded from these reports. Given the two immediately obvious options of

altering the dashboard settings for ALL users company-wide or
replicating the dashboard for HVU only

...it was decided that I should create a "dev" instance where HVU can see a variant of his dashboard. To accomplish this, I ported the .rdl family to another ReportServer instance in an identical directory tree & cloned all the stored procedures in the pertinent datasets from Schema1 to Schema1_dev.

Question: the stored procedures & security schema all exist on the same server & database so there's no risk of orphan users. Is it possible / what's the best way to clone all current user permissions from Schema1 to Schema1_dev other than checking through the SSMS GUI?

This should give you a start. It assumes that you've already cloned all the relevant objects and that all of your permissions are at the object or schema level (if you have column-level permissions, the script gets a little more complex, but not too bad).

DECLARE
  @oldschema SYSNAME = N'schema1',
  @newschema SYSNAME = N'schema1_dev';

DECLARE
  @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
 ' + p.state_desc + N' '
 + p.[permission_name] + N' ON '
 + QUOTENAME(@newschema) + N'.' + QUOTENAME(o.name)
 + N' TO ' + QUOTENAME(dp.name) + N';'
FROM sys.database_permissions AS p
INNER JOIN sys.objects AS o
ON p.major_id = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
WHERE p.class_desc = N'OBJECT_OR_COLUMN'
AND s.name = @oldschema;

SELECT @sql += N'
  ' + p.state_desc + N' '
  + p.[permission_name] + N' ON SCHEMA::'
  + QUOTENAME(@newschema)
  + N' TO ' + QUOTENAME(dp.name) + N';'
FROM sys.database_permissions AS p
INNER JOIN sys.schemas AS s
ON p.major_id = s.[schema_id]
INNER JOIN sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
WHERE p.class_desc = N'SCHEMA'
AND s.name = @oldschema;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that if you have a lot of objects and/or a lot of users, the PRINT output might not show everything.


Update , Select and Join using 3 tables There are 3 tables:

Label (Label_ID,Label_Name), Label_ID is Primary Key.
Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column
Top100 (Prefix,Label) No keys in this table.

My query should be such that , For each prefix row in Tracks table, find an equivalent prefix in top100 table then select Label from top100 table, Match thus selected Label with Label_name in Label table, get its Label_ID and Update its value in Tracks table.

Can anyone please help me with this query?? Any help much appreciated

I guess you don't want (for every Tracks.prefix) a new row to be inserted but the existing row to be updated (otherwise you would get collisions on the primary key of Tracks). You can this query to check first what will be changed:

SELECT
    tr.prefix,
    tr.label_id AS existing_label_id,
    top.label,
    lbl.label_id AS new_label_id
FROM
    Tracks AS tr
  JOIN
    Top100 AS top  ON top.prefix = tr.prefix
  JOIN
    Label AS lbl  ON lbl.label_name = top.label ;

and then update:

UPDATE
    Tracks AS tr
  JOIN
    Top100 AS top  ON top.prefix = tr.prefix
  JOIN
    Label AS lbl  ON lbl.label_name = top.label
SET
    tr.label_id = lbl.label_id ;


Parameterized Plan Flushed I cannot find documentation anywhere on what would trigger this recompilation reason. We are investigating a sudden query performance drop and the only thing that I can think of is that the plan got recompiled for the parameterized query when executed against a small dataset which resulted in messing with row estimates. We noticed that when this process was running (after it started taking hours instead of seconds) it was hitting tempdb pretty hard. Statistics on consumed tables were not changed and the only other reason that makes sense in the list of recompilation reasons is #12 "Parameterized plan flushed".

The process in question was calling a view and filtering on a single INT column. This was done through Entity Framework.  There is only 1 Entity Key on the class and it is the PK of the main table in the view.   All records are unique.

I am curious if anyone can point me to any documentation out there that explains why a plan might be recompiled due to "Parameterized plan flushed".

Query Plans are flushed from the cache for several reasons, including being aged out, being flushed due to memory pressure, flushed due to user action (DBCC FREEPROCCACHE etc), flushed due to restart and flushed due to explicit recompilation (OPTION (RECOMPILE or sp_recompile).

If you can see no evidence of forced recompilation or a manual flush, then it is most likely the plan was aged out or it was flushed due to memory pressure. From Docs: Plan Cache Internals:

  Evicting plans from cache is based on their cost. For adhoc plans, the
  cost is considered to be zero, but it is increased by one every time
  the plan is reused. For other types of plans, the cost is a measure of
  the resources required to produce the plan. When one of these plans is
  reused, the cost is reset to the original cost. For non–adhoc queries,
  the cost is measured in units called ticks, with a maximum of 31. The
  cost is based on three factors: I/O, context switches, and memory.
  Each has its own maximum within the 31-tick total.

  I/O: each I/O costs 1 tick, with a maximum of 19.
  Context switches: 1 tick each with a maximum of 8.
  Memory: 1 tick per 16 pages, with a maximum of 4.

  When not under memory pressure, costs are not decreased until the
  total size of all plans cached reaches 50 percent of the buffer pool
  size. At that point, the next plan access will decrement the cost in
  ticks of all plans by 1. Once memory pressure is encountered, then SQL
  Server will start a dedicated resource monitor thread to decrement the
  cost of either plan objects in one particular cache (for local
  pressure) or all plan cache objects (for global pressure).

Take a look at this StackExchange answer which provides heaps of great links and information on plan reuse and caching. You'll have to put in place monitoring to catch the exact cause if this reoccurs, but you should also look at leveraging Query Store which will allow you to force a known good plan for this query and track regressions across your database as this may be affecting other queries as well.


How do I select by a JSONB array containing at least one element within a range? I need to model products with properties.  Properties need to be dynamic, in the sense that application users can add / delete properties to products.  For this reason, I am using a JSONB column storing a JSONB object to model those dynamic properties, with each field of the object a dynamic property, for instance:

{
  product_number: "snsug1",
  color: "white",
  weight: [100, 200, 300]
  ...
}

Furthermore, as can be seen in the example above, some properties are allowed to have multiple values, as some products can come in different configurations (different values for the weight property for instance, for packets of sugar of different sizes).  I am modeling those as JSONB arrays.

I need to be able to select products based on some filter values for the properties.

Filtering based on comparisons on single value properties are straightforward (for instance where properties->'color' = '"white"'::jsonb) and overlap queries for multi-value properties are straightforward too when the overlap target contains discreet values, like where properties->'weight' @> any(array[to_jsonb(200), to_jsonb(300)]).

The problem comes with filtering on a multi-value property based on overlap with a range of values (instead of overlap with discreet values as above).  So my question is:  how can I filter on a multi-value property like weight, with criteria that any of the values in the array should fall with a range?

Something like (pseudo code):

select id from products where properties->'weight' @> range(50, 150);

should return the entry in my example above, but

select id from products where properties->'weight' @> range(350, 500);

should not.

The weight @> range(50, 150) bit in the pseudo code query above is what I am asking about.  What should that look like?

If I am barking up the wrong tree altogether and this is not doable with my current model, then what would be a good alternative for solving my problem?  Is there a more appropriate way of modeling the data that can lead to elegant and fast queries of the type above?  The solution needs to perform well on large data sets, it needs to be able to make use of indexes for the above types of queries.

You need to use a sub-query that unnests all array elements together with an EXISTS predicate:

select p.*
from products p
where exists (select *
              from jsonb_array_elements(p.properties->'weight') as t(weight)
              where t.weight::int <@ range(50, 150));


Nullif and equal values The online documentation defines nullif() as a function which returns NULL if the argument values are equal. But why does it return NULL even if the argument values are not equal, as in the case below:

select nullif(convert(varchar(10), ' '), convert(varchar(10), ''))

----------
NULL

(1 row affected)

SQL server ignores trailing spaces when performing comparisons.  So ' ' is considered the same as ''.  The former is the latter with trailing spaces.


Selecting distinct records I have a user relation to address (1 to 1).

So the address has two types which is a type column.

One is the VerifiedAddress and the other one is the SearchAddress.

So the user who had verified his address has two entries in the addresses table.

In case you wonder why is that, the reason is that user can go to a travel and change the SearchAddress but the VerifiedAddress remains the same.

I'm trying to find all the users who haven't got verified their address. I have some user list from which I know who has verified address and who hasn't got it.

This is what I have tried the last, not exactly what I wanted :

select id from addresses
where user_id in (8112, 18620, 23, 19668, 10212)
and type = 'VerifiedAddress';

Gives me all the verified users, but if I change the type != VerifiedAddress I still get the same results because users who have verified their address have two entries in a addresses table, this time I get the user_ids from the SearchAddress.

How can I get this data from addresses table?

Note: SearchAddress can be null as well as VerifiedAddress. Naturally people who don't have searchaddress don't have the verified as well.

This is called a left anti-join. You want all users who don't have a verified address. So you do a left join to find all users and whether they do have a verified address, then discard all the ones that have a verified address.

Something like:

SELECT *
FROM users
LEFT OUTER JOIN addresses a
ON (users.id = a.user_id AND a.type = 'VerifiedAddress')
WHERE a.user_id IS NULL;

This may be easier to understand when expressed with NOT EXISTS as:

SELECT *
FROM users
WHERE NOT EXISTS (
  SELECT 1
  FROM addresses a
  WHERE users.id = a.user_id
  AND a.type = 'VerifiedAddress'
)

both of which will optimize to the same query plan, in general.


Fail to attach database : MSDTC Check status of transaction I am attaching a database from a snapshot taken of the production database volumes. These databases will be anonymised and then restored to our DEV servers.

Here is my attach statement:

CREATE DATABASE MY_DB ON
( FILENAME = N'H:\MY_DB.mdf' ),
( FILENAME = N'j:\MY_DB.ldf' ),
( FILENAME = N'i:\MY_DB.ndf' ),
( FILENAME = N'H:\MY_DB.mdf' ),
( FILENAME = N'i:\MY_DB.ndf' )
 FOR ATTACH
GO

And I am getting this error:

  An error occurred while recovering database 'MY_DB'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (2:2141366340). Fix MS DTC, and run recovery again.

I have:

Checked security in MSDTC via component services
Restarted MSDTC
Restarted MSSQL
Restored another database from the same volume

Googling around hasn't helped much and my worst fear is having to recreate the volume snapshot just for this database. There are 4 others on the same volume that will have to be redone as well, if I don't find an alternative solution.

Question: Anyone know how to solve the error above?

This will mark all the MSDTC transactions as failed and allow you to start the database. It's a better idea to use backup/restore to refresh the database though

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'in-doubt xact resolution', 2 -– presume abort
GO
RECONFIGURE


How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB? Recently, I was asked to refresh a couple of databases on a test server from production.  Always wanting to follow best practices, I performed the backup using the WITH CHECKSUM option, and eventually got the following error:

Msg 3043, Level 16, State 1, Line 32
BACKUP 'DBName' detected an error on page (1:428321) in file 'F:\path\DBName.mdf'.
Msg 3013, Level 16, State 1, Line 32
BACKUP DATABASE is terminating abnormally.

Surprised at the error, I ran DBCC CHECKDB to see what's going on, but unfortunately (or perhaps fortunately), it comes back clean.

All of the information I have found so far encourages the use of DBCC CHECKDB's findings as a guide on where to go next, so I'm not sure how to fix this?

Some quick info:

SQL Server is 2008 R2.
I found confirmation of the error in the msdb.dbo.suspect_pages table.  It contains event types 1 and 3.
The DBCC command I used was DBCC CHECKDB (DBName) WITH NO_INFOMSGS, ALL_ERRORMSGS;
I am not the official DBA, but since I have some experience, I was permitted to perform the backup and restore.
I did read the similar post What types of corruption can DBCC CheckDB miss?.  However, while I believe I understand why (the point of that post), I don't know how to resolve it.
I was able to successfully perform the backup by omitting the WITH CHECKSUM option.

Thanks in advance for any guidance!

UPDATE

The comment provided by "usr" below got me on the right track.  Just what exactly does page (1:428321) contain?  I did some more research and found the DBCC PAGE command.  However, I noticed something odd.  When I executed the following...

DBCC TRACEON (3604);
DBCC PAGE ('DBName', 1, 428321, 3);

I got the following back (note the page number on the first line)...

PAGE: (1:2021305)

BUFFER:

BUF @0x0000000CFEFCC580

bpage = 0x0000000CFE764000           bhash = 0x0000000000000000           bpageno = (1:428321)
bdbid = 18                           breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 20204                        bstat = 0xc00809
blog = 0xbcca2159                    bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000CFE764000

m_pageId = (1:2021305)               m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 5313   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594386120704                                 Metadata: PartitionId = 0
Metadata: IndexId = -1               Metadata: ObjectId = 0               m_prevPage = (1:2021304)
m_nextPage = (1:2021306)             pminlen = 194                        m_slotCnt = 10
m_freeCnt = 228                      m_freeData = 7944                    m_reservedCnt = 0
m_lsn = (121329:238172:13)           m_xactReserved = 0                   m_xdesId = (0:0)
m_ghostRecCnt = 0                    m_tornBits = 297819981

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED
PFS (1:420576) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Page 2021305???  Didn't I enter 428321?  If I simply run the command with the page before it (428320), I get that page.  So I guess I'm getting closer, but still don't know what that page contains.

I'm not sure how to fix this

In the comments we investigated but never found out conclusively whether the page is allocated or not.

If it's allocated this is definitely a corruption that CHECKDB missed. A bug in CHECKDB. Please report that to Microsoft.

If it's not allocated here's how I would fix it: Fill the file with dummy data  e.g. CONVERT(binary(8000), 0x). Now the page should have been consumed by the dummy data and overwritten with something clean.

Actually, you can use this idea to test whether SQL Server thinks the page is allocated. If this works it's definitely not allocated.

Alternatively, you could use the write page feature to zero fill the page but that leaves an audit trace in the database boot page. Microsoft support will not like finding this, I guess, when they help you in the future with something. They might think your page write caused damage. Also, it steals all the fun from this investigation by terminating it without resolution :)


Oracle Client trace not generating I'm using Oracle Client 12.1.0.2 and I need to generate client trace because im unable to connect my sql developer to the database server. However, i'm unable to generate the trace.

Here is my sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = 'D:\app\client\product\12.1.0\client_1\network\admin'
TRACE_TIMESTAMP_CLIENT = ON
TRACE_UNIQUE_CLIENT = ON
DIAG_ADR_ENABLED=OFF

I have the rights to write in the folder but unable to generate the logs.

I tried the same with net manager also.

But no luck.

Add following line to sqlnet.ora:

adr_base=off

You should be able to find your traces then.
Without it, all client traces go to %userprofile%\oracle\oradiag_%username% folder.


MySQL 5.5 Innodb_buffer_pool_bytes_data status variable missing We're running a MySQL 5.5.29 server in Production. If I do:

SHOW GLOBAL STATUS;

I'm not getting the status variable 'Innodb_buffer_pool_bytes_data' which according to the official documentation for this version should be there. What am I missing here?

Run show global status like 'Innodb_buffer_pool_bytes_data';


SQL Server 2016 Cluster - mail works in alerts; sp_send_dbmail dosen't send in jobs I upgraded from 2008 R2 to a 2016 cluster [Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)].  We have some scheduled emails that happen from the agent that are reporting success as the agent runs them, but no email is sent to recipients.  If I take the job out of the agent and run it in the query window it works fine.  But if the job executes it, it reports success but no email is generated.  Alerts however send out just fine from the agent.

Here is the start of an sp_send_dmail script

Select @asOfDate = yesterday_end from dbo.DTE_udfCommonDates(GETDATE())

SET @s = 'Historical Working Data as of '+CONVERT(VARCHAR(12),@asOfDate,107)
Set @b = @s
Set @f = 'HistoricalWorkingDataAsOf-'+replace(CONVERT(VARCHAR(12),@asOfDate,101), '/', '-')+'.csv'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domain.com',
@body= @b,
@subject = @s ,
@profile_name = 'Default Database Mail',
@query = '.....

Have any suggestions of some configuration we missed?  Things to try?

AMtwo Got me to the solution.  In this new installation we separated the service accounts that run the server/agent and the agent didn't have access to run the database mail or run scripts in the databases.

I added the agent user to the server security then gave them access to the [DatabaseMailUserRole] role in [msdb].  In addition to that it needed access to the database it was pulling information from.

Thanks for your help.


What happens when the same application from different server tries to connect to the always on primary server In our environment we have an always on availability group and the reporting server is acting as a secondary replica with Synchronous Mode, manual failover. XSLT report editor that is a vendor application will connect to the application server which in turn connects to the database server. The application server (separate server)  connects to the listener to serve client connections. My question is if I set up a separate application server just for the XSLT report which in turn connects to the reporting, now will there be any problems if the reporting server for some disaster recovery becomes the primary where the Client application and the XSLT application will both  connect, both the application is same  that is installed on separate server one for client another for the xslt report.

I am not comfortable to set up transactional replication which is an additional overhead. Any advice will be greatly appreciated.

From a technical standpoint, there are no problems with multiple applications, running on multiple different machines, connecting to the same SQL Server instance.

If you experience the failover scenario that you described, it's possible that the reporting workload will interfere with your other workloads from a performance standpoint.

In other words, all the long read queries your reporting app does could block queries coming from your other apps.  And in general the reporting app queries will require CPU time and I/O time that is normally solely used by the other app.

It's hard to say without more detail whether this would just slow things down a little, or bring your whole app grinding to a halt due to blocking or resource contention.


Query does not use index (according to EXPLAIN SELECT) Can anyone give me an indication, why this query does not use the Key on the table 'gtg'):

+---+--------+------------------+-------+-----------------+---------+------+------+----+--------+---------------------------------+
| 1 | SIMPLE |      gtg |   ALL |         PRIMARY | NULL    | NULL | NULL |  2 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | hsv2 | range | PRIMARY,gtg| PRIMARY | 4    | NULL | 22 | 100.00 |  Using where; Using join buffer |
+---+--------+------------------+-------+-----------------+---------+------+------+----+--------+---------------------------------+

this is the query:

EXPLAIN
SELECT gtg.name,
       sum(h)
FROM hsv2
JOIN gtg ON gtg.id=hsv2.gtg
WHERE hsv2.id<1000
GROUP BY gtg.name
ORDER BY gtg.name

table definition (summary):

CREATE TABLE IF NOT EXISTS `hsv2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `gtg` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`,`pid`),
  KEY `pid` (`pid`),
  KEY `gtg` (`gtg`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
 PARTITION BY RANGE (pid)
(PARTITION hs_v2_0 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION hs_v2_1 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION hs_v2_39 VALUES LESS THAN (3800000) ENGINE = InnoDB,
 PARTITION hs_v2_40 VALUES LESS THAN (4000000) ENGINE = InnoDB)

gtg.id is the primary key.

any clues, I am lost. I thought the partitioning might have to do something with it, but but the gtg table is not partitioned, so....

mysql 5.5 on a standard debian 7

Antonis answer was correct. I tested it by hardcoding some JOINs (for 2 rows) and it disappeared from the slow-query log.


MySQL count time between each in/out and show total I am having an issue with my time clock query. Basically users clock in/out several times per day (can clock in/out unlimited times per day), and it is counting the time between the first clock in and last clock out and I need it to count time between each in out and add up to provide result.

SELECT
    user,
    '2020-01-01' as date,
    'Total Hours for Week:' as WeekDay,
    SUM(Minutes) as Minutes
    FROM (SELECT
            tc.user,
            DATE(tc.timeinout) as date,
            CASE
                WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday'
                WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
                WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
                WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
                WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
                WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
                WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
            TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
        FROM timeclock AS tc
        LEFT JOIN (SELECT
                    user,
                    MIN(timeinout) as min
                  FROM timeclock
                  GROUP BY user,DATE(timeinout)) as tcmin
            ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
        LEFT JOIN (SELECT
                    user,
                    MAX(timeinout) as max
                  FROM timeclock
                  GROUP BY user,DATE(timeinout)) as tcmax
            ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
        WHERE
           tc.timeinout>= :startdate
            AND tc.timeinout< :enddate
            AND tc.user= :user
        GROUP BY tc.user,DATE(tc.timeinout)
        ORDER BY date ASC) AS Total
    UNION ALL
    SELECT
    tc.user,
    DATE(tc.timeinout) as date,
    CASE
        WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday'
        WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
        WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
        WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
        WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
        WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
        WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
    TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
    FROM timeclock AS tc
    LEFT JOIN (SELECT
            user,
            MIN(timeinout) as min
          FROM timeclock
          GROUP BY user,DATE(timeinout)) as tcmin
    ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
    LEFT JOIN (SELECT
            user,
            MAX(timeinout) as max
          FROM timeclock
          GROUP BY user,DATE(timeinout)) as tcmax
    ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
    WHERE
    tc.timeinout>= :startdate
    AND tc.timeinout< :enddate
    AND tc.user= :user
    GROUP BY tc.user,DATE(tc.timeinout)
    ORDER BY date ASC

user   min                   max                   WeekDay     Minutes
----   -------------------   -------------------   ---------   -------
1      2015-07-27 20:20:32   2015-07-27 20:29:25   Monday      8
1      2015-07-28 20:32:04   2015-07-28 20:36:28   Tuesday     4
1      2015-07-29 20:40:00   2015-07-29 20:45:39   Wednesday   5
1      2015-07-30 20:20:32   2015-07-30 20:20:56   Thursday    0
1      2015-07-31 20:29:25   2015-07-31 20:32:04   Friday      2
1      2015-08-01 20:36:28   2015-08-01 20:40:00   Saturday    3
1      2015-08-02 20:20:32   2015-08-02 20:40:00   Sunday      19

What's the question?

If you need to add up the last column, there two ways:

SELECT ... SUM(minutes) ...
    FROM (
          your query
         ) x
    GROUP BY ...

or...

GROUP BY ... WITH ROLLUP

Add WITH ROLLUP to your query and see if that is enough.


SQL Server Snapshot Replication - Is there an option to clean up the snapshot folder after X amount of time? We're running snapshots from a 2016 to a 2019 SQL Server.
I've looked through Microsoft's documentation and I'm not seeing a clear cut way to have this, and I'm not seeing any jobs on the Publisher/Subscriber that would run this sort of cleanup.
We recently had a drive fill up and I want to make sure to get this sorted out if it's a native feature of replication, otherwise I'm going to run a PowerShell script to take care of it.

set the retention period on the publication
also see the link below ( this is the replication escalation engineers blog site - so a great resource ) for a slight gotcha.
https://repltalk.com/2010/07/14/snapshot-files-deleted-when-immediate_sync-ntrue-and-retention-0/


Mysql - Select To XML I'm using a trigger to capture an audit trail. I want to convert the inserted / updated / deleted value to XML and store it in another table.

My trigger look like this:

DELIMITER $$

CREATE
    TRIGGER `MyDB`.`TriggerAudit` AFTER INSERT
    ON `MyDB`.`settings`
    FOR EACH ROW BEGIN
    INSERT INTO insert_audit_trail (user_uid, table_name, inserted_value)
    VALUES ('the Username', 'the table name', 'Select to XML In Here')
    END$$

DELIMITER ;

In MS SQL Server I can use the FOR XML clause, but I don't know to do this in MySQL. Is there any solution for this?

It appears this has already been answered here.

The answer references this article.

From the answer:

  From the article:

use strict;
use DBI;
use XML::Generator::DBI;
use XML::Handler::YAWriter;

my $dbh = DBI->connect ("DBI:mysql:test",
                       "testuser", "testpass",
                       { RaiseError => 1, PrintError => 0});
my $out = XML::Handler::YAWriter->new (AsFile => "-");
my $gen = XML::Generator::DBI->new (
                               Handler => $out,
                               dbh => $dbh
                           );
$gen->execute ("SELECT name, category FROM animal");
$dbh->disconnect ();


Delayed or Asynchronous DML Trigger Execution I have created a DML trigger (AFTER INSERT, UPDATE, DELETE) on a table
Trigger's logic takes about 30 seconds to execute
So if you change even 1 row, it takes ~ 30 seconds due to trigger execution
Developer asked me "Is there a chance that the trigger could be a fire & forget action?"
I said no, but is it really so ?
Question:
Can trigger be executed in "asynchronous" mode?
Application updates couple of rows in few ms, and thinks that transaction is completed, and then trigger is silently executed under the hood ?
I understand that this actually does not look good from consistency point of view, but still, is it possible ?

As far as I'm aware this is not possible with the triggers feature of Azure / SQL Server. One reason likely being, as you mentioned, it breaks the Consistency property of the ACID principals of a database:

...any data written to the database must be valid according to all
defined rules, including constraints, cascades, triggers, and any
combination thereof. This prevents database corruption by an illegal
transaction...

Triggers are meant to fire transactionally in lockstep with the operation that caused them to fire. Imagine if the row of data that the trigger was dependent on was deleted before the trigger got to execute asynchronously. This could get you into a lot of trouble.
Alternatively, you can have a trigger that simply adds a row to a queue table, and a SQL Agent Job for example, that constantly runs checking that queue table and firing a stored procedure with the logic from your original trigger to emulate an asynchronous event. Perhaps that would improve your workflow?


Can a rollback/commit occur, independent, for each iteration inside a cursor? We have this ecosystem of accounting solutions and, basically, it got two tables:

dbo.AccountFluxRecord
(
   Id,
   TotalCredits,
   TotalDebits,
   CreditMinusDebit,
   Application (the application used to insert the record)
)

dbo.AccountFluxRecordDetail
(
   Id,
   AccountFluxId,       (FK to above table)
   Type,                (Credit or Debit)
   Value,
   CreditBankAccountId, (nullable FK)
   DebtBankAccountId    (nullable FK)
)

We have a few applications using it and all use a stored procedure to insert new records.

SP_InsertAccountFluxRecord

We pass the list os details to the SP as a custom user table type.
It makes lots of validations to ensure all records have at least one credit and one debit and the sum of credits minus debts to be zero.
It also validates if BankAccountId is valid for each kind of operation.

It's working flawlessly, for a couple of years now, in hundreds of customers, inserting many millions of records each week.
We host the database for most of our customers but some of them insist their data must be hosted in house.
We are Ok with that since we can get a open channel for maintenance.

Today we got an issue with one of those customers self-hosting their database.

We have found a dozen "bad" records.
Those records are missing the debt detail, that is impossible since that particular application creates an AccountFluxRecord
from a pair of debit/credit details, one is the mirror of the other.
It cannot even accept a user input without a valid pair of bank accounts but we found the DebtBankAccountId field to be null in those bad records.

We keep a log of all operations, the log reflects the bad records are inserted as they are, there are no deletes or updates over them.
Obs: We do use snapshot transaction mode to avoid some deadlock problems we got in the past.

TLDR
I got a dozen invalid records, inserted by an SP made to block invalid records to be inserted at all, as if all application and Stored Procedure validation where bypassed.
Below there's a snippet showing how validations are handled inside the SP.

IF (@SumDetailDebit <> @SumDetailCredit)
BEGIN
    set @Mesage = @ErrorPrefix + N'TOTAL DEBIT IS DIFFERENT FROM CREDIT!';
    THROW 50000, @Mesage ,1
END

Question: Inside an SP there is a cursor inserting a detail record for each iteration, after making some validations.
Can one insert be commited and other be backrolled even if we are not using transaction control inside the loop?

After testing a lot I found the answer. Yes, it can since you run it AD HOC, without transaction control.

create table dbo.TOBEINSERTEDINTO
(
  ID int not null
)
GO

/*********************************************************************/
/***   Test scenario                                               ***/
/*********************************************************************/
create procedure TestCursor
as
begin
    declare @id int

    declare cursor_test cursor read_only fast_forward
    for
    select * from (values(1),(2),(3),(null)) as src(id) order by id desc

    open cursor_test

    declare @status int = 0

        while @status = 0
        begin
            fetch next from cursor_test into @id

            set @status = @@fetch_status

            if (@status = 0)
            begin

                if (@id is null)
                begin
                    throw 50000, n'!!!!!!!!! id cannot be NULL !!!!!!!!!!' ,1
                end

                insert into dbo.TOBEINSERTEDINTO(ID) values(@id)
            end
        end

    close cursor_test
    deallocate cursor_test
end;
GO

/*********************************************************************/
/***   Calling inside a transaction, with a try..catch can be ok   ***/
/*********************************************************************/

begin tran
begin try
    Exec TestCursor
end try

begin catch
    if @@trancount > 0
    rollback
end catch

if @@trancount > 0
    commit transaction;

select * from dbo.TOBEINSERTEDINTO

GO

/*********************************************************************/
/***   Lack of transaction control leads to inconsistence          ***/
/*********************************************************************/
exec TestCursor
GO

select * from dbo.TOBEINSERTEDINTO
GO

The application got a solid transaction control.

The problem originated because someone was tampering with the base. I guess someone put a profile to run and tried to mimic some app calls.


How to set malloc-lib in mysql 5.7 in ubuntu 18.04 LTS I recently installed jemmaloc 3.6.0-11 using apt-get install libjemalloc1 on my ubuntu server 18.04

The installation were successfull, but when I check using

pt-mysql-summary | grep -A5 -i "memory management"

I get

    Memory management library
    jemalloc is not enabled in mysql config
for process with id 89539
    The End

How I can do this ?

I am using Mysql 5.7.26

I had to do a bit more to get this to work.  This worked for me.  First the problem:
Starting in Ubuntu 18.04, Percona / MySQL are configured thru systemd
This means setting malloc-lib= in my.cnf no longer works!  It also no longer works "out of the box" (at least not for me).
So how do we get this to work?

Create an override.conf file to load jemalloc.  This is done using systemd controls.
sudo systemctl edit mysql

This will pop up an editor.  Unless this has been done before, odds are this will be a blank new file. Enter this in:
[Service]
Environment="LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1"

Then sudo systemctl daemon-reload which loads the override.conf

Then sudo systemctl restart mysql which restarts the server

Resources:

The heavy lifting: https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/using-systemd.html
Percona blog article: https://www.percona.com/blog/2017/01/03/enabling-and-disabling-jemalloc-on-percona-server/

The instructions on how to verify the jemalloc is actually being used are key.


Dynamic Condition Creation function I have a function which needs to create  a dynamic where clause by reading the columns from the temporary table .

CREATE FUNCTION [dbo].[GetWhereClause]
(
 @TempTableName nvarchar(600)

)

RETURNS nvarchar(max)
   BEGIN
   DECLARE @sql as nvarchar(max)

 SET @sql='(SELECT STUFF('+'(SELECT space(2)+'+
            ''''+'and '+'''' +
            '+space(2)+'+''''+'B'+'.'+''''+'+'+'name+'+''''+
            ' = '+''''
            +'+'+''''+'A'+'.'+''''+'+'+'name+'+''''+''+''''+
            'from tempdb.sys.columns where object_id =
            object_id('+''''+'tempdb..'+@TempTableName+''''+')'+
            'FOR XML PATH ('+''''''+')),1, 5,'+ ''''''+'))'

RETURN @sql

END

code to see the ouput

     CREATE TABLE #SingleTempTable(ID  uniqueidentifier  ,Name  nvarchar    (50)COLLATE    SQL_Latin1_General_CP1_CI_AS,Age  nvarchar  (50)COLLATE  SQL_Latin1_General_CP1_CI_AS)
   DECLARE @QueryTable AS TABLE (ID SMALLINT IDENTITY (1, 1) PRIMARY KEY,Clause NVARCHAR (1000));

     DECLARE @sqlsource AS NVARCHAR (MAX) = '';

     DECLARE @whereClause AS NVARCHAR (MAX) = '';

     DECLARE @TempTableName AS NVARCHAR (MAX) = '#SingleTempTable';

     SET @sqlsource= [dbo].[GetWhereClause](@TempTableName,'')

      INSERT INTO @QueryTable
      EXECUTE sp_executesql @sqlsource

      SELECT @whereClause=Clause FROM @QueryTable

      SELECT @whereClause

Is there any better way I can write this line in function

 SET @sql='(SELECT STUFF('+'(SELECT space(2)+'+
            ''''+'and '+'''' +
            '+space(2)+'+''''+'B'+'.'+''''+'+'+'name+'+''''+
            ' = '+''''
            +'+'+''''+'A'+'.'+''''+'+'+'name+'+''''+''+''''+
            'from tempdb.sys.columns where object_id =
            object_id('+''''+'tempdb..'+@TempTableName+''''+')'+
            'FOR XML PATH ('+''''''+')),1, 5,'+ ''''''+'))'

Now its not in a good format and its difficult to understand

How about this?

set @sql = '(SELECT STUFF((
              SELECT space(2)+''and ''+space(2)+''B.''+name+'' = ''+''A.''+name
              from tempdb.sys.columns
              where object_id = object_id(''tempdb..'+quotename(@TempTableName)+''')
              FOR XML PATH ('''')),1, 5,''''))'


SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher Background

I have two instances of SQL server running on two different machines:

One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional.

They both start off with the same snapshot of my replicated database.

I have a utility which runs on the publisher machine which executes the SQL commands against it (all are inserts, updates, deletes). These changes are then pushed out to the subscriber.

I want to know if I am able to TRUNCATE the data in the publisher database so that it has a lower overhead (all I want is the replication ability, I couldn't care less about the actual data in my replication publisher as long as it makes it to the subscribers).

If I do the above and also modify my utility which executes commands to turn INSERTS and UPDATES into a MERGE command, will the data be properly replicated to all subscribers?

Example scenario:

The original command that my utility will execute:

  UPDATE calendar SET active = 0 WHERE id = 1932

The publisher database doesn't have a calendar record with id of 1932, because I truncated it, so an update will affect zero records.
But the subscriber database DOES have a calendar record with id 1932.
If my utility translates the UPDATE above into a MERGE, will the change be pushed out to the subscriber database even though it executes as an INSERT on the publisher, but an UPDATE on the subscriber?

Thanks in advance.

If you truncated the table on the publisher the record in the transaction log would be an insert, and therefor an insert would be executed against the subscriber.

Now what you can do is modify the stored procedures that replication uses at the subscriber so that when the insert procedure fires it first looks to see if the record exists and if it does it does an update, and if it doesn't it does an insert.


Unknown column 'sitename' in 'where clause' public function getSiteValues($value) {
    $this->db->select(
        'site',
        'id, option_name, option_value',
        NULL,
        'option_name=' . $value
    ); // database, what to take, where value
    $res = $this->db->getResult();

    echo '<pre>';
    print_r($res);
    echo '</pre>';
}

In this case I would like to put for example:

getSiteValues('sitename')

and so that this would return the value of sitename row and from option_value column, but it keep giving me error such as

[0] => Unknown column 'sitename' in 'where clause'

What can I do to fix this?

PS. I have tried to look at other solutions but they have keep giving me wrong solutions. I have tried most of them. btw I am using this crud library - https://github.com/rorystandley/MySQL-CRUD-PHP-OOP for db connections

Sitename is a string, but you're not enclosing it in apostrophes, so SQL is interpreting it as a column. Please try adjusting like so:

"option_name='" . $value . "'"


Linked servers - Upgrading from 2005 to 2012 Best way to find other server are linked to 2005 SQL Instance as I am migrating all to 2012 SQL Server.

I will migrate all linked server that is linked to 2005 SQL Instance but what is the best way to find other server in my environment are linked to 2005 SQL instance so I can change after migration to 2012 ?

More than focusing on other servers that link to your current server, I would worry about applications. In complex environments, some applications may be out of control and keep pointing to your old server.

In these cases, this is the plan I am using:

install the new server with the same instance name as the old one
migrate all server objects, including logins and everything else
switch off or rename the old server
add a CNAME in your DNS to point the old server name to the new
server name

This way, you don't have to worry about linked servers, ETL jobs or applications still pointing to the old server.

Obviously, this assumes that you already have tested everything to be compatible with the new version.

UPDATE

Based on your comments, if you still want to discover incoming linked servers, you can register all your servers and run a multi-server query like this:

select name
from sys.servers
where product = 'sql server'
    and data_source = 'your sql server 2005'


How do I kill Oracle DataPump expdp job stuck in DEFINING state? My job failed because dump file already existed.
Now it's stuck in DEFINING state.

How do I stop/kill it?

declare
h1 number;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('EXPORT_TABLE_2017_1205_1402',user);
--DBMS_DATAPUMP.STOP_JOB (h1,1,0);
DBMS_DATAPUMP.stop_job (h1, 1);
DBMS_DATAPUMP.detach (h1);
END;
/

  2    3    4    5    6    7    8    9  declare
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3873
ORA-06512: at line 4

Using expdp getting segmentation fault:

 expdp stgdata@ORADB1S attach=EXPORT_TABLE_2017_1205_1402

Export: Release 11.2.0.3.0 - Production on Tue Dec 5 14:56:11 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPORT_TABLE_2017_1205_1402
  Owner: STGDATA
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 5F9D23C27A291F20E05328C414AC3548
  Start Time: Tuesday, 05 December, 2017 14:02:38
  Mode: TABLE
  Instance: ORADB1S
  Max Parallelism: 0
  EXPORT Job Parameters:
Segmentation fault

Any idea how do kill em?

select sid, serial# from v$session where action like 'EXPORT_TABLE%';

520 1161    wait for unread message on broadcast channel
587 635     wait for unread message on broadcast channel

Trying to restart same job:

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "STGDATA.DELETE_EXPORT_TABLE", line 59
ORA-06512: at line 1

expdp stgdata@ORADB1S attach=EXPORT_TABLE_2017_1205_1402
EXPORT> Kill_job

Have you tried dropping the export table?

DROP TABLE SYS.EXPORT_TABLE_2017_1205_1402;


for xml path('') output When I run the following

select t.type
  from (values ('Green'),('Blue'),('Red')) as t(type)
   for xml path('')

I receive this output

<type>Green</type>
<type>Blue</type>
<type>Red</type>

If I run the following

select t.type + '/'
  from (values ('Green'),('Blue'),('Red')) as t(type)
   for xml path('')

I receive this output

Green/Blue/Red/

Why does adding the concatenation in the select lead to the removal of the type tags and output on one line in the xml file? Running SQL Server 2012.

XML is bonkers

When you add the concatenated string, you lose the "path element".

For example if you do this:

SELECT t.type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML PATH('');

SELECT t.type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML PATH('type');

You get this back:

<type>Green/</type>
<type>Blue/</type>
<type>Red/</type>

The column name or alias acts as the path element.

Some other examples that might help

Using RAW, ELEMENTS

SELECT t.type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, ELEMENTS;

SELECT t.type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, ELEMENTS;

In the first example, you get the generic "row" element name, but in the second you get row/type.

When using RAW, TYPE:

SELECT t.type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, TYPE;

SELECT t.type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, TYPE;

The first query returns valid-ish XML, the second throws an error because the path element lacks an identifier.

Using AUTO, the table alias and column name turns into the path:

SELECT type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

SELECT type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

But without an alias, you get a similar error:

SELECT type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

I'd gin up an example with FOR XML EXPLICIT but it would be irresponsible for me to start drinking right now.


Is it bad form to use "LIMIT 1" on queries that should only return one row anyway? If you have a query like this:

SELECT * FROM table WHERE primary_key_column = 123 LIMIT 1;

What do you make of the LIMIT 1?
It is technically not necessary, as we are selecting on a unique column. But I have always had the habit of putting the LIMIT 1 on the end as it shows explicitly what you expect.

I think the main negative perhaps is it could mask bad logic in the query that makes it return more than one row, and make a straightforward error harder to solve.

And what about UPDATE or DELETE? This sort of 'belt and braces' approach:

UPDATE table SET a = 'ABC' WHERE primary_key_column = 123 LIMIT 1;

We all know the LIMIT 1 is good sense here when typing queries into the terminal to protect against updating rows we did not intend to. But do you still advocate it for hard-coded and tested queries in the application?

Question is general in nature no specific DBMS in mind.

For SELECT, I think one of the main considerations has to do with performance.

When using LIMIT 1 statistics on row correlations could play a part in determining slower/faster query execution. Although this may not be immediately observable with small data sets, it could become an issue as row numbers and index size grows (depending on DB).

Take this example, where a Postgres user encountered different execution times due to the DB's different assumptions about execution costs (based on available statistical data in the pg_statistic catalog and particularly correlation values) and therefore, resulted in different index scanning strategies.

As a rule of thumb I would use the LIMIT clause only where necessary rather than to externalize my expectations.

For UPDATE and DELETE as already mentioned, the behavior is highly DB dependent.


Transfer data from standard mysql server to Percona XtraDB Cluster My old mysql 5.5 server is having some hardware issue, so now I'm going to setup a new Percona 5.7,  two node cluster in my environment and connect my app into it.

Now the problem is,  How should I transfer all my data in mysql 5.5 server into my cluster ? Both are having InnoDB engine.
Will that be possible to take a sql dump from my old server and directly restore that in one of the nodes in my new cluster ? Or else what is the safest and effective way ?

If you have disk files, then the best would be to install 5.5 somewhere in order to get at the data.  5.7 is probably too advanced to be able to read 5.5 files without some hiccups.

If you have a data dump, then it will probably load into 5.7 with minimal hiccups.

A PXC node can be a Slave to your Master.  If we can get that set up, and if there are not conflicts with table names, etc, then we can feed the data into the cluster this way...  After setting up a Master with no data, "load" the data from a backup of your 5.5 data.  Probably (but I am not sure) a 5.5 Master should be able to replicate to a 5.7 Slave on a PXC cluster.

Or, it should be possible to simply load the backup directly onto any PXC node.  (Or the designated Master node, if you configured it that way.)

Do you have GTIDs enabled?  Galera type?  Or Oracle type?  (If enabled this may be another hiccup point.)

Sorry to be vague, but I hope I have given you some tips on where to look next.


select distinct one column with all instances of another column in a second column If I have a table with the following information:

ParentID | ChildID
---------+--------
     6   |      6
     6   |      7
     6   |      8
     6   |      9
     6   |     10

I need to display the distinct parent with all instances of child in the second column possibly concatonated with a comma e.g.

ParentID | ChildID
---------+--------
   6     |     6, 7, 8, 9, 10

Thanks in advance!

You can use a pattern like this:

SELECT files.database_id, db.name AS DatabaseName,
        STUFF((SELECT ', ' + names.name
                FROM sys.master_files names
                WHERE names.database_id = files.database_id
                FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
            , 1, 2, '') AS NameList
FROM sys.master_files files
JOIN sys.databases db
    ON files.database_id = db.database_id
GROUP BY files.database_id, db.name


MySQL - CSV UPDATE (not INSERT) into existing table? I have a table with the following columns:

id | name | city

The table has 1000 entries in it.

I have a CSV file with a limited number of lines like this:

id,city
34,Denver
45,Kansas City
145,New York

Can I use LOAD DATA INFILE to import my CSV into my table but instead of inserting new entries, it updates rows based off the id column?

REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs.

Here is something else you can do.

Suppose your table is called name_city and it looks like this:

CREATE TABLE name_city
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

and you want to do the LOAD DATA INFILE.

Here are your steps to update the city based on id:

Step 01) Create another table to use for the import

CREATE TABLE name_city_import LIKE name_city;

Step 02) Drop the name column from the import table

ALTER TABLE name_city_import DROP COLUMN name;

Step 03) Perform the import into name_city_import

LOAD DATA INFILE 'somefile.csv' INTO TABLE name_city_import ...

Step 04) Perform an UPDATE JOIN

UPDATE name_city_import B
INNER JOIN name_city A USING (id)
SET A.city = B.city;

Step 05) Remove the import table

DROP TABLE name_city_import;

That's it.

Give it a Try !!!


Indexes with the same object_id but different names When I run

SELECT
  count(object_id) AS count,
  object_id,
  min(name) as name1,
  max(name) AS name2
FROM
  sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count

I will get a list of indexes having the same object_id, but not necessarily with the same name.

Should not all indexes with the same object_id have the same name?

The Object_ID belongs to the object the index belongs to, such as an indexed view or a table.

When you look at the object_id in the sys.indexes documentation

  object_id  - ID of the object to which this index belongs.

If you want to uniquely identify the index you need the Object_ID + index_id columns or the index_id for a specific object_id.

To verify this, you can add the OBJECT_NAME() function to your query

SELECT
  count(object_id) AS count,
  object_id,
  OBJECT_NAME(object_id),
  min(name) as name1,
  max(name) AS name2
FROM
  sys.indexes
GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count;


In Job Step > Properties, only see two tabs: Package and Configuration — EncryptSensitiveWithPassword I am going to echo another question almost exactly, only my question has a different objective:

In Job Step > Properties, only see two tabs: Package and Configuration

I am trying to set a job step that executes a package requiring a package password, since the package protection level is set to EncryptSensitiveWithPassword. The job is running on SQL Server 2014. I see all kinds of examples of how to do this, where the job step properties page has 9 tabs (Set Values, Verification, Command Line, General, Configurations, Command Files, Data Sources, Execution Options and Logging). Command Line allows me to enter the password (with the /DECRYPT command line option).

When I look at my job step, logged in to the server as an Admin, running SSMS as administrator, all I see are two tabs: Package and Configuration. I deployed the packages using the Project Deployment method. How do I get those other tabs to show up?

(My ultimate goal is to give the SQL Agent who runs the job access to a MySQL DB password, and I understand setting the package protection level to EncryptSensistiveWithPassword is the correct way to do that, but I cannot find another way to provide the package password.)

In the job step properties after selecting SQL Server Integration Services Package as the type, switch Package source to SSIS Package Store to get the 9 tabs. SSIS Catalog is the default and only has 2 tabs.

SSIS Package Store:

SSIS Catalog (the default):


SQL Server 2000 export database to csv I need to move an SQL Server 2000 database to MySQL. I'm running into several complications. I'm no expert on SQL Server. To get going I'd like to have a csv-dump of structure and data.

First issue, 3 tables which are clearly in use by the application are not listed in the Tables view. When generating a diagram, a popup appears stating that those tables couldn't be loaded. The weird thing is that data from those tables is actually returned in the application (not cache).

The second issue is the use of DTS. I'm using the wizard to export the DB to text. I'm selecting "Copy table(s) and view(s) from the source database".
Then, I get to choose the file type, delimiters etc. There's a drop-down which has the first table selected. My problem is, I can only select 1 table but I need to export the entire DB. When continuing, the server will only export the first table. How do I get it to export the entire DB? Doing it table by table will take far too much time.

There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).

Look at this question over at DBA.SE for a number of references to migration tools. Also, the MySQL website publishes a white paper on MS SQL migration which has some useful information about how data types and functions could be substituted and describes how MySQL Workbench could be used for migration.

While DTS could be used to perform a one-time migration, it probably is not worth the effort - it has been created with repeatable procedures in mind (i.e. database synchronization, repeated export tasks etc.). The amount of time for creating a transformation package is going to be far higher than what you are aiming for.


How to see what's using TempDb space in Azure SQL DW? I'm having problems with TempDb being full. Are there any system views I can see to diagnose what's using up TempDb and/or how much space is still available on it?

It would be awesome to also see the TempDb transaction size limit too if possible.

So far, I've found the below, using these pages:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017

sys.dm_pdw_nodes_db_file_space_usage doesn't appear to hold any information about tempDb, so therefore we can only use sys.dm_pdw_nodes_io_virtual_file_stats.

The example in Microsoft's documentation doesn't explain what each file_id means, although the example they give filters on it. Until I can find any more information, I'll sum all files in TempDb as this appears to give the most sensible result (but it's a complete guess!).

        select sum(num_of_bytes_written) from sys.dm_pdw_nodes_io_virtual_file_stats
where database_name = 'tempdb'

I cannot find any documentation of transaction size limits for Azure DW Gen2.

This only applies to gen 1 - https://docs.microsoft.com/en-gb/azure/sql-data-warehouse/sql-data-warehouse-develop-transactions#transaction-size

Edit:

after further experimentation, it appears this query doesn't work either, the storage use always increases, so it appears to be more like a write counter. the size_on_disk_bytes never changes, so I'm not sure this would do it either.


Simply inserting to temp table and failing Can anyone tell me why this won't work for me?

CREATE TABLE #UndistributedCmds
(
pendingcmdcount int,
estimatedprocesstime INT
)
INSERT INTO #UndistributedCmds

EXEC sp_replmonitorsubscriptionpendingcmds ...

The error:

  Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 152An INSERT EXEC statement cannot be nested.

The error seems to imply that line 152 of the stored procedure definition is the issue, but I'm having trouble understanding this. Is this happening because the stored procedure itself has multiple stored procedures within it?

The error is happening because the stored procedure also has a #temp table or table variable and inserts into it using insert...exec. You can see it by looking at its OBJECT_DEFINITION():

insert into @countab (pendingcmdcount)
  exec @retcode = sys.sp_MSget_repl_commands

So basically you asking for this (pseudo-code):

INSERT INTO #outer EXEC
  (SELECT * FROM (INSERT INTO #inner EXEC ... ));

This nesting of insert...exec calls is prohibited in SQL Server, for reasons that are unknown/undocumented. The typical workaround is:

SELECT * INTO #UndistributedCmds
  FROM OPENQUERY([LoopbackLinkedServer], N'EXEC procedure...');

You'll need a linked server that references the local server machine, and it will need the attribute "data access" set to true...


Best-practice use of Resource Governor in SQL Server 2017 I am working on a server that houses both an OLTP workload and a data warehousing/reporting workload.  The OLTP system requires sub-second (in the milliseconds) response time while the reporting workload has a threshold of minutes.  The issue is some users will run multiple reports during peak transactional activity in our OLTP environment, thereby slowing the OLTP queries down.  On their own, the OLTP queries tend to run 20-30ms,  but when many reporting/data warehouse queries are fired up simultaneously the OLTP queries begin to run in the 4-5 second range.  Our bottleneck appears to be CPU, as this is where all of our waits are.

I would of course like to separate the workloads on two different servers, but I’m wondering if we could get a quick improvement via use of Resource Governor.

I’m considering two approaches:  (1) set a minimum allotment of CPU to the OLTP system so that it is guaranteed 25% resources, or (2) max and cap out the reporting\data warehouse queries so they cannot take more than 50% of the CPU.

Does anyone here have any practical experience with Resource Governor that would guide me in one of these two directions? Or is there another usage pattern that you’d suggest instead?

Supporting a hybrid workload requires more than just resource governance.

The docs call this scenario Real-Time Operational Analytics, and it's a bunch of features all working together.

First you need row versioning to ensure that the report users don't create blocking in the database. So set the database to READ COMMITTED SNAPSHOT or force the reports to run with SNAPSHOT isolation.

With locking solved, you then need resource governance and isolation to prevent the report users from using excessive server resources.  For many workloads the OLTP part doesn't need data file physical IO, but does depend on log file IO.  So physical separation of data and log can help prevent IO from reports from interfering with OLTP.  For resource governance I would start by capping the reporting workload's CPU, IOPS, Memory, and perhaps maxdop.

The next thing you need is to make the reporting workload less expensive.  For this use some combination of updatable nonclustered Columnstore indexes, filtered indexes, indexed views, and Columnstore compression delay.

And the final thing is that you need to use Query Store to monitor the workloads and provide visibility into the plans for the report developers.


Postgres externally supplied parameters I'm reading PostgreSQL 9.6 manual about Partioning.
And it says:

  Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters).

What does "externally supplied parameters" means? Is it env vars? Or config/runtime parameters? Or prepared statement parameters?

@a_horse_with_no_name is right in the comments, from the docs

  SPI_execute_with_args executes a command that might include references to externally supplied parameters. The command text refers to a parameter as $n, and the call specifies data types and values for each such symbol. read_only and count have the same interpretation as in SPI_execute.

So you can do

SELECT * FROM partition_master WHERE foo = 5;

or using prepared statements,

SELECT * FROM partition_master WHERE foo = $1;

But you can't do...

SELECT * FROM whatever AS w
JOIN partition_master AS p
WHERE p.foo = w.bar;


When setting up security for linked servers, what does the Impersonate checkbox do? Question:

what does this checkbox do?

Impersonate
Pass the username and password from the local login to the
linked server. For SQL Server Authentication, a login with the exact
same name and password must exist on the remote server. For Windows
logins, the login must be a valid login on the linked server.
To use impersonation, the configuration must meet the requirement for
delegation.

Create Linked Servers (SQL Server Database Engine)
In other words, this option corresponds to code
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'remote_server', @locallogin = N'domain\account', @useself = N'True'