MSSQL how do I query sql for a latest state record after latest date I have a table with openingdays, location, state (Open, Day Closed, Shift Closed) and the actual date. I would like to know when the day is closed for the last time (state = D) and everything what happens after that date (state Opened, Shift Closed) for the specific location.

location  day          state  actual_date
-----------------------------------------------------
ams,      2015-11-25,  O      2015-11-25 12:20:59.000
ams,      2015-11-25,  D      2015-11-26 02:10:00.000
ams,      2015-11-26,  O      2015-11-26 12:20:59.000
ams,      2015-11-26,  D      2015-11-27 02:10:00.000
sch,      2015-11-25,  O      2015-11-25 14:12:27.000
sch,      2015-11-25,  D      2015-11-26 03:23:13.000
sch,      2015-11-26,  O      2015-11-26 14:12:27.000
sch,      2015-11-26,  S      2015-11-26 18:26:24.000

What I would like to have is

ams,      2015-11-26,  D      2015-11-27 02:10:00.000
ams,      2015-11-26,  O      2015-11-26 12:20:59.000
sch,      2015-11-26,  S      2015-11-26 18:26:24.000
sch,      2015-11-26,  O      2015-11-26 14:12:27.000
sch,      2015-11-25,  D      2015-11-26 03:23:13.000
sch,      2015-11-25,  O      2015-11-25 14:12:27.000

I already created this:

SELECT k.location, k.day, k.state, k.actual_date
FROM myTable k
INNER JOIN (
    SELECT state, max(day) as MaxDate
    FROM myTable k2
    GROUP BY state
) k2 ON k.state = k2.state AND k.day = k2.MaxDate

Which outputs everything on the latest day, but it wont show me the results on the days after the latest Day Closed (state = D).

Solved it myself.. was actually very easy..

SELECT *
FROM myTable
WHERE day >= ( SELECT max(day)
            FROM [dbo].[myTable]
            WHERE state = 'D'
            AND location = 'ams'
)
ORDER BY [location] DESC, [actual_date] DESC

Thanks anyway for looking up to my issue.


SQL Query for Internal Messaging System I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema:

Messages

_MessageID (PK)
_MessageParentID (if a reply this will equal MessageID)
_Body
_Date
_CreatorID (FK userID of the message creator)

MessageRecipients

_MessageRecipientID (PK)
_MessageID (FK to message)
_SendTo (FK UserID)

In short what I'm doing is creating a one to many relationship for messages. One user can send a message to many recipients. But the replies are one to one, in short message blasts are possible but there are no group conversations.

I created a SQL Fiddle to demo: http://www.sqlfiddle.com/#!6/6421e3/7

In the query I have now, I'm close. The User 101 should have 3 rows instead of the 4 showing. MessageID 4 is a reply to MessageID 3 (MessageParentID = MessageID) so I'm wanting the latest message to display and since the date of MessageID 4 is greater it should be displayed in the query. Think how FB Messaging works, you see the latest reply to a thread - not a separate outbox.

E.g. expected output:

3 rows

MessageID 1, 2, 4

Any help would be great.

Why not use this query (sqlfiddle):

SELECT [MessageID]
    , [Body]
    , [Date]
    , [MessageParentID]
FROM Messages
WHERE MessageID NOT IN (
        SELECT MessageParentID FROM Messages
    )

Basicaly, if a MessageID is also a MessageParentID it means there is at least 1 reply. Therefore it only looks for message without replies (ie. without MessageParentID).

With this limited sample, it gives the correct output:

MessageID   Body                            Date                        MessageParentID
1           Its getting hot in hure         December, 27 2015 00:00:00  0
2           Where is all the chicken?       December, 28 2015 00:00:00  0
4           Sometimes its a little hotter   January, 05 2016 00:00:00   3

I don't unsderstand why:

are you using a GROUP BY with all the columns in SELECT. Do you need a DISTINCT?
are you using Max(M.Date) and anyway, it is also included in the group by
you are joining to MessageRecipients since anythin from the recipient list or count are not needed


Updating row of one table using rows of another table based on specific conditions I want to update OFFICE_ID,OFFICE_TYPE of FA_SUBLEDGER_MST table, by using OFFICE_ID,OFFICE_TYPE from EMPLOYEE_MST table based on following conditions:

EMPLOYEE_MST.SL_CODE = FA_SUBLEDGER_MST.SL_CODE
EMPLOYEE_MST.OFFICE_ID <> SL.OFFICE_ID
  OR EMPLOYEE_MST.OFFICE_TYPE <> SL.OFFICE_TYPE
AND EMPLOYEE_MST.OFFICE_TYPE != 'DHB'

I tried this query:

UPDATE FA_SUBLEDGER_MST sl
SET
  (sl.OFFICE_ID, sl.OFFICE_TYPE) =
  (SELECT
       emp.OFFICE_ID, emp.OFFICE_TYPE
   FROM EMPLOYEE_MST emp
   WHERE emp.OFFICE_ID <> sl.OFFICE_ID
      OR emp.OFFICE_TYPE <> sl.OFFICE_TYPE
      AND sl.SL_CODE = emp.SL_CODE
      AND emp.OFFICE_TYPE != 'DHB')
WHERE sl.STATUS = 'A'
  AND EXISTS (SELECT 1
              FROM EMPLOYEE_MST emp
              WHERE emp.OFFICE_ID <> sl.OFFICE_ID
                 OR emp.OFFICE_TYPE <> sl.OFFICE_TYPE
                AND emp.SL_CODE = sl.SL_CODE
                AND emp.OFFICE_TYPE != 'DHB');

But both gave this error:

  SQL Error: ORA-01427: single-row subquery returns more than one row
  01427. 00000 - "single-row subquery returns more than one row"

And this:

UPDATE
  (SELECT em.OFFICE_ID emoffid,
    SL.OFFICE_ID sloffid,
    em.OFFICE_TYPE emofftype,
    SL.OFFICE_TYPE slemofftype,
    SL.STATUS
  FROM EMPLOYEE_MST em
  JOIN FA_SUBLEDGER_MST SL
  ON em.SL_CODE      =SL.SL_CODE
  WHERE em.OFFICE_ID<>SL.OFFICE_ID
  OR em.OFFICE_TYPE <>SL.OFFICE_TYPE
  AND em.OFFICE_TYPE!='DHB'
  AND SL.STATUS      ='A'
  ) t
SET t.sloffid   =t.emoffid
WHERE t.emoffid<>t.sloffid
OR t.emofftype <>t.slemofftype
AND t.emofftype!='DHB'
AND t.STATUS    ='A';

This returns those errors:

  SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
  01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
  *Cause:    An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.
  *Action:   Modify the underlying base tables directly.

And also this:

UPDATE FA_SUBLEDGER_MST
SET
  (
    OFFICE_ID,
    OFFICE_TYPE
  )
  =
  (SELECT OFFICE_ID,
    OFFICE_TYPE
  FROM EMPLOYEE_MST
  WHERE (FA_SUBLEDGER_MST.OFFICE_ID != EMPLOYEE_MST.OFFICE_ID
  OR FA_SUBLEDGER_MST.OFFICE_TYPE!  =EMPLOYEE_MST.OFFICE_TYPE)
  AND FA_SUBLEDGER_MST.SL_CODE      = EMPLOYEE_MST.SL_CODE
  AND EMPLOYEE_MST.OFFICE_TYPE!     ='DHB'
  ) ;

It worked but it updates all 124,451 rows, while the rows that met with the above specified condition are only 4,522 rows.

Both the tables contain more than 100,000 records and OFFICE_ID can be 1,2,3,4,5...18 and OFFICE_TYPE can be 'DE','DI','HO', Hence they repeat frequently.

I need a oracle query to do so. Can't use a procedure.

Would be thankful for your help.

You could try MERGE instead of UPDATE. It is often faster and (in my opinion) more clear than UPDATE when updating a table based on another table

merge into FA_SUBLEDGER_MST s1
using (
    select SL_CODE, OFFICE_ID, OFFICE_TYPE
    from EMPLOYEE_MST
    where OFFICE_TYPE != 'DHB'
) emp
on (s1.SL_CODE = emp.SL_CODE)
when matched then update set
    s1.OFFICE_ID = emp.OFFICE_ID,
    s1.OFFICE_TYPE = emp.OFFICE_TYPE
where sl.STATUS = 'A'
    and (s1.OFFICE_ID <> emp.OFFICE_ID
    or  s1.OFFICE_TYPE <> emp.OFFICE_TYPE);

Make sure that using-subquery doesn't return duplicate rows for each SL_CODE

Also your last example is missing the STATUS = 'A' condition


