the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead? I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all.

the way I have been doing this is demonstrated below:

declare @t table ( [rows] float )

insert into @t
select 1.344
union all select 32.3577
union all select 65.345
union all select 12.568
union all select 12.4333

select * from @t

Now I will do my convertion and save them all into a temp table and then we are going to look at the structure of the temp table.

begin try
   drop table #radhe
end try
begin catch
end catch

select
[Rows Formated] = REPLACE(CONVERT(VARCHAR(50),CAST(sp.[Rows] AS MONEY),1), '.00','')
into #radhe
from @t sp

select *
from #radhe

that is all fine, it worked great, it did what I wanted, however, when I look at the structure of the table I get the following:

use tempdb
go

sp_help '#radhe'

you can see on the picture, the varchar(8000).
can we avoid that?

I would be happy with a varchar(50)

From the docs on REPLACE():

  If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

This implies to me that it will always be varchar(8000) or nvarchar(4000) when the input isn't a max type. So try performing an additional CONVERT after the REPLACE:

SELECT
  [Rows Formated] = CONVERT(varchar(50),REPLACE(CONVERT(varchar(50),
    CAST(sp.[Rows] AS MONEY),1), '.00',''))
INTO #radhe
FROM @t AS sp;


Any existing programs that track the origin of data in your database? I am doing some integrity work at the moment, investigating cases when something goes wrong with incoming data.

The system I am working with involves multiple servers, including Sybase and SQL Sever 2016. The data is generally pulled from a stream, uploaded into a database in the first Server, cleaned and sent to a database in the next server, etc. Data is also sometimes manually uploaded or manually updated, or entered in through other processes.

Ultimately what this means is if something is wrong with the data, which is only captured/logged in the final server (SQL Server) before being sent out, there are numerous fail points where it can happen. Even different cells in the same row can have different origins by the time the data reaches the final server.

My question is: Is there any existing software which could actually track the origin of a given piece of data? If something goes wrong I would like to have an easier time backtracking the path the data took, to indicate whether the issue was a glitch in our cleaning process or if the data itself had issues from the moment it entered our system.

Alternatively are there any general systems/practices of data management which would make this tracking process either, rather than having to check every step of the way to see where the failure occurred?

The answer to the "systems/practices" would be to include a column with all data that is imported that signifies its source.

As for whether the issue was caused by your cleaning process, you simply need to save or log the changes that were made to each row, or enable change tracking.  Any of these solutions will introduce overhead that will affect performance, so let's be careful out there!


Transfer all read request to Slave in Master-Slave MySQL I have configured master slave on my system (MYSQL). Now, is there is any script or way available or possible to transfer all read request to SLAVE.

So, let me make my question here more clear.
I have a application written in JAVA-SPRING and use Bone-CP to connect to Mysql db.
So, now I have to implement Master-Slave where all write request goes to MASTER and all read request goes to SLAVE via Master or directly.
I don't want to make any changes in code, so I am looking for some solution which I could implement on MYSQL to transfer all read/Select request to Slave.

I have googled around but no solution or hint found.

There might be two possible solution for it.
1. All request first hit MASTER and then master transfer all request to SLAVE.
2. A layer between APPLICATION and MYSQL which decides where to transfer the request accordingly

But I have no idea how to implement it, So can anyone provide any link, hint or solution to my problem.

this is called  Read/Write Splitting

MySQL itselft have no ablity to meed your demand,

but MySQL officially release a simple program called MySQL Proxy,
which will help you to archive your goal and more.
however it has lots of limitations, such as doesn't support procedure, etc.
and are offically NOT recommended to use in production environment.

Many companies create their own middleware and some are open sourced,
but as those middlewares are customized by their own scenario, it's actually not easy to use
in other enviroment. so my advice is ,according to your business, write your own middleware.

hope that useful!

added 2014-6-26 7:31 GMT

you probably could consider some other high availability solution such as :

Galera Cluster

Multi-Master Replication Manager for MySQL(a.k.a MMM)

they will partially meet your request, which means write/read on master node, read on several slave nodes

note that if you use MySQL Replication and read from the slave side, you probably read data slower than master side particularly there are heaviy wirte on master side, because hundreds of thousands of threads are writen simultaneously, while only one slave I/O thread propagate data to slave node.


MySQL - SQL statement to test minimum length of the word 'ft_min_word_len' I'm trying to understand FULLTEXT indexes.

Is there some SQL query to test 'ft_min_word_len' variable?

I've tried to match four characters only in the example below but it doesn't work. Why the third row is returned if there is no match for '(database|data)'

mysql> SHOW VARIABLES LIKE 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE articles\G
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('data' WITH QUERY EXPANSION);
Empty set (0.00 sec)

To retrieve ft_min_word_len as a query, run this:
SELECT variable_value FROM information_schema.global_variables
WHERE variable_name='ft_min_word_len';

But wait, you are asking

Why the third row is returned if there is no match for '(database|data)'

Did you know there are 543 words that are excluded from FULLTEXT indexes ?
Note the following from your query and row data...

The words will and this are in the stopword list.
The word show is not in the stopword list, so searching for show should produce a tangible result.
The word data is not in the body column as its own word. Try searching for data* or +data*

I wrote about this before and how to disable the default stopword list:

Jan 26, 2012 : Mysql fulltext search my.cnf optimization
Dec 29, 2011 : MySQL Full Text index issue

UPDATE 2013-04-25 12:26 EDT
@Michael-sqlbot has pointed out in his comment to my answer that QUERY EXPANSION does a double lookup to include "near-misses" due to relevance. If @Michael-sqlbot adds his comment as an answer, I will retract my answer, upvote his answer, and ask that @Michael-sqlbot's answer be accepted.


Need an idea for a complex SQL question I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-)

Here is what I am trying to accomplish (for PostgreSQL):

Got a table with logged communications related to products.
The table contains an ID (PK, serial), date/time when inserted, a product ID, an error code field (and some other fields not relevant for now)
Rows get validated upon reception, and all rows are stored.
Rows are either accepted or rejected (error code field).
When accepted, fine, the rows get processed (as a 'step 2' - not relevant for now).
However, when rejected the sender gets an error message and is requested to correct the data and resend.
So, when the corrected data is received again and is validated and accepted, we should have a product row that got rejected and a product row that got accepted.
We have learned that not all senders check these error message and resend their (corrected) data.

So, what I am looking for are these rows for product that were rejected but never sent again correctly - it may happen that rows get send multiple times and rejected multiple times by our validation process.

I have been at it for a couple of hours, but so far without much luck.
What SQL trick could I pull out of the hat to find these rows?

It sounds like what you're looking for is the list of communications for which there is no accepted message. You're only interested in checking for communications where there is at least one rejection, so the logic might be something like:

select distinct communications
where  the status is rejection
and there does not exist an acceptance for that communication.

Alternatively, you could check the status on the record with the highest received timestamp for each communication to ensure that it is an acceptance.


how to join table from different database with different Users of database? I have Two database called.

Database1:

host: 127.0.0.1
user: root1
pass: root1
dbname: db1

Table 1: Category

ID    INT(11)          Primary Key
Name  VARCHAR(255)

Database2:

host: 127.0.0.2
user: root2
pass: root2
dbname: db2

Table 2: Product

ID    INT(11)          Primary Key
Name  VARCHAR(255)
CatID INT(11)          FK( Table1: Category: ID )

I am calling query like this;

SELECT *
FROM db2.product AS p
LEFT JOIN db1.category AS c ON p.CatID = c.ID

It will return error:

SELECT command denied to user 'root2'@'127.0.0.2' for table 'Product'
SELECT command denied to user 'root1'@'127.0.0.1' for table 'Category'

Can anyone help me How can I resolve this issue.

This looks like MySQL.

But independent of the rdbms, you need to grand a user access to a database for it to be able to do anything (like reading data) in that database. In your example you have one user that has access to the one database and another user that has access to the other database. You need to give the user that you connect as when executing this query access to both databases.

How you do that is dependent on your hosting provider. If you use PHPMySQL you can go to the User section and grant the necessary rights. However, you hosting provider might have turned that option off. In that case you need to talk to them to get this resolved.


in sqlite how to check the table is empty or not? I need the check the table is empty or not
 employee table contains columns are id name

id  name
1   john
2   hunter
3   smith
4   will

If your table can contain a lot of rows, you can win some time with

SELECT count(*) FROM (select 0 from employee limit 1);


For how long postgres will keep alive connection after changing credentials? I have an app running Ruby on Rails, standard nothing fancy at all. Today when I was changing password in Postgresql I spot that app is still running with old credentials. This gives me time to rolling restart app with new db credentials. Open connections still work, because was authenticated with old credentials. How long Postgres will keep those connection active? I assume that there must be some time after client must re-authenticate. Where I can find more about it? Some setting? Tried to find something on https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html but still don't know how it works in details.

A PostgreSQL instance will keep a client connection up and running until the connection either reaches a specified client timeout or the client (application) closes the connection.

A change of password has no effects on existing connections and will only affect new connections.

There is no mechanism that constantly checks if the connection is still "password valid". A client connection is a one time authentication and if it was valid at the time of authentication will keep on being valid until closed.


Oracle: Helping hand writing this query. Do I need a sub query here? I have the below query that returns the number of orders created on a particular customer number per month for the past 2 years.

SQL

SELECT TO_CHAR(o.entry_date, 'MM'),
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2013 THEN 1
             ELSE 0
           END) AS val_2013,
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2014 THEN 1
             ELSE 0
           END) AS val_2014
