Remove Dates from table t-sql I need the table to go from this:

 CalanderDateTime   PtID      EventDateTime    GapinMinutes
 2017-09-23 08:34   NULL          NULL              NULL
 2017-09-23 08:35   NULL          NULL              NULL
 2017-09-23 08:36   NULL          NULL              NULL
 2017-09-23 08:37    1      2017-09-23 08:37        1
 2017-09-23 08:38    1      2017-09-23 08:38        4
 2017-09-23 08:39  NULL          NULL             NULL
 2017-09-23 08:40  NULL          NULL             NULL
 2017-09-23 08:41  NULL          NULL             NULL
 2017-09-23 08:42  NULL          NULL             NULL
 2017-09-23 08:43    1      2017-09-23 08:43       3
 2017-09-23 08:44  NULL          NULL             NULL
 2017-09-23 08:45  NULL          NULL             NULL
 2017-09-23 08:46  NULL          NULL             NULL

to this:

CalanderDateTime   PtID      EventDateTime    GapinMinutes
 2017-09-23 08:34   NULL          NULL              NULL
 2017-09-23 08:35   NULL          NULL              NULL
 2017-09-23 08:36   NULL          NULL              NULL
 2017-09-23 08:37    1      2017-09-23 08:37        1
 2017-09-23 08:38    1      2017-09-23 08:38        4
 2017-09-23 08:43    1      2017-09-23 08:43        3
 2017-09-23 08:44  NULL          NULL             NULL
 2017-09-23 08:45  NULL          NULL             NULL
 2017-09-23 08:46  NULL          NULL             NULL

In this example i would want the next 4 rows removed from the row where 4 is shown in the 'GapinMinutes' column (but this can be any number > 3 and therefore whatever number of rows removed is based on the number shown in the 'GapinMinutes' column, so if the column has a 5, remove the next five rows, if its a 7 remove the next 7 rows, if its a 10, remove the next 10 rows but if its a a number <= 3, keep the next 1,2 or 3 rows...

Here's the script i have so far:

With VentHours (ptID, t, ET, VGM)  AS
(
    SELECT sig.PatientID, sig.Time,

        Case When sig.PatientID = LEAD(sig.PatientID) OVER (ORDER BY sig.PatientID, Sig.Time) AND DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) between 0 and 720 THEN DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) ELSE 0 END Vent_Gap_Mins

        FROM GCUH_Reporting.dbo.VIEW_GCUH_Vent_Signals sig
        inner join GCUH_Archive.dbo.Patients p ON sig.PatientID = p.PatientID
),

JoinTime as
(
    Select *
    from GCUH_Reporting.dbo.AUX_DateDimension ts
    left outer join VentHours vh ON convert(DateTime,ts.DateTime) = convert(DateTime,vh.t)
)

select jt.DateTime, jt.ptid, jt.et, jt.vgm

from JoinTime jt

--where  jt.ptID   is not null

order by jt.DateTime

This is Sql server 2008 script.i think it can be easily translated to other rdbms.

Secondly your sample data is wrong.It should be 08:43 instead of 09:43

create table #t (CalanderDateTime datetime, PtID int,EventDateTime  datetime,GapinMinutes int)
insert into #t VALUES
 ('2017-09-23 08:34',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:35',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:36',   NULL,          NULL         ,  NULL)
 ,('2017-09-23 08:37',    1  ,    '2017-09-23 08:30'   ,  1 )
 ,('2017-09-23 08:38',    1  ,    '2017-09-23 08:35'   ,  4 )
 ,('2017-09-23 08:39',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:40',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:41',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:42',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:43',    1  ,    '2017-09-23 09:40'   , 3  )
 ,('2017-09-23 08:44',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:45',  NULL ,         NULL          ,NULL  )
 ,('2017-09-23 08:46',  NULL ,         NULL          ,NULL  )

 ;WITH CTE as
 (
  select *
 ,ROW_NUMBER()OVER(ORDER BY CalanderDateTime)rn
 from #t
 )
,CTE1 AS(
 select c.rn from cte c
cross apply(select rn from cte c1
 where c1.GapinMinutes is not null and c1.GapinMinutes>3 and
 (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1)) )ca
 )
 select * from cte c
 where not exists (select rn from cte1  c1 where c.rn=c1.rn)

 drop TABLE #t


What is the best way to handle a join / group_concat query in MySQL I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation.
I have 3 tables: tasks, assignees, and users
---tasks----
id, task, completed

---assignees----
user_id, task_id

---users---
id, name

I need to select tasks with concatenated user names of assignees, like so
tasks.task  |   assigned_names.names
---------------------------------------
"My task"   |   "Joe Schmoe, Bob Mcbart, Sally McGuire"

Here is what I have currently
SELECT tasks.task, assigned_names.names

FROM tasks

LEFT JOIN (
   SELECT
      GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
      assignees.task_id
      FROM assignees
      LEFT JOIN users ON users.id = assignees.user_id
      GROUP BY assignees.task_id

) assigned_names ON assigned_names.task_id = tasks.id

WHERE tasks.completed IS NULL

My main concern is that the assignees table has hundreds of thousands of records, and doing a group on the whole table every time seems like not a good idea.
Is there a better strategy?
Also, I would be happy to rephrase my question title if someone could suggest better language.
Thanks!

Simplicity is beautify. Multiple JOINs are allowed. There is no need to the JOINs to be LEFT JOINS as you want data that maches always.
SELECT tasks.task, GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
FROM assignees
JOIN tasks ON assignees.task_id = tasks.id
JOIN users ON assignees.user_id = users.id
WHERE tasks.completed IS NULL
GROUP BY assignees.task_id


Table size increase after data alter I have noticed when changing the data type of a column, the table size can double with a simple ALTER statement (Reclaiming Space After Column Data Type Change).

Why does SQL Server do this and what causes it?

I have seen posts for how to resolve the issue, how to observe the issue, but nothing about why it is happening and what SQL Server is doing under the hood.

The seemingly correct method is to create a new table, put all the data in the new table with the correct data type, drop the old table, and sp_rename the new table (as seen when you script out the change from using the Designer in SSMS). But it is odd that a straight table alter would not provide a warning or error message when it considers the operation one that "requires table re-creation".

It depends on the nature of the ALTER. A lot of times this is in fact implemented by creating a new column, copying the data across and marking the old column as dropped. Example:

CREATE TABLE T1(X CHAR(10));

INSERT INTO T1 VALUES (REPLICATE('X',10));

ALTER TABLE T1 ALTER COLUMN X CHAR(12);

And then looking at the row in SQL Server internals viewer you can clearly see this

You can also look at the results of the query under Inspecting the physical table structure

  the main question is what is happening during a column alter to double
  the size of a table?

There are several possible things that can contribute.

In the above case the row size was doubled with a single ALTER COLUMN so that would easily explain the overall table size doubling.

Even in the case that the row itself doesn't double in size the fact that the row becomes wider can cause page splits and increase the level of internal fragmentation. This is the case in the article you linked. It goes from having pages 99.8% full to 55% full. Every leaf page needed to be split as there was not sufficient room to accommodate the wider rows so that doubles the number of leaf pages.

Also sometimes previous changes that were implemented as metadata only are deferred and will be written to the row now it is being updated anyway. An example of such a change would be ALTER TABLE T1 ADD Y CHAR(12) NULL this is generally a metadata only change at time of execution and the physical row won't be updated to reflect the additional 12 bytes for this column until next time it needs to be written to.


Access and filter in oracle plan I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them.

My question is what are the reasons that can cause that(all the involved columns are in the index).

For example(However I want it to be more a generic question):

...
|* 18 |      HASH JOIN              |                 |  4064K|   294M|   307M| 55108   (1)| 00:11:02 |       |       |
|  19 |       PARTITION RANGE SINGLE|                 |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|* 20 |        INDEX FAST FULL SCAN | IX_TAB_TEMP1    |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|  21 |       PARTITION RANGE SINGLE|                 |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |
|* 22 |        INDEX FAST FULL SCAN | IX_TAB_TEMP1    |  6456K|   233M|       | 12217   (1)| 00:02:27 |    20 |    20 |

...
  18 - access("ITEM"."PARENTID"="HOL"."MID")
  20 - filter("ITEM"."ID1"=1110 AND "ITEM"."ID2"=1112)
  22 - filter("HOL"."ID1"=1110 AND "HOL"."ID2"=1112)

Index is (ID1, ID2, MID, PARENTID) local. partition is range on ID1, ID2

Based on the statistics the oprimizer has estimated this as the cheapest way to get the data. A INDEX FAST FULL SCAN reads the entire index as it is stored on disk using multiblock read. This kind of operation is prefered to other index operation   because  a high number/fraction of rows with ID1=1110 and ID2=1112 exists in the index and the data is not needed sorted. It is prefered to a full table scan because  all the data needed (ID1, ID2, MID, PARENTID) is contained in the index.


A column is missing from the Select Top 1000 Rows result I have a view in my database. When I am applying the Select Top 1000 Rows command to it in the Object Explorer in SSMS, the resulting script is missing one column, as you can see in the picture below (the missing column is BranchCode).

I have applied the sp_refreshview system stored procedure in an attempt to fix the problem, but it didn't help.

How is this possible and how can I resolve this?

First I would do as Andriy M said in the comments, try doing a refresh on the view.

If that doesn't work, drop and recreate to a new window. I know the "have you tried turning it off and on again" answer isn't cool. But it's a pretty good catch all.


I suspect it is related to the fact that this DMV is updated asynchronously behind the scenes.  Per the sys.allocation_units BOL page:

  ...the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available

While this implies only drops and/or truncates won't be tracked in real-time, I just ran the same query against one of my environments where nothing is ever dropped or deleted and am getting similar behavior to your results.  I suspect this view just is updated in the background and whatever it is that has to trigger to spawn the update hasn't happened yet.

Hopefully someone else has a better answer as I'd be interested to know for certain as well.


Would it be better for query plans to be split by statement for re-usability? From my limited knowledge of how query plans are compiled, stored and retrieved by queries I understand that a multi statement query or stored procedure will generate it's query plan which will be stored in the query plan cache to be used by the query in future executions.

I think this plan is retrieved from the query plan cache with the query hash, which means if the query is edited and executed the hash is different and a new plan is generated as no matching hash can be found in the query plan cache.

My question is: If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query? I expect the answer is no because the hash values will obviously not match, but would it be better to hash each statement in a multi-statement query so they could be used by users running individual statements from the query?

I expect there are complications that I'm not taking into account (And it's these that I really want to know about) but it seems like we could be storing the same 'statement plan' in many query plans taking up more space and taking more CPU and time to generate.