How to backup MS SQL Server databases off-site? Microsoft SQL Server supports backing up to remote network locations only by means of UNC names the SQL Server (and user account it's running under) has access to.

Is it possible to create backups and stream them back to a SQL client by means of using the same database connection?

I think you would have to pull the database schema and data, then join those together to create a SQL script that can be used to recreate the database being backed up, but is this a viable solution to backing up SQL Server databases?

PS: I know there are commercial solutions that offer off-site back-ups but I'm not interested in commercial solutions at the moment.

I'm not sure I would advocate it as a good solution, neither performance wise nor as a best practice, but there actually is a possibility.

In short, the steps would  be

Make a backup on a swap file on the server
Create a temporary table with a varbinary field
Insert the backup into the table
Fetch column data using ADO.NET
Save the stream in a file

The full source code is available on codeplex Transferring backup files from a remote SQL Server instance to a local machine without network shares, FTP, HTTP

The gist of the code is:

Step 1:

_sql = String.Format("BACKUP DATABASE {0} TO DISK " +
       "= N'{1}\\{0}.bak' WITH FORMAT, COPY_ONLY, INIT, " +
       "NAME = N'{0} - Full Database Backup', SKIP ",
       _dbname, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 2:

_sql = String.Format("IF OBJECT_ID('tempdb..##{0}') IS " +
                     "NOT NULL DROP TABLE ##{0}", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))",
                     temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 3:

_sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM OPENROWSET" +
       "(BULK '{1}\\{2}.bak',SINGLE_BLOB) bck",
       temporaryTableName, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();

Step 4 & 5:

_sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);
SqlDataAdapter da = new SqlDataAdapter(_sql, _conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables[0].Rows[0];
byte[] backupFromServer = new byte[0];
backupFromServer = (byte[])dr["bck"];
int aSize = new int();
aSize = backupFromServer.GetUpperBound(0) + 1;

FileStream fs = new FileStream(String.Format("{0}\\{1}",
                AlocalPath, fileName), FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(backupFromServer, 0, aSize);
fs.Close();

The original code was written by Adrian Pasik under the bsd license and published on codeplex.


Improve CPU consumption of query I'm researching why the following query consumes a lot of CPU. Please chime in and suggest any improvements/recommendations that can be made or why this query seems to consume high CPU. Or if there is anything structurally wrong in the query design.

(
        @fromDate DATETIME
        ,@toDate DATETIME
        )

SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT TOP (25) [T1].[C13] AS C1
    ,[T1].[C14] AS C2
    ,[T1].[C15] AS C3
    ,[T1].[C16] AS C4
    ,[T1].[C17] AS C5
    ,[T1].[C18] AS C6
    ,[T1].[C19] AS C7
    ,[T1].[C20] AS C8
    ,[T1].[C21] AS C9
    ,[T1].[C22] AS C10
    ,[T2].[Name] AS C11
    ,[T2].[ID] AS C12
FROM (
    SELECT TOP (25) [T3].[NetObjectType] AS C13
        ,[T3].[NetObjectID] AS C14
        ,[T3].[NetObjectValue] AS C15
        ,[T3].[EventID] AS C16
        ,[T3].[Acknowledged] AS C17
        ,DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18
        ,[T3].[EventType] AS C19
        ,[T3].[Message] AS C20
        ,ISNULL([T4].[BackColor], 0) AS C21
        ,[T5].[NodeID] AS C22
        ,0 AS C23
    FROM dbo.Events AS T3
    LEFT JOIN dbo.NodesData AS T5 ON [T3].[NetworkNode] = [T5].[NodeID]
        AND 0 = 0
    LEFT JOIN dbo.EventTypes AS T4 ON [T3].[EventType] = [T4].[EventType]
        AND 0 = 0
    WHERE [T3].[Acknowledged] = 'false'
        AND [T3].[EventTime] >= @fromDate
        AND [T3].[EventTime] <= @toDate
    ORDER BY [C18] DESC
    ) AS T1
INNER JOIN dbo.Sites AS T2 ON [T2].[SiteID] = [T1].[C23]
ORDER BY [C6] DESC

This is an external vendor query. I have no authority to make changes, only suggestions.

Have to have TOP (25). The results are displayed on a website and they are only interested in the last 25 events and it updates on a schedule.

Here is the estimated execution plan:

Ordering by this might be slowing it down quite a bit:

DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18

I would change ORDER BY [C18] DESC to order by [T3].[EventTime] desc to see if that changes the cost.

Your join for dbo.Sites references [T1].[C23] which is hard-coded 0 as [C23] in your [T3] query. So for each row you are returning from [T3] you are returning every row from dbo.Sites.SiteId = 0 without any other criteria. Is that intended?

On another note... your column and table alias choices are frightening.


InnoDB slow query (few derived and dependent queries) I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins).

I need to get all 0 values from those tables in the last WHERE clause.

I'm talking about the following query:

SELECT * FROM (
    SELECT
        p.product_id, p.productcode, p.product_naam, s.station_omschrijving,
        (SELECT COALESCE(SUM(ab.bedrag),0) FROM afschrijvingen_bedragen ab
            WHERE ab.product_id = p.product_id AND ab.station_id = pro.station_id) as afschrijvingenbedrag,
        (SELECT COALESCE(SUM(dkb.bedrag),0) FROM directe_kosten_bedragen dkb
            WHERE dkb.product_id = p.product_id AND dkb.station_id = pro.station_id) as directekostenbedrag,
        (SELECT COALESCE(SUM(ikb.bedrag),0) FROM indirecte_kosten_bedragen ikb
            WHERE ikb.product_id = p.product_id AND ikb.station_id = pro.station_id) as indirectekostenbedrag,
        (SELECT COALESCE(SUM(mb.bedrag),0) FROM materiaalverbruik_bedragen mb
            WHERE mb.product_id = p.product_id AND mb.station_id = pro.station_id) as materiaalverbruikbedrag,
        (SELECT COALESCE(SUM(pb.bedrag),0) FROM personeelsinzet_bedragen pb
            WHERE pb.product_id = p.product_id AND pb.station_id = pro.station_id) as personeelsinzetbedrag,
        (SELECT SUM(pr.aantal) FROM productie pr
            WHERE pr.product_id = p.product_id AND aantal != 0) as productieaantal
    FROM producten p
    INNER JOIN (SELECT DISTINCT product_id, station_id FROM productie) pro
            ON pro.product_id = p.product_id
    INNER JOIN (SELECT DISTINCT station_id, station_omschrijving FROM stations) s
            ON s.station_id = pro.station_id
) innerQuery
WHERE innerQuery.afschrijvingenbedrag = 0
   OR innerQuery.directekostenbedrag = 0
   OR innerQuery.indirectekostenbedrag = 0
   OR innerQuery.materiaalverbruikbedrag = 0
   OR innerQuery.personeelsinzetbedrag = 0

An EXPLAIN SELECT gives me these results:

How to speed up the query? re-do the index? query code?

CREATE TEMPORARY TABLE pro ( PRIMARY KEY (product_id, station_id) ) ENGINE=InnoDB
    SELECT DISTINCT product_id, station_id FROM productie;
CREATE TEMPORARY TABLE s ( PRIMARY KEY (station_id)  ) ENGINE=InnoDB
    SELECT DISTINCT station_id, station_omschrijving FROM stations;

SELECT
        ....
    FROM producten p
    JOIN pro  ON pro.product_id = p.product_id
    JOIN s    ON s.station_id = pro.station_id
HAVING p.afschrijvingenbedrag = 0
    OR p.... = 0
    ....;

Notes:

By creating tmp tables with useful indexes, those JOINs will run much faster.
I got rid of inner_query and changed WHERE inner_query. into HAVING p.  This may not improve speed, but it simplifies the code.

Another thought...  Did you really need the DISTINCTs?  Keep in mind that SELECT DISTINCT a,b is like GROUP BY a,b, not like GROUP BY a.

Another thought...

s.station_omschrijving
...and...
INNER JOIN (SELECT DISTINCT station_id, station_omschrijving FROM stations) s
        ON s.station_id = pro.station_id

could perhaps be replaced by

( SELECT DISTINCT station_omschrijving FROM stations
      WHERE station_id = pro.station_id ) AS station_omschrijving
...and no join...

It is, however, unclear whether the JOIN is restricting the overall query and whether the DISTINCT is correct.  I simply don't know what the data looks like in that table.  Maybe DISTINCT station_omschrijving needs to be MAX(station_omschrijving) to avoid multiple rows?


MySQL query to find time difference based on a condition Mysql version 5.7.28

I have a db with data in the below format

Id     ign     sp    time
1       1      25    10:00
2       1      22    10:01
3       1      0     10:02
4       1      0     10:03
5       1      0     10:04
6       1      0     10:05
7       1      0     10:06
8       1      8     10:07
9       1      25    10:08
10      1      22    10:09
11      1      0     10:10
12      1      0     10:11
13      1      0     10:12
14      1      0     10:13
15      1      8     10:14
16      1      10    10:15

I need to design a query that will give me the result based on the condition
Ign =1 and sp =0 and the time difference between time where sp is consecutively 0. In this case, sp=0 between time 10:02 to 10:06 and 10:10 to 10:13.

Desired output:

Sp Start Time  Sp Stop Time
10:02          10:06
10:10          10:13

SELECT MIN(`time`) time_from,
       MAX(`time`) time_till
FROM ( SELECT *,
              @group := @group + CASE WHEN NOT sp AND @prev THEN 1 ELSE 0 END group_num,
              @prev:=sp
       FROM test, (SELECT @prev:=-1, @group:=0) variable
       WHERE ign = 1
       ORDER BY `time` ) temp
WHERE NOT sp
GROUP BY group_num;

fiddle


MySQL user without SUPER privilege This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question.

When creating users in mysql using grant all privileges on mydb.* to 'mydb'@'%' identified by 'password'; , the user is getting super privilege. I used to create all my mysql users in this manner so, setting read-only=1 in my slave machine have no effect as the super user can write with read-only option set. So my question here is, using the above command for creating users is dangerous? If yes, what is the correct/safe method? Or I have to manually revoke the super privilege?

I have a surprise for you.

This command

grant all privileges on mydb.* to 'mydb'@'%' identified by 'password';

does not give away the SUPER privilege. How do I know this ?

SUPER is a global grant privilege. Global grants exist in the table mysql.user

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

Notice it has a column called super_priv.

If you run the command SHOW GRANTS FOR mydb.'%';, it will echo two lines. The first line will say GRANT USAGE .... When you see USAGE, this tells you that all global privileges in the mysql.user table from that user are 'N'. To prove this, run this command:

SELECT * FROM mysql.user WHERE user='mydb' AND host='%'\G

That being said, you are probably wondering, what privileges are granted for grant all privileges on mydb.* to 'mydb'@'%' identified by 'password'; ?

Look carefully at the command. You are granting all privileges to the user for the mydb database. Where are database-level grants stored ? That's right, you guessed it. It's in the table mysql.db, which looks like this:

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

Please notice that the column super_priv does not exist in mysql.db

Now, run this command

SELECT * FROM mysql.db WHERE user='mydb' AND host='%'\G

and see for yourself.

Now, do you have any global users ?

SELECT user,host FROM mysql.user WHERE super_priv='Y';

This will give you a list of users that currently have the SUPER privilege.

If you want to manually revoke that privilege from every user except root, login as root and do this:

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

SUMMARY

Do not worry about any database-level users. They do not possess the SUPER privilege and never will.


MySQL Utilities seems outdated? http://dev.mysql.com/downloads/utilities/ has MySQL Utilities 1.4.4 for Mac OS X 10.7.  Is this still updated and recommended to use along with MySQL Workbench? I'm at the latest MySQL Workbench 6.1 CE on Mac OS X 10.9.2.

Yes, that (1.4.4) is the latest version of the utilities as of 2014-08-20 (although the version 1.5.1 is probably going to be released very soon). Since approximately when MySQL 5.6 was released, the MySQL utilities have had an independent download from MySQL workbench (so they can be easily installed on text-only servers) and they have its own numbering schema.

The MySQL Utilities 1.5 will probably be released at the same time that Workbench 6.2 (or close). You have the release notes here.


Restoring Oracle XE database I had Oracle 10G XE installed on my Windows machine. The machine got corrupted and I will have to install Oracle again. I did not take a backup in the form of SQL queries.

How can I use the old database with the new installation?

Make a backup of the:

existing datafiles (SYSTEM01.DBF, SYSAUX01.DBF, UNDOTBS01.DBF, USERS01.DBF or similar) and tempfile (TEMP01.DBF - actually its not really needed, but keep it simple...)
controlfiles (CONTROL01.CTL, CONTROL02.CTL, or similar)
redo logs (REDO01.LOG, REDO02.LOG, etc..)
and the database parameterfile (ORACLE_HOME/database/initXE.ora or    spfileXE.ora)

Note the path of these files, as you should copy them back to their original path if you want to make it simple for yourself.
You can simply move/rename the whole existing Oracle directory if you want.

Install the new operating system, install Oracle 10g XE again at the same directory (so delete the previous installation after making backup of the above), let it create a new database and listener. When its done, stop the database and the belonging service at Windows Services by stopping the OracleServiceXE service. Now copy back the above files at their original place, and start the database by starting the OracleServiceXE service. The database should start and open after performing a crash recovery if your computer was shut down uncleanly earlier.

Note that, this is and oversimplified example of a way more complex process, and it assumes that you put everything to the place where they originally were.


"Login failed for user..." with cross-instance access on same server in SSIS package I have 2 SQL Server instances running on the same server.
Lets call the instances Instance_A and Instance_B and the server Svr0045.
Both server use the same service accounts (a service account is bound to the server, not the instance, so they all share the same service accounts). For simplicity sake, this service account is named Svr0045_SQLSvc.

I have an SSIS package that is running on Instance_B under the service account which tries to access Instance_A using an OLEDB Connection Manager set to Windows Authentication.
I say "tries" because i get the "Login failed for user 'Svr0045_SQLSvc'" error when i run the package.
Since the service account is the same for both instances i assumed that it should work.
The service account has sufficiant rights (since it's a member of the sysadmin role), is granted connection to the instance and is enabled.

Questions:
Is this problem known / is there a reason to this?
How can i get it to work (if there even is a way)?

Check your Sever Authentication mode:

Should be "SQL Server and Windows Authentication mode"


Postgresql - how to do backup/restore on a database level? I'm a developer learning Postgresql and I'd like to know more about backups.
As long-time Sql Server user, I understand backup and restore usually is done on a database level.
So far every tool I see for Postresql (except pg_dump) - barman, wal-e, wal-g, pgbackrest, etc. - seems to work cluster-wide. How does that work?
If I have 20 databases inside my cluster and I deployed an application with a bug, how do I restore just that one broken DB? Is that possible?

Except pg_dump which a logical backup tool, all other tools are physical backup tools and copy all database files from the cluster and transaction logs (WAL). AFAIK currently there is no way to make a physical backup of a single database.

If you need to restore one single database from a physical backup you need to restore the whole cluster-wide backup to a temporary instance and use pg_dump/pg_restore to restore the single database from the temporary cluster into the original cluster.


Delete old records based on its identifier For example, I have a table similar to this:

deviceid | servertime
1          2019-05-13 12:00:00
1          2019-05-14 12:00:00
2          2019-05-13 13:00:00
2          2019-05-12 13:00:00
2          2019-05-11 13:00:00
2          2019-05-10 13:00:00
3          2019-05-13 10:00:00
4          2019-04-16 15:00:00
...

I need a script that would run every day and delete all records belonging to deviceid "2" that is older than a year (from current date). In addition, it needs to delete records with other deviceids that are older than a month.

I would prefer to avoid cron jobs, because I'm concerned about security (I don't think storing MySQL password in a cron file is safe)

I'm a complete noob at SQL, I myself wouldn't know where to begin. I have phpMyAdmin if it's of relevance.

To delete records which have deviceid = 2 and are older than a year from the current date use

DELETE
FROM table
WHERE deviceid = 2
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR; -- or, maybe, <=.

To delete records which are older than a year from the current date except those which have deviceid = 2 use

DELETE
FROM table
WHERE deviceid != 2 -- or deviceid <> 2
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

To perform this action daily use MySQL's Event Scheduler. Create a schedule which is executed every day

CREATE EVENT delete_old_records
ON SCHEDULE EVERY 1 DAY          -- execute daily
            STARTS CURRENT_DATE  -- at 00:00
DO
    DELETE
    FROM table
    WHERE deviceid != 2
      AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

And do not forget to enable event scheduler!!! Once (until disable or server restart) - by the query

SET GLOBAL event_scheduler = ON;

Permanently - by adding  --event-scheduler=ENABLED to the service command-line or event-scheduler=ENABLED to the server configuration file (preferred).


Help with Replication from an Always On Availability Group I have a two node SQL Server 2016 Multi subnet Availability group. I am trying to setup transnational replication from the AG to a standalone instance for reporting, which is also acting as a remote distributor. We only need a small subset of the tables so adding the standalone instance as a third replica isn't an option.
I've followed the Microsoft Guide for configuring this. I've run the EXEC sys.sp_redirect_publisher and when I run EXEC sys.sp_validate_replica_hosts_as_publishers it returns the AG listener. But when we failover the AG the replication stops working with the following error reporting in replication monitor

Error messages: Unable to query the redirected server
'AGL' for original publisher 'PUB1' and publisher
database 'AlwaysOnTest' to determine the name of the remote server;
Error 7303, Error message 'Error 7303, Level 16, State 1, Message:
Cannot initialize the data source object of OLE DB provider
"SQLNCLI11" for linked server
"[B66E8E27-E422-41DB-BBBC-254D450468D7]".'. (Source: MSSQL_REPL, Error
number: MSSQL_REPL21879) Get help: http://help/MSSQL_REPL21879 Errors
were logged when validating the redirected publisher. (Source:
MSSQL_REPL, Error number: MSSQL_REPL22037) Get help:
http://help/MSSQL_REPL22037