FROM   orders o
WHERE  o.customer_no = '0'
       AND EXTRACT(year FROM entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM')

Results

TO   VAL_2013   VAL_2014
-- ---------- ----------
01        201        205
02        246        235
03        286        243
04        234        206
05        251        161
06        217        187
07        239        210
08        190        204
09        206        224
10        266        175
11        171          0
12        133          0

12 rows selected.

Question

The query above returns the number of orders but I also want the value of these orders in the same format as above.

I have a table order_totals which has order_final_total but there is no customer number column in this table. This is the part that is confusing me. Instead of the SUM of the number of orders, I need the SUM of the order value but I don't understand how to get this for just the orders on a particular customer number.

Expected Results

TO   VAL_2013   VAL_2014
-- ---------- ----------
01     9201.42    3467.67
02     1287.94    7819.25
...

Do I need to use a sub query, to first get the order numbers?

I've only been using Oracle for around 2 months so any help is appreciated!

50% there query

Ok I've come up with the below query that shows the value of these orders over the past 2 years split in to months but in a single column. I couldn't work out how to split it by year as the above query. Any ideas?

SELECT TO_CHAR(o.entry_date, 'MM'),
       SUM(t.order_final_tot)
FROM   orders o,
       order_totals t
WHERE  o.order_no = t.order_no
       AND o.customer_no = '0'
       AND EXTRACT(year FROM o.entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM')

Results

TO SUM(T.ORDER_FINAL_TOT)
-- ----------------------
01              222333.09
02              216587.53
03              257628.37
04              245838.47
05               227823.5
06               241722.9
07              270359.57
08              277249.43
09              245064.45
10              227929.83
11               85159.13
12               56982.31

12 rows selected.

@ypercube's answer

I get:

MO ORDER_TOTAL_2013 ORDER_TOTAL_2014
-- ---------------- ----------------
03                0            88.26
05                0             45.2
06             16.1            23.75
07                0            69.37
09                0           116.68

Why do I only get months 03, 05, 06, 07 and 09?

Using PIVOT:

WITH
   combined AS (
      SELECT
         o.customer_no,
         EXTRACT(month FROM o.entry_date) AS order_month,
         EXTRACT(year FROM o.entry_date) AS order_year,
         t.order_final_tot
      FROM orders o
      JOIN order_totals t ON (o.order_no = t.order_no)
   )
SELECT *
FROM combined
PIVOT (
   SUM(order_final_tot) AS ord_tot,
   COUNT(*) AS ord_cnt
   FOR order_year IN (2013, 2014)
);


Why Does the Update Statement also updates indexes? I am currently learning about SQL queries and how indexes work with them, and I ran into a situation that I don't understand. Let's say I have this query for that updates a tables' values with another table and they both have a dozen columns with a hundred thousand rows each:

UPDATE TA
SET TA.Column = TB.Column,
    TA.ColumnTwo = TB.ColumnTwo,
    TA.ColumnThree = 3
...
FROM TableA TA
INNER JOIN TableB TB ON TA.ColumnFour = TB.ColumnFour

Table TA has 9 indexes on it while Table TB only has 2. This update query would take longer than 5 minutes to update only a few hundred rows that matches the condition TA.ColumnFour = TB.ColumnFour. I assumed that it was because of the extra indexes since the query execution plan shows 9 Index Update (1 for each index). I removed the duplicate/unecessary indexes and I was able to lower the number of indexes to 4 but the query would still run slow.

My question is why does the update statement also updates indexes and how would I be able to prevent the query from creating the multiple Index Update?

For consistency. There are times the access path will use only the data from the indexes or will start from the index and jump to the table. In both cases the informatoin on these two entities should be compatible.

A index is just a separate ordered set of data from a table. When you remove an entry from the table, the entry on the index pointing to this row should also be removed. The same should happen when you update/insert. That's the burden of indexes: faster select and slower DML (insert, update, delete).

When you do, for example, a

select count(*) from table t1 where c1 = :value;

Your result should be the same whether you have indexes or not. And the beauty of Database Management Systems is that they do that automatically for you. :)

You cannot prevent this update. The only time this will not happen is when you do not change the data referenced on the index.
If you have a table with four columns (a, b, c, d), and two of them are indexed (a, b), updating only the other two (c, d) will not trigger this extra update.


Inner join in select statement within stored procedure I'm trying to Create a stored procedure but there is a field actually exist in another field but its id is used as foreign key in current table, SQL Query is:

Create Procedure sp_Select
As
Begin
    SELECT [ProductID]
           ,[Name]
           ,SELECT tblCategories.CategoryName
            FROM  tblCategories INNER JOIN tblProducts
            ON    tblCategories.CategoryId = tblProducts.CategoryID;
           ,[Model]
           ,Cast([RAM] As nvarchar(10)) +' '+[RAMMemUnit] As RAM
           ,Cast([SecondryMem] As nvarchar(10)) + ' ' + [SecondMemUnit] As Memory
           ,[OSVersion]
           ,[Processor]
           ,CAST([Battery] As nvarchar(10)) +' '+ [BatteryType] As Battery
           ,UnitsInStock  AS 'Quantity In Stock'
    FROM [tblProducts]
End

Error in this following portion:
SELECT tblCategories.CategoryName FROM tblCategories INNER JOIN tblProducts ON  tblCategories.CategoryId = tblProducts.CategoryID;

Someone help me please.

There are actually two problems with your first query.  The first is that you have an extra semi-colon for the subquery where you are getting the category name.  The second is that if that query returns more than one row then you will receive an error.  I've corrected it for you to eliminate both of those issues.

CREATE PROCEDURE sp_Select
AS
BEGIN
    SELECT [ProductID]
        ,[Name]
        , CategoryName = (SELECT TOP 1 tblCategories.CategoryName
                            FROM tblCategories
                            WHERE tblCategories.CategoryId = tblProducts.CategoryID)
        ,[Model]
        , Cast([RAM] AS NVARCHAR(10)) + ' ' + [RAMMemUnit] AS RAM
        ,Cast([SecondryMem] AS NVARCHAR(10)) + ' ' + [SecondMemUnit] AS Memory
        ,[OSVersion]
        ,[Processor]
        ,CAST([Battery] AS NVARCHAR(10)) + ' ' + [BatteryType] AS Battery
        ,UnitsInStock AS 'Quantity In Stock'
    FROM [tblProducts];
END

However, queries that have sub-queries like the above generally perform worse than ones that can achieve the same results with a sub-query.  It lets you access other fields from the tblCategories table without incurring additional sub-query costs, and you can more easily search for products that are in particular categories.

Some of the comments have mentioned changing the join from using a LEFT OUTER to be an INNER join.  I chose LEFT OUTER initially because that more closely matched the result set you would have received in your first query.  Essentially, it would have NULL's in place where the CategoryName would be assuming you had Products without Categories.  An INNER JOIN would eliminate products that had no matching category.

CREATE PROCEDURE sp_Select
AS
BEGIN
    SELECT P.[ProductID]
        ,P.[Name]
        , C.CategoryName
        ,P.[Model]
        , Cast(P.[RAM] AS NVARCHAR(10)) + ' ' + P.[RAMMemUnit] AS RAM
        ,Cast(P.[SecondryMem] AS NVARCHAR(10)) + ' ' + P.[SecondMemUnit] AS Memory
        ,P.[OSVersion]
        ,P.[Processor]
        ,CAST(P.[Battery] AS NVARCHAR(10)) + ' ' + P.[BatteryType] AS Battery
        ,P.UnitsInStock AS 'Quantity In Stock'
    FROM [tblProducts] AS P
        LEFT OUTER JOIN tblCategories AS C ON C.CategoryId = P.CategoryID;
END


How to do SUM() but with some rules to follow? Maybe this question is confusing to understand ( as I'm not fluent in English ) but,
I need to select sum(cod) for example, but this sum() needs to have the following rule:

Part 1:
it needs to be ALL cods, except for 21.

Part2:
Another column, with another sum(), but only with cod 21.

it's not the entire query, But after this, I think I can do it.

I tried this:

select sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames
        in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22 )
    )Corpo_Delito,
sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames  = 21 )
    )Cadaverico

It works If I have only

select sum(vit_codigotiposexames) from
    (
    select * from tb_vitima where vit_codigotiposexames
        in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22 )
    )Corpo_Delito

is there a way to make this query, a unique query? and, is there a way to name these columns? because I know I need to alias them, but they stay with that (no column name) above it.

I think you can differentiate this SUM's using a SUM(CASE) statement:

select sum(case when vit_codigotiposexames = 21
                then vit_codigotiposexames
                else 0 end) Cadaverico,
       sum(case when vit_codigotiposexames <> 21
                then vit_codigotiposexames
                else 0 end) CorpoDelito
from   tb_vitima;

I've set up an example:

create table tb_vitima (id int identity, vit_codigotiposexames int);
insert into tb_vitima values (2),(5),(10),(11),(21),(21),(10),(23);

select sum(case when vit_codigotiposexames = 21 then vit_codigotiposexames else 0 end) Cadaverico,
       sum(case when vit_codigotiposexames <> 21 then vit_codigotiposexames else 0 end) CorpoDelito
from   tb_vitima;
GO

Cadaverico | CorpoDelito
---------: | ----------:
        42 |          61

dbfiddle here


How to Drop Database in Single_User Mode How do I drop a database which shows DatabaseName (Single User) as its name?

When I try to delete it, I get the following error:

  Alter failed for Database 'DatabaseName'.  (Microsoft.SqlServer.Smo)

  ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5064)

I tried to execute the ALTER below and still have the same issue.

ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT

If you are going to drop a database, you have to be the only connection to that database. If any other connections are there, you can't drop it. From the error message (that error means your database is in Single_User mode but there is already a connection so you can't connect) my assumption here is that you tried to set it to Single_User mode and then attempted to do the drop but either you grabbed a connection that you didn't know about, or some other process has. The fact that restarting SSMS worked for you tells me it was probably you grabbing that connection. So here is how you can fix that.