Could just be showing my ignorance though.

If a user executes a statement that is one of the statements in the multi-statement query can it use that relevant part of the query plan already in the cache for the multi-statement query?

No. The basic unit of plan reuse in SQL Server is the batch.

  Would it be better to hash each statement in a multi-statement query so they could be used by users running individual statements from the query?

A system tuned for high levels of plan reuse will place common code (at a suitable granularity) in reusable objects (e.g. procedures, functions, triggers) on the SQL Server. It will also explicitly parameterize any application-generated or client-side code. For maximum plan reuse, these generated batches should differ only in parameter values.

  I expect there are complications that I'm not taking into account

It sounds like you are asking why SQL Server was designed to cache and reuse at the batch level, rather than at the statement level. I doubt anyone aside from the original designers could answer this question authoritatively. Anyway, it seems to me that a batch is a natural granularity to use because it is relatively self-contained natural unit of work, and represents a reasonable trade-off between implementation complexity and plan reuse probability.

There are a few things that make batches not entirely self-contained (for example local temporary tables created and referenced across stored procedure boundaries). These exceptions reduce orthogonality and have been associated with unexpected 'by design' behaviours and bugs over the years.


Does MySQL close cursor if stored procedure encounters an exception? I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines.

Assuming, that CURSOR is opened when DBMS raises MYSQLEXCEPTION and goes to EXIT HANDLER if it is declared...

Approach #1:

DELIMITER $$

CREATE PROCEDURE `test1`()
BEGIN
DECLARE `EOS` BOOLEAN DEFAULT FALSE;

DECLARE `buffer` INT UNSIGNED;

DECLARE `MyCursor` CURSOR FOR
SELECT
    `id`
FROM
    `MyTable`
LIMIT 50;

DECLARE EXIT HANDLER FOR MYSQLEXCEPTION
    BEGIN
    ROLLBACK;

    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Oops... FATAL ERROR!', MYSQL_ERRNO = 5656;
    END;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET `EOS` = TRUE;

START TRANSACTION;

OPEN `MyCursor`;

cycle: LOOP

    FETCH `MyCursor` INTO `buffer`;

    IF `EOS`
    THEN LEAVE cycle;
    END IF;

    -- INSERTs to another tables, UPDATEs of another tables and DELETEs from to another tables
    -- Section that might throw a MYSQLEXCEPTION

END LOOP cycle;

CLOSE `MyCursor`;

COMMIT;

END$$

DELIMITER ;

Note that if MYSQLEXCEPTION will be thrown in commented section, then MyCursor probably will not be closed. Or it will be closed and deallocated in this case?

Somewhere I've seen following...

Approach #2:

DELIMITER $$

CREATE PROCEDURE `test2`()
BEGIN
DECLARE `EOS` BOOLEAN DEFAULT FALSE;

DECLARE `buffer` INT UNSIGNED;

DECLARE `MyCursor` CURSOR FOR
SELECT
    `id`
FROM
    `MyTable`
LIMIT 50;

DECLARE EXIT HANDLER FOR MYSQLEXCEPTION
    BEGIN
    ROLLBACK;

    CLOSE `MyCursor`; --   <---- is this even needed here ?

    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Oops... FATAL ERROR!', MYSQL_ERRNO = 5858;
    END;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET `EOS` = TRUE;

START TRANSACTION;

OPEN `MyCursor`;

cycle: LOOP

    FETCH `MyCursor` INTO `buffer`;

    IF `EOS`
    THEN LEAVE cycle;
    END IF;

    -- INSERTs to another tables, UPDATEs of another tables and DELETEs from to another tables
    -- Section that might throw a MYSQLEXCEPTION

END LOOP cycle;

CLOSE `MyCursor`;

COMMIT;

END$$

DELIMITER ;

And now I'm a bit confused about CURSOR handling.

Question: Which of this approaches is more accurate and reliable? If I'm not closing CURSOR in EXIT HANDLER will it impact anything? And if it is, how "bad" might be consequences?

use Approach #1:
according to http://www.brainbell.com/tutorials/MySQL/Working_With_Cursors.htm

If you do not explicitly close a cursor, MySQL will close it automatically when the END statement is reached.


Does SQL Server log record uncommited operations? I often see statements like sql server log records every transction and opeation.

But I am confused about what happens when a transaction is eventually rolled back.

Say an explicit transaction has 3 statements: statement A, statement B, statement C, and finally a rollback statement D.

Now say when the execution has not reached the rollback statement D, will the modifications resulting from statements A through C get recorded to sql server log?

Understanding 1:

Statements A through D all get recorded. SQL Server records everything, no matter what.

Understanding 2:
Modifications are only stored somewhere in memory, and only recorded to log when SQL Server see a commit statement. If it turns out to be a rollback statement, SQL Server simply ignore the transacrion, no writing to log happens because it serves no purpose. In other words, SQL Server logs when there is a net result before and after the transactions.

Both seems logical, at least to me but they can't both be right. Thanks for any help.

Understanding 1 is correct. SQL Server records every operation that changes data to the transaction log. A rollback is a change to data so it also records that to the transaction log as well. As statement A run it will write data to the transaction log and will also reserve data in the transaction log in case statement A needs to be rolled back. The same is true for B and C. When you rollback the transaction more information will be written to the log.

There are plenty of ways to see this in action so below is a quick demo. Here is the query that I'll use to see what was written to the log:

SELECT
  COUNT(*) transaction_count
, SUM(database_transaction_log_bytes_used) used_bytes
, SUM(database_transaction_log_bytes_reserved) reserved_bytes
FROM sys.dm_tran_database_transactions
where database_id = 10;

My table:

create table TLOGDEMO (FLUFF VARCHAR(1000));

BEGIN TRANSACTION

Query A uses minimal logging:

INSERT INTO TLOGDEMO WITH (TABLOCK)
SELECT REPLICATE('A', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After A:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║   24006640 ║      175429451 ║
╚═══════════════════╩════════════╩════════════════╝

Query B does not use minimal logging:

INSERT INTO TLOGDEMO
SELECT REPLICATE('B', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After B:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7352935708 ║     1613986255 ║
╚═══════════════════╩════════════╩════════════════╝

Query C changes less data:

INSERT INTO TLOGDEMO
SELECT REPLICATE('C', 1000)
FROM master..spt_values c;

After C:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7355821748 ║     1614545331 ║
╚═══════════════════╩════════════╩════════════════╝

Now I will issue a ROLLBACK and query the DMV while the rollback happens. Below is a table of a few snapshots:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║ 1                 ║ 7393305528 ║ 1573797677     ║
║ 1                 ║ 7458767420 ║ 1502635737     ║
║ 1                 ║ 7682482356 ║ 1259440979     ║
║ 1                 ║ 7803881368 ║ 1127471233     ║
║ ...               ║ ...        ║ ...            ║
╚═══════════════════╩════════════╩════════════════╝

During the ROLLBACK, the used bytes increases and the reserved number of bytes decreases. That's because SQL Server is using the space it set aside before to undo the transaction. To undo the transaction it must change data so it writes more data to the log.


Format for recording RGB color in MySQL React client is sending color to my server with this format
 {"r":1 "g":8 "b":5 "a":2}, for instance.

I'm not sure what is the best way to record color in MySQL.

I don't want to use this data in server. Just I want to send it to the client with the same format.

If "you want to send it to the client with the same format", then it is reasonable to treat it as "opaque".

That is, store it as a string with no meaning or structure that you care about.  And it does not matter (within limits) what datatype you use.  VARCHAR(99), TEXT, BLOB -- any of these would suffice.


MySQL can't connect using private IP address I have a fresh install of MySQL 5.7 on my Windows machine. When I make a connection using root@localhost, I am able to connect, but when i try to make a connection root@192.168.1.10 (the private IP address of my server) I  get the following error:

mysql 5.7 access denied for user 'root'@'192.168.1.10'.

I opened my my.ini file and added the line bind-address=0.0.0.0. I saved the file and restart my MySQL instance, but still not luck the same error appears.

I then tried:

**GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;**

This did not work either. I then tried to change the password for root to make sure I was using the correct password:

UPDATE mysql.user SET authentication_string=PASSWORD('password')  WHERE  User='root';

But no luck.

I have reinstalled MySQL and now everything works.


SSRS make a conditional format on the column In a SSRS matrix report I'm trying to make a conditional format on the 'Total' column
By checking one of the columns for a specific value,

For example:
In case in one of the Groups (Toys, Clothing, Food), the revenue for a certain day is zero then I want to display the total's values in red
See – 05/01/2015 and 'Clothing' then the 'Total' of this day will be in red.

Any idea how to do it

Thanks for your respond

For each data cell of your table (or matrix) you will have to set an expression on the font color (general tab under text box properties):

Now I am making assumptions of what your dataset/columns are called so you will have to translate those appropriately to your report setup.

For the Revenue and Quantity cells within each category the expression will be something similar to:

==IIF(Fields!ToysRevenue.Value <= 0,"Red","Black")

The expression for your Total Revenue and Total Quantity are a bit more lengthy since you have to check each Revenue value for each category, but it should look something like:

==IIF(Fields!ToysRevenue.Value <= 0 Or
Fields!ClothingRevenue.Value <= 0 Or
Fields!FoodRevenue.Value <= 0, "Red","Black")


Select distinct then calculate a number Structure of table:

user-----headline----day----number
1   -----lotr    -----1 -----  2
1   -----lotr    -----1 -----  2
1   -----cpo     -----2 -----  2
1   -----cpo     -----3 -----  6
2   -----ouch    -----1 -----  5

Results I am trying to get:

user-----headline-----number
1 -----    lotr  ----- **4**
1 -----    cpo   ----- **8**

echo
1 has lotr 4
1 has cpo 8

The query that I am trying to get is based on the user logged in and his id, in this case = 1.

I am trying like this, but no result:

SELECT DISTINCT headline, sum( number ) as result
FROM table
where user = '$login_session'"

echo
result;

What is the best way to get these results and echo them?

I tried Rolando's query but nothing happened:

The query you want is the following

SELECT headline,SUM(number) as sum_number
FROM table WHERE user = '$login_session'
GROUP BY headline;

You can make SQL echo the result by putting that query in a subquery

SELECT
    CONCAT('$login_session has ',headline,' ',sum_number,'</BR>') as result
FROM
(
    SELECT headline,SUM(number) as sum_number
    FROM table WHERE user = '$login_session'
    GROUP BY headline
) A;


How can I disable database lock in sqlite with python? I made a application in python and it should read and write simultaneously in multiple instances(multi CMD widows)(multi-threading). each thread is configured independently so it will not change the data that written with other instances. so in action app instance 1 will work with John's data and app instance 2 will work with Jane's data and so On.

the application concept is simple enough but in action when running multiple instances at once, there may be a situation that I get database locked error and the app will stop working.

I want to disable this locking system, because its not necessary in my application, I designed it Independent just for this reason.

can anybody tell me to do it in python? is it possible? where should I place a code or sql command or anything?

Using embedded sqlite is good for developing and testing, and I'm assuming you are using db like this:

db = sqlite3.connect('data/mydb')

In this case you could use several db like mydb1, mydb2 and so on. It would prevent the lockdown but overall it's not the solution.

I would suggest using several tables in single database instance via any RDS service you find appropriate. For example this one

Another solution could be moving from relational database to NoSQL solution. It would completely prevent the lockdown situation and it's highly scalable and affordable when data exchange volumes are relatively low.


Getting a query to work on SQLite as well as on MySQL I have a subquery with a condition in the main query where clause.

Here is the sample schema

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `discount_id` int(11),
  `amount` int(11)
);

CREATE TABLE `discounts` (
  `id` int(11) NOT NULL,
  `rate` int(11) NOT NULL
);

And the query

SELECT
  discounts.*,
  (SELECT COUNT(id)
   FROM payments
   WHERE payments.discount_id = discounts.id
   GROUP BY payments.discount_id) AS usage_count
FROM discounts
WHERE
  rate > 10
  AND usage_count > 1

Working on SQLite : http://sqlfiddle.com/#!5/9f159/2
Not working on MySQL : http://sqlfiddle.com/#!9/9f1593/1

MySQL shows error Unknown column 'usage_count' in 'where clause'

I can get it to work on MySQL using a HAVING clause but then it fails on SQLite with TypeError: e.STATEMENT is undefined

Not working on SQLite : http://sqlfiddle.com/#!5/9f159
Working on MySQL : http://sqlfiddle.com/#!9/9f1593/2

Is there any way to have a single way of working with both?

Why? I am developing using the Laravel framework, our unit tests run on SQLite and the app server runs MySQL.

SELECT * FROM
  (SELECT
  discounts.*,
  (SELECT COUNT(id)
   FROM payments
   WHERE payments.discount_id = discounts.id
   GROUP BY payments.discount_id) AS usage_count
FROM discounts
WHERE rate > 10) table1
WHERE usage_count > 1


Database Backup for data and log files I have been told that for data files backup operate at extent level and for log file, backup operates at page level.

I know that the file type for data file is always "Rows Data" and is stored in the form of extent(Either mixed or uniform extent) whereas logs are stored in the form of Log i.e. VLF(Virtual Log Files).

Can please somebody shed some light on this concept in a bit detailed level as I am a bit puzzled on how backup distinguishes between data and log. If its full backup, it would store all the committed change written to data file, for differential - all changes since last full backup from data file. And for log backup - all changes which is committed however not written to data file.

Appreciate your valuable input on this.

Don't worry about extents and pages.

A Full backup contains all the data pages.  The pages are not written to the backup at a single point-in-time.  So a Full also contains the log records needed to redo/undo the changes from the earliest page to latest transaction committed during the backup.

A differential backup omits all pages not changed since the last Full backup.

A log backup contains all log records since the last log backup.


How to identify where a package execution has spent most of the time? Team BI has a package deployed as a project on SQL Server 2012.

How can I find how long a package takes to run on a daily basis, and where it has spent most of the time?

I know that the catalog.executions view can be queried, anything else?

I am getting this request from developers:

Today’s BI Incremental ran without error but took far longer than usual.  The Sales Incremental took 1hour 12mins.  Yesterday it took 15 mins.  There has been no release/code changes.

The Sales ETL hits databasename on servername.  Was there something going on at the time of the load (5am)?  Anything logged?

Method 1 :  YOu can use the below VB code in SQL agent job to find the information on packages running on sql server 2012:

  '...
  '   Declare and instantiate objects here.
    Dim app As New Application
  '...
  ' Create a RunningPackages collection, named pkgs, and fill it
 ' with the running packages from the application object.
    Dim pkgs As RunningPackages = app.GetRunningPackages(Nothing)

 ' Enumerate over each package in the collection and display some data.
     For Each package As RunningPackage In pkgs
    Console.WriteLine("InstanceID: " & package.InstanceID.ToString())
    Console.WriteLine("PackageDescription: " & package.PackageDescription)
    Console.WriteLine("PackageID: " & package.PackageID.ToString())
    Console.WriteLine("PackageName: " & package.PackageName)
    Console.WriteLine("UserName: " & package.UserName)
     Next
     '   Insert more code here.

Method 2: Refer to below excellent read from Jamie Thomson which have neat and clean scripts to provide info on you're running packages:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

Method 3: YOu can use below SQL code which i've been using:

     SET NOCOUNT ON
  -- Check if the SQL Server Agent is running
    IF EXISTS ( SELECT  1
        FROM    MASTER.dbo.sysprocesses
        WHERE   program_name = N'SQLAgent - Generic Refresher' )
BEGIN
    SELECT  @@SERVERNAME AS 'InstanceName' ,
            1 AS 'SQLServerAgentRunning'
END
 ELSE
     BEGIN
    SELECT  @@SERVERNAME AS 'InstanceName' ,
            0 AS 'SQLServerAgentRunning'
    RAISERROR('The SQL Server Agent is not running.', 16, 1) WITH SETERROR ;
END
     -- Execute the script
    IF EXISTS ( SELECT  *
        FROM    tempdb.dbo.sysobjects
        WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]') )
DROP TABLE [tempdb].[dbo].[Temp1]
GO
   CREATE TABLE [tempdb].[dbo].[Temp1]
(
  job_id UNIQUEIDENTIFIER NOT NULL ,
  last_run_date NVARCHAR(20) NOT NULL ,
  last_run_time NVARCHAR(20) NOT NULL ,
  next_run_date NVARCHAR(20) NOT NULL ,
  next_run_time NVARCHAR(20) NOT NULL ,
  next_run_schedule_id INT NOT NULL ,
  requested_to_run INT NOT NULL ,
  request_source INT NOT NULL ,
  request_source_id SYSNAME COLLATE database_default
                            NULL ,
  running INT NOT NULL ,
  current_step INT NOT NULL ,
  current_retry_attempt INT NOT NULL ,
  job_state INT NOT NULL
)
 DECLARE @job_owner SYSNAME
  DECLARE @is_sysadmin INT
  SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)
 SET @job_owner = SUSER_SNAME()
 INSERT  INTO [tempdb].[dbo].[Temp1]
    EXECUTE MASTER.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE  [tempdb].[dbo].[Temp1]
  SET     last_run_time = RIGHT('000000' + last_run_time, 6) ,
    next_run_time = RIGHT('000000' + next_run_time, 6) ;
-----
   SELECT  j.name AS JobName ,
    j.enabled AS Enabled ,
    CASE x.running
      WHEN 1 THEN 'Running'
      ELSE CASE h.run_status
             WHEN 2 THEN 'Inactive'
             WHEN 4 THEN 'Inactive'
             ELSE 'Completed'
           END
    END AS CurrentStatus ,
    COALESCE(x.current_step, 0) AS CurrentStepNbr ,
    CASE x.running
      WHEN 1 THEN js.step_name
      ELSE NULL
    END AS CurrentStepName ,
    CASE WHEN x.last_run_date > 0
         THEN CONVERT (DATETIME, SUBSTRING(x.last_run_date, 1, 4) + '-'
              + SUBSTRING(x.last_run_date, 5, 2) + '-'
              + SUBSTRING(x.last_run_date, 7, 2) + ' '
              + SUBSTRING(x.last_run_time, 1, 2) + ':'
              + SUBSTRING(x.last_run_time, 3, 2) + ':'
              + SUBSTRING(x.last_run_time, 5, 2) + '.000', 121)
         ELSE NULL
    END AS LastRunTime ,
    CASE h.run_status
      WHEN 0 THEN 'Fail'
      WHEN 1 THEN 'Success'
      WHEN 2 THEN 'Retry'
      WHEN 3 THEN 'Cancel'
      WHEN 4 THEN 'In progress'
    END AS LastRunOutcome ,
    CASE WHEN h.run_duration > 0
         THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
              + ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
                                                          / 100 % 100 )
              * 60 + ( h.run_duration % 100 )
         ELSE NULL
    END AS LastRunDuration ,
    js.command AS SSISPackageExecutionCommand
     FROM    [tempdb].[dbo].[Temp1] x
    LEFT JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
    JOIN msdb.dbo.sysjobsteps js ON js.job_id = j.job_id
    LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
    LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id
                                                AND x.last_run_date = h.run_date
                                                AND x.last_run_time = h.run_time
                                                AND h.step_id = 0
 WHERE   x.running = 1
    AND js.subsystem = 'SSIS'

 DROP TABLE [tempdb].[dbo].[Temp1]


Display values from two tables with multiple conditions table1

 id | course
----------
 1  | A
 2  | B
 3  | C
 4  | D

table2

id | course |     date    | flag
-------------------------------
1  |    A   | 02-10-2020  | 1
2  |    A   | 03-10-2020  | 0
3  |    B   | 21-01-2019  | 1
4  |    C   | 12-12-2001  | 1
5  |    A   | 30-12-2019  | 1
6  |    C   | 02-10-2019  | 0
7  |    A   | 01-11-2019  | 1

I want output to be like

 course | Date
------------------------
    B   | 21-01-2019
    A   | 01-11-2019

The conditions are
1) only the courses of table1 that are present in table 2
2) date of the courses should be greater than today
3) only the courses with flag 1 has to be shown
4) choose the closest date to today when there are multiple flag=1 entries
5) output has to be in order by date asc

This should work:

SELECT table2.course, MIN(table2.date)
  FROM table1 INNER JOIN table2
    ON table1.course = table2.course
  WHERE table2.flag = 1
    AND table2.date > NOW()
  GROUP BY table2.course
  ORDER BY MIN(table2.date)

For your current dataset, you don't even need to JOIN with table1.


INSERT multiple values via join I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys.

CREATE TABLE Person (
person_id SERIAL PRIMARY KEY,
username VARCHAR NOT NULL UNIQUE
);