Couple of things I've checked and tried

Checked linked servers for the subscriber exist on all secondary replica hosts.
Switched the security mode to SQL auth using sp_changedistpublisher based on this.

When the AG is failed over if I re-run on the distributor EXEC sys.sp_validate_replica_hosts_as_publishers it returns

Unable to query sys.availability_replicas at the availability group
primary associated with virtual network name 'AGL' for the server
names of the member replicas: error = 7303, error message = Error
7303, Level 16, State 1, Message: Cannot initialize the data source
object of OLE DB provider "SQLNCLI11" for linked server
"[7A7DA613-BFDA-4C51-B62C-9C7CD94E769E]"..',

I am stuck what to try next....help please!!!
UPDATE 16/06/2020
When the AG is failed over I tried connecting to the AG listener from the remote distributor. SSMS connected fine, but when I tried connecting with SQLCMD it returned an error. Also I can connect to the failover instance directly from the distributor, bypassing the listener.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes
Provider: Could not open a connection to SQL Server  2. . Sqlcmd:
Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout
expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A
network-related or instance-specific error has occurred while
establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and i f SQL Server is
configured to allow remote connections. For more information see SQL
Server Books Online..

The root cause of this was connection timeouts because the availability group was in a Multi-subnet Availability Group.
This was resolved by following this document from Microsoft.


Is there a way I can shorten the below query into a single query? I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice.

    DECLARE @highRegion TABLE(regionId INT, countR INT)
    DECLARE @lowRegion TABLE(regionId INT, countR INT)
    DECLARE @midRegion TABLE(regionId INT, countR INT)

    INSERT INTO @highRegion
    SELECT c.fRegionID,
           COUNT(1) AS VALUE
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name IN ('High income: nonOECD', 'High income: OECD')
    GROUP BY
           c.fRegionID

    INSERT INTO @lowRegion
    SELECT c.fRegionID AS VALUE,
           COUNT(1)
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name = 'Upper middle income'
    GROUP BY
           c.fRegionID

    INSERT INTO @midRegion
    SELECT c.fRegionID,
           COUNT(1) AS VALUE
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name IN ('Lower middle income', 'Low income')
    GROUP BY
           c.fRegionID

    SELECT r.Name,
           r.NoOfCountries,
           h.countR AS HIGH,
           l.countR AS LOW,
           m.countR AS mid
    FROM   census.Country c
           INNER JOIN @highRegion h
                ON  c.fRegionID = h.regionId
           INNER JOIN @lowRegion l
                ON c.fRegionID = l.regionId
           INNER JOIN @midRegion m
                ON c.fRegionID = m.regionId
           INNER JOIN census.Region r
                ON  c.fRegionID = r.RegionID

Yes you should be able to do it, with something like this:

SELECT r.Name,
       r.NoOfCountries,
       sum(case when ig.Name IN ('High income: nonOECD', 'High income: OECD') then 1 else 0 end) AS HIGH,
       sum(case when ig.Name IN ('Lower middle income', 'Low income') then 1 else 0 end) AS LOW,
       sum(case when ig.Name = 'Upper middle income' then 1 else 0 end) AS mid
FROM   census.Country c
       INNER JOIN census.Region r
            ON  c.fRegionID = r.RegionID
       INNER JOIN census.IncomeGroup ig
            ON  c.fIncomeGroupID = ig.IncomeGroupID
group by
    r.Name,
    r.NoOfCountries


Insert Into with Select works in once instance, but not the other. Subquery error or incorrect data I am trying to write a script that will combine specific survey results based on the demographs used in the survey. I have been able to write a script that adds demographs together to create a new demograph e.g. M18+ and F18+ = A18+ which works perfectly however when trying to create a script that will create a demograph range e.g. A18-24, i am encountering some issues i cannot resolve.

My first script that adds two demographs together is as follows:

create table #SurveyTemp
(
    Com nchar(6),
    Survey nvarchar(50),
    Demo nchar(50),
    Wk int,
    Time int,
    Aud decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, Time, Aud)