Logically you have to put the database back into multi_user mode so you can then put it into single_user mode again (but this time you'll be in control of that single connection allowed and drop the database before something else connects) and then your database will be gone.

In code here is how you need to do this (but first close your query windows that are connected to that database. Restart SSMS and make sure you don't select this database in the object browser):

-- Then attempt to take your database to multi_user mode, do this from master
USE MASTER
GO

ALTER DATABASE myDatabaseName
SET multi_user WITH ROLLBACK IMMEDIATE
GO

-- Now put it into single_user mode and drop it. Use Rollback Immediate to disconnect any sessions and rollback their transactions. Safe since you are about to drop the DB.
ALTER DATABASE myDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE myDatabaseName
GO


SQLPlus Command Line In-line Set Commands I have the need to run linux command line direct query, including  SET options.

The query by itself runs fine.

However, when I attempt to add set commands, it fails.

(my alias 'db' connects to SQLPlus successfully)

OK/works fine (when sending single commands, issue seems to be with strining them together):

$db<<<"SELECT * from mydb.mytable;"
$db <<<"show heading;"
$db <<<"SET heading OFF;"

Not OK - Any variation of quotes/not quotes I try results in error:

$db<<<SET HEADING OFF "SELECT * from mydb.mytable;"
$db<<<"SET HEADING OFF, SELECT * from mydb.mytable;"
$db<<<"SET HEADING OFF "SELECT * from mydb.mytable;"

Results in complete error or:

SQL> SP2-0158: unknown SET option ","

Please assist.

You should not put SET and SELECT in the same line, they are seperate commands. For example this works:

$ alias db='sqlplus -s user/password'

$ db <<< 'set heading off
set timing on
set echo off
select * from dual;'

X

Elapsed: 00:00:00.00

If you insist on using a one-liner, you could try this:

$ echo -ne 'set heading off\nset timing on\nset echo off\nselect * from dual;' | db

X

Elapsed: 00:00:00.00


What is the usage of performance_schema in MySQL 5.5? I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list.

What is the usage of this new schema..?
How can we monitor using this schema..?
What are the advantages of using this schema..?
Is there any overhead by enabling of this schema..?

All of your answers can be found by reading the documentation:

What is the usage of this new schema..?

  The Performance Schema monitors server events. An “event” is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements.

What are the advantages of using this schema..?

  The Performance Schema is a tool to help a DBA do performance tuning by taking real measurements instead of “wild guesses.” source

Is there any overhead by enabling of this schema..?

  Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable.

How can we monitor using this schema..?

This page gives a basic overview of how to use the performance schema


How to use document age as weigt in ts_rank In a search mode I would like to impact the rank of my search related to age of the document. I have a working version but I found it very complicated.

It say "Does the same founded younger document is more relevant than an older one"?

SELECT
  ts_rank(to_tsvector('an example of document'), to_tsquery('document')) /
    (EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '3 days')))/3600)::int >
  ts_rank(to_tsvector('example of document'), to_tsquery('document')) /
    (EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '10 days')))/3600)::int AS relevancy;

How to deal with more precise time without having "complex" result like:

SELECT ts_rank(to_tsvector('example of document'), to_tsquery('document')) /
    (EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '10 days')))/3600)::int; -- 1.4441788961179666e-07

Should I round for performance?

I don't even get how this code is supposed to work

You have

(EXTRACT(epoch FROM (SELECT (NOW() - INTERVAL '10 days')))/3600)::int

All that's doing is resolving to a static int. How is dividing a result set by a static int significant.

SELECT ts_rank(to_tsvector('example of document'), to_tsquery('document'))
  / 420953;


Galera Cluster - 3rd Quoroum Only Node - No Replication - Possible?HowTo? I want to setup a 2 node cluster for MariaDB Galera cluster. What I read says it "requires" 3 nodes minimum. This is not correct, I am familiar with HA through using pacemaker, and basically, with only 2 nodes, you don't have quorum, which Galera Cluster Uses.

I found a good article, which basically explains, the 2 options I already figured existed.

Option 1:

Disable Quorum Check

SET GLOBAL wsrep_provider_options='pc.ignore_sb=TRUE';

Option 2:

Post Split-Brain, manually tell a node to become Primary

SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';

What I'd like, is to set up a 3rd VM, that doesn't replicate any DB's, but acts an a quorum box.