CREATE TABLE Group (
group_id SERIAL PRIMARY KEY,
group_name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE GroupMember (
person_id INTEGER REFERENCES Person,
group_id INTEGER REFERENCES Group,
PRIMARY KEY (person_id, group_id)
);

I would like to insert multiple entries into the relationship relation GroupMember, but using the natural keys of the associated entities. Something along the lines of:

INSERT INTO GroupMember (person_id,group_id)
SELECT person_id,group_id
FROM Person P, Group G, (
('alice','girls'),
('bob','boys'),
('alice','coolkids'),
('bob','coolkids')
) AS X
WHERE P.username = X.1 AND G.group_name = X.2;

Obviously the above syntax isn't valid, but hopefully conveys the idea. Is there a way to do this in PostgreSQL?

Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs.

INSERT INTO groupmember (person_id, group_id)
SELECT p.person_id, g.group_id
FROM  (
   VALUES
     ('alice'::varchar, 'girls'::varchar)
   , ('bob','boys',
   , ('alice','coolkids')
   , ('bob','coolkids')
  ) x (username, group_name)
JOIN   person p  USING (username)
JOIN   "group" g USING (group_name);

Aside: don't use the reserved word group as table name.


What would be the correct entity-relationship diagram for this CSV file? I received this from a customer and I was asked to model this as a database.

    Key Article number  color code  description price   action price    delivery    q1  size    color
    2800104 2   pants   Gaastra    8.00     1-3 working days    baby    104 gray
    00000002groe56  2   pants   Gaastra    8.00     1-3 working days    baby    56  green
    00000002groe62  2   pants   Gaastra    8.00     1-3 working days    baby    62  green
    00000002groe68  2   pants   Gaastra    8.00     1-3 working days    baby    68  green
    00000002groe74  2   pants   Gaastra    8.00     1-3 working days    baby    74  green
    00000002groe80  2   pants   Gaastra    8.00     1-3 working days    baby    80  green
    00000002groe86  2   pants   Gaastra    88.00        1-3 working days    baby    86  green

So far I detected a Color entity type, maybe a Category entity type (either q1attribute or Color Code attribute), and a Product entity type with attributes Key, Article Number, Description, Price and Action Price, maybe Delivery as well.

I would like to hear opinions if my analysis is correct or if I am missing something.

I have similar data model. It is not exactly as you expect. But surely it will help you to get start.

From the CSV file, the following columns can be derived:

Key
Article_Number
Color_Code
Description
Price
Action_Price
Delivery
Category
Size
Color

For the above mentioned table, the following model can be designed. Depends on your exact requirement, you may normalize this model or denormalize.

I hope this answer will help you.


postgres nested jsonb query I have a jsonb structure on postgres named data where each row (there are around 3 million of them) looks like this (I have GIN index on it):

[
    {
        "number": 100,
        "key": "this-is-your-key",
        "listr": "20 Purple block, THE-CITY, Columbia",
        "realcode": "LA40",
        "ainfo": {
            "city": "THE-CITY",
            "county": "Columbia",
            "street": "20 Purple block",
            "var_1": ""
        },
        "booleanval": true,
        "min_address": "20 Purple block, THE-CITY, Columbia LA40"
    },
    .....
]

I would like to query the min_address field in the fastest possible way. In Django I tried to use:

APModel.objects.filter(data__0__min_address__icontains=search_term)

but this takes ages to complete (also, "THE-CITY" is in uppercase, so, I have to use icontains here. This also has the problem that it ONLY searches the first elements - I'd want to search all the elements.

I tried dropping to rawsql like so:

cursor.execute("""\
    SELECT * FROM "apmodel_ap_model"
    WHERE ("apmodel_ap_model"."data"
    #>> array['0', 'min_address'])
    @> %s \
    """,\
    [json.dumps([{'min_address': search_term}])]
)

but this throws me strange errors like:

LINE 4:       @> '[{"min_address": "some lane"}]'
              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I am wondering what is the fastest way I can query the field min_address by using rawsql cursors.

You don't want #>>, but instead #>, from the docs

  #>   Get JSON object at specified path
  #>>  Get JSON object at specified path as text

Also, you should consider normalizing that. Storing json-arrays, of json-objects and expecting a query on those objects to be fast is insane.


setting up multi-user, multi database server(sql) here's the thing, i'm taking an advanced java class and we are going to be accessing a database to edit tables and such,  we tried using freemysqlhosting.net but we all couldn't connect at the same time due to same external IP (possibly). I was wondering if I could host a sql server at home using any microsoft or free sql server program for everyone to access their own database from school without any problems.  Is there any possible way to do this?

As Max said, you can do this with the free SQL Server edition, SQL Server Express. You should be aware of some of the limitations (on top of the fact that I have no idea which JDBC client / library / driver / provider to recommend, and whether it will have any of its own problems):

Each database is limited to 10GB in data files;
each instance is limited to 1 GB of memory and 1 socket / 4 cores;
there is no SQL Server Agent or Database Mail; and,
many other features are simply not present (see the full feature list).

If those limitations don't affect you, then okay, SQL Server Express is a candidate. That said, just having SQL Server Express installed on your home machine is not the end of the story. You will need to consider the following things, and may need to speak to your ISP to resolve some of them:

They probably do not allow incoming connections on port 1433 (or virtually any other port that you could use for SQL Server). If you discover an open port that you're not already using, it may work technically, but you are likely violating your terms of service, since they typically do not support or condone you running your own services that are accessed remotely. If that port isn't part of the service they have guaranteed, they could shut that port down at any time and without warning.
Your IP address may change frequently and unpredictably, so you would need some kind of dynamic DNS service to keep that reliable (or constantly have to tell your students the new IP whenever it changes). I'm not sure which of these services are free, but you can do some of that research on your own to see if any provider makes sense for you.
Depending on what they are doing with the data, you could very easily exceed your bandwidth cap and - if you are trying to do this on the sly - alert them that you are hosting services (see #1). They may have clauses in your contract whereby they can terminate the agreement or upcharge if they deem the activity to constitute any type of denial of service attack.

So without any problems? Maybe. It depends on how your ISP operates, but I suspect that is a tall order.


flatten JSON array into comma delimited list I am trying to construct a SELECT statement which will take a JSONB column in the following format:

{
  "Cities": [
    {
      "Name": "Atlanta"
    },
    {
      "Name": "London"
    },
    {
      "Name": "New York"
    }
  ]
}

The output of the column result set needs to be in the following format:

Atlanta, London, New York

UPDATE

@a_horse_with_no_name's answer below is correct, but my requirements are actually a little more complicated than originally posted. I actually need to fit this select in to a larger (join) query as follows:

select eo.citydata.cities.names <-- Flattened Comma delimited JSON Array
from orderline o
join eventorders eo on eo.orderlineid = o.id
join events e on e.id = eo.eventid
where e.id = '123'

Clearly the answer provided will need to be modified in order for this to work and I'm struggling to figure out how to do it.

Unnest the array, then aggregate back:

select string_agg(city, ',')
from (
  select x.val ->> 'Name' as city
  from the_table t
     cross join jsonb_array_elements(t.the_column -> 'Cities') as x(val)
) t;

If you have a bigger query, use that in a derived table:

select string_agg(t2.city, ',')
from (
  select x.val ->> 'Name' as city
  from (
    select eo.citydata
    from orderline o
      join eventorders eo on eo.orderlineid = o.id
      join events e on e.id = eo.eventid
    where e.id = 123
  ) t1
     cross join jsonb_array_elements(t1.citydata -> 'Cities') as x(val)
) t2;

Alternatively - if you need that very often - you can create a function that does this:

create function get_element_list(p_value jsonb, p_keyname text)
  returns text
as
$$
   select string_agg(x.val ->> p_keyname, ',')
   from jsonb_array_elements(p_value) as x(val);
$$
language sql;

Then you can use it like this:

select get_element_list(eo.citydata -> 'Cities', 'Name')
from orderline o
  join eventorders eo on eo.orderlineid = o.id
  join events e on e.id = eo.eventid
where e.id = 123;


Database Design Table for forecasting I am designing a database to mainly share data regarding projects and their progress. Part of this is to be able to store data regarding the forecast of each table.
I am designing the ForecastTbl and I am not sure how to proceed.
The forecast will hold the step during the project and how much is spent for 5 years. How do i design the table. i was thinking of having a ForecastTbl, YearTbl, MilestoneTbl...
For example:

Milestone Name     2016/2017     2017/2018  2018/2019   2019/2020   2020/2021

Construction         ...$           ...$       ...$         ...$      ...$

Security             ...$           ...$       ...$         ...$      ...$

Travel               ...$           ...$       ...$         ...$      ...$

The "typical" method for accomplishing this type of design is something like this:

You could use pivot functionality to show the data in a table format, similar to your example.  In SQL Server this might look something like:

SELECT *
FROM (
    SELECT names.MilestoneName
        , FY = CONVERT(varchar(4), YEAR(dates.MilestoneStartDate))
                + '-' + CONVERT(varchar(4), YEAR(dates.MilestoneEndDate))
        , [Money Spent(Budgeted)] = CONVERT(varchar(20), details.MoneySpent)
                + ' (' + CONVERT(varchar(20), details.MoneyBudgeted) + ')'
    FROM dbo.MilestoneDetails details
        INNER JOIN dbo.MilestoneDates dates
            ON details.MilestoneDateID = dates.MilestoneDateID
        INNER JOIN dbo.MilestoneNames names
            ON details.MilestoneID = names.MilestoneID
) src
PIVOT (
    MAX(src.[Money Spent(Budgeted)])
    FOR
        src.FY IN (
            [2015-2016]
            , [2016-2017]
            , [2017-2018]
        )
    ) pvt

╔═══════════════╦══════════════════════╦══════════════════════╗
║ MilestoneName ║ 2015-2016            ║ 2016-2017            ║
╠═══════════════╬══════════════════════╬══════════════════════╣
║ Construction  ║ 400.0000 (900.0000)  ║ 700.0000 (1200.0000) ║
║ Security      ║ 500.0000 (1000.0000) ║ 800.0000 (1300.0000) ║
║ Travel        ║ 600.0000 (1100.0000) ║ 900.0000 (1400.0000) ║
╚═══════════════╩══════════════════════╩══════════════════════╝

This is the code to create the tables, etc, if anyone is interested:

IF OBJECT_ID(N'dbo.MilestoneDetails', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneDetails;
IF OBJECT_ID(N'dbo.MilestoneDates', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneDates;
IF OBJECT_ID(N'dbo.MilestoneNames', N'U') IS NOT NULL
DROP TABLE dbo.MilestoneNames;

CREATE TABLE dbo.MilestoneDates
(
    MilestoneDateID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_MilestoneDates
        PRIMARY KEY CLUSTERED
    , MilestoneStartDate datetime NOT NULL
    , MilestoneEndDate datetime NOT NULL
);

CREATE TABLE dbo.MilestoneNames
(
    MilestoneID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_MilestoneNames
        PRIMARY KEY CLUSTERED
    , MilestoneName varchar(48) NOT NULL
);

CREATE TABLE dbo.MilestoneDetails(
    MilestoneID int NOT NULL
    , MilestoneDateID int NOT NULL
    , MoneySpent decimal(18, 4) NOT NULL
    , MoneyBudgeted decimal(18, 4) NOT NULL
    , CONSTRAINT PK_MilestoneDetails
        PRIMARY KEY CLUSTERED
        (MilestoneID ASC, MilestoneDateID ASC)
);

ALTER TABLE dbo.MilestoneDetails  WITH CHECK
ADD  CONSTRAINT FK_MilestoneDetails_MilestoneDates FOREIGN KEY(MilestoneDateID)
REFERENCES dbo.MilestoneDates (MilestoneDateID)

ALTER TABLE dbo.MilestoneDetails CHECK CONSTRAINT FK_MilestoneDetails_MilestoneDates

ALTER TABLE dbo.MilestoneDetails  WITH CHECK
ADD  CONSTRAINT FK_MilestoneDetails_MilestoneNames FOREIGN KEY(MilestoneID)
REFERENCES dbo.MilestoneNames (MilestoneID)

ALTER TABLE dbo.MilestoneDetails CHECK CONSTRAINT FK_MilestoneDetails_MilestoneNames

INSERT INTO dbo.MilestoneNames (MilestoneName)
VALUES ('Construction')
    , ('Security')
    , ('Travel');

INSERT INTO dbo.MilestoneDates (MilestoneStartDate, MilestoneEndDate)
VALUES (N'2015-01-01T00:00:00', N'2016-01-01T00:00:00')
    , (N'2016-01-01T00:00:00', N'2017-01-01T00:00:00')

INSERT INTO dbo.MilestoneDetails (MilestoneID, MilestoneDateID, MoneyBudgeted, MoneySpent)
VALUES (1, 1, 900, 400)
    , (2, 1, 1000, 500)
    , (3, 1, 1100, 600)
    , (1, 2, 1200, 700)
    , (2, 2, 1300, 800)
    , (3, 2, 1400, 900);


Does DBCC SHRINKDATABASE free physical disk space during its run? I want to run exactly this command:
DBCC SHRINKDATABASE mydb

When I run this just for a while and then kill it, will the physical database file be a little bit smaller?
In other words: Does the command immediatedly free physical disk space during its run, or does it first a full reorganization, and only then free all physical space?
EDIT: We have simple recovery model, so our log file is very small and we only have one big database file.

DBCC SHRINKDATABASE does not rollback when the process is killed so theoretically you'd be able to run it for a period of a time, kill the process, and have some reclaimed space (assuming there is any that can be reclaimed). See this for more information.
Also please see and research the warnings I provided against shrinking in my answer to your other StackExchange question.

...[it's] heavy operations, generally nonsensical to do because if
they grew to a certain size they'll likely grow back to that size soon
anyway and a growth operation is also heavy, and shrinking any
database files (except Temporary) does have a small risk of data
corruption.


Query XML without namespace I can query XML, which has defined xmlns tag with common table expression

DECLARE @XML XML

SET @XML =
'<NodeA xmlns="https://XYZ.xsd">
<NodeB verzePis="">
    <NodeC1 attrA="Hello" />
    <NodeC2 attrA="World" />
  </NodeB>
</NodeA>
'

;WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

But what if source XML does not contain any xmlns tag ?
How can i workaround missing namespace ?

You can just remove the WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd') part:

DECLARE @XML XML

SET @XML =
'<NodeA>
<NodeB verzePis="">
    <NodeC1 attrA="Hello" />
    <NodeC2 attrA="World" />
  </NodeB>
</NodeA>
'

;--WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

produces

+-------------+-------+
| SectionName | attrA |
+-------------+-------+
| NodeC1      | Hello |
| NodeC2      | World |
+-------------+-------+

Please note, WITH XMLNAMESPACES does not create a common table expression, it just declares an optional namespace


How to correct SQL Server Import and Export Wizard error that says my data conversion length must be between 0 and 4000? The message I get from the 2017 SQL Server Import and Export Wizard which I need help with is,

  "Error 0xc0204016: SSIS.Pipeline: The 'Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[Description]' has a
  length that is not valid. The length must be between 0 and 4000"

The data source column in question is titled "Description" and is of Excel type "General". This spreadsheet was given a long filename extension of ".iqy.xslx", and it was produced by a SharePoint Online's "Export to Excel" command which I performed on a SharePoint list. I'm using Excel installed locally via the Office365 website. Back in the SharePoint list, the original column "Description" is of type "Multiple lines of text". The destination I am trying to populate using the SQL Server Import and Export Wizard is a VARCHAR(MAX) column within a table inside an Azure SQL Database.

You can clearly see in the following screen how the SQL Server Import and Export Wizard perceives the Excel data column in question. Here it calls it a "LongText", and I'm unable to change the data types of neither the source nor the destination:

The error message pops up once I begin the conversion in a subsequent screen, and you can see it if you click on the Messages link as seen below.

How can I properly input this simple Excel spreadsheet into my table on Azure?

I've finally obtained an easy enough workaround. What I did was reopen my *.idy.xlsx Excel spreadsheet where my source data resided and re-saved it as an Excel 97-2003 Workbook.  These get a filename extension of *.xls. I went back to the SQL Server Import and Export Wizard and repeated the import steps, this time selecting the newly saved *.xls file. Ladies and gentlemen, it worked. A pair of trivial warnings appeared but the data came through flawlessly for me!


MySQL NOW() function - whose clock is used? For the mysql NOW() function, whose system time is used for time calculations? The host of the database or the client executing the statement?

That should be the time of the host (DB Server).
To be sure, login to MySQL from your client and run
SELECT NOW();

and then run the date() command in PHP and compare.
The NOW() and PHP date() would only be the same if you are running MySQL and Apache/NGINX on the same server.
CAPTAIN's LOG : SUPPLEMENTAL
You may also compare the difference between NOW() and SYSDATE() if you care about the datetime being deterministic.
The example in the documentation for [SYSDATE()] show how
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

Sorry, if this is WTMI, but just know that NOW() comes from the DB Server, not the WebServer.
Back in March of 2014, I talked about monkeying around with the server time and its effect on binary logs : Risk of changing clocktime on MySQL server host


Are LOBs also backed up if I take dump of all partitions of table individually? I am taking export of a large table in oracle through datapump. I am taking export of all partitions one by one. The table has a LOB column as well. Are LOBs also backed up with partitions? Because partitions seem to be very small as compared to LOBs

Yes, they should be. Unless, you have configured datapump to exclude them somehow. Please share the command (and parameter file) that you use to make the datapump export.

Reasons why the dump file is smaller could be that you have large LOB indexes (which are not included, and should not be), or you are using compression.


Run a program on a remote computer after a SQL job has completed We have a SQL Agent Job that performs a few tasks every night (schedulled to run using SQL Agent). The database server is SQL 2012 if that makes a difference.

We have another process (a .net program) that needs to run on a different server after the job has finished on each server. The program cannot be installed on the database server.

Is there a way to get a SQL Agent Job to run a program on another computer after it has finished, or is there an easy way to indicate to a program on another computer that the job has finished?

You can do it using

PSEXEC from sysinternals.
powershell using remoting functionality. (https://social.technet.microsoft.com/wiki/contents/articles/7703.powershell-running-executables.aspx
)

you cam have a batch file call your program and can use xp_cmdshell (if allowed) or operating system task in SQL agent job to run the job locally. This job can be called using linked server or powershell after the main step in the original job is completed.


Find first-time purchases with certain category I'm trying to find the first purchase of a customer who buys a computer category table only within all time purchase to segment them using SQL. My trial is. The JOIN part worked by someone else, so I just copy them and add the WHERE condition.

select order_table.guest_email as "Email",MIN(order_table.date_purchased as "First Purchase", main_category_name as "Category"
FROM (SELECT *,
                date_trunc('day', date_purchase) AS day_date
          FROM order_order

        ) AS order_table

    JOIN order_line
      ON order_table.id = order_line.order_id
    JOIN catalogue_productcategory
      ON order_line.product_id = catalogue_productcategory.product_id
    JOIN (SELECT catalogue_category.id, catalogue_category.name, main_category.name AS main_category_name FROM catalogue_category
          JOIN catalogue_category as main_category
            ON main_category.path=substring(catalogue_category.path, 1, 4))
          AS category_table
      ON category_table.id = catalogue_productcategory.category_id
    WHERE main_category_name = 'Computer'

The Min used to find the first purchase, and Where at the bottom used to filter 'Computer' category only. However, my work is wrong, the query returns the first 'Computer' which the customer purchase. The query should skip customers who did not purchase "Computer" on their first purchase. Are there any suggestion to this?

My educated guess:

SELECT *
FROM (
   SELECT DISTINCT ON (o.guest_email)
          o.guest_email AS "Email", o.date_purchased AS "First Purchase", mc.name AS "Category"
   FROM   order_order               o
   JOIN   order_line                l  ON l.order_id = o.id
   JOIN   catalogue_productcategory p  ON p.product_id = l.product_id
   JOIN   catalogue_category        c  ON c.id = p.category_id
   JOIN   catalogue_category        mc ON mc.path = left(c.path, 4)
   ORDER  BY o.guest_email, o.date_purchased
   ) sub
WHERE  "Category" = 'Computer';

Or, if all those joins never exclude rows (referential integrity enforced with FK constraints), you can exclude irrelevant rows early:

SELECT o.guest_email AS "Email", o.date_purchased AS "First Purchase", mc.name AS "Category"
FROM  (
   SELECT DISTINCT ON (guest_email)
          id, guest_email, date_purchased  -- more columns?
   FROM   order_order
   ORDER  BY guest_email, date_purchased
   ) o
JOIN   order_line                l  ON l.order_id = o.id
JOIN   catalogue_productcategory p  ON p.product_id = l.product_id
JOIN   catalogue_category        c  ON c.id = p.category_id
JOIN   catalogue_category        mc ON mc.path = left(c.path, 4)
WHERE  mc.name = 'Computer';

I largely rewrote the query and removed some cruft.

The subquery retrieves the first order per email.
The outer query filters first-timers who bought a 'Computer'.

About DISTINCT ON:

Select first row in each GROUP BY group?

Depending on your complete situation, there may be faster query techniques:

Optimize GROUP BY query to retrieve latest record per user


Fractional Transaction Count Based on this question I understand the meaning of the following sorts of errors:

  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

However I'm seeing:

  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.266

I find a fractional transaction count to be equal parts funny, scary, and odd. I'm sorry I don't have a simple script to share that would reproduce.

Can anyone explain this behavior?

Well, I feel silly. I eventually saw this again, but it said "current count = 2.266" I thought that was again strange.

And then I noticed that 266 was the error number. Hmm.

Facepalm

Turns out I inherited some boiler plate error handling that was appending the error number to the error message without any separation. So what looked for all the world like a wacky ms thing, was in fact a wacky construct. I must have looked at it 100 times without seeing the actual problem.


sys.database_files and sys.master_files show different physical_name values I received the following alert:

  The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000000012c000 in file 'C:\VeeamFLR\UK->SB->SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and...

The following script shows sys.database_files and sys.master_files have different physical_name values:

declare @dbname varchar(30)

set @dbname = (select name from sys.master_files where physical_name like 'C:\VeeamFLR\%.mdf')

select name as 'MasterFiles', physical_name as 'FullPath' from sys.master_files where physical_name like 'C:\VeeamFLR\%.%df'order by physical_name desc
exec('USE '+ @dbName + '; select name as ''DatabaseFiles'', physical_name as ''FullPath'' from sys.database_files')

Results:

MasterFiles              FullPath

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

MyDB10_STAGE        C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume3\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log    C:\VeeamFLR\UK-SB-SQL11_4406bb77\Volume2\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected

DatabaseFiles            FullPath

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

MyDB10_STAGE        E:\DATA\MyDB10_STAGE.mdf

MyDB10_STAGE_log    D:\Logs\MyDB10_STAGE_log.ldf

(2 row(s) affected)

The sys.database_files value is correct. (Sys.master_files refers to a previous Veeam restore mount point. Not sure why). I've tried a detach and re-attach with the correct path specified:

-- detach:
ALTER DATABASE MyDB10_STAGE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyDB10_STAGE'
GO

-- No need to move physical files as actually already in correct location

USE [master]
GO
CREATE DATABASE MyDB10_STAGE ON
( FILENAME = N'E:\DATA\MyDB10_STAGE.mdf' ),
( FILENAME = N'D:\LOGS\MyDB10_STAGE_log.ldf' )
FOR ATTACH
GO

--Put database in multi user mode again:

ALTER DATABASE MyDB10_STAGE SET MULTI_USER
GO

But this has not updated physical_name in sys.master_files. Can anyone recommend how to update physical_name in sys.master_files to the correct value please?

I couldn't find anything about this in the documentation for sys.database_files or sys.master_files, or even sys.sysfiles or sys.sysaltfiles, but there will likely be several state changes that won't be truly reflected across all file-related catalog views until the SQL Server service has been restarted. If the changes are isolated to a single database, it might be worth trying to set the database OFFLINE and ONLINE again, but I haven't confirmed that will work.


MVCC in PostgreSQL: visible tuple with xmin > current txid I'm trying to better understand MVCC and am confused by the following (contrived) scenario. I was under the impression that for a row to be visible to a transaction, the transaction's id must be greater than the tuple's xmin value.

create table test (id int);

(session 1)
begin;
select txid_current();
┌──────────────┐
│ txid_current │
├──────────────┤
│          704 │
└──────────────┘

(session 2)
begin;
insert into test values (1);
commit;

(session 1)
select xmin, xmax, id from test;
┌──────┬──────┬────┐
│ xmin │ xmax │ id │
├──────┼──────┼────┤
│  705 │    0 │  1 │
└──────┴──────┴────┘

How can a transaction with an id of 704 see a tuple with an xmin of 705?

Update:

Reading http://www.interdb.jp/pg/pgsql05.html helped clarify things a lot more for me. A few videos I had previously watched seemed to imply that, as a hard rule, a tuple is only visible if its xmin is less than the current transaction's txid.

However, the above reading painted a slightly different picture for me. In an isolation level of READ COMMITTED, a transaction gets a new snapshot of the state of other transactions at the start of each query.

The first query in session 1 would get a snapshot of 704:704: (you can see this by running select txid_current_snapshot()) which means that from the point of view of txid 704, all txids < 704 are inactive (committed or aborted) and all transactions >= 704 are active (in progress or not yet started).

The second query of session 1 would get a new snapshot of 704:706: which means that from the point of view of txid 704, all txids < 704 are inactive, and all txids >= 706 are active. However, since txid 705 fits in between these values, and since it has in fact been committed, it too is part of the new snapshot that txid 704 received, and is visible. Therefore, it is not a hard rule that a tuple is only visible if its xmin is less than the current transaction's txid.

This depends on the isolation level. The default level for postgresql is "Read Committed" at which level this can happen. In postgresql it won't happen at "Repeatable Read" or "Serializable".

This behavior is often not a problem and higher serialization levels can slow down the server which is the reason why they are not the default.

For an extensive explanation please see the manual https://www.postgresql.org/docs/9.6/static/transaction-iso.html


Count values from one table using two columns in another table I have a problem. I have table_1 (col1, col2, value) and table_2 (col1, col2).

I want to count values from table_1 but only the rows which exist in table_2. In both tables col1 and col2 are key (not primary).

I've tried:

select col1, col2, sum(value)
from table_1
where table_1.col2 in (select col2 from table_2)
 group by col1, col2 ;

It works , but wrong, select too many rows from table_1

Assuming no duplicates in the 2nd table, it's straightforward.

ANSI syntax:

select col1, col2, sum(value)
from table_1
natural join table_2
group by col1, col2;

The "old" way:

select t1.col1, t1.col2, sum(value)
from table_1 t1, table_2 t2
where t1.col1 = t2.col1
and   t1.col2 = t2.col2
group by t1.col1, t1.col2;

Could have used WHERE EXISTS too, I guess.

SQL Fiddle for completeness.


Parallel updates on header and line item tables in SSIS This is my scenario

I have 2 tables - a header and a line item table which join on 2 columns contract number and contract version
First, I update the header table to set the status code as 'T' for whatever records have an expiration date in the past on the header table. The query looks like this:

Update Contract_Header
set status_code = 'T'
where status_code = 'A'
and expiration_date < getDate()
Then, I update the line item table to set status code as 'T' for all active line items having an expiration date in the past, and are corresponding to those contract numbers and versions in the HEADER table with a status code of 'A' (The logic might sound a bit crazy but that's how it was designed in the first place)

Update C
set C.Status_Code='T'
FROM Contract_Line_items C
JOIN Contract_Header H
ON C.contract_nbr = H.contract_nbr AND C.contract_version = H.Contract_Version
WHERE H.status_code='A'
AND C.status_code='A'
AND C.expiration_date is not null
AND C.expiration_date < getDate()
Both these queries are put in a Container task in SSIS, and are unconnected data flow tasks as shown in the below image

Technically, only one of those tables - either the header or the line item can be set to 'T' for a contract number and version combination, because by the time the line item table gets updated, the header status for that record would already have been set to 'T'. But, due to some reason, last week, both of them were set to 'T' for the same contract number, version combinations. In the subsequent execution, the issue got resolved by itself and only the header was set to 'T', and not the line items
We think this is some sort of a race condition, but would linking the tasks to execute after each other help the purpose? Is this really a candidate for a race condition? Any other suggestions are also appreciated

Thank You!

Less of a race condition and more of an issue with order of operations and dependencies.
The way you stated your question, in steps, is how you want to think of the SSIS package.  First it has to do this, then it has to do that.
Since the line items are dependent on the latest information from the header, that should always come second.  Letting them go simultaneously, you don't really know what happens first.


Powershell function to drop table(s) from SQL Server database ### List of tables that require full sanitizing
$requiredTables = 'table1','table2','table3'

foreach ($table in $requiredTables){

function Delete-FromTable{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString =  "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText =

"
delete from $table
"

$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 0
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$test=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}

Delete-FromTable

}

Here's how I'd go about this:

function Get-IfSqlTableExists {
    param (
        [Parameter(Mandatory = $true)]
        [string]$SqlServerName,

        [Parameter(Mandatory = $true)]
        [string]$DatabaseName,

        [Parameter(Mandatory = $true)]
        [string]$TableName
    )

    $ConnectionString = "data source = $SqlServerName; initial catalog = $DatabaseName; trusted_connection = true;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandText = "select count(*) from sys.tables where object_id = object_id(@table_name);"

    $TableNameParam = New-Object System.Data.SqlClient.SqlParameter("@table_name", [System.Data.SqlDbType]::NVarChar, 512)
    $TableNameParam.Value = $TableName
    $SqlCmd.Parameters.Add($TableNameParam) | Out-Null

    try {
        $SqlConnection.Open()
        $Return = $SqlCmd.ExecuteScalar()
    }
    finally {
        $SqlCmd.Dispose()
        $SqlConnection.Dispose()
    }

    if ($Return -eq 1) {
        return $true
    }
    else {
        return $false
    }
}

function Delete-SqlTable {
    param (
        [Parameter(Mandatory = $true)]
        [string]$SqlServerName,

        [Parameter(Mandatory = $true)]
        [string]$DatabaseName,

        [Parameter(Mandatory = $true)]
        [string]$TableName
    )

    if (!(Get-IfSqlTableExists -SqlServerName $SqlServerName -DatabaseName $DatabaseName -TableName $TableName)) {
        Write-Warning "$TableName doesn't exist in $DatabaseName on $SqlServerName"
        return
    }

    $ConnectionString = "data source = $SqlServerName; initial catalog = $DatabaseName; trusted_connection = true;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection

    # commented out the actual drop table command text to
    # prevent accidental running
    #$SqlCmd.CommandText = "drop table $TableName;"

    try {
        $SqlConnection.Open()
        $SqlCmd.ExecuteNonQuery() | Out-Null
    }
    finally {
        $SqlCmd.Dispose()
        $SqlConnection.Dispose()
    }

    Write-Verbose "Successfully deleted $TableName in $DatabaseName on $SqlServerName"
}

$SqlServerName = "localhost\sql2012"
$DatabaseName = "TestDatabase"
$TableNameList = "table1", "table2", "table3", "notarealtable", "table4"

foreach ($TableName in $TableNameList) {
    try {
        Delete-SqlTable -SqlServerName $SqlServerName -DatabaseName $DatabaseName -TableName $TableName -Verbose
    }
    catch {
        Write-Error "Error attempting to drop table $TableName in $DatabaseName on $SqlServerName"
        # or handle however you want
    }
}

It looks like a lot of code, but it would scale.  It does things like checking to see if the table actually exists before attempting to delete.

My output on my test instance (with verbose output) looks like this:

VERBOSE: Successfully deleted table1 in TestDatabase on localhost\sql2012
VERBOSE: Successfully deleted table2 in TestDatabase on localhost\sql2012
VERBOSE: Successfully deleted table3 in TestDatabase on localhost\sql2012
WARNING: notarealtable doesn't exist in TestDatabase on localhost\sql2012
VERBOSE: Successfully deleted table4 in TestDatabase on localhost\sql2012

You could (and should) form this type of logic in any way you want.  This is just one approach with a style I typically like to take.

Note: I commented out the actual DROP TABLE command from the function to prevent accidental running.


Backup certain tables in MySQL Is it possible to take backup of certain table in mysql? Suppose I have 10 tables, and I want to take a backup of 8 tables through one command.

How can I do this?

mysqldump -u username -p databasename table1 table2 table3

or

mysqldump --opt -u username -p databasename table1 table2 table3


Arithmetic overflow error when using sp_Blitz I am getting this error:

  Msg 8115, Level 16, State 2, Procedure sp_Blitz, Line 469  Arithmetic overflow error converting expression to data type int.

when running sp_Blitz in a server with 52 user databases.  I also get it when I run it with @CheckUserDatabaseObjects = 0.

I have the latest version from 10/14/2016.

Any ideas what I am doing wrong or what over parameters I should run this with since there are so many user databases?

There was an issue with the latest release, shown in ISSUE 526. It was updated, by changing this code on line 472.

FROM

SELECT @MsSinceWaitsCleared =
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000

TO

SELECT @MsSinceWaitsCleared =
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000.0

If you do a new Pull request through github it has the fix in it.


Add row after every rows of a table from select statement There are two tables. I want to add every record of one table under every record of the other table. The two tables and the result table are shown below.

TABLE_1    TABLE_2     TABLE_3
TYPE        NAME        NAME      TYPE  VALUE
T_1         PARTY_1     PARTY_1   T_1    100
T_2         PARTY_2     PARTY_1   T_2    200
                        PARTY_2   T_1    120
RESULT_TABLE
RESULT     VALUE
PARTY_1     -
  T_1       100
  T_2       200
PARTY_2     -
  T_1       120
  T_2        0

How to do it? I am using SQLite database.

I have done so far ...

string qry = "SELECT " +
            "[TABLE_2].[NAME] AS [RESULT] " +
            "FROM [TABLE_2] " +
            "UNION " +
            "SELECT " +
            "'  - ' || [TABLE_1].[TYPE] " +
            "FROM [TABLE_1]";

But it shows as per below...

RESULT
PARTY_1
PARTY_2
- T_1
- T_2

Your question says two tables but your example shows three.  I'm going to take table_3 as the association table between table_1 and table_2, showing data which actually exists.

To get all possible combinations of two tables you need a CROSS JOIN. The number of rows returned is the product of the number or rows in each table. Be careful as this can get very large.  With two rows in each table you get four rows from a cross join.  With one thousand rows in each you get one million rows returned:

table_1 CROSS JOIN table_2

That will give you all possible combinations.  To include your actual values you will need a LEFT JOIN from that to table_3:

table_2 AS t2 CROSS JOIN table_1 AS t1
  LEFT JOIN table_3 AS t3
    ON  t3.name = t2.name
    AND t3.type = t1.type

If you want the extra "party" rows, you'll need one more subquery, combined with UNION to the previous one and all wrapped in an extra derived table to have the results in the wanted ordering. The final query will be something like:

SELECT
    result, value
FROM
  ( SELECT
        name, name AS result, NULL AS value, 0 AS ordering
    FROM
        table_2

    UNION ALL

    SELECT
        t2.name, '  - ' || t1.type, COALESCE(t3.value, 0), 1
    FROM
        table_2 AS t2 CROSS JOIN table_1 AS t1
          LEFT JOIN table_3 AS t3
            ON  t3.name = t2.name
            AND t3.type = t1.type
  ) AS x
ORDER BY
    name, ordering, result ;


How (if at all) does Galera enforce authentication for SST via rsync when adding a node? I have to be missing something here.

It just hit me as added a new node to my cluster in order to prepare for the removal of a different node:  "How does the cluster know that it is okay to send the new node a SST?"

I am pretty sure that the only information the new node has about the cluster is the gcomm:// address.  Surely that isn't looked at as "secure" information that passes for authentication.  To my knowledge, no shell account on the new node has the same password as on the existing cluster nodes.

So what would prevent anyone from spinning up a new node and pointing it's gcomm:// address at one or more of my nodes to just get an SST and be able to see all of my data?

Of course, certificates will be put in place.  But I'm talking about a default setup and how things work "out of the box."  I couldn't find much of anything that talked about this out there.

Am I going nuts?

The cluster simply doesn't know. It'll send a sst to every node which requests it.


Sql 2016 STD SP1 Always On with DB Mirroring I have 2 standard editions of sql 2016 sp1.
Since only 1 dB is allowed for always on I have to setup other 4 db’s in db mirroring.

My always on endpoint is already using port 5022.
Is there a place in db mirroring where I can change 5022 to let’s say 7022?

Thank you

Since only 1 dB is allowed for always on I have to setup other 4 db’s in db mirroring.

That makes absolutely no sense as they work basically the same way. Yes, there are some very real differences between them, but if someone told me "this works for mirroring but can't be used in availability groups" I'd ask why. the capture mechanism is roughly the same, the transfer mechanism is roughly the same, and the apply mechanism is roughly the same. When I say "roughly" I mean they are not 100% identical.

I'd put them all in AGs.

  My always on endpoint is already using port 5022. Is there a place in db mirroring where I can change 5022 to let’s say 7022?

No, because there can only be a single mirroring endpoint which is what both availability groups and mirroring uses. The reason that it's called a "mirroring" endpoint and not an "availability group" endpoint is two fold. First, mirroring came first. Second, if the name was changed it could cause a compatibility issue with older applications and code where the keyword would change, so they won't change it due to backwards compatibility.

I'd be happy to understand why someone is saying that a database can belong to mirroring but not belong to an availability group.


MySQL - Get maximum Tuple without Grouping everything I need some help by writing a SQL statement.

I have a table (it's already a result from another query) with 4 columns e.g.:

Firstname | Lastname | Softwarename | Hours

With a example data set like:

Row1: Max | Huber | Word | 25h

Row2: Max | Huber | Excel | 12h

The result of the query should be the first row.
My problem is that I am forced to add the Softwarename to the group by, but when I do that the result is obviously incorrect.

How do I do that?

Thank you!

If I've understood you (at last) correctly, I think you want something like this (see fiddle here):

Create your table:

CREATE TABLE test_tab
(
  first_name VARCHAR (25) NOT NULL,
  last_name  VARCHAR (25) NOT NULL,
  soft_name  VARCHAR (25) NOT NULL,
  soft_hours VARCHAR (25) NOT NULL
);

Populate it:

INSERT INTO test_tab VALUES ('Max', 'Huber', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Max', 'Huber', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Outlook', '35h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Excel', '20h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Word',  '8h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Outlook', '3h');

And use the following premliminary SQL (included to demonstrate the thought processes that went into formulating the answer. I find that people learn better if they can see a "breadcrumb trail" (cf. Hansel and Gretel)):

SELECT
  first_name,
  last_name,
  MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
FROM test_tab
GROUP BY first_name, last_name
ORDER BY last_name;

The CAST is necessary because of the inclusion of the hin the string for the duration of usage (much better to use a plain INTEGER). If the CAST isn't present, 8h will be "greater than" 20h for Donald Duck when sorted as a character string - CASTing to UNSIGNED removes that problem.

Result:

first_name  last_name   use_duration
    Donald       Duck             20
       Max      Huber             25
     Felix     TheCat             35

Having obtained the first and last names and the use_duration, we are now in a position to JOIN back to our original table this way:

SELECT
  t1.first_name, t1.last_name, t1.soft_name,
  CAST(t1.soft_hours AS UNSIGNED) AS "usage"
FROM test_tab t1
JOIN
(
  SELECT
    first_name,
    last_name,
    MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
  FROM test_tab
  GROUP BY first_name, last_name
) AS t2
ON t1.first_name = t2.first_name AND t1.last_name = t2.last_name
AND CAST(t1.soft_hours AS UNSIGNED) = t2.use_duration
ORDER BY t1.last_name;

Result:

first_name  last_name   soft_name   usage
    Donald       Duck       Excel      20
       Max      Huber        Word      25
     Felix     TheCat     Outlook      35

Now, I would recommend that you keep the duration as just a pure INTEGER rather than use 25h. I hope this responds to your needs, - if not, then please expand on your explanation and I'll endeavour to revise my answer to your satisfaction. p.s. welcome to the forum! :-)


Error while trying to create a database for SSRS I have installed SQL Server Reporting Services 2017, and my database engine is developer edition 2017. Now, while trying to configure SSRS from its Configuration Manager I get this error

I have found some really scarce online resources about this error which does not solve my problem.

I need help in figuring out what I should do? Do I need another edition of SQL Server database engine?

Version: Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64)   Jul 21 2018 07:47:45   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 10.0  (Build 17134: )

I've downloaded SSRS from here: https://www.microsoft.com/en-us/download/details.aspx?id=55252

clicking the link provided in Developer Edtion setup.exe

SSRS Version: 14.0.600.892, Native mode

Thanks for your time.

I installed SQL Server 2017 side-by-side with an existing SQL Server 2014 instance.

I installed SQL Server Reporting Services 2017 as per the download link.

During the installation I was asked which edition of SSRS I would like to install. The options were:

Developer Edition
Evaluation Edition (180 days)
Express Edition

I selected Developer Edition and everything worked fine.

Mixed Editions

If I install the SSRS as an Express Edition, then the configuration of the SSRS datbase will fail with the mentioned error message:

Solution

You have to install the SSRS 2017 using the same Edition as your SQL Server 2017 Edition.

If you are running SQL Server 2017 Developer Edition then you have to install SQL Server 2017 Reporting Services with the option Developer Edition.
If you are running SQL Server 2017 Evaluation Edition then you have to install SQL Server 2017 Reporting Services with the option Evaluation Edition.
If you are running SQL Server 2017 Express Edition then you have to install SQL Server 2017 Reporting Services with the option Express Edition.

You will have to de-install your current SQL Server 2017 Reporting Services and ensure you install the identical Edition.

SSRS 2017 Developer Edition with SQL Server 2017 Developer Edition

I had no issues installing SSRS 2017 Developer Edition with SQL Server 2017 Developer Edition.

SSRS Setup Step 1

SSRS Setup Step 2

SSRS Setup Step 3

SSRS Setup Step 4

SSRS Setup Step 5

SSRS Setup Step 6

SSRS Setup Step 7

If the editions match, then you won't have any issues.
If the editions don't match, then you will encounter the error message.


How to enforce the structural constraints of rectangularly arrayed data? (I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and "many-to-many"?)

Much experimental data in biomedical research is collected in "plates" of rectangularly arrayed "wells".  These well array plates are commercially available a few standardized sizes: 2 × 3, 4 × 6, 8 × 12, 16 × 24, and 32 × 48.

Consider the following two alternatives for storing measurements from wells of 2 × 3 plates in an RDB:

-- alternative 1
CREATE TABLE measurement_foo (
    plate_id FOREIGN KEY REFERENCES plate(plate_id),
    plate_row CHAR(1),
    plate_column INTEGER,
    value REAL
);

-- alternative 2
CREATE TABLE measurement_foo (
    plate_id FOREIGN KEY REFERENCES plate(plate_id),
    a1 REAL,
    a2 REAL,
    a3 REAL,
    b1 REAL,
    b2 REAL,
    b3 REAL
);

My instinct is to go with alternative 1: it generalizes to plates of any size, and it can be modified in a straightforward way to record multiple different measurements per well, as in

CREATE TABLE measurement (
    plate_id FOREIGN KEY REFERENCES plate(plate_id),
    plate_row CHAR(1),
    plate_column INTEGER,
    foo FLOAT,
    bar FLOAT,
    baz FLOAT
);

In contrast, to adapt alternative 2 to the 16 × 24-well format (for example), would entail defining a table with 1 + 384 columns: plate_id, a01, ..., a24, b01, ... , b24, ..., p01, ..., p24.  Moreover, a different such table would need to be defined for each type of measurement.

My main reason for even considering alternative 2 is that it alone enforces the constraint that each plate contains exactly 6 wells; alternative 1 does not.

(Also, one record of the table in alternative 2 would hold as much information as up to 6 records of the one in alternative 1, which makes me wonder whether the table in alternative 1 is fully normalized.)

For alternative 1, I can imagine ways to enforce the weaker constraint that a plate contains at most 6 wells, namely, by imposing a uniqueness constraint on (plate_id, plate_row, plate_column), and restricting columns plate_row and plate_column to the domains ('a', 'b') and (1, 2, 3), respectively.  But it is still possible for wells to be missing from the data.

How should the database enforce the structural relationship between plates and wells?

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables.

We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a certain row/column combination exists for a given Plate Type.

Lets create a set of tables:

create table Plate_Types (
Plate_Type_id int,
Plate_Size int,
Plate_row int,
Plate_col int)

This table holds description of every Plate size like this:

Id     Size   Row     Col
1       6       1       1    -- 2x3
1       6       1       2
...
1       6       2       3
5       1536    1       1    -- 32x48
...
5       1536    32      32

Then, in your main table from Alternative 1 we introduce a Foreign Key - a "link" to another table to check if row and column are valid for this Plate size.

create table MyTable (
well_id int,
plate_id int,
plate_size int,
row_id int,
col_id int,
value real);

ALTER TABLE MyTable
ADD CONSTRAINT FK_Plate_SizeCheck
   FOREIGN KEY (Plate_size_id, Row_id, Column_id)
    REFERENCES Plate_Types (Plate_type_id, Plate_row, Plate_col);

This Constraint here does the following: for every inserted row DB goes to table Plate_Types and looks for combination of Plate_size_id, Plate_row and Plate_col. In other words, it checks if this Plate Size can have row I and column J. If there is no match, then the DB fires an error.

Please note that this is one of several possible solutions for data integrity enforcement for your example. Medical data often comes in huge volumes and performance of this particular design is different question.

PS. This is a shortened explanation for non-developers. Code as well as table design is for concept illustration only.


Master/Master/Slave Replication Just trying to get some information on the best route to setup replication for this DB I've got. I'm  using MySQL 5.1

I found this and it seems like the idea we have in mind.

We're looking to have the Main Master be replicated by a Master/Slave and then a final machine replicating from the Master/Slave. That solution sounds wonderful and turning on --log-slave-updates on the Master/Slave machine sounds like what I want.

The question I have is when the Main Master(Master1) goes down we tell the services to write to Master/Slave(Master2) and then what happens with Master1? We rebuild it etc.. but where in the link does it go? Does Master/Slave(Master2) become the new Main Master? And does the Slave only machine get moved to Master/Slave?

Or does Master1 go down and gets rebuild and just becomes Master2(Master/Slave) keeping the Slave machine just that, a slave?

I feel like it's the latter of the 2 options. Just looking for some advice.

Thanks!

Using the topology you mentioned from the MySQL Documentation

Let's setup the first scenario
EXAMPLE IPs for Each DB Server

Master1 : 10.20.30.40
Master2 : 10.20.30.50
Slave_1 : 10.20.30.60
replication user is repluser@'%'
replication password is replpass

Make sure binary logging is enabled on all the Slaves
We will do the following

Promote Master2 to Master1
Promote Slave_1 to Master2
Demote Master1 to Slave1

Step 01 : On Master2, run the following
mysql> SET GLOBAL read_only = 1;
mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO master_host='';
mysql> FLUSH TABLES;
mysql> SET GLOBAL read_only = 0;

Step 02 : Move your CNAME/VIP to Master2
Step 03 : On Slave_1, run mysql> RESET MASTER; FLUSH TABLES;
Step 04 : On Slave_1, dump the data
NEW_MASTER_HOST="10.20.30.60"
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
echo "STOP SLAVE;" > MySQLData.sql
echo "CHANGE MASTER TO master_host='${NEW_MASTER_IP}'," >> MySQLData.sql
echo "master_port=3306," >> MySQLData.sql
echo "master_user='repluser'," >> MySQLData.sql
echo "master_password='replpass'," >> MySQLData.sql
echo "master_log_file='bogus'," >> MySQLData.sql
echo "master_log_pos=1;" >> MySQLData.sql
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
gzip MySQLData.sql

Step 05 : When Master1 comes back up, rsync or scp MySQLData.sql.gz from Slave_1 to Master1
Step 06 : Login to MySQL on the Master1 and setup it up to replicate from Slave_1
Don't worry about the real binary log filename and position.
Using --master-data=1 embeds CHANGE MASTER TO command with the real coordinates on line 23 of a standard dump.
You can see it with the following
less MySQLData.sql.gz | head -35 | tail -1

Step 07 : Load the data into Master1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}

Step 08 : Login to MySQL on Master1
mysql> SHOW SLAVE STATUS\G

and Make sure Slave_IO_Running and Slave_SQL_Running both say Yes
EPILOGUE
When Done, the topology should be

Master1 : 10.20.30.50
Master2 : 10.20.30.60
Slave_1 : 10.20.30.40

DISCLAIMER
Please try this out on Test Servers before deploying to Production
GIVE IT A TRY !!!
Note: I would advise that you have multiples slaves

One for Nightly Backups
The others for load balancing SELECTs


How to combine date and time to datetime2 in SQL Server? Given the following components

DECLARE @D DATE = '2013-10-13'
DECLARE @T TIME(7) = '23:59:59.9999999'

What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'?

Some things which don't work are listed below.

SELECT @D + @T

  Operand data type date is invalid for add operator.

SELECT CAST(@D AS DATETIME2(7)) + @T

  Operand data type datetime2 is invalid for add operator.

SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)

  The datediff function resulted in an overflow. The number of dateparts
  separating two date/time instances is too large. Try to use datediff
  with a less precise datepart.

* The overflow can be avoided in Azure SQL Database and SQL Server 2016, using DATEDIFF_BIG.

SELECT CAST(@D AS DATETIME) + @T

  The data types datetime and time are incompatible in the add operator.

SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)

  Returns a result but loses precision 2013-10-13 23:59:59.997

This seems to work and keep the precision as well:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))

The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment* at CAST and CONVERT page at MSDN.)

* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value (@D).

Test at: SQL-Fiddle

As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED column, the '19000101'** needs to be replaced by 0 or CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day,
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d),
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

**: DATEDIFF(day, '19000101', d) isn't deterministic as it does an implicit conversion of the string to DATETIME and conversions from strings to datetime are deterministic only when specific styles are used.


Mongodb how to show user login history If I created 3 mongodb users: user1, user2, user3, as a mongodb admin with root privilege. Is there any way that I can check the login (as well as usage) history of these users?

Yes (but..). You need to enable auditing, what is (sadly) enterprise feature.


in-place upgrade native SSRS without the SQL Server portion We have a single server (2012R2) running SQL 2008R2 + reporting services native mode 2008R2
Can the reporting services portion be upgraded alone without the database engine?
Or can the SSRS db be migrated off, SQL instance shut down and then SSRS alone upgraded?

Because of licensing concerns I cannot have SQL Server 2016 running but I still need to upgrade the SSRS to 2016.

They require the same license.  So if you can't run SQL Server 2016, you can't run SSRS 2016.


function based index as a conditional unique key? I have a search table which has a generated primary key PK_ID,and also has primary keys from three or four other tables in the FK_ID column

CREATE TABLE METADATA
(
  PK_ID        NUMBER(10)                       NOT NULL,
  OBJ_OWNER    VARCHAR2(35 BYTE)                NOT NULL,
  OBJ_PACKAGE  VARCHAR2(35 BYTE),
  OBJ_NAME     VARCHAR2(35 BYTE)                NOT NULL,
  OBJ_TYPE     NUMBER(10)                       NOT NULL,
  FK_ID        NUMBER(10)                       NOT NULL
);

For example, when OBJ_TYPE = 1 then every FK_ID should be unique for that value of OBJ_TYPE.

I think I can do this with a function based index but the link to Tom Kyte's discussion on this is broken across a few web sites and I'm not sure how.
(Oracle 9.2.0.8)

A composite index will do the job.  A bonus would be if  a function based index could be used as a foreign key for the other tables.  Is this possible?

I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1
                      then fk_id
                      else null
                   end) );


Connecting to SQL Server instance through C# I made a program, using c# and sql server with sql authentication. It works properly in my device, but after making the program setup file .exe extension using Innosetup, I cannot connect to my database.

I've have tried exporting my database in script, and run the script on another device but it does not work. I think I need to create port or something to connect to this database, or in my app.config change the server name

Here is my Connection String

<connectionStrings>
    <add name="MarketDatabaseEntities" connectionString="metadata=res://*/AmanjCenter2Entity.csdl|res://*/AmanjCenter2Entity.ssdl|res://*/AmanjCenter2Entity.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=RABAR;initial catalog=MarketDatabase;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

You may need to use SQL Server Configuration Manager to enable TCP/IP connections.

The default is disabled, as below.  You must enable TCP/IP to allow remote connections to the SQL Server machine.