select   Com, Survey, 'A18+', Wk, Time, sum(Aud)
from     table_survey
where    survey = 'LO2017'
and      demograph like 'F18+'
or       surveyid = 'LO2017'
and      demograph like 'M18+'
group by Com, survey, Wk, Time:

insert into table_survey
select temp.Com, temp.Survey, temp.Demo, temp.Wk, temp.Time, temp.Aud
from   #SurveyTemp temp

drop table #SurveyTemp

This script works fine. It adds two demographs together and inserts new rows of data, creating a sum of the audience for each Com, Survey, Wk, and then Time. See the sample data set below where the addition of M18+ and F18+ has created a new record called A18+

Let me start with a sample data set:
(Disclaimer: the live database has MANY rows for each demograph)

com | surveyid | demo | wk | time | audience
-------------------------
 1 | LO2017    | A18+ | 1  | 300  |   4.7
 1 | LO2017    | F18+ | 1  | 300  |   1.9
 1 | LO2017    | M18+ | 1  | 300  |   2.8
 1 | LO2017    | A25+ | 1  | 300  |   2.3
 1 | LO2017    | A18+ | 2  | 100  |   3.7
 1 | LO2017    | F18+ | 2  | 100  |   1.9
 1 | LO2017    | M18+ | 2  | 100  |   2.8
 1 | LO2017    | A25+ | 2  | 100  |   4.3

What I need to write now is a script that creates a range. Notice how in the table above there is a A18+ and a A25+. Please bear in mind that in my live table there will be MULTIPLE rows for all demographs.

In this example, I will be using the above table. So, I need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

In the script below, I am trying to create a range called 'A1824' but I am either getting a "subquery returned more than 1 row" error OR it is inserting the wrong data into the temp table where the sum in AudOne and AudTwo is the SAME for every single Wk and Time.

create table #SurveyTemp
(
    Com nchar(6),
    Survey nvarchar(50),
    Demo nchar(50),
    Wk int,
    Time int,
    Aud decimal(18,8),
    AudOne decimal(18,8),
    AudTwo decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0,
          (select sum(aud) from table_survey where demo = 'A18+'),
          (select sum(aud) from table_survey where demo = 'A25+')
 from     table_survey
 where    surveyid = 'LO2017'
 group by Com, survey, Wk, Time,
)

update #SurveyTemp
set Aud = AudOne - AudTwo;

insert into table_survey
select temp.Com, temp.survey, temp.demo, temp.Wk, temp.Time, temp.aud
from   #SurveyTemp temp

drop table #SurveyTemp

It is this second script that i need to get working and have spent hours trying to resolve without much luck.
Please let me know if i can provide any more information to help you help me!

Edit:
My expected results would be something like the following:

com | surveyid | demo  | wk | time | audience
-------------------------
 1  | LO2017   | A1824 | 1  | 300  |   2.4

But there would be multiple rows where there are multiple 'wk' and 'time' entries. E.g.:

com | surveyid | demo  | wk | time | audience
-------------------------
 1  | LO2017   | A1824 | 1  | 100  |   2.4
 1  | LO2017   | A1824 | 1  | 200  |   3.7
 1  | LO2017   | A1824 | 2  | 100  |   2.1
 1  | LO2017   | A1824 | 2  | 200  |   6.2

I'll start after you has generated #SurveyTemp table.

I'm not sure to understand your expected result, take a look at your table ordered by demo:

SELECT   *
FROM     @table_survey
WHERE    demo IN ('A18+','A25+')
ORDER BY com, survey_id, demo, wk;

com | survey_id | demo | wk | time | audience |
--: | :-------- | :--- | -: | ---: | :------- |
  1 | LO2017    | A18+ |  1 |  300 | 4.7      |
  1 | LO2017    | A18+ |  2 |  100 | 3.7      |
  1 | LO2017    | A25+ |  1 |  300 | 2.3      |
  1 | LO2017    | A25+ |  2 |  100 | 4.3      |

There are 2 records for each demo. If you want to add new record as a result of some operation between these two records, you should decide if you want to group by wk and time (that I think it's the correct way), or not.

In your insert command:

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0,
          (select sum(aud) from table_survey where demo = 'A18+'),
          (select sum(aud) from table_survey where demo = 'A25+')
 from     table_survey
 where    surveyid = 'LO2017'
 group by Com, survey, Wk, Time,
)

You're grouping by Com, Survey, Wk, Time, but you're calculating overall audience of all demo records, (without considering wk and time):

(select sum(aud) from table_survey where demo = 'A18+')
(select sum(aud) from table_survey where demo = 'A25+')

IMHO, at least wk should be added to the new records, and you should decide what to do with time.

My proposed solution: GROUP BY WK AND TIME

SELECT   Com, survey_id, 'A1824', wk, time,
         (SELECT sum(audience) FROM @table_survey
          WHERE demo = 'A18+' AND wk = t1.wk AND time = t1.time)
          -
         (SELECT sum(audience) FROM @table_survey
          WHERE demo = 'A25+' AND wk = t1.wk AND time = t1.time) AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id, Wk, time;

NOTE: I've added wk and time to calculate sum(audience) for every demo.

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
  1 | LO2017    | A1824            |  1 |  300 | 2.4
  1 | LO2017    | A1824            |  2 |  100 | -0.6

As you can see, first record match your expected result.

Without group by wk and time

SELECT   Com, survey_id, 'A1824', 0 AS wk,
         avg(time) as time,
         (SELECT sum(audience) FROM @table_survey WHERE demo = 'A18+')
          -
         (SELECT sum(audience) FROM @table_survey WHERE demo = 'A25+') AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id;

This is the result:

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
  1 | LO2017    | A1824            |  0 |  200 | 1.8

NOTE: You don't need to INSERT & UPDATE, it can be done on a single INSERT operation.

Check it: dbfiddle here


Organizing Stored Procs and Views I have a database with 112 stored procs, 7 views and 88 tables.
The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be doubling up on work. I already know I have a couple of reports I generate that use essentially the same information but are pulling from two different tables. I am trying to bring everything down to as few tables and stored procs as possible.

The only thing I can think of is running a query that queries the stored procs to see what tables they all reference, this should get me a list of all the tables that are actually even being used any more.

Also I would want to know which procs are actually being run so a query that shows last run time for each proc.

We have all been there. It sounds like you don't have a specific question, but just need some help getting started. You need to learn about the sys tables available in SQL Server and the DMV's. They are insanely useful.

Ones that will be helpful in your case:

sys.procedures - gets you a list of your stored procedures
sys.object - gets a list of all objects
sys.dm_exec_procedure_stats - gives stats of your stored procedures, how many times they executed, when the last execute was, etc.
sys.dm_sql_referenced_entities - returns entities that are referenced by the object you provide (pass a stored procedure name and it will return all the tables and views it hits)
sys.dm_sql_referencing_entities - returns entities that refer to the object you pass (pass a table name and it will return all the stored procedures that hit that table)


Get row values only if a particular column's value does not exist I have two tables in my database students:

GRADE_REPORT:

+----------------+-------------+------+
|Student_No .    | SectionId   | Grade|
+----------------+-------------+------+
| 11             | 85          | B    |
| 17             | 112         | B    |
| 17             | 119         | C    |
| 17             | 135         | A    |
| 8              | 85)         | A    |
| 8              | 92          | A    |
+----------------+-------------+------+

STUDENT:

+----------------+-------------+------+--------+----------------+
| Studentnumber  | name        | Classification| DepartmentName |
+----------------+-------------+---------------+----------------+
| 17             | Bill Smith  | 1             |      CS        |
| 8              | Bob Brown   | 2             |      CS        |
| 11             | Nick Fiener | 4             |      MATH      |
+----------------+-------------+------+-----+---------+---------+

I am trying to find retrieve the student number, name and major departments of all students who do not have any grade A in any of their courses. So I am expecting the following when I run the query:

+----------------+-------------+----------------+
| Studentnumber  | name        | DepartmentName |
+----------------+-------------+----------------+
| 11             | Nick Fiener | MATH           |

This is my statement so far:

SELECT students.STUDENT.StudentNumber, students.STUDENT.name,
  students.STUDENT.DepartmentName
FROM students.GRADE_REPORT
INNER JOIN students.STUDENT
ON students.GRADE_REPORT.StudentNo = students.STUDENT.StudentNumber
WHERE
( (students.GRADE_REPORT.Grade = "B"
   OR
   students.GRADE_REPORT.Grade = "C" )
   AND  students.GRADE_REPORT.Grade != "A")
GROUP BY students.GRADE_REPORT.StudentNo;

SELECT Studentnumber, name, DepartmentName
    FROM Student AS s
    WHERE NOT EXISTS (
            SELECT 1
                FROM Grade_Report
                WHERE Studentnumber = s.Studentnumber
                  AND Grade = 'A'
                     )


Return multiples tables with procedure I apologize if the title for the question doesn't match what I am asking what to do.  I am not sure how to describe what I am trying to accomplish.

I have the following tables in my database:

Table customers
    customer_id (PK, int, NN)
    customer_name (varchar(100), NN)

Table jobs
    job_id (PK, int, NN)
    job_number (varchar(10), NN)
    job_description (varchar(MAX), NN)
    customer_id (FK, int, NN)

Table job_statuses
    status_id (PK, int, NN)
    status_description (varchar(50), NN)

Table job_status_logs
    log_id (PK, int, NN)
    job_id (FK, int, NN)
    status_id (FK, int, NN)
    log_date (date, NN)

Now I am trying to program a procedure where I pass it a Job Number and it does the following:

1) Checks if the Job Number exists within the database, if not, return an error message.

2) If the Job Number exists return the following columns from the following tables:

jobs: job_id, job_number, job_description
customers: customer_name
job_status_logs: log_date (WHERE status_description is 'Open') & log_date (WHERE status_description is 'Closed')

I am mainly having an issue with the very last part, as I am unsure how to form the query looking for the last two points of data (the ones from the job_status_logs table).

EDIT: This is currently what I have for the procedure