Is this possible? Any links on HowTo? - Maybe I am over-thinking this, is there a way to join a cluster, but not define any Databases to replicate? (Seemed to me when I set up Galera that it just replicated everything automatically, which is good, but I'm not so sure there is a "don't replicate option")

http://galeracluster.com/documentation-webpages/twonode.html

The GALERA ARBITRATOR is exactly what I needed.

The recommended deployment of Galera Cluster is that you use a minimum of three instances. Three nodes, three datacenters and so on.

In the event that the expense of adding resources, such as a third datacenter, is too costly, you can use Galera Arbitrator. Galera Arbitrator is a member of the cluster that participates in voting, but not in the actual replication."

Galera Arbitrator serves two purposes:

When you have an even number of nodes, it functions as an odd node, to avoid split-brain situations.
It can request a consistent application state snapshot, for use in making backups.

HowTo:

http://galeracluster.com/documentation-webpages/arbitrator.html


mysqlworkbench after cmd mysql startup went wrong i have problems with my mysql server. I have installed the newest mysqlserver but having problems after starting my server from command line on localhost. Before that i was running xammp. The problem is that when starting from command line and not from xammp, mysqlworkbench shows me tables from another datadir when connecting with database . I really don't know how to fix my problem.

It sounds like you have two installations of MySQL: one installed normally and one installed through XAMPP. When starting MySQL through XAMPP it will start the one it installed, when started through the command line it installs the one that is installed natively. They both bind to the same port (so you can only have one active at a time) so MySQL Workbench will display the contents of the MySQL server that is started.

Because you have two installations of MySQL you should either only use one of them (and possibly remove the other) or only start the one that you need to access.


Alternative to "FOR JSON" in SQL Server 2012? In SQL Server 2016 I can output query results as JSON as described in the documentation Format Query Results as JSON with FOR JSON (SQL Server)

SELECT *
FROM x
FOR JSON PATH

But I'm finding that this is not supported in SQL Server 2012.

Is there a built-in way to convert tabular data to JSON data in SQL Server 2012?

To make this cross compatible I would ideally like to be able to use for json path in SQL Server 2012.

No. JSON support was new in SQL Server 2016.

If you want to convert data to JSON, you have a few options:

Fetch the rows as you would any other query result, and convert it in the application tier (like C#, Java, etc)
Build T-SQL logic in a stored procedure or function to convert it to JSON yourself (but keep in mind that you won't be able to use "FOR JSON PATH" the exact same way 2016 does)
Build CLR logic to do it (again, won't be compatible with "FOR JSON PATH")


Failover Cluster and AlwaysOn Availability Groups across Data Centers We have two 3 node clusters in separate data centers, let's call them ClusterP and ClusterS.

ClusterP is our primary and ClusterS is our secondary for disaster recovery. Each cluster has multiple failover cluster instances on it but they are completely segregated from each other.

We'd like to introduce AlwaysOn Availability Groups from ClusterP to ClusterS. I've read multiple guides to this and there is one question I can't really answer. We do NOT have the ability to have cross-datacenter shared storage (no SAN mirroring across datacenters). With this setup, is it possible to setup AlwaysOn Availability Groups?

I understand if we had a standalone server in the secondary datacenter, we could setup an AG with an FCI by adding that server as a node in WFCS, removing it as an owner from any of the FCIs on ClusterP, and setting it up as an AG member. This method does not use shared storage based on my research.

My confusion comes from having two-three node clusters and how they can work together with AGs. It seems to me I need to disband the cluster on ClusterS, add the nodes to ClusterP, add the FCIs needed then setup the nodes so that the physical nodes in location of ClusterS can only host those FCIs and the nodes in location of ClusterP can only host those FCIs. Will this work?

I'd appreciate any tips.

We'd like to introduce AlwaysOn Availability Groups from ClusterP to ClusterS. I've read multiple guides to this and there is one question I can't really answer. We do NOT have the ability to have cross-datacenter shared storage (no SAN mirroring across datacenters). With this setup, is it possible to setup AlwaysOn Availability Groups?

No, that is not possible.  Outside of a migration, an availability group cannot straddle multiple failover clusters (WSFC).

If you want to leverage an AG, you'll have to ensure that all participating replicas reside within the same WSFC cluster.  As for SQL Server failover cluster instances participating in the AG, that is not a problem.  A FCI can be an AG replica.

You can achieve a geographically dispersed cluster with a few different strategies.  If you need to have a witness vote (which, if you have three node clusters then that wouldn't help and you'd be best with a node majority quorum model) you could utilize a file share witness.

If what you're looking for is your availability group to span two data centers, then yes that is possible.  The only requirement for this situation would be a single WSFC cluster (which is possible to span multiple data centers).


Connection to localhost dramatically slower than to network server I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB.

When I'm on the network at work, the connection is almost instantaneous. When I am at home working with a local copy of the database, the connection takes about 5 seconds. At first I thought the performance hit had to do with the queries, and even posted a question on Stackoverflow with that incorrect assumption: https://stackoverflow.com/q/10467876/138938

The connection is just as slow locally whether I specify "localhost" or a ".". It seems like my code is having a hard time resolving where exactly my localhost is. Is there something I should do with my hosts file or some other setting that I can modify to speed up my connection on my local machine?

What other thing should I look at to troubleshoot?

Thanks!

Try enabling TCP/IP on your local instance:

Open SQL Server Configuration Manager (mmc.exe -> [Ctl] + [M] -> Add "SQL Server Configuration Manager" snap-in)
Expand "SQL Server Network Configuration"
Click on "Protocols for MSSQLSERVER" (or whatever your local instance name is)
Set "TCP/IP" to Enabled

I just ran into this problem tonight.  Contrary to @Aaron Bertrand's seemingly intuitive suggestion in the comments, having TCP/IP disabled was actually causing my delay.  TCP/IP is disabled by default, so this is likely a common source of frustration for others, too.

I ran ProcMon.exe while connecting to a remote server and a local instance of SQL Server.  I was using the Jet/ACE ODBC TraceSQLMode to debug the delay.  TraceSQLMode writes to a text file every time an ODBC command is issued.

When connected to the remote server, seven commands were written to the text file in under one second total.  With the local instance, there were almost exactly two seconds between each ODBC call.

I assume that the Jet/ACE ODBC driver was attempting to connect to the localhost via TCP/IP initially with every ODBC call.  Since TCP/IP was disabled, this likely failed after a two-second timeout, at which point the Jet/ACE ODBC connection appears to have fallen back on Shared Memory access.

Enabling TCP/IP eliminated the two-second timeout delay, resulting in a massive performance increase (especially when making multiple calls to the database).


Basic SQL question about setting constraints In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works?  Is pk_dept something the database uses internally, since it doesn't seem to be a visible part of the table?

create table dept
    (deptno decimal(2,0),
    dname  varchar(14),
    loc varchar(13),
    constraint pk_dept primary key (deptno));

A primary key is an object that lives in the meta data of the database.  It will have a name no matter how it is created.  The question is will it have a meaningful name that matches your coding standards and that your team can understand at a glance or will it have a meaningless name the the system generates and everyone will continually have to look up?

The RDBMS does not care what string of characters constitute the object's name.  It's a computer; all strings are equally meaningful/less.

To exaggerate the point to an extreme, you could have all your tables called T1, T2, T3 etc and columns C1, C2, C3.  The application would work, the DBMS wouldn't care, but boy it would be hard to maintain!

You can get problems with replication for unnamed objects.  The source and target systems know the objects by different names internally and replication gets confused trying to map the source to target.


how to calculate minus value between rows and between time I have a table contains id, server_time and value:

id   server_time      value
---  ---------------  -----
100  04/10/2016 0:00  3
101  04/10/2016 0:15  6
102  04/10/2016 0:30  8
103  04/10/2016 0:45  9
104  04/10/2016 1:00  10
105  04/10/2016 1:15  10
106  04/10/2016 1:30  12
107  04/10/2016 1:45  13
108  04/10/2016 2:00  14

This data will be inserted every 15 minutes, and the value is a cumulative value.

I need to output columns min15, hour1 and hour2:

column min15 will be the difference between this row's value and the one 15 minutes ago.
column hour1 will be the difference between this row's value and the one 1 hour ago.
column hour2 will be the difference between this row's value and the one 2 hours ago.

id   server_time      value  min15  hour1  hour2
---  ---------------  -----  -----  -----  -----
100  04/10/2016 0:00  3      3      null   null
101  04/10/2016 0:15  6      3      null   null
102  04/10/2016 0:30  8      2      null   null
103  04/10/2016 0:45  9      1      null   null
104  04/10/2016 1:00  10     1      7      null
105  04/10/2016 1:15  10     0      null   null
106  04/10/2016 1:30  12     2      null   null
107  04/10/2016 1:45  13     1      null   null
108  04/10/2016 2:00  14     1      4      11

Or should I display these columns (min15, hour1, hour2) as separate results?

Any suggestion for the query would be appreciated.

I see no guarantee that there are no gaps on the table and all rows follow every 15 minutes.

But if and only if the data is like that - and you don't have anything outside these specs - then you could simply use the LAG() function:

SELECT
    d.id, d.server_time, d.value,
    min15 = d.value - LAG(value) OVER (ORDER BY d.server_time),
    hour1 = d.value - LAG(value, 4) OVER (ORDER BY d.server_time),
    hour2 = d.value - LAG(value, 8) OVER (ORDER BY d.server_time)
FROM server_data AS d
ORDER BY server_time ;


Creating Event that execute at end of each month I need to create an event in mysql that runs at the end of every month 11 PM.I have tried creating an event that creates another events with code below:

DELIMITER |
CREATE EVENT `2014-10-31_CALL_ISRSP` ON SCHEDULE AT '2014-10-31 23:50:00'
DO
BEGIN
call myStoredProcedure();
set @creatNextEvnt := Concat("CREATE EVENT ",LAST_DAY( NOW() + INTERVAL 1 MONTH ),"_CALL_ISRSP ON SCHEDULE AT '",LAST_DAY( NOW() + INTERVAL 1 MONTH )," 23:50:00' DO BEGIN call IncidentsSummaryRecorder(); set @creatNextEvnt := Concat(\"CREATE EVENT \",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\"_CALL_ISRSP ON SCHEDULE AT '\",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\" 23:50:00'\"); PREPARE cer FROM @creatNextEvnt; EXECUTE cer; DEALLOCATE PREPARE cer; set @delLastEvent := Concat(\"DROP IF EXISTS EVENT \",LAST_DAY(NOW()),\"_CALL_ISRSP\");  PREPARE del FROM @delLastEvent; EXECUTE del; DEALLOCATE PREPARE del; END;");
select @creatNextEvnt;
PREPARE cer FROM @creatNextEvnt;
EXECUTE cer;
DEALLOCATE PREPARE cer;
set @delLastEvent := Concat("DROP IF EXISTS EVENT ",LAST_DAY(NOW()),"_CALL_ISRSP");
PREPARE delFROM @delLastEvent;
EXECUTE del;
DEALLOCATE PREPARE del;
END|

but this gives me an error that prepared statement does not support this type of command yet.

I tried storing my SQL strings to a file and executing them within the same event, but I do not know how to execute the file I created. Any help?

You should never have to drop and create new events automatically.

You should just make the event repeatable:

Every minute, check the date and time
If the hour is 23 and minute is 50 on the last day of the month, call IncidentsSummaryRecorder();

Here is that code

DELIMITER $$

CREATE EVENT ISRSP
ON SCHEDULE
    EVERY 1 MINUTE
    STARTS '2014-10-31 23:50:00'
    ON COMPLETION PRESERVE
DO
BEGIN
    DECLARE rightnow DATETIME;
    DECLARE hh,mm TINYINT;

    SET rightnow = NOW();
    SET hh = HOUR(rightnow);
    SET mm = MINUTE(rightnow);

    IF DATE(rightnow) = LAST_DAY(DATE(rightnow)) THEN
        IF hh = 23 THEN
            IF mm = 50 THEN
                CALL IncidentsSummaryRecorder();
            END IF;
        END IF;
    END IF;

END $$

DELIMITER ;

Give it a Try !!!


File locations for Full Text Catalogs using SQL Server 2005 on VMware I have an issue with one of our development SQL Server Servers. We have databases that use full text search catalogs. Just some background info, our servers are configured to use separate drives for logs, data, and the default installation location This server is a virtual machine. When I try to create a new full text search catalog, it forces me to create it in the default installation directory, citing that you can't create a catalog on a removable drive. This seems kind of contrived since this is a VM, none of the drives are "physical", and the drive it will let me install it on isn't any less "removable" than the the others.
It does this on any of the test servers, but for this conversation the specifications are:

Windows Server 2008-R2 x64 Standard Edition
SQL Server 2005 9.0.5057
VMware vCenter Server 5.1.0.88016

All of these drives are attached to the vm as Hard disks, not mapped drives.

But when creating a new catalog, I always receive an error unless I put the catalog on the same drive the instance is installed on.

Is this just a quirk with sql server, or is there a work around? I know in newer versions of sql server, the catalogs are stored internally, and so this isn't an issue, but a number of our servers are still and will stay for quite some time on sql server 2005. Has anyone else seen this issue? Did you find a solution?

Use a folder other than the root of the hard drive.  UAC is preventing you from writing to the root of the drive.


Top N earliest punched in Employees across departments in a month Find two of the earliest punched in Employees across departments in a month The dataset.

Id   EmpName         EmpPunchInTime        EmpDept
1      Dharma        26-JAN-2018 08:45     Engineering
2      Gumpina       26-JAN-2018 08:46     Finance
3      Kumar         26-JAN-2018 08:47     HR
4      Kamal         26-JAN-2018 08:48     Engineering
5      Muruges       26-JAN-2018 08:49     Finance
6      Gumpina       25-JAN-2018 09:01     Finance
7      Kumar         25-JAN-2018 08:42     HR
8      Biju          25-JAN-2018 08:40     Engineering
9      Divya         25-JAN-2018 08:49     HR
10     Vali          24-JAN-2018 08:52     Engineering

The expected output is,

Id   EmpName       EmpPunchInTime        EmpDept
8      Biju          25-JAN-2018 08:40     Engineering
1      Dharma        26-JAN-2018 08:45     Engineering
2      Gumpina       26-JAN-2018 08:46     Finance
5      Muruges       26-JAN-2018 08:49     Finance
7      Kumar         25-JAN-2018 08:42     HR
9      Divya         25-JAN-2018 08:49     HR

FYI I the query which I used

SELECT *
FROM (SELECT *
            ,DENSE_RANK() OVER ( PARTITION BY EmpDept ORDER BY EmpPunchInTime) RANK
      FROM EMPLOYEE_PUNCH) emp_pun
WHERE RANK <= 2

I have tried to solve by using dense rank(partition by EmpDept, order by EmpPunchInTime) but am not seeing desired results.

Note: If the same employee in a department punches early in most of the days(like Kumar of HR dept here in this dataset) then pick the earliest date among his dates and the employee who punches next to him.

It seems to me the specification failed to mention that the ordering should be done using only the hour and minute parts of the date.

The below returns the expected output.

with data as
(
  select 1 as id, 'Dharma' as empname, to_date('26-JAN-2018 08:45', 'DD-MON-YYYY HH24:MI') as emppunchintime, 'Engineering' as empdept from dual union all
  select 2 ,      'Gumpina',           to_date('26-JAN-2018 08:46', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 3 ,      'Kumar',             to_date('26-JAN-2018 08:47', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 4 ,      'Kamal' ,            to_date('26-JAN-2018 08:48', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual union all
  select 5 ,      'Muruges',           to_date('26-JAN-2018 08:49', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 6 ,      'Gumpina',           to_date('25-JAN-2018 09:01', 'DD-MON-YYYY HH24:MI'),                   'Finance'                from dual union all
  select 7 ,      'Kumar',             to_date('25-JAN-2018 08:42', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 8 ,      'Biju',              to_date('25-JAN-2018 08:40', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual union all
  select 9 ,      'Divya',             to_date('25-JAN-2018 08:49', 'DD-MON-YYYY HH24:MI'),                   'HR'                     from dual union all
  select 10,      'Vali',              to_date('24-JAN-2018 08:52', 'DD-MON-YYYY HH24:MI'),                   'Engineering'            from dual
)
select f.id, f.empname, f.emppunchintime, f.empdept from
(
  select e.*, row_number() over (partition by e.empdept order by to_char(e.emppunchintime, 'HH24:MI')) as rn2 from
  (
    select d.*, row_number() over (partition by d.empname order by to_char(d.emppunchintime, 'HH24:MI')) as rn from data d
  ) e
  where e.rn = 1
) f
where f.rn2 <= 2
order by f.empdept, f.emppunchintime;

Output:

        ID EMPNAME EMPPUNCHINTIME    EMPDEPT
---------- ------- ----------------- -----------
         8 Biju    25-JAN-2018 08:40 Engineering
         1 Dharma  26-JAN-2018 08:45 Engineering
         2 Gumpina 26-JAN-2018 08:46 Finance
         5 Muruges 26-JAN-2018 08:49 Finance
         7 Kumar   25-JAN-2018 08:42 HR
         9 Divya   25-JAN-2018 08:49 HR


using mysql calculate balance of each currency where credit and debit in same column? With the following MySQL table containing debit or credit "actions" with associated amounts and currency, how is it possible to select all CLIENT_IDs with a non-zero "balance" of each currency? I have tried but something isn't working correctly.

CLIENT_ID    ACTION_TYPE    ACTION_AMOUNT    Currency
1            debit          1000                USD
1            credit         100                 USD
1            credit         500                 DR
2            debit          1000                EURO
2            credit         1200                DR
3            debit          1000                EURO
3            credit         1000                USD
4            debit          1000                USD

My MySQL query that doesn't work:

select CLIENT_ID,
       sum(if(ACTION_TYPE='credit',ACTION_AMOUNT,0)) as credit,
       sum(if(ACTION_TYPE='debit',ACTION_AMOUNT,0)) as debit,
       sum(if(currency='USD',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as USD_balance,
       sum(if(currency='EURO',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as EURO_balance,
       sum(if(currency='DR',ACTION_TYPE='credit'- ACTION_TYPE='debit')) as DR_balance
    FROM my_table
    GROUP BY CLIENT_ID,currency

The result I am expecting is something like:

CLIENT_ID    USD_Balance    EURO_Balance    DR_Balance
1              -900             0              500
2                0            -1000            1200
3              1000           -1000             0
4             -1000              0              0

I have no idea what else to try. Any help would be great.

I would try something like this to get the results that you are looking for. What this is doing is determining how to add everything up based on the record being a credit or debit and subtracting the debit values instead of adding them.

SELECT CLIENT_ID,
       SUM(IF(currency='USD',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as USD_balance,
       SUM(IF(currency='EURO',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as EURO_balance,
       SUM(IF(currency='DR',IF(ACTION_TYPE='credit',ACTION_AMOUNT, - ACTION_AMOUNT))) as DR_balance
FROM my_table
GROUP BY CLIENT_ID,currency


Is there a syntax for accessing table names containing slashes? I have a linked server registered in my SQL Server 2005 which accesses our SAP database (test system). It uses an ODBC driver provided by SAP, and is connected using the Microsoft OLEDB For ODBC driver. The remote database is a MaxDB 7.7.

I'm currently exploring the possibilities of directly accessing data within our SAP system, but have run into problems regarding certain tables.

The following works without problems:

SELECT Field FROM LinkedServer..Schema.Table WHERE MANDT = '110'

or, respectively

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT Field FROM Table WHERE MANDT = ''110''')

Now, there's some nasty table names within SAP that can contain slashes. I think it's meant to be a "namespace" type of thing contained within the actual table name. On the ABAP side of things, I can easily do

SELECT * FROM /MYWHATEVER/MYTABLE

provided the table exists. But I can't get the table names containing / working via linked server.

Trying

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM /MYWHATEVER/MYTABLE')

gives me "Error 7321 while preparing query...". Using

SELECT * FROM LinkedServer..Schema.[/MYWHATEVER/MYTABLE]

yields "Error 7306 OLEDB-Provider "MSDASQL" can't open table "Schema./MYWHATEVER/MYTABLE" for linked server LinkedServer, it contains errors or doesn't exist".

Is there any way I can access tables containing / on a MaxDB via LinkedServer?

[The error messages may not be literally the same as on an english system, since I had to translate them from german]

I'd try escaping stuff and see what happens

SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM //MYWHATEVER//MYTABLE')
SELECT * FROM OPENQUERY(LinkedServer, 'SELECT * FROM \/MYWHATEVER\/MYTABLE')
...

I assume that the local ODBC layer is bollixing you. The bit inside OPENQUERY should be a literal to SQL Server so it is external to SQL Server. So it could be be the ODBC layer: can you profile (or log) what happens on the MaxDB to see what gets through?

Another option would be use a view or proc in the MaxDB to give "proper" names


EXPDP error ORA-39374 + ORA-13158 Oracle object does not exist While exporting one of the tables in my db I got the following error :

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39374: Statistics failed to export. Failing error is
ORA-13158: Oracle object 3752503189677886288 does not exist
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

After this on the general print out where my export is running I saw . . Exported TABLE_NAME 51.31 GB 273283803 rows however the same is not printed in the expdp log..

Also the export command failed to return for almost 8 hours after which I forcefully tried to close it with Ctrl-C + Export> kill_job which also did not close the export session in a clean way.

dba_datapump_jobs:

+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+
| OWNER_NAME | JOB_NAME            | OPERATION | JOB_MODE | STATE     | DEGREE | ATTACHED_SESSIONS | DATAPUMP_SESSIONS |
+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+
| APPUSER    | SYS_EXPORT_TABLE_02 | EXPORT    | TABLE    | EXECUTING | 1      | 0                 | 2                 |
+------------+---------------------+-----------+----------+-----------+--------+-------------------+-------------------+

I am unable to figure out why my export job failed and more importantly how to close the active sessions in a clean way..

expdp command

expdp user/pass directory=TEST_DIR dumpfile=xxx.dmp logfile=xxx.log tables=TABLE_NAME

It is recommended to exclude statistics when doing an export. It is better to export the stats in a different way (DBMS_STATS) or re-gather once the data is imported. I know it does not solve the root cause, but Oracle actually recommends that you do not export the statistics.

Try to do the export again with LOGTIME=ALL and METRICS=Y to get a better idea of what it is spending time on. Otherwise, you might have to turn on 10046 tracing to see what query it is stuck in.

Do you have many objects (10.000+) to export or many partitions?


When should I mark a column as the primary key? I have a table like this:

// cookies
+----+---------+-------------------+------------+
| id | user_id |       token       |   expire   |
+----+---------+-------------------+------------+
| 1  | 32423   | dki3j4rf9u3e40... | 1467586386 |
| 2  | 65734   | erhj5473fv34gv... | 1467586521 |
| 3  | 21432   | 8u34ijf34t43gf... | 1467586640 |
+----+---------+-------------------+------------+

A few days that I think about it. I guess I don't need to id column. Currently id column is PK and also I have an unique-index on token column to make it fast for searching.

Now I want to know, can I remove id column and make token column as PK? Is that normal?

To be honest, I've never created a table without id column (It's always been the PK), So that's weird for me to choose token column as the PK.

It depends on many things. Assuming you're using innodb it comes with a big price. So you need to value and balance pros and cons for your use case.

Downsides of big varchar column:

Due to the random order of tokens

your inserts are going to be significantly slower,
will cause massive fragmentation and
much more frequent page splits

Due to this fragmentation the table will actually use you more space than you can save on omitting the the 4 or 8 byte integer key you're using.
If you have any other keys they are going to contain this big primary key at the end further bloating your table: https://www.percona.com/blog/2006/10/03/long-primary-key-for-innodb-tables/

Upsides of natural primary key:

By the nature of InnoDB primary key lookups are very fast so if you query your table by only this columns this might give you some edge (although adaptive hash index helps quite a lot with secondary key lookups)

Most of the times people are trying to use natural keys to eliminate the id column for space consideration which is just the opposite of what is actually happening. If you want that for query optimization and that outweighs the downsides than go for it.


Users complain that system runs slow when mysqldump is in progress The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables.
We are running the backup using mysqldump
mysqldump --skip-opt --quick --single-transaction --create-options --extended-insert --disable-keys --add-drop-table --complete-insert --set-charset -- compress --log-error=Proddb0635.err -u root -pjohndoe Proddb> \devNas\devNas\sqlbackup\LIVE\db\Proddb0635.sql

The backup file Proddb0635.sql is stored on a separate server from the database server.
RAM is 12 GB.
INNODB Buffer Pool size is 6 GB.
Additional mem.pool is 32 MB.
Query Cache size is 2 GB
Net buffer length is 16 M
Max. packet size 1 GB.

mysql version is 5.0.67.

When backup is not running users are happy with the performance.

When backup is running INNODB Buffer pool hit rate is high close to 100%.
There are no pending reads or pending writes. innodb wait free is 0.
CPU usage is not high min 9% to max 15 %
Query cache hit rate is low about 40% with or without mysqlbackup running. Currently Windows Task Manager  is displaying that 10GB of RAM is being used. Should I increase Query Cache with only 2GB of RAM available?
mysqlld-nt is taking 9.2 GB of RAM and mysqldump is taking 5 MB of RAM.
Alos, noted that the size of the dump file is same in presence or absence of --compress option.

SHould I decrease the iNNODB Buffer Pool size?

Thanks

There is a known issue in Windows, that when you push a large file to another server all the memory ends up getting allocated to the System cache instead of the user processes.  You can look  in the Physical Memory (MB) section of task manager to see how much memory is allocated to the system cache.

This can be solved by backing up to a local disk, then having the remote machine pull that file.


Microsoft SQL 2016 database for Malwarebytes Endpoint Protection I don't know if this is the correct forum, but hopefully someone can help.

I am a newly promoted entry level Systems Administrator and the first project I was tasked with was to spin up a new server and install Malwarebytes Endpoint Protection utilizing an external database. I got everything spunned up, however, Malwarebytes won't connect to the database. When it prompts for me to choose "Use Embedded Database," or "Use External Database" I choose the "use external." I then type in the database address, which is the server and instance, then the username and password. This is where I get hung up. I get an error message stating "Login failed for user ..." "Installation was unable to access the database."

Now in Microsoft SQL Server Management Studio, under Security --> Logins I've created a user and I've set its Server Roles to dbcreator and sysadmin. However, I've tried the credential I've just created and other credentials that are on our AD domain admin users, and no matter which credential I've tried, it still won't let me login to install. Am I missing a step?

If the application required a user name and a password, that means it attempts to connect using SQL Authentication and not using Windows AD authentication.

By default, SQL Server allows only for Windows Authentication, so you you need to enable the "mixed authentication" mode for SQL server. It can be enabled when installing, but if you used the defaults, see this article on how to change the server authentication mode.

Also make sure that the user you are creating on the SQL Server is a SQL login and not a windows login. It should be:

CREATE LOGIN <login_name> WITH PASSWORD = 'Password'

and not

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS

HTH


How to INSERT row in another database/schema in MySQL Let's say I have the following database/schema setup with the following tables:

    SCHEMA_1 (default):
        TABLE_1

    SCHEMA_2:
        TABLE_2

How do I insert rows in another schema's table? The following does not seem to work:

    INSERT INTO TABLE_2 (...) VALUES(...)

How is it possible to insert rows in another another schema?

Fully qualify the table name:

insert into SCHEMA_2.TABLE_2 values( ... );


Perform group by with random group expression (randomly partitioning rows) MYSQL Let's say I have a table (with millions of rows)

cat1|cat2|value
A   |   B|   10
...

and I want to group the data by cat1 and also split the grouping into 10 (at least roughly) equal partitions of the data.

The purpose of the random grouping is for the machine learning technique of cross-validation.  (It's similar to splitting subjects into a control group and experimental group.)

What I would imagine to use is the following:

select cat1, r, sum(value)
from t
group by cat1, floor(rand()*10) as r

which doesn't work.

One way which I believe would work would be simply to insert a new column with rand into my table and then group appropriately on that.

Is there any way of doing this grouping otherwise?

Your post piqued my curiosity and I look at the RAND() function - which you can't perform GROUP BYs with. I found this and if your table has a PRIMARY KEY, there is a MySQL pseudo-column (_rowid), much like is found in Oracle, Firebird (and maybe others) then you can issue queries such as this one (see structure and data for comments table (sample) below).

select MOD(_rowid, 3) AS my_field, COUNT(ticket_id)
FROM comments
GROUP BY my_field;

+----------+------------------+
| my_field | count(ticket_id) |
+----------+------------------+
|        0 |                6 |
|        1 |                7 |
|        2 |                7 |
+----------+------------------+

Now, if your PK is some sort of AUTO INCREMENT and "random" with respect to your data, you could perhaps use the _rowid pseudo-column (with MOD) to generate a sufficient degree of randomness for your requirements, and perform your aggregate queries?

---- structure and data in comments table---

CREATE TABLE `comments`
(
  `comment_id` int(11) NOT NULL,
  `ticket_id` int(11) NOT NULL,
  PRIMARY KEY (`comment_id`)
);

mysql> SELECT * FROM comments;
+------------+-----------+
| comment_id | ticket_id |
+------------+-----------+
|          1 |         1 |
|          2 |         1 |
|          3 |         1 |
|          4 |         1 |
|          5 |         2 |
|          6 |         2 |
|          7 |         2 |
|          8 |         2 |
|          9 |         3 |
|         10 |         3 |
|         11 |         3 |
|         12 |         3 |
|         13 |         4 |
|         14 |         4 |
|         15 |         4 |
|         16 |         4 |
|         17 |         5 |
|         18 |         5 |
|         19 |         5 |
|         20 |         5 |
+------------+-----------+


Oracle 11g r2: Cannot create partition on timestamp field I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this.

CREATE TABLE t (id NUMBER , tstamp timestamp )
  PARTITION BY RANGE (tstamp)  (
    PARTITION t_jan_2009 VALUES LESS THAN (to_timestamp('2009-02-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2')),
    PARTITION t_feb_2009 VALUES LESS THAN (to_timestamp('2009-03-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2'))
);

INSERT INTO t SELECT 1, '2009-02-10 12:34:45.56' from dual;

This is not the actual data but an equivalent sample. It gives the same error.
SQL Error: ORA-01843: not a valid month

Why am I getting this error? Do I need to do something more?

change to

insert into t select 1, to_timestamp ('2009-02-10 12:34:45.56','YYYY-MM-DD HH24:MI:SS.FF2') from dual;

should work. Don't rely on default formats.


SQL : get rows from two tables relatively I've two tables named categories and products. Simple structure is given below.

categories

cat_id        cat_name
    1           c1
    2           c2
    3           c3

products

prod_id     cat_id      prod_name
    123         1           p1
    546         2           p2
    562         3           p3
    567         3           p4
    234         1           p5

I want to get product list along with their related category name. ex.

Category : c1, Product : p1
Category : c2, Product : p2
Category : c3, Product : p3
Category : c3, Product : p4
Category : c1, Product : p5

For that i'm using below simple queries,

if($res1 = mysqli_query("SELECT cat_id, cat_name FROM categories")) {
    while($tbl1 = mysqli_fetch_assoc($res1)) {

    if($res2 = mysqli_query("SELECT prod_name  FROM products WHERE cat_id = ".$tbl1['cat_id'])) {
        while($tbl2 = mysqli_fetch_assoc($res2)) {
            echo "Category :". $tbl1['cat_name'] . " Product". $tbl2['prod_name'];
        }
    }

}
}

It can be Improved in a single query but having no idea about it.
Please help.

Also suggest list those products according to Category order. (ex. first c1 categories products, c2 categories products and so on... )

Thank you...

Here is the query to order it the way the question has it

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;

Here is that query formatted

SELECT CONCAT('Category : ',c.cat_name,', Product ; ',p.prod_name) DisplayLine
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;

If you want to it order by category, then

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY cat_name,prod_name;

Give it a Try !!!


Querying sys.server_principals does not return results for specific user I'm trying to figure out why this query:

SELECT * FROM sys.server_principals WHERE sid = SUSER_SID('Domain\User') OR name = 'Domain\User'

Does not return results for one specific user? At the same time if I remove/comment out WHERE clause I can see that SELECT Domain\User... I've tried to remove and re-add this user under SQL Server > Security > Logins - but it does not help. Can someone indicate what can be wrong here? User name contains non-Latin characters, but no special characters.

Tried your query, worked fine for me.. only thing I can think about for you is try adding N to the 'Domain\User' in your case:

SELECT * FROM sys.server_principals WHERE sid = SUSER_SID(N'Domain\User') OR name = N'Domain\User'


Why does SQL Server truncate my nvarchar(max) string in an expression? It looks like a 2nd variable in an expression is determining the maximum length of my 1st variable.

An example is easier to explian:

DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing';
DECLARE @MySrc AS NVARCHAR(6) = '';

SELECT @MyDest = ISNULL(NULLIF(@MySrc, N''), @MyDest);

SELECT @MyDest;

The result is 'Some l' while I would expect 'Some long text, for testing'.

This is a simplified version of a more complicated bug I'm trying to fix. Is this a bug in SQL server or should I be doing this differently?

It is truncating because the replacement_value parameter is being converted to the same data type as the check_expression.

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

ISNULL ( check_expression , replacement_value )

  replacement_value must be of a type that is implicitly convertible to
  the type of check_expresssion.

  Return Types Returns the same type as check_expression. If a literal
  NULL is provided as check_expression, returns the datatype of the
  replacement_value. If a literal NULL is provided as check_expression
  and no replacement_value is provided, returns an int.


Non clustered index for non-evenly distributed data in large SQL table I have the following table containing 2.000.000.000 rows:

CREATE TABLE [VeryLargeTable]
(
   [dataCol1] [NVARCHAR(MAX)]
   , [dataCol2] [NVARCHAR(MAX)]
   , [Manufacturer] [NVARCHAR(10)]
   , [MsaiSalesType] [NVARCHAR(2)]
   , [Return Volume] [NVARCHAR(2)]
)

The data is distributed this way:

I have to determine the first column(Sales Types), which is determined by the following 3(Manufacturer, MsaiSalesType, Return Volume).

The table will be created on the spot, then the data will be inserted via SSIS from a flat file. After that, data will not be inserted, updated, deleted.

I have taken into consideration filtered indexes and partitioned tables.

Would they be efficient if the data is so un-evenly distributed ?
What are my other options ?

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data.

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data.

In that case, separating that 95% of the data is way to go. If you want to stay with SSIS, the obvious solution is to redirect those records to a another flat file (can also be a separate db) during import. Specifying this flat file in SSIS is granular too. That is, you can send malformed data rows to a separate rejects flat file, for example. That way you can have clean SalesType1 file.

To specify which records to put in the flat file, use Conditional Split in SSIS to filter the rows (during import) using the same logic as your Where condition consisting of Manufacturer, MsaiSalesType, and Return Volume.


Is it possible to create an empty or dummy index in postgres? I am working on cleaning up a database so that an application upgrade script can run. The state is that the upgrade script expects an index to exist only so that it can drop that index. The index was dropped in production by hand some time ago. Artificially altering the upgrade script or telling the upgrade process it has already run that step isn't an option for non-technical reasons beyond my control.

Instead of taking the time to recreate the index is it possible to create a "dummy" or empty index with the same name so that the upgrade script can run?

This is for postgresql 9.0 or later.

upgrade script expects an index to exist only so that it can drop that index [...] Instead of taking the time to recreate the index is it possible to create a "dummy" or empty index with the same name so that the upgrade script can run?

Just delete that line from the update script. Or even better, use the IF EXISTS clause so it throws a notice not an error.

Command:     DROP INDEX
Description: remove an index
Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

But yes, the DROP INDEX doesn't even know if the index is on a specified table if you're dropping by index name and not by table(column) location.

Here is an example,

CREATE TABLE foo ( x int );
CREATE INDEX ON foo(x);
CREATE TABLE bar ( LIKE foo );

\d foo;
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 x      | integer |
Indexes:
    "foo_x_idx" btree (x)

--
-- HERE WE DROP FOR THE FIRST TIME
--
DROP INDEX foo_x_idx ;
DROP INDEX

--
-- RECREATE
--
CREATE INDEX foo_x_idx ON bar(x);

--
-- DROP AGAIN
--
DROP INDEX foo_x_idx;
DROP INDEX


Why is MySQL order by performance poor within a single partition of a partitioned table? I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a varchar). One very common query i'll be making is for all data for a single day, ordered by sequence number.

Storing the data in one table per day works really well. Querying all data from a table takes 0.8 seconds (which is acceptable), and adding order by sequence number takes it up to 1.0 seconds (still acceptable), but this does result in a massive number of tables that i'd rather have less of.

Storing in one table per month (and adding an indexed day number field), the time to get a day's data goes up to 1.6 seconds (not great), but adding partitioning by day number brings it right back down to 0.8.

However... when I add the order by sequence number to the partitioned table query, the time goes up to 2.5 seconds! Does this make sense? I would have thought that since my where clause (day number = X) instantly limits to all the data in a single partition, it should then be about the same performance as one of my original day tables (which it is, until I add the order by clause, then everything goes to hell).

It's like it is doing the order by before checking the partitions or something. Anyone got any ideas? (either "yes, that'll happen, and here's why", or "here's what you need to do to speed it up").

Thanks.

Assuming that you range partition your table by the (auto incrementing) sequence and you limit your query by another value (day number), you might see, that a lot of partitions will be touched when you use:

EXPLAIN PARTITIONS SELECT * FROM table_name WHERE day_number=X ORDER BY...

The reason for that is, that there is no linkage between the partitioning attribute and the data filter. Since MySQL uses a different index for each partition, it needs to open all partitions to find all data matching day_number.

If you know the partition name, maybe by querying INFORMATION_SCHEMA.PARTITIONS before the real select, and you use MySQL-5.6+, you can use partition selection like

SELECT * FROM table_name PARTITION(partition_name) WHERE day_number=X ORDER BY...

But this is just chitchat based on a lot of assumtions. To give you a real answer, you need to post the query plan and more information about table structure - like Phil said before.


SQL Server 2012 always-on backup, full vs copy-only I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required.

I have SQL Server 2012 setup with always on for multiple databases. The availability groups are setup with preferred backup on replica.

only copy-only and transaction log backups are possible on replica. Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?

Thank you

Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?

NO -- You can add t-log backups to a restore of a COPY_ONLY backup


Why are queries getting dropped from query store? I see sometimes that queries are dropped from the query store. But I don't know why. The query store disk-space is not full and the query is far from the stale query treshold.

What I experience is that a query that I know has been executed has no history of getting executed in query store.

I will run this query to get the query_id:

SELECT   t.query_sql_text, q.query_id, object_name(q.object_id) AS parent_object, q.query_text_id, q.count_compiles
    FROM sys.query_store_query_text t JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
WHERE  t.query_sql_text LIKE  N'%part_of_query%'

And then I use the query_id in 'Tracked queries'.

But, sometimes, if I later in the day want to look at this query and run the above query again I get a new query_id and there is no trace of the old query_id.
This can even impact 'Total resource consumption'. If the new query_id was created 11:00 and consumed a lot of resources, then it will look like there was a sudden increase in resource usage at 11:00.

The queries I often look at involve a temporary table, which means that there is a batch_sql_handle stored with the query_id. I know that database_id is part of this and that a failover on an Always On AG would cause a similar situation. ref: https://milossql.wordpress.com/2019/10/21/dude-wheres-my-forced-plan-part-1/
I have been trying to find more information about what the batch_sql_handle is based on and how the auto cleanup is working, but have not found anything that helps me.

Size Based Cleanup Mode is set to Auto

After setting up an extended events session looking at query store cleanup I saw my query_id in an event: query_store_size_retention_query_deleted

So my queries are getting dropped to avoid reacing size limit.
flush_interval_settings was set to 1 min, which probably contributed to this.


How do you create a new database in SQL Developer? I  am a software developer that supports and develops an application that connects to both Oracle databases and SQL Server databases.  I want to learn more about database administration.

In SQL Studio Manager you can open a connection to a server and then selected the master database and then run a statement like this:

CREATE Database TestDatabase

TestDatabase is then available to connect to on that database server.

However, with SQL Developer you have to connect to a database already created (rather than connecting to the server).  Therefore how do you create a new database in SQL Developer?

I thought I would be able to find my answer by Googling.  However, this was not the case.  There seem to be hundreds of tutorials about creating tables but I cannot find any about creating a database.

The term database has a different meaning in Oracle. The definition of database by Oracle:

  A set of files, located on disk, that store data. Because a database
  instance and a database are so closely connected, the term Oracle
  database is often used to refer to both instance and database.

Oracle Database Architecture

If you wanted to seperate data per application, you could do that by using seperate tablespaces and schemas.

Creating such a database is a complex process, and it is done with DBCA or SQL*Plus on the database server, not with a remote connection:

Creating and Configuring an Oracle Database

In 12c, the multitenant architechture was introduced, that is similar to the concept in SQL Server.

Overview of Managing a Multitenant Environment

If you have a container database, there you can easily create new pluggable databases from SQL Developer, when using a DBA connection.

Managing Pluggable Databases (PDBs) using Oracle SQL Developer


identify blocking and send alert I need to create an alert that will notify me when any query has been blocked for more than 60 seconds.  For example if someone has a transaction open on a table and forgets to run a commit or a rollback.  Is this possible to get from the system tables?

I liked Martin's suggestion of using event notifications, so I followed his example link, and put this together for our server. You'll need to either put your email address in the call to sp_send_dbmail, or modify the procedure to read email addresses from a config table of some sort. Also, adjust the blocked process threshold to your liking. The result is a concise report message, with info about the blocked and blocking processes.

The error handling is really basic; it just ends the conversation if it receives an error message or end dialog message.

Make sure Database Mail is configured on your system to allow the use of sp_send_dbmail.

USE msdb
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'blocked process threshold', 30
RECONFIGURE
GO

CREATE QUEUE BlockedProcessQueue

CREATE SERVICE BlockedProcessService ON QUEUE BlockedProcessQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

USE master
GO

CREATE FUNCTION [dbo].[wait_resource_name](@obj nvarchar(max))
RETURNS @wait_resource TABLE (
    wait_resource_database_name sysname,
    wait_resource_schema_name sysname,
    wait_resource_object_name sysname
)
AS
BEGIN
    DECLARE @dbid int
    DECLARE @objid int

    IF @obj IS NULL RETURN
    IF @obj NOT LIKE 'OBJECT: %' RETURN

    SET @obj = SUBSTRING(@obj, 9, LEN(@obj) - 9 + CHARINDEX(':', @obj, 9))

    SET @dbid = LEFT(@obj, CHARINDEX(':', @obj, 1) - 1)
    SET @objid = SUBSTRING(@obj, CHARINDEX(':', @obj, 1) + 1, CHARINDEX(':', @obj, CHARINDEX(':', @obj, 1) + 1) - CHARINDEX(':', @obj, 1) - 1)

    INSERT INTO @wait_resource (wait_resource_database_name, wait_resource_schema_name, wait_resource_object_name)
    SELECT db_name(@dbid), object_schema_name(@objid, @dbid), object_name(@objid, @dbid)

    RETURN
END
GO

CREATE PROCEDURE StartBlockedProcessNotification
AS
CREATE EVENT NOTIFICATION BlockedProcessNotification ON SERVER FOR BLOCKED_PROCESS_REPORT TO SERVICE 'BlockedProcessService', 'current database'
GO

EXEC sp_procoption 'StartBlockedProcessNotification', 'startup', 'on'
EXEC StartBlockedProcessNotification

USE msdb
GO

CREATE PROCEDURE BlockedProcessActivationProcedure
AS

--Service Broker
DECLARE @message_body xml
DECLARE @message_body_text nvarchar(max)
DECLARE @dialog uniqueidentifier
DECLARE @message_type nvarchar(256)

WHILE 1 = 1
BEGIN --Process the queue
    BEGIN TRANSACTION;

    RECEIVE TOP (1)
        @message_body = message_body,
        @dialog = conversation_handle,
        @message_type = message_type_name
    FROM BlockedProcessQueue

    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Nothing more to process', 0, 1)
        ROLLBACK TRANSACTION
        RETURN
    END

    IF @message_type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    BEGIN
        DECLARE @mail_body nvarchar(max)

        DECLARE @post_time varchar(32)
        DECLARE @duration int
        DECLARE @blocked_spid int
        DECLARE @waitresource nvarchar(max)
        DECLARE @waitresource_db nvarchar(128)
        DECLARE @waitresource_schema nvarchar(128)
        DECLARE @waitresource_name nvarchar(128)
        DECLARE @blocked_hostname nvarchar(128)
        DECLARE @blocked_db nvarchar(128)
        DECLARE @blocked_login nvarchar(128)
        DECLARE @blocked_lasttranstarted nvarchar(32)
        DECLARE @blocked_inputbuf nvarchar(max)
        DECLARE @blocking_spid int
        DECLARE @blocking_hostname nvarchar(128)
        DECLARE @blocking_db nvarchar(128)
        DECLARE @blocking_login nvarchar(128)
        DECLARE @blocking_lasttranstarted nvarchar(32)
        DECLARE @blocking_inputbuf nvarchar(max)

        SET @post_time = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/PostTime)[1]', 'datetime'), 109)
        SET @duration = CAST(@message_body.value(N'(//EVENT_INSTANCE/Duration)[1]', 'bigint') / 1000000 AS int)
        SET @blocked_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)[1]', 'int')
        SET @waitresource = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(max)')
        SET @blocked_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)[1]', 'nvarchar(128)')
        SET @blocked_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@currentdb)[1]', 'int'))
        SET @blocked_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)[1]', 'nvarchar(128)')
        SET @blocked_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@lasttranstarted)[1]', 'datetime'), 109)
        SET @blocked_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/inputbuf)[1]', 'nvarchar(max)')
        SET @blocking_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)[1]', 'int')
        SET @blocking_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)[1]', 'nvarchar(128)')
        SET @blocking_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@currentdb)[1]', 'int'))
        SET @blocking_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)[1]', 'nvarchar(128)')
        SET @blocking_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@lasttranstarted)[1]', 'datetime'), 109)
        SET @blocking_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(max)')

        SELECT
            @waitresource_name = wait_resource_object_name,
            @waitresource_schema = wait_resource_schema_name,
            @waitresource_db = wait_resource_database_name
        FROM master.dbo.wait_resource_name(@waitresource)

        SET @mail_body = 'Posted: ' + ISNULL(@post_time, '') + CHAR(10) +
            'Duration: ' + ISNULL(CAST(@duration AS varchar) + ' s', '') + CHAR(10) +
            CHAR(10) +
            '==========Blocked Process==========' + CHAR(10) +
            'SPID: ' + ISNULL(CAST(@blocked_spid AS varchar), '') + CHAR(10) +
            'Wait Resource: ' + ISNULL(ISNULL(QUOTENAME(@waitresource_db) + '.' + QUOTENAME(@waitresource_schema) + '.' + QUOTENAME(@waitresource_name), @waitresource), '') + CHAR(10) +
            'Hostname: ' + ISNULL(@blocked_hostname, '') + CHAR(10) +
            'Current Database: ' + ISNULL(@blocked_db, '') + CHAR(10) +
            'Login Name: ' + ISNULL(@blocked_login, '') + CHAR(10) +
            'Last Transaction Started: ' + ISNULL(@blocked_lasttranstarted, '') + CHAR(10) +
            '----------Input Buffer----------' + CHAR(10) +
            ISNULL(@blocked_inputbuf, '') + CHAR(10) +
            CHAR(10) +
            '==========Blocking Process==========' + CHAR(10) +
            'SPID: ' + ISNULL(CAST(@blocking_spid AS varchar), '') + CHAR(10) +
            'Hostname: ' + ISNULL(@blocking_hostname, '') + CHAR(10) +
            'Current Database: ' + ISNULL(@blocking_db, '') + CHAR(10) +
            'Login Name: ' + ISNULL(@blocking_login, '') + CHAR(10) +
            'Last Transaction Started: ' + ISNULL(@blocking_lasttranstarted, '') + CHAR(10) +
            '----------Input Buffer----------' + CHAR(10) +
            ISNULL(@blocking_inputbuf, '') + CHAR(10)

        EXEC sp_send_dbmail @recipients = 'Your address here', @subject = 'Blocked Process Report', @body = @mail_body
    END
    ELSE IF @message_type IN ('http://schemas.microsoft.com/SQL/ServiceBroker/Error', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        END CONVERSATION @dialog
    END

    COMMIT TRANSACTION
END

GO

ALTER QUEUE BlockedProcessQueue WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = [BlockedProcessActivationProcedure],
    MAX_QUEUE_READERS = 1,
    EXECUTE AS OWNER
)


Calculate rolling sum over 7 consecutive day period on PostgreSQL I need to get the rolling sum over a period of 7 days for each row (1 row per day).

For example:

| Date       | Count | 7-Day Rolling Sum |
------------------------------------------
| 2016-02-01 | 1     | 1
| 2016-02-02 | 1     | 2
| 2016-02-03 | 2     | 4
| 2016-02-04 | 2     | 6
| 2016-02-05 | 2     | 8
| 2016-02-06 | 2     | 10
| 2016-02-07 | 2     | 12
| 2016-02-08 | 2     | 13 --> here we start summing from 02-02
| 2016-02-09 | 2     | 14 --> here we start summing from 02-03
| 2016-02-10 | 5     | 17 --> here we start summing from 02-04

I need this in one query that returns the rows with 7-day rolling sum and the date of the last day of the range of the sum. For example, day=2016-02-10, sum 17.

So far I have this but it's not fully working:

DO
$do$
DECLARE
    curr_date date;
    num bigint;
BEGIN
FOR curr_date IN (SELECT date_trunc('day', d)::date FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d)
LOOP
    SELECT curr_date, SUM(count)
    FROM generate_series (curr_date-8, curr_date-1, '1 day'::interval) d
    LEFT JOIN m.ping AS p ON p.date = d
    LEFT JOIN m.ping_type AS pt ON pt.id = p.ping_type_id
    LEFT JOIN m.ping_frequency AS pf ON pf.id = p.ping_frequency_id
    WHERE
        pt.url_slug = 'active' AND
        pf.url_slug = 'weekly';
END LOOP;
END
$do$;

I am using PostgreSQL 9.4.5. There could be multiple rows with the same date. If there is a gap (a day is missing), the 7-consecutive-day range will still be followed.

By far the cleanest solution is to use window function sum with rows between:

with days as (
        SELECT date_trunc('day', d)::date as day
        FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d ),
    counts as (
        select
            days.day,
            sum((random()*5)::integer) num
        FROM days
        -- left join other tables here to get counts, I'm using random
        group by days.day
    )
select
    day,
    num,
    sum(num) over (order by day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
from counts
order by day;

The important part is to generate the timeframe in days CTE and join onto it in order not to miss any days for which there is no data.

Example

For example, if I create some test data with 20 records in last 14 days:

SELECT (current_date - ((random()*14)::integer::text || 'days')::interval)::date as day, (random()*7)::integer as num
into test_data from generate_series(1, 20);;

And also add a value before that:

insert into test_data values ((current_date - '25 days'::interval), 5);

Then use the query above:

with days as (
        SELECT date_trunc('day', d)::date as day
        FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d ),
    counts as (
        select
            days.day,
            sum(t.num) num
        FROM days
        left join test_data t on t.day = days.day
        group by days.day
    )
select
    day,
    num,
    sum(num) over (order by day rows between 6 preceding and current row)
from counts
order by day;

And get the results for whole month:

    day     | num | sum
------------+-----+-----
 2016-01-31 |     |
 2016-02-01 |     |
 2016-02-02 |     |
 2016-02-03 |     |
 2016-02-04 |     |
 2016-02-05 |     |
 2016-02-06 |   5 |   5
 2016-02-07 |     |   5
 2016-02-08 |     |   5
 2016-02-09 |     |   5
 2016-02-10 |     |   5
 2016-02-11 |     |   5
 2016-02-12 |     |   5
 2016-02-13 |     |
 2016-02-14 |     |
 2016-02-15 |     |
 2016-02-16 |     |
 2016-02-17 |     |
 2016-02-18 |   2 |   2
 2016-02-19 |   5 |   7
 2016-02-20 |     |   7
 2016-02-21 |   4 |  11
 2016-02-22 |  15 |  26
 2016-02-23 |   1 |  27
 2016-02-24 |   1 |  28
 2016-02-25 |   2 |  28
 2016-02-26 |   4 |  27
 2016-02-27 |   9 |  36
 2016-02-28 |   5 |  37
 2016-02-29 |  11 |  33
 2016-03-01 |   5 |  37
(31 rows)


Very slow queries selecting @@ session variables. We're running locally managed databases for our Atlassian suite (Jira/Confluence/Fisheye).   I noticed something strange in the slow query logs.  There are a bunch of selects of session variables showing up like

# Schema: fisheyedb  Last_errno: 0  Killed: 0
# Query_time: 2.487396  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 90  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
# No InnoDB statistics available for this query
use fisheyedb;
SET timestamp=1511794875;
SELECT @@session.tx_isolation;

We have the slow query time threshold set to 2 seconds.

They're so prevelant that they're the number 1 entry in our daily pt-query -digest archives

# Query 1: 0.08 QPS, 0.22x concurrency, ID 0x30AAEC10BC3D0C37 at byte 7953939
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.33
# Time range: 2017-11-27 09:01:15 to 2017-11-28 00:11:48
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25    4120
# Exec time     19  11926s      2s     14s      3s      5s   972ms      3s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0   4.02k       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     0 362.12k      90      91   90.00   88.31       0   88.31
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     2 116.68k      29      29      29      29       0      29
# String:
# Databases    fisheyedb (4105/99%), confluence... (15/0%)
# Hosts        x.x.x.x
# Last errno   0
# Users        fisheyeuse... (4105/99%), confluence... (15/0%)
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #
# EXPLAIN /*!50100 PARTITIONS*/
SELECT @@session.tx_isolation\G

What can cause reading a simple session variable like tx_isolation to be anything less than instantaneous?

Sometimes things like this happen because of other things going on at the same time.  Can you spot such in the slowlog?
The probably will probably be solved in version 8.0, when a number of things get turned into real InnoDB tables.
Some 3rd party products issue an awful number of seemingly spurious and redundant information queries.  Let's see the output from SHOW GLOBAL STATUS LIKE 'Com%';
Set long_query_time = 0.1 and see if you catch a lot more of these queries, plus other things.


See SQL Server Reporting Services Reports without logging in? I have installed SQL Server 2015 with SSRS and I have some reports I need to run, but in order to access http://reportsserver/Reports I need to start Internet Explorer in administrator mode.

This is clearly suboptimal: any user would need to know the Administrator password.

How do I set up SSRS to not require auth to see reports, or how can I make users not need to run IE in Administrator mode?

You only have to run in Administrator mode because you haven't added any users to SSRS. You need to grant permissions. For example on the root folder in SSRS add a windows user/group to the Browser role.

For more details see https://technet.microsoft.com/en-us/library/ms156014%28v=sql.110%29.aspx


Distributed transaction coordinator between SQL Server and Oracle I'm not sure about distributed transaction coordinator so please bare with me on this, I'm probably asking this question wrong. I know distributed transaction coordinator is used in SQL Server and can be used to link 2 SQL Server databases, but I am wondering can distributed transaction coordinator be used to link a SQL Server database and an Oracle database?

Technically it is possible. This Oracle Documentation might help you to setup the distributed transaction processing