CREATE PROCEDURE dbo.GetJobData(
    @JobNum   VARCHAR(10),
    @response NVARCHAR(500) OUTPUT
) AS
BEGIN
    DECLARE @JobID INT
    SELECT @JobID = job_id
      FROM jobs
     WHERE job_number = @JobNum
    IF @JobID IS NULL
    BEGIN
        SET @response = 'Cannot find data for Job "' + @JobNum + '".  Job does not exist.'
        RETURN
    END
    SELECT j.job_id AS [ID], j.job_number AS [Job], j.job_description AS [Description], c.customer_name AS [Customer]
      FROM jobs AS j INNER JOIN
           customer AS c ON c.customer_id = j.customer_id
     WHERE j.job_id = @JobID
END
GO

As stated above, I do not know how to set up the query to grab the data I am looking for from the job_status_logs table.

Try this - there are two examples.  The first result (jobid=100) has both and open and a closed logdate.  The second result (jobid=200) only has an open logdate.  Take note of the LEFT JOIN against the 'closed' statuses.

--demo setup
DECLARE @customers TABLE (
    customer_id INT
    ,customer_name VARCHAR(100)
    )
insert into @customers(customer_id,customer_name) values(1,'bob')

DECLARE @jobs TABLE (
    job_id INT
    ,job_number VARCHAR(10)
    ,job_description VARCHAR(MAX)
    ,customer_id INT
    )
insert into @jobs(job_id,job_number,job_description,customer_id) values
(1,'100','jobdesc',1),(2,'200','jobdesc2',1)

DECLARE @job_statuses TABLE (
    status_id INT
    ,status_description VARCHAR(50)
    )
insert into @job_statuses(status_id,status_description) values(1,'open'),(2,'closed')

DECLARE @job_status_logs TABLE (
    log_id INT
    ,job_id INT
    ,status_id INT
    ,log_date DATE
    )
insert into @job_status_logs(log_id, job_id,status_id,log_date) values
(1,1,1,'2018-10-01'),(2,1,2,'2018-11-01'),(3,2,1,'2018-12-01')

--the solution
--parameter passed into SP
DECLARE @JobNumber INT

SET @JobNumber = 100    --has both open and closed status

SELECT j.job_id
    ,j.job_number
    ,j.job_description
    ,c.customer_name
    ,jslo.log_date AS LogOpen
    ,jslc.log_date AS LogClosed
FROM @jobs j
JOIN @customers c
    ON c.customer_id = j.customer_id
JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'open'
    ) jslo
    ON jslo.job_id = j.job_id
LEFT JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'closed'
    ) jslc
    ON jslc.job_id = j.job_id
WHERE j.job_number = @JobNumber

---------------------------

SET @JobNumber = 200    --has only open status

SELECT j.job_id
    ,j.job_number
    ,j.job_description
    ,c.customer_name
    ,jslo.log_date AS LogOpen
    ,jslc.log_date AS LogClosed
FROM @jobs j
JOIN @customers c
    ON c.customer_id = j.customer_id
JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'open'
    ) jslo
    ON jslo.job_id = j.job_id
LEFT JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'closed'
    ) jslc
    ON jslc.job_id = j.job_id
WHERE j.job_number = @JobNumber

Result1

| job_id | job_number | job_description | customer_name | LogOpen    | LogClosed  |
|--------|------------|-----------------|---------------|------------|------------|
| 1      | 100        | jobdesc         | bob           | 2018-10-01 | 2018-11-01 |

Result2

| job_id | job_number | job_description | customer_name | LogOpen    | LogClosed |
|--------|------------|-----------------|---------------|------------|-----------|
| 2      | 200        | jobdesc2        | bob           | 2018-12-01 | NULL      |


Create a Voting database Scheme I have the following tables:

create table dbo.Places (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) not null
)

create table dbo.Posts (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) not null
)

create table dbo.Users (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) not null
)

I would like to register Votes made by Users in Places and Posts.

Each Vote has one Value and the CreatedDate so I can track the votes by time.

Which scheme would you advice for this case? A two table scheme:

create table dbo.PlacesVotes (
  PlaceId int not null,
  UserId int not null,
  CreatedDate datetime not null,
  Value int not null
)

create table dbo.PostsVotes (
  PostId int not null,
  UserId int not null,
  CreatedDate datetime not null,
  Value int not null
)

Or three tables:

create table dbo.PlacesVotes (
  PlaceId int not null,
  UserId int not null,
  VoteId int not null
)

create table dbo.PostsVotes (
  PostId int not null,
  UserId int not null,
  VoteId int not null
)

create table dbo.Votes (
  Id int not null,
  CreatedDate datetime not null,
  Value int not null
)

Where PostsVotes and PlacesVotes work only as a junction table.

In this moment I am voting only Places and Posts but I will need to add votes to other tables in the future.

A user can vote for multiple places and multiple posts. However can only vote once for each place or each post.

Assuming a person can only submit one and only one vote, you just need two tables, PlaceVotes and PostVotes, as follows

create table dbo.PlaceVotes (
 Id int not null,
 PlaceId int not null,
 UserId int not null,
 CreatedDate datetime not null,
 Value int not null,
 constraint unique index ux_userid (UserId, place),
 constraint primary key pk_placevotes(id)
)

create table dbo.PostVotes (
 Id int not null,
 PostId int not null,
 UserId int not null,
 CreatedDate datetime not null,
 Value int not null,
 constraint unique index ux_userid (UserId, postid),
 constraint primary key pk_postvotes(id)
)

This way, the voting event is represented as one entity.


'go to symbol' in Allround Automation PL/SQL Developer? I really like using my PC just by keyboard and any time I have to use a mouse is a real pain for me. I really like 'go to symbol' feature in VS Code or Sublime Text, so I can navigate through any source code with ease. Is there anything that would bring this feature also into PL/SQL Developer, so I would be able to easily navigate through my PL/SQL packages?

The plugin rrProject adds some keyboard features. The Code Contents List Tool allows you to find the function or procedure of the code contents panel and go to it. The IDE Insight tool is a global search for windows, connections, menu items and browser objects and performing the necessary action.

It’s not exactly that 'go to symbol', but it allows you to use the mouse less.


How to make value in a row equal to now() in postgres I'm new to PostgreSQL, and new to DB administration - please excuse a naive understanding of terms. Searching for answers to this question has proven difficult - if anyone can suggest better wording please advise.

I have a table containing historical and ongoing experiments that I would like to query for assessing past and present performance. I've been so far keeping this table in Excel and now as it's growing past Excel's usefulness I want to add it into my database.

Essentially, the columns of the table are Experiment_Id (string), StartDateTIme (datetime), and EndDateTime (datetime). There are some experiments that are still ongoing, so in my Excel worksheet there are rows with EndDateTime values of =Today(). The actual timestamp during the day is not important - these are tests which run for weeks and I can live with a timestamp of midnight.

I have read that PostgreSQL has a now() function, but I've only read of it being used in queries, not as values in tables. Is what I'm asking for possible in PostgreSQL?

I realize that a workaround to this problem could be to have a separate table containing a single column of Experiment_id and either a binary key of 'yes/no' is currently running. This would then be a massive list of zeros/falsies and with a few ones/truthies littered at the bottom.

The downside of this workaround is that then two tables would need to be updated when an experiment is completed instead of just one, and this could cause more errors. These tables are being updated manually.

You can use now(), CURRENT_DATE and their friends wherever an expresison is accepted.  That means, you can define your table like

CREATE TABLE experiment (
    e_id text PRIMARY KEY,
    e_started date NOT NULL DEFAULT CURRENT_DATE,
    e_ended date
);

Here, when you insert an experiment, it is enough to insert its ID:

INSERT INTO experiment (e_id) VALUES ('this_experiment_about_AAAAA');

Still, the start date will be the day when this INSERT was issued.  The end date is left to NULL - this is the usual way of having undefined values.  You will find the running experiments like

SELECT e_id, e_started FROM experiments WHERE e_ended IS NULL;

When one finishes, you update the given row and set the end date as necessary (to CURRENT_DATE, for example).

Notes:

I used different column names than you.  I'd advise to use snake_case instead of CamelCase - the latter often leads to confusion.
another naming consideration is that the table defines the data type, no need to reflect this in the column name.
the last naming thing: I prefixed the column names with the table name acronym.  You don't have to do this, it's a matter of taste more than anything else.


Search SQL Server table for a hexadecimal range I have a table of hexadecimal values stored in a varchar(32) column. The values can be anywhere between 000FFF100000 and 000FFFA90FFF

I would like to find all rows between 000FFF300000 and 000FFF7FFF05.

Any suggestions?

Assuming that you can't do the sensible thing and change the datatype of the column to be VARBINARY(10), then just convert the value in the VARCHAR column into VARBINARY(10) and then test to see if it is in that range. For example:

SELECT tmp.*
FROM   (VALUES ('000FFF5060B0'), ('000FFFD00DEE')) tmp(col)
WHERE  CONVERT(VARBINARY(10), tmp.[col], 2) BETWEEN 0x000FFF300000 AND 0x000FFF7FFF05;

returns only the following row:

000FFF5060B0

The style number of "2" in the CONVERT function allows a string of hex bytes without the preceding "0x" to be converted into binary without assuming that the hex bytes are encoded characters.

IF you will be doing such queries frequently, then it would be best to do the following:

Create a non-persisted Computed Column for that expression (i.e. ALTER TABLE {schema_name}.{table_name} ADD {column_name} AS (CONVERT(VARBINARY(10), [{varchar_column_name}], 2)); )
Create a non-clustered Index on that non-persisted computed column


Replica identity value on PostgreSQL read-only replica In PostgreSQL will the replica identity of a table in a read-only replica database be the same as the replica identity in the primary? I.e., if I run the following query on primary and replica databases will I always get the same result (assuming replication is caught up)?
SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class c
WHERE oid = 'mytable'::regclass;

The query will return the same on both servers, because streaming replication keeps them physically identical.
"Replica identity" is a concept from logical replication, which is something quite different.


Data discretization based on rank position Lets say I have a table with the schema:

id   value
1    0.3
2    0.6
5    0.1
4    0.7

Provided by

CREATE TABLE foo AS
SELECT * FROM (
  VALUES (1,0.3::float),(2,0.6),(5,0.1),(4,0.7)
) AS x(id, value)

I want to discretize the value column.

The idea is to rank the values and associate 1 to the first half and 2 to the second.

id   value  normalized
1    0.3    1
2    0.6    2
5    0.1    1
4    0.7    2

I cannot figure out how to do that in SQL, any help?

PS: I am using Postgres, so any solution Postgres dependant would be fine as well

You can use the NTILE() function:

select id, value,
       ntile(2) over (order by value) as normalized
from table_name ;

The 2 means that the values should be split into two buckets, after they are ordered according to the over clause: (ORDER BY value).


Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start MySQL process was killed due to memory issues.  When start command was issued, server started and is working but a status command shows the following:

InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.3...514901
Plugin 'FEEDBACK' is disabled.
Server socket created on IP: '::'.
/usr/sbin/mysqld: ready for connections.

Is this something that needs to be addressed?

only for refer

in windows server mysql 5.6

The reason why a crash recovery is performed even after a "normal shutdown" is because windows service manager does not wait long enough before killing the service forcefully. 60 seconds might not be enough to flush all dirty pages to disk.
 There's a MySQL feature request to improve that. https://bugs.mysql.com/bug.php?id=45547

The question then is how to make mysqld shutdown quicker so the service manager doesn't timeout after 60 seconds of waiting .
 Answer is :

o) Set in my.ini a small value for this. innodb_max_dirty_pages_pct=10 # (default value is 75)
 o) Manually run STOP SLAVE; before the shutdown.

 o) Manually shutdown the mysqld using "mysqladmin shutdown -uroot -p" before rebooting server. Instead of using windows service stop command..


TLS 1.2 Implementation Validation I have a validation question regarding implementation of TLS 1.2.

What tools, scripts, processes, etc.. can be used to validate that TLS 1.2 is working when clients, webserver, and SQL Servers are communicating between each other?

Do I need to perform network packet sniffing, run traces, use process explorer? Is there a verbose option somewhere that can be turned on with logs reviewed?

Note: if it makes a difference, the SQL Servers involved run a variety of versions: 2008R2, 2012, and 2014.

First, see Aaron Bertrand's answer on TLS 1.2 with older SQL Server, and/or Aaron's SentryOne blog post.

If you're talking about the normal SQL Server connection encryption (SQL Server Configuration manager, Force Encrypt yes), with modern SQL Server, then there are two steps:

select encrypt_option, count(*) from sys.dm_exec_connections GROUP BY encrypt_option

If there are any FALSE results, you have unencrypted connections.  Fix that.
If there are 100% TRUE or NULL results, continue.

With buy-in from and in coordination with your IT Security team, download Microsoft Message Analyzer, Microsoft's Windows packet sniffer, and watch some connections.  You're looking for the ClientHello and ServerHello messages, within which you can see cipher suites offered and which one happened to be accepted.

It's a combination of registry and group policy changes to adjust that at the Windows OS level of your SQL Server installations.

WATCH OUT FOR THIRD PARTY SOFTWARE

Lots of it does NOT support TLS of ANY kind, much less TLS 1.2, either in the application, or, much more often, during one or more parts of the installation and/or upgrade process.  The vendor will be clueless if you ask beforehand, and just as clueless watching it happen in front of them.


SQL Server - What access do I have during a transaction rollback? As the title says, do I have read/write access to my entire database while it is rolling back a transaction?

Or read/write access to only the tables that the transaction did not write to? What is the locking behavior?

If I do have read access, what happens when I am reading a table where a transaction rolling back wrote to (does the query fail or just wait)?

Sorry for the questions, I have tried reading online (ROLLBACK TRANSACTION (Transact-SQL)), but it seems to not touch on this level of depth.

The answer really depends largely on your isolation level, what type of locks are held by the transaction being rolled back, and what your non-rollback session is trying to do.

Step 1) What is locked?

Before the ROLLBACK was issued, that transaction had done work and acquired locks. You can see details on those locks by looking in sys.dm_tran_locks:

SELECT tl.resource_type, tl.resource_associated_entity_id,
       tl.request_status, tl.request_mode, tl.request_session_id,
       tl.resource_description
FROM sys.dm_tran_locks tl
WHERE tl.request_session_id = <ROLLBACK session id>;

The resource_type and resource_associated_entity_id will tell you what is locked. If the resource_type is "Object" then resource_associated_entity_id is an object_id. If resource_type is "Key" or "Page" then resource_associated_entity_id is a hobt_id. There are more types to decode, but those two cover most scenarios.

SELECT tl.resource_type, tl.resource_associated_entity_id,tl.resource_database_id,
       CASE WHEN resource_type = 'OBJECT'
                THEN object_name(tl.resource_associated_entity_id,tl.resource_database_id)
            WHEN resource_type IN ('KEY','PAGE')
                THEN object_name(p.object_id)
        END AS resource_name,
       tl.request_status, tl.request_mode, tl.request_session_id,
       tl.resource_description
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id
                           AND tl.resource_database_id = db_id()
WHERE tl.request_session_id = <ROLLBACK session id>;

Lastly, the lock mode gives information on how that lock is being used. BOL gives a rundown of all the different lock modes.

Step 2: What will be blocked?

If the transaction being rolled back does not have a lock on an object, then the ROLLBACK won't block other sessions. That part is easy.

Whether these locks cause blocking is going to depend on your isolation level, and what you're trying to do. There's a very in-depth series on isolation levels by Paul White, which you could read for lots of details.

Different combinations of reading, writing, and isolation levels will have different blocking outcomes. Generally speaking, writes always block writes. However, writes may or may not block reads. Under SQL Server's default READ_COMMITTED isolation level, writes will block reads. If you are using READ_COMMITTED_SNAPSHOT (aka "RCSI" aka "Read Committed with Row Versioning"), then writes will not block reads.

In all cases, if another session is blocked by the session in ROLLBACK, the waiting session would just wait until the ROLLBACK released the lock.


GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to the user, the newly created user is still not able to see the tables using \dt in psql.  Here are some snippets of the script to show what I've tried so far:

CREATE USER api WITH ENCRYPTED PASSWORD 'password';

ALTER DEFAULT PRIVILEGES
    FOR USER api
    IN SCHEMA public
    GRANT ALL ON ALL TABLES TO api;

DROP DATABASE IF EXISTS new_db;
CREATE DATABASE new_db;

CREATE TABLE addresses (
    address_id INTEGER,
    address_line_1 VARCHAR(50) NOT NULL,
    address_line_2 VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(12) NOT NULL,
    PRIMARY KEY (address_id)
);

-- Create more tables....

-- Added these in for good measure at the end:
GRANT all PRIVILEGES on DATABASE new_db to api;
GRANT ALL ON DATABASE new_db TO api;
GRANT ALL ON SCHEMA public to api;
GRANT ALL ON ALL TABLES IN SCHEMA public TO api;

After running this, \dt shows all the tables in psql when logged in as the postgres user.  \dt shows Did not find any relations. when logged in as the api user.

Running \dn+ with the postgres user gives me this information:

  Name  |  Owner   |   Access privileges   |      Description
--------+----------+-----------------------+------------------------
 public | postgres | postgres=UC/postgres +| standard public schema
        |          | =UC/postgres         +|
        |          | api=UC/postgres       |

Version information:
PostgreSQL 10.5 (Ubuntu 10.5-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

Other SO questions I've encountered so far:

Granting access to all tables for a user
https://stackoverflow.com/questions/7866572/why-can-i-only-see-postgresql-relations-or-tables-when-im-logged-in-as-the-post
https://stackoverflow.com/questions/7758533/postgresql-database-owner-cant-access-database-no-relations-found
https://stackoverflow.com/questions/37543078/cant-see-tables-in-postgresql

I've looked at countless SO questions and tried everything suggested, yet still no luck. Is there anything else I can check or try? My hunch is that I'm missing something obvious.

Create a group, which means a ROLE with NOLOGIN.

CREATE ROLE api_group NOLOGIN;

Create (or drop first if you want) your database. And then connect to that database.

CREATE DATABASE new_db;
\c new_db

Add default privileges for your group. The code below says if postgres role creates an object grant ALL to the role api_group. Instead of ALL you can be more specific (e.g. SELECT, INSERT, USAGE etc).

ALTER DEFAULT PRIVILEGES FOR ROLE postgres ALL ON TABLES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres ALL  ON SEQUENCES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON FUNCTIONS TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON TYPES TO api_group ;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL  ON SCHEMAS TO api_group ;

Now you can use postgres user to create new objects.

CREATE TABLE addresses (
    address_id INTEGER,
    address_line_1 VARCHAR(50) NOT NULL,
    address_line_2 VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(12) NOT NULL,
    PRIMARY KEY (address_id)
);

-- Create more objects....

The setup is ready. If you add new user (lets say) api even after you create all the objects the user will have all the privileges. The INHERIT keyword there is for inheriting the privileges of roles it is a member of. The codes below create 2 users as a member of api_group.

CREATE ROLE api INHERIT WITH ENCRYPTED PASSWORD 'pwd' IN ROLE api_group;
CREATE ROLE another_api INHERIT WITH ENCRYPTED PASSWORD 'pwd' IN ROLE api_group;

Suggested sections of the documentation;

ALTER DEFAULT PRIVILEGES
CREATE ROLE


Shrinking old partitions I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have never more than 2 partitions I'm actively writing to at once. I have a FILLFACTOR set to 75 on the index since the data is coming in somewhat randomly.

The old data partitions are taking up quite a bit of space, utilizing only about 60-70% of that space. Is it possible to automatically change the FILLFACTOR on said partitions, rebuild the indexes and shrink the files? Since I'm not writing to those partitions anymore they shouldn't grow back in size.

Yes, this is possible. Here is how

Create a copy of the table (you can use the partition management tool for that: https://sqlpartitionmgmt.codeplex.com/)
Switch the partition you want to "compress" into the copy
Rebuild the indexes on the copy with FF = 100.
Switch the partition back into the original table

It should be pretty trivial to automate this with a SQL agent job.

Step 3 is best done in this order:

Drop all secondary indexes
Rebuild the cluster index with FF = 100
Create all the secondary indexes again with FF=100

Also, if you have SQL Server Enterprise Edition, you could compress the data with PAGE compression. This would require a different process:

Create a copy of that table as above
Switch the data you want to compress into the copy
Compress the data by rebuilding indexes as above
ALTER the original table to enable page compression on the now empty partitions (the ones you switched out)
SWITCH the compressed data back into the original table.

With SQL Server 2014 Enteprise Edition, you can do the above as an online operation directly on the partition you want to compress. However, it is still a good idea to drop the secondary indexes in a copy first, because it will typically give you better data layout on the disk.


replication database vs mirroring database Can anyone explain the differences from a replication db vs a mirroring db server?

I have huge reports to run. I want to use a secondary database server to run my report so I can off load resources from the primary server.

Should I setup a replication server or a mirrored server and why?

Replication:

Its mainly for Reporting and database synchronization
Secondary database is online and searchable. Basically you can run queries against your secondary database and treat it like a normal database while protecting the performance of your primary database.

There are different forms of replication

Transactional - Reads transactions to your secondary DB(s) as they occur on your Primary with a slight delay

Snapshot - Takes dumps at scheduled times of the changes recorded in your logs since the last sync, and inserts them at a scheduled time

Merge - The term says a lot for itself. It will merge content between databases so you can update data in both DB's. You might use this if you have two identical databases from day 1, and you have App 1 talking to DB 1, and App 2 talking to DB 2 for load balancing purposes or something. At the end of the day you might want to merge the transactions that occurred during the day to make sure you have two full lists of transactions in each DB.

Mirroring :

Its mainly for disaster recover solution which now is deprecated and replaced with AlwaysOn AG. Secondary database is in an offline mode while mirroring is in place. This is to ensure integrity in the secondary database in the event of a failover being required. Nothing can write to the secondary database, so you know it will exactly match your primary.
Can replicate schema changes. What this means is if you add a new index, or a new table, it will automatically be mirrored over. This is not the case with replication, it will only transfer across data level changes.
There are many configurations you can put together for replication, or mirroring, and you'd have to identify exactly what setup would suit your needs, but the above is a good baseline as to the best practices. Note you could possibly have snapshots of the mirrored database for reading purposes but that comes with its caveat of additional disk space resources and management, data integrity, and more. Generally you would not use this for your reporting solution.

Just gearing away from database mirrroring, if you are on SQL 2012 or later, can you consider AlwaysOn Availability Groups. It works just like database mirroring but allows the secondary replicas to be readable.


Host 'host_name' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' I'm getting following message:

  Host 'host_name' is blocked because of many connection errors. Unblock
  with 'mysqladmin flush-hosts'

I've tried to follow MySQL :: MySQL 8.0 Reference Manual :: B.6.2.5 Host 'host_name' is blocked like so, yet without success(

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
# mysqladmin flush-hosts
# echo $?
0
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2221
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> FLUSH HOSTS;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
#

I even tried to restart mysqld, still getting same message(

Please advise.

I was able to resolve my issue with following:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 216305
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL max_connect_errors=10000;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
#

I'm not entirely sure, if that actually what addressed my issue, however after above SQL statement, my app started to work (without being blocked anymore).


Access ODBC reserved error 7711 I have user who is trying to access some tables from our Microsoft SQL Server 2008 R2 system (which very recently got moved to a brand new hardware/software stack) using Access 2007 via an ODBC connection, a method they've been using for years. Now they're getting 'Reserved Error (-7711); there is no message for this error.'

In my googling process I haven't been able to identify any relevant information that isn't related to 2003 versions of Sybase. I'm guessing there's some security of configuration setting I've forgotten to set on the new box but to me the error seems pretty obscure and I'm not sure where to go from here.

Any help you can provide would be greatly appreciated.

Turns out the issue was with an assembly created with 'Unrestricted' permissions, when trying the ODBC connection under Excel I received a more verbose error related to a .NET error and this assembly, changing the 'Permission set' to 'Safe' seemed to solve the problem.


archive command is not working One of our ancient PostgreSQL server's used disk space has increased to 90%. First I've checked the archive command:

archive_command = 'rsync -aq %p /data/wal-archive/%f'

The problem with that command is rsync is not installed. So I've replaced it with:

archive_command = 'test ! -f /data/pgsql/data/mnt/server/archivedir/%f &&
    cp %p /data/pgsql/data/mnt/server/archivedir/%f'

After the replacement here is the output of postgresql.log:

  cp: cannot stat `pg_xlog/000000010000000000000001': No such file or directory

I presume beginning of the pg_xlog files removed by hand. Do you have any idea how to make this archive process work?

First, take a pg_basebackup, just in case. Then set your archive command to /bin/true. That will allow Postgres to delete everything to its own satisfaction that has already been deleted. To restore from backup you need the WAL since your last basebackup. Restoring to a point in time before is now impossible I’m afraid. When this is finished, set the archive command correctly. And get some monitoring for this!


How can I retrieve the image which is stored in bytea column of a pg_toast schema? I have a table called photo, that contains 3 columns

chunk_id oid
chunk_sq int
chunk_data bytea

How can I see the stored image?

The (bytea) type column contains data like

'034\350sec\302\340s\221\\030seq\301\037\022\034seq`uence!V!\364s\030ett\241\376\001\310sho\214rt\224\204\002Tize\001\240\370sloDR#\024\002\020\023\273\003\240\360ourc\324\.................'

select * from pg_toast.photo

Certain graphical DB clients will show you the image when going to the table data view.  In plain query output (for example in psql), though, you cannot expect images to appear.  Otherwise, you have to 'decode' the bytea.

Here is an example solution (written in PHP) on gis.stackexchange.com:

$conn = pg_connect("...");
$res = pg_query($conn, "SELECT chunk_data AS photo FROM photo WHERE ...");
$raw = pg_fetch_result($res, 'photo');
header('Content-type: image/jpeg');
echo pg_unescape_bytea($raw);

Note:

pg_toast is not usually a schema you query directly from.  What is stored there has a 'parent' table, do your queries against it instead.  Then you can possibly use sane column names in your queries, and so on.


refreshing 500k rows per day, what primary key to use? Usually I create a PK of type int/bigint for every table. But in this case I have some doubts.

I have a table which is updated many times a day, the total number of rows is around 500k, but they get refreshed few times a day.
Using update would be expensive, so for every batch of data to update I just delete the old rows (by a NC key) and bulkupload the new data.

Now my doubt is... I can just choose to have a bigint PK and forget about it, it will increase by 500k a day, and it will takes forever before to run out of values, but is there a better approach?

I'd stick with BIGINT.

An INT takes up 4 bytes of space. On 500,000 rows, that works out to 2 million bytes, or just under 2MB per day of overhead (which is practically nothing these days).

Double that for BIGINT, which is 8 bytes in size, so 500,000 rows will need under 4MB of space.

We generally recommend BIGINT if you're going to add (or change) over 2 billion rows over the lifetime of the table or index.

While INT supports a range of 4 billion values from roughly -2 billion to 2 billion, people generally start their IDENTITY value at 0 or 1, cutting out half of the available range.

In a case where you're doing 500,000 a day, a BIGINT might be better for you. It's a good trade-off between storage and data churn in the medium to long term.


Limited utility in RECURSIVE VIEWS? In the docs for recursive queries with WITH, you'll find this

  Recursive queries are typically used to deal with hierarchical or tree-structured data.

This seems to be true, but how does this function with RECURSIVE VIEWs? In a recursive view you can't define the edge case from the caller of the view. There is also 0-opportunity for the planner to push that edge case down..

For instance, take this query

WITH RECURSIVE t(name, groupid, parentid) AS(
  SELECT name,groupid,parentid
    FROM rl.groups
    WHERE groupid = 2 -- this defines the edge case
  UNION
  SELECT groups.name, groups.groupid, groups.parentid
    FROM rl.groups AS groups, t
    WHERE groups.groupid = t.parentid
)
SELECT * FROM t;

This will show you group 2, and all parents of that group and their parents. However, how do I make that into a useful RECURSIVE VIEW? So far as I can tell, you can't. And that eliminates the hierarchical and tree-structured data use-case. What are they good for then? Just building recursive mathematical sequences?

A recursive view is just syntactic sugar for a recursive CTE.

create or replace recursive view foo (name, groupid, parentid) as
  select name, groupid,parentid from rl.groups where groupid=2
  union
  SELECT groups.name, groups.groupid, groups.parentid
    FROM rl.groups AS groups, foo
    WHERE groups.groupid = foo.parentid ;

is the same as your CTE.

If you want to parameterize a CTE, wrap it in a function.


Prevent execution of heavy-loading and / or runaway queries in postgres Is there a way to stop/kill queries being executed on a postgres database based on criteria such as:

maximum execution time
load (CPU and/or memory) imposed on the server?

You can use statement_timeout to limit execution time:
https://www.postgresql.org/docs/current/runtime-config-client.html


Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah) is there any effective way to tune the query below?

I have a filtered index on OrganisationID <> 0
Primary key is on another field, not relevant for this query ItemCode varchar(20).

I have tagged sql server 2005 because it needs to work there, but if there is anything else in any other sql server version, please bring it up.

--------------------------------------
-- START CLEAR
--------------------------------------

begin try
drop table #T
end try
begin catch
end catch

--------------------------------------
-- THE TABLE
--------------------------------------

CREATE TABLE #T
(
a1 int,
a2 int,
ID INT,
OrganisationID INT,
Distance INT,
constraint PKt1 PRIMARY KEY  CLUSTERED (A1,A2,ID)
)

--------------------------------------
-- ADDING SOME DATA
--------------------------------------

INSERT INTO #T
SELECT 1,1,0,10,100
UNION ALL
SELECT 1,1,1,10,200
UNION ALL
SELECT 1,1,3,10,50
UNION ALL
SELECT 1,1,4,20,80
UNION ALL
SELECT 1,2,5,20,300
UNION ALL
SELECT 1,2,6,0,100
UNION ALL
SELECT 1,3,7,0,100
UNION ALL
SELECT 1,3,4,10,100

GO
--------------------------------------
-- INDEX CREATION
--------------------------------------

create index idx_T_OrganisationID
on #T (id)
WHERE OrganisationID <> 0
GO

--------------------------------------
-- THE QUERY
--------------------------------------
--SET STATISTICS IO ON
--SET STATISTICS TIME ON

    SELECT
        PID.ID,
        PID.OrganisationID
    FROM #t AS PID
    WHERE id IN (
        SELECT MIN(id) FROM dbo.#t
        WHERE OrganisationID <> 0
        GROUP BY OrganisationID
    )

After Comprehensive Testing - The Results

out of these 3 queries:

    -- query 1

        SELECT
            PID.ID,
            PID.OrganisationID
        FROM #t AS PID
        WHERE id IN (
            SELECT MIN(id) FROM dbo.#t
            WHERE OrganisationID <> 0
            GROUP BY OrganisationID
        )

    -- query 2

        SELECT  rn.ID, --... other columns go here
            rn.OrganisationID
    FROM (
        SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id)
    FROM #t
    ) rn WHERE n= 1
           AND OrganisationID <> 0

    -- query 3
    SELECT OrganisationID, MIN(ID)
FROM #t T
WHERE OrganisationID <> 0
GROUP BY OrganisationID ;

the first one does not even bring the most correct results,
the query 3 (as suggested in the comments by  spaghettidba and  ypercubeᵀᴹ ) is the one with best performance in my live environment, with my real table and data, as you can see below, the query I had originally and the one based on query 3:

for this exercise in particular, using the table #T query 2 and query 3 perfom more of less equally as per the query plan below (picture):

This query usually perform better:

SELECT  rn.ID, --... other columns go here
        rn.OrganisationID
FROM (
    SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id)
FROM #t
) rn WHERE n= 1


How do I connect to a database which is a linked server via ODBC? (All the searches for this sort of thing tell me about where the linked server is connecting to the original database via ODBC, but I'm connecting to the intermediate server via ODBC.)

I have a Python script which I want to connect to an Oracle database. In between the two I have a server running Server 2003 and SQLServer 2008. I have created a linked database on this intermediate server which successfully connects to the Oracle server. (Using Management Studio I can run a query on the remote Oracle server successfully.)

When I set up the machine that the Python script is running on, (happens to be another Server 2003, but I'll be running it on XP and Win7 machines too), I try to create an OBDC data source to the intermediate server. When the list of databases on the server comes up it doesn't list the linked server databases, only the normal databases on that intermediate server.

Is it possible to get ODBC to look at a linked server database? If so, how?

You have created a linked server, not a linked database, so there is no list of databases to select from. You need to either use a "four-part name" or use the OPENQUERY() function.

Four-part names describe an object on another server. Usually a four-part name would look like "servername.databasename.schemaname.tablename", but for Oracle you apparently need to leave the database name out. A query using a four-part name for Oracle might look like this:

SELECT name FROM OracleSvr..joe.titles WHERE id = 101

where "OracleSvr" is the name of the linked server.

Alternatively, here is an example of a statement that uses OPENQUERY. It should do the same thing, some people find this style easier to read.

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

OPENQUERY is documented here.

No matter which style of query you choose, you would connect via your Python program to the SQL Server and then run the query.

You can do this via ODBC, you don't need to specify the database name when setting up the connection, you do it at query time as described above.


Resource Usage Statistics by Login Is there a way to get resource usage statistics by login in SQL Server?

Ideally, I'd like to have a version of sys.dm_exec_sessions that tracks usage for all sessions, rather than just the currently-connected ones. Then I could just run something like this:

select login_name, sum(reads) as reads,
       sum(writes) as writes,
       sum(cpu_time) as cpu_time,
       count (login_name) as sessions
from sys.dm_Exec_Sessions
where login_name is not null
group by login_name

Obviously this runs currently, but only shows statistics for currently-connected sessions.

If anyone's curious, I ended up doing this. It gives me something, but I'm not really convinced that the data is good enough to be better than no data.

-- Set up table
-- Put this somewhere other than TempDB if you want it to persist across restarts
USE TempDB
DROP TABLE Sessions
CREATE TABLE Sessions (last_request_end_time datetime, login_name varchar(100), reads int,
writes int, cpu_time bigint)

-- Insert info about sessions from sys.dm_exec_sessions
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time
FROM sys.dm_exec_sessions

/*
In a loop, every 100ms insert data from sys.dm_exec_sessions where
last_request_end_time is greater than what has already been written
to the sessions table.

This data will be inaccurate in that very short-lived
sessions might never get counted, and long-running sessions will get
activity counted multiple times. As a result, you might get very different results
depending on what the WAITFOR delay is set to.

Optionally run this part as an Agent job.
*/

WHILE (2 > 1)
BEGIN
INSERT INTO Sessions (last_request_end_time, login_name, reads, writes, cpu_time)
SELECT last_request_end_time, login_name, reads, writes, cpu_time
FROM sys.dm_exec_sessions
WHERE last_request_end_time > (SELECT MAX(last_request_end_time) FROM Sessions)
WAITFOR DELAY '00:00:00.100';
END

-- After exiting the loop (stop Agent job, manually abort, etc) report on results
SELECT login_name,
SUM(reads) AS reads,
SUM(writes) AS writes,
SUM(cpu_time) AS cpu_time,
COUNT (login_name) AS session_count
FROM TempDB.dbo.Sessions
GROUP BY login_name


What to name a field that includes Store and Version ID I am building a database that manages the definitions for a POS system (menu items, cashiers etc). It will include many stores and many versions.

So, typical table will look like this (MenuItems table)...

StoreID PK
VersionID PK
MenuItemID PK
MenuItemName

But I would like to merge the StoreID and VersionID into a single ID that represents the store and version (so, version 5 for store 101). So, I simplify the primary key on each table (because every table is going to need this)

And then have my tables looks like this...

SomethingID PK
MenuItemID PK
MenuItemName

Please help me name that StoreIDAndVersionID field. I have not idea what to call it.

You should not retain two (or more) different values, each from a distinct domain, within the same column, because that procedure would only introduce unnecessary complexity to the restriction, manipulation (insertion, modification, deletion and retrieval), general maintenance and interpretation of the data. See this relevant Stack Overflow post by @PerformanceDBA  for some related aspects.

I definitely would keep each contextually individual datum in its corresponding, dedicated, constrained and meaningful column. So, in your scenario, I would set (a) one specific column to hold the StoreId and (b) one separate column to hold the VersionId in all the pertinent tables, such as you have presented in your first sample. This approach avoids the manual extraction and validation of each of the individual values contained in a combined column, which is very important since said operations would undermine the system speed and, most likely, would also eventually compromise the data integrity.

In case you want to merge those columns to meet some particular information display requirements, you can concatenate their values on the fly by means of a SELECT statement or, perhaps, with the aid of your application program code, and then show them inside a single column to your data users. Some feasible names for this computed or derived column could be, for instance, StoreId and VersionId, StoreId & VersionId, StoreId – VersionId, StoreId / VersionId or VersionId for StoreId, since these denominations would express the —purely expository— blend of two distinct data points.


How do aggregations work in SSAS? I understand the concept of pre-aggregating data so that multi dimensional queries perform faster, but the descriptions I've seen of aggregations in SSAS seem to imply that an aggregation only involves a single dimension or attribute.  For example the example in this article shows an aggregation for just time:

http://msdn.microsoft.com/en-us/library/ms174587.aspx

How does SSAS leverage such an aggregation when you query with attributes from two different dimensions such as route and time?  It would have to somehow take the aggregation for 2nd Quarter packages of 6005 and break it up into sea, air, etc. which I don't see how it would be able to do that without simply going back to the leaf level data.

The only other possibility I could imagine is that it has pre-aggregations which store many possible combinations of dimensions.

Since you can't actually view the pre-aggregated data, to see what its little wizard comes up with, it is difficult for me to visualize how this works. (I'm the kind of person who has to know the low level details before I really feel like I understand something and can use it properly).

How are aggregations structured, and how is SSAS able to leverage a pre-aggregation for queries which query multiple dimensions?

When a cube is "materialized", all combinations of aggregates over all dimensions are computed and stored.. the WITH CUBE operator does exactly this..

http://msdn.microsoft.com/en-us/library/ms175939%28v=sql.90%29.aspx


SSIS slow performance to insert short rows (Edit: I changed the post after some more explicative tests)

I'm experiencing a lack of performance between a SSIS server (name: Server1) and MSSQL server (name: 7551) when my ETL inserts rows of short length. The same server is much faster with longer rows.

Following are the resulting Elapsed vs Row length, testing with 1 million rows from flat file to table, with three configurations:

From Server1 to 7551: the configuration with bad issue;
From Server1 to 8221: server 8221 is another MSSQL Server, used as comparison;
From my laptop to 7551: I've used my laptop as source alternative;
From my laptop to 8221: final test is from laptop to comparison Db;

As you can see, the configurations #2 (red) and #4 (violet) has the same pattern, expected: longer the row, longer the elapsed.

Configurations #1 (blue) and #3 (green) maintain the same pattern only for longer rows, but show an offset for the shorter row length. Watching at the data, the offset is like 85-90 seconds for 64 bytes row and decrease to quite zero for 1170 bytes rows.

Note that #1 takes more to load 64 bytes rows than 2048 bytes rows (61.1MB in 91s vs 2GB in 54.8s).

The table is:

create table dbo.ALMG_TEST_LOAD (
  N bigint primary key,
  A nvarchar (4000) not null
)
Go

Finally, the ETL loads data with a Data Flow Task from a Flat File to an OLEDb destination:

Do you know why this could happen?

Note that:

Destination databases are similar: 7751 is backup/restored from 8221. Difference is that 7751 is SQL2014 and 8221 is SQL2012;
I'm the only one who is using 7751 and 8221 during tests. I've got same results in different days and hours;
SSIS version is 2012;
I've test with heap destination table or indexed destination table, and time profiles are the same;
ETL are performed with OLEDb connection and data loads are bulk.

Thanks

P.S.
I cannot access directly to destination server and it's not easy to talk to server admin to gather all kind of informations. Apparently they are not able to fix this issue and I have to help as much as I can. So any kind of suggestion is welcome.

There are a lot of things that come into play in regards to the performance difference.

1) is hardware\Instance configuration (RAM, Disk IO speed, Disk Size, Block Size, SQL-server memory configurations, Other software and tasks running)

2) Business of the server, If their are concurrent operations that it your tasks is awaiting resource access to.

3) Query Optimization.  Perhaps you are doing a full table scan on the 58 bytes but the 2MB is fully contained with in an index.  also perhaps there is more repetitive data in the 2MB.  If you have built the Select Statements in T-SQL copy them into SSMS and look at the execution plans.

Perhaps there are differences between 2012 and 2014 but I haven't seen huge performance differences in moving my packages to 2014 yet.