Mysql sub query issues I have a table where I record a number of values:

channel, cnam (both varchar)
calldate (date/time)
billsec (integer)
disposition (varchar - either 'ANSWERED' or 'NO ANSWER' or 'BUSY')

I need to report on the total number of records and billsec (which is a number of seconds) for certain cnams, along with a breakdown of billsec by disposition, and the average duration.

I have a query that lets me select the correct set of rows to be reported on:

SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS
chan1,cnam,billsec,disposition
FROM asteriskcdrdb.cdr
WHERE calldate >= '2017-12-08 00:00:00' AND calldate <= '2017-12-08
23:59:59' AND (duration-billsec) >=0
HAVING chan1  LIKE 'SIP/3%' or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
ORDER BY chan1 asc

This returns results like the following:

chanl     | cnam     | billsec  | disposition
----------+----------+----------+----------------
SIP/2000  | foo      |    180   |  ANSWERED
SIP/2000  | foo      |     30   |  NO ANSWER
SIP/2000  | foo      |     60   |  ANSWERED
SIP/3010  | foo foo  |     2    |  BUSY
SIP/3010  | foo foo  |     3    |  BUSY
SIP/3010  | foo foo  |    240   |  ANSWERED
SIP/3020  | foo2     |     30   |  ANSWERED
SIP/3020  | foo2     |    180   |  ANSWERED
SIP/3020  | foo2     |     10   |  NO ANSWER
...

I have the start for how the query for my final results would go:

SELECT cnam,
       sum(disposition) as TOTAL,
       sum(disposition where disposition = 'ANSWERED') as COMPLETED,
       sum(disposition where disposition = 'NO ANSWER' OR case when
       disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as 'Total DURATION',
       SEC_TO_TIME(avg(billsec)) as 'AVG DURATION'

My final results should look like this:

CNAM    | TOTAL | COMPLETED | MISSED | Total DURATION | AVG DURATION
--------+-------+-----------+--------+----------------+---------------
foo     |    3  |         3 |      1 |           1:30 |         0:32
foo foo |    3  |         3 |      2 |          03:05 |         1:02
foo2    |    3  |         2 |      1 |          03:34 |         1:12

I even know I can use a subquery to combine all this together. What I can't quite figure out is how to do that, given all the stuff that's in my initial query.

Without the DDL and sample (raw) data, and not sure how you go from the first result set having all cnam = "foo foo" to the second result set having CNAM = "foo" or "foo2" ...

You could start with something like ...

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED' then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

from   (SELECT SUBSTRING(channel,1, LOCATE("-",channel, LENGTH(channel)-8)-1) AS chan1,
               cnam,
               duration,
               disposition

        FROM   asteriskcdrdb.cdr

        WHERE  calldate >= '2017-12-08 00:00:00'
        AND    calldate <= '2017-12-08 23:59:59'
        AND    (duration-billsec) >=0
        HAVING chan1 LIKE 'SIP/3%'
            or chan1 IN ('SIP/2000','SIP/2016','SIP/2046')
       ) dt

group by cnam
order by cnam asc

... keep in mind that I'm not 100% sure of your logic (eg, should total/avg duration be based only on COMPLETED data?) so I mostly cut-n-pasted what you had originally.

OK, thinking about this a bit more ...

The derived table isn't really doing anything other than filtering out rows from the base table, which means the derived table could be eliminated.

One possible flattening of the query (using RDFozz's modified WHERE clause):

select cnam,
       count(disposition) as TOTAL,
       sum(case when disposition = 'ANSWERED'  then 1 else 0 end) as COMPLETED,
       sum(case when disposition = 'NO ANSWER' then 1 when disposition = 'BUSY' then 1 else 0 end) as MISSED,
       SEC_TO_TIME(sum(billsec)) as "Total DURATION",
       SEC_TO_TIME(avg(billsec)) as "AVG DURATION"

FROM   asteriskcdrdb.cdr

WHERE  calldate >= '2017-12-08 00:00:00'
AND    calldate <= '2017-12-08 23:59:59'
AND    (duration-billsec) >=0
AND    (   channel LIKE 'SIP/3%'
        OR channel LIKE 'SIP/2000-%'
        OR channel LIKE 'SIP/2016-%'
        OR channel LIKE 'SIP/2046-%')

group by cnam
order by cnam asc


Invalid use of group function For this query, I'm trying to average the price of all orders for a given restaurant. It works perfectly fine for the sum:

SELECT r.restaurantName, SUM(m.price) AS 'totalPrice w/o Tax', SUM(m.price *1.10) AS 'totalPrice w/ Tax'
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN FoodOrder f
ON m.itemNo = f.itemNo
WHERE r.restaurantName = "Eureka Pizza";

But when I try to average that I get an error:

SELECT r.restaurantName, SUM(m.price) AS 'totalPrice w/o Tax', SUM(m.price *1.10) AS 'totalPrice w/ Tax', AVG(SUM(m.price)) AS 'avg base price'
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN FoodOrder f
ON m.itemNo = f.itemNo
WHERE r.restaurantName = "Eureka Pizza";

You just need AVG(m.price) in your select list.


What is the optimized way to pass multiple parameter from a single table to a function? I have a function [dbo].[FN_CalcAerialDistance] which accepts 4 parameters and returns its result.
Now that 4 parameters come from the same table, I do not want to hit Database multiple times to bring same data.
I have tried:

SELECT [dbo].[FN_CalcAerialDistance]
    (
       (select latitude from loc.locations where place_name = 'Delhi'),
       (select longitude from loc.locations where place_name = 'Delhi'),
       (select latitude from loc.locations where place_name = 'Mumbai'),
       (select longitude from loc.locations where place_name = 'Mumbai')
    )

  Any way to optimise the code? I have also tried to access table datatype like an array

As a complement to Edgars answer you can do it without declaring any variables as:
SELECT dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name = 'Delhi'
  AND y.place_name = 'Mumbai' ;

I wouldn't expect it to perform any better though.
In addition you can calculate several distances at once
SELECT x.place_name
     , y.place_name
     , dbo.fn_calcaerialdistance (x.latitude
                                 ,x.longitude
                                 ,y.latitude
                                 ,y.longitude)
FROM loc.locations as x
CROSS JOIN loc.locations y
WHERE x.place_name in ('Delhi', 'Bangalore')
  AND y.place_name = 'Mumbai' ;


SQL Server Audit Clutters Error log I have a Server level Audit running and informational messages are continuously placed in the SQL Error log (every hour):

Audit: Server Audit: 65976, Initialized and Assigned State: STARTED
Audit: Server Audit: 65976, Initialized and Assigned State: START_FAILED
Audit: Server Audit: 65975 Session has been closed
Audit: Server Audit: 65975, State changed from: STARTED to: SHUTTING_DOWN

My question is, why does this happen and more important can this be prevented (while continuing the audit)?

This isn't clutter. You need to figure out why the audit is failing, and resolve it.


Autorise computer accounts through AD group I would like to grant SQL access for computer accounts.
It works if I declare a login for each account, like CREATE LOGIN [contoso.com\srv1$] FROM WINDOWS

but I can't make it works when using a AD security group containing the computername.
Say SRV1 is member of contoso.com\server_group , and I create a login for it like this CREATE LOGIN [contoso.com\server_group] FROM WINDOWS
. Now, trying to request the DB with the computer account the following error is raised :
Error get database Exception calling "Open" with "0" argument(s): "Login failed for user 'contoso.com\srv1$'."

Any idea ?

Found it! The issue occured because  we have set the group membership too early (before the computer login once to Active Directory). If we set group membership after a first login it is working as expected


Is there a name for this "restricted one to many" relationship? Can it be enforced in the DB? In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level.

A standard one-to-many might be people to pets. One person can have many pets, and each pet is of a specified species. You can have 0 pets, or 2 dogs and a cat, or just a parakeet, etc.

In our case, we want to say something like "One person can have many pets, but no more than one of each type." You can have 0 pets, or just a cat, or a dog and a parakeet, but you can never have 2 of any species.

I can only see two ways to do this:

Have a column on people for each possible pet's foreign key - a cat_id and a dog_id, etc. This would mean a lot of NULLs on the table, though.
Make a standard one-to-many, where each row in pets has a foreign key to people, and enforce the uniqueness by species using code external to the database.

Any alternate ideas?

I don't know that there's an official or technical name for the relationship you're describing (I also don't know if you'll find many pet lovers who would like to be restricted to one dog, one cat, etc.), but here is how I would probably approach this:

CREATE TABLE dbo.People
(
  PersonID INT PRIMARY KEY
  --, ... name, etc.
);

CREATE TABLE dbo.PetTypes
(
  PetTypeID INT PRIMARY KEY
  --, ... species name, etc.
);

CREATE TABLE dbo.PeoplePets
(
  PersonID INT, -- FK to dbo.People
  PetTypeID INT, -- FK to dbo.PetTypes
  --, ... pet name etc.
  PRIMARY KEY (PersonID, PetTypeID)
);

Now whether this is simple enough, I'm not sure. You might have different attributes for different types of pets, and so you may need to extend this, but I think in general the concept is sound.


Set a check constraint for a set of conditions which aren't universally true I need to make sure my database is self-consistent. There are two columns, which share a relationship when there is some specific value the other should be not null. I tried using constraint check (status = 2 and PatientId is not null), but it returns

SQL Error [4025] [23000]: (conn=20) CONSTRAINT `pId_status` failed for `prod`.`sch`

since I need an specific check for every value of status and some of them aren't neither for null or the PatientId column. I'm using mariadb and I'm aware that checks are not ignored as mysql.

Other status values may or may not accept patientid to be null, so this check should first make sure that status is 2 and then if patientid is not null. Status is not nullable. The above is just the simplest check that should conform business logic, since that status always have a patientid.

Since the status column is not nullable, the check constraint expression in your own solution

case
  when status = 2 and PatientId is null then false
  else true
end

is equivalent to this more concise expression:

not (status = 2 and PatientId is null)

Applying De Morgan's laws, you can transform it further:

        not (status = 2 and PatientId is null)   →

    →   not (status = 2) or not (PatientId is null)   →

    →   status <> 2 or PatientId is not null

which is what McNets suggested in the comments.

Using the final expression, your check constraint definition would look like this:

alter table tbl
  add constraint cons_name
  check (status <> 2 or PatientId is not null);


Way to test alerts for backups? We have some SQL Servers that are backed up using T-SQL and some that are using 3rd party backups (like Egnyte).

Is there a way to setup alerts inside SQL Server for that? I also need a way to test so we can be sure the alerts are working.

Any help is appreciated.

Note: This is for Microsoft SQL Server.

For testing purpose, I downloaded a corrupt database backup file from SQLSkills.com

Below is a quick script that will help you (obviously you can adapt the logic to do it dynamically for all your databases and send email in a nice HTML format) :

begin try
    BACKUP DATABASE broken TO DISK='D:\broken2005\broken2.bck'
    WITH CHECKSUM  --- very Important
end try
begin catch

    declare @errorSubject char(100)
    declare @errorBody varchar(max)

    set @errorSubject = 'Full backup failed for database Broken on ' +@@servername
    set @errorBody = 'Full backup failed '+ char(10)+Cast(Error_Number() as nvarchar)+' - ['+Error_Message()+']'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'databaseMail Profile',
    @recipients ='DBATeamEmailAddress@somecompany.com',
    @body = @errorBody,
    @subject = @errorSubject

end catch

Alternatively, you can use SQL Server Backup solution from Ola Hallengren.


High Cardinality Time Series Database? I have data that is being collected every 30 minutes from a website. This data looks like the following. The "epoch" row labels are placeholders for actual epoch timestamps of each sample. The columns of names along the top would expand indefinitely as more unique names are found.

table n (multiple tables of data like this)
-------------------------------------------
        James   Tom   Jeff   Leon   Sarah  [...]
epoch1  1       44    22     NULL   3
epoch2  4       33    23     1      NULL
epoch3  5       32    22     2      NULL
...
epochn  x       x     x      x      x

What I'm doing is scraping the website for anything classified as a name by an NLP library, and recording frequencies for the top 40 names found. So I scrape the entire "page" of this website and find that there are 22 instances of the name "Jeff", then record it in the table at Jeff/Epoch1 with frequency 22.

I already have the name tagger working, but it's just a matter of finding something that could handle this sort of data and pull metrics like "10 most popular names in table 4 at epoch between 24 hours ago and now". I've looked at PostgreSQL but it has a conservative column limit. Ideally columns would be added for each new name found, but this doesn't seem feasible with the databases I've found so far.

Anyone know of any open-source software that could store this data, or best practices?

Just normalize it.

crawl-
crawl_id|crawltime
1       |2018-01-02 2:47 PM

terms-
crawl_id | term | count
1        | Joe  | 15


How can I make this DECLARE PROCEDURE working? I'm new to Oracle DB, coming from MS SQL Server.
I created this script:
DEFINE USER_NAME = &1
DEFINE PASSWORD = &2
DEFINE TABLESPACE = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

DECLARE PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
BEGIN
  SELECT COUNT(*)
    INTO dbCount
    FROM CDB_USERS u
      INNER JOIN v$pdbs d ON u.CON_ID = d.CON_ID
    WHERE d.NAME = '&DB_NAME'
      AND u.USERNAME = userName;

  IF dbCount > 0
    THEN
      EXECUTE IMMEDIATE 'DROP USER ' || userName || ' CASCADE';
      DBMS_OUTPUT.PUT_LINE('User ' || userName || ' dropped.');
    END IF;

  createStmt := 'CREATE USER ' || userName || ' IDENTIFIED BY ' || password || ' DEFAULT TABLESPACE ' || tSpace || ' QUOTA UNLIMITED ON ' || tSpace;
  DBMS_OUTPUT.PUT_LINE('User about to be created:');
  DBMS_OUTPUT.PUT_LINE(createStmt);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('Creating user ' || userName || ' ...');
  EXECUTE IMMEDIATE createStmt;
  DBMS_OUTPUT.PUT_LINE('Database ' || userName || ' successfully created.');

  END Create_User;

BEGIN
  Create_User('&USER_NAME', '&PASSWORD', '&TABLESPACE');
  END;
  /

When I run it in SqlPlus, like this:
SQL> DEFINE DB_NAME = 'MYDB'
SQL> DEFINE DB_ADMIN = 'SA'
SQL> DEFINE DB_PWD = 'pwd'
SQL> @/db-install/sql/add-user.sql 'USERNAME' 'pwd' 'TSPACE'

I get this error message:
Connected.
DECLARE PROCEDURE Create_User
*
ERROR at line 1:
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36

Can someone please point me to the right direction? What am I doing wrong?
Your answer is appreciated.

EDIT
I have this exact same code in another script, and it's working fine there:
DEFINE DB_NAME = &1
DEFINE DB_ADMIN = &2
DEFINE DB_PWD = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

BEGIN
  DBMS_OUTPUT.PUT_LINE('Adding tablespaces to database ...');
  END;
  /

--- Procedure: dropping and creating a given tablespace ---
DECLARE
  dbCount INTEGER;
  createStmt VARCHAR2(500);
  filePath dba_data_files.FILE_NAME%TYPE;
PROCEDURE Create_TS
...

EDIT 2
It doesn't make a difference if I put the variables between DECLARE and PROCEDURE:
DECLARE
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
BEGIN

Connected.
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36

And that is a great example why you should never name your PL/SQL variables the same as column names.
set serveroutput on
DECLARE
  PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
BEGIN
  SELECT COUNT(*)
    INTO dbCount
    FROM DBA_USERS u
    WHERE u.USERNAME = userName;
  dbms_output.put_line('User count: ' || dbcount);
end;
begin
  create_user('abc', 'abc', 'abc');
end;
/

User count: 26

The u.USERNAME = userName filter is username = username, which returns all rows where username is not null.


Is there an easy way to debug and find out why a query runs slow? I'm running onto a weird issue.

We've got two db servers, one is development and the other one is production.

Both have same specs.

VMs (Hyper-V)
Windows Server 2008 R2 64 Bit
SQL Server 2008 R2 64 Bit

If I run the exact same query on our dev server it takes 10 seconds to give me 10 results. The same query in the production server takes 1:30 minutes sometimes more.

Is there an easy way to find the source of the problem? Is it something related to indexes, workload?

This is just one example, the queries are dynamically generated, this is used by our web application and it generates queries on an advanced search menu

EDIT Added Query

SELECT Client_Id, Project_Id, Person_Id, Membership_No, Family_Name,
       First_Name, Password, Company, Country_Name, EMail, Member_Type,
       Member_Type_Description, Member_Status, Member_Status_Description,
       Category, Member_Category_Description, Balance, Date_Registered
FROM uvw_Members_Display
WHERE Client_Id='EHA'
  AND Project_Id='EHA'
  AND ( Client_Id='EHA'
    AND Project_Id='EHA'
    AND (  (( uvw_Members_Display.Member_Type LIKE '01' ))
        OR ( uvw_Members_Display.Member_Type LIKE '02' )
        OR ( uvw_Members_Display.Member_Type LIKE '03' )
        OR ( uvw_Members_Display.Member_Type LIKE '04' )
        )
    AND ( uvw_Members_Display.Member_Status LIKE 'I' )
    AND ( Balance =0 )
    AND ( uvw_Members_Display.Category LIKE '02' )
    AND Date_Registered IS NOT NULL
      )
ORDER BY Person_Id DESC

Thanks to mrdenny! Your commment pointed me in the right direction!

I've pasted the query into SQL Management Studio

Then used the option Display Estimated Execution Plan

This gave me an output that an index in one of the tables belonging to the view needed to be recreated, so I deleted it and create it from scratch using the suggested output.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MemberOrders] ([Client_Id],[Project_Id],[Person_Id])

Now the query takes only 2 seconds and not the 1:30min that it used to take!

Thanks a lot guys


SQL Server 2012 replication permissions with virtual user and agent credentials For development/testing I set up replication in SQL Server 2012 RC0 (11.0.1750) using the default permissions on the agent, and I'm getting this error:

  Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\ReplData\unc\WKSTATION$SQL2012_DB7_DB7\20120104171149\' is denied.

I'm having trouble figuring out what MS wants me to do in this case since I want to use the agent credentials. How do I give permissions to that folder if I can't see the virtual user (Nt Service\SQLAgent$SQL2012) in my local machine's user list?

IT kind of seems like the Agent account didn't have access to that filepath.  On our network permissions to the Program Files directory are very restricted.  When you created the new folder you probably changed the security on the new folder, whether realizing it or not, and that allowed the account for the replication to reach that folder.


Design for hierarchical embedded database The goal is to construct a hierarchical database of Objects having the following format:

TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.]
TypeB -> ObjectB [values, contains ObjectC*, ObjectD*, etc.]
TypeC -> ObjectC [values, ...]
TypeD -> ObjectD [values, ...]
TypeE -> ObjectE [values, ...]

edit: Each type can contain multiple objects of other types. eg. ObjectA can contain Multiple typeB, typeC. etc.

It is fixed and known what kind of values and objects each Type[A,B,C,...] may contain.
 Select or searches can be on on all objects of a certain type, and the system would construct an object containing all the sub-objects referenced by that object.
Example: select all objects of TypeA -> returns :

ObjectA {
  values,
  ObjectB { values, ObjectC, ObjectD, etc. }
  ObjectC { values, ... }
  ...
 }

Doing this would mean a large number of nested select queries on the table. This being an embedded database, it is needed that the number of queries be minimum and there be no large joins, is there any way to partition the table or create indexes or a design principle that would help in optimizing the queries?

With the restrictions you have supplied and if you want to implement this is DBMS, I think you could use a supertype/subtype pattern for the 5 (or more) types of objects and only one additional table for the "object contains objects" list:

-- auxiliary table that has only 5 rows, one for each type
CREATE TABLE types
( level TINYINT NOT NULL PRIMARY KEY
, type_name VARCHAR(10) NOT NULL UNIQUE
) ;

INSERT INTO types (level, type_name)
VALUES (1,'A'), (2,'B'), (3,'C'), (4,'D'), (5,'E') ;

-- this is the supertype
CREATE TABLE objects
( object_id INT NOT NULL UNIQUE
, level TINYINT NOT NULL REFERENCES types (type_id)
, UNIQUE (level, object_id)
) ;

-- and the subtypes
CREATE TABLE a
( object_id INT NOT NULL PRIMARY KEY
, level TINYINT NOT NULL CHECK (level = 1)
--- various columns
, FOREIGN KEY (level, object_id)
    REFERENCES objects (level, object_id)
) ;

--- and similarly for the other 4 tables
--- all referencing objects table

and finally the list table, which is simple many to many junction table with only an additional constraint to ensure that an object of higher level can contain only objects of lower levels:

CREATE TABLE contains
( container_level TINYINT NOT NULL
, container_id INT NOT NULL
, item_level TINYINT NOT NULL
, item_id INT NOT NULL
, PRIMARY KEY (container_id, item_id)
, FOREIGN KEY (container_level, container_id)
    REFERENCES objects (level, object_id)
, FOREIGN KEY (item_level, item_id)
    REFERENCES objects (level, object_id)
, CHECK (container_level > item_level)
) ;

SQLite has CHECK constraints and has recently implemented recursive CTEs, if you want to get the results of an object and all the objects underneath it in one query.

From a fast search, H2 also supports recursive CTEs but Derby does not.


Monthly summary of active users based on a start/end date roster I have a table that dictates who was on a project, from a startdate to an enddate. I'd like some help writing a query that will return the number of "active" users at the end of every month, since the start of this year.
DROP TABLE "public"."roster";
CREATE TABLE "public"."roster" ("id" int,"user_id" int,"project_id" int,"start_date" datetime,"end_date" datetime,"closed_date" datetime, PRIMARY KEY ("id"));

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(1, 1, 1, '2019-05-27 00:00:00', '2021-01-15 00:00:00', NULL);

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(2, 2, 2, '2020-05-27 00:00:00', '2021-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(3, 3, 3, '2020-05-27 00:00:00', '2024-02-01 00:00:00', '2021-02-05 00:00:00');

INSERT INTO "public"."roster" ("id", "user_id", "project_id", "start_date", "end_date", "closed_date") VALUES
(4, 4, 4, '2020-05-27 00:00:00', '2021-03-05 00:00:00', NULL);

id  user_id project_id  start_date            end_date              closed_date
1   1       1           2019-05-27 00:00:00   2021-01-15 00:00:00   NULL
2   2       2           2020-05-27 00:00:00   2021-02-01 00:00:00   2020-02-05 00:00:00
3   3       3           2020-05-27 00:00:00   2024-02-01 00:00:00   2020-02-05 00:00:00
4   4       4           2020-05-27 00:00:00   2021-03-05 00:00:00   NULL

And the result would be something that shows how many distinct users had an active project within each month (only since the start of this year).
So for the dataset above, we can see that all 4 projects were "active" in the month January 2021, because the end date is in the future.
2021-01-31 | 3 (4 projects were active in some way, during January)
2021-02-28 | 2 (3 projects were active in some way, during February)
2021-03-31 | 1 (1 project was active in some way, during March)

The last bit of complexity is that sometimes a project can be closed before the EndDate and I'd like to exclude any users who's endDate is in the future but the project has actually closed.
For example in the dataset above, the third project has an end date of Feb 2024, however the project closed in Feb 2021. So technically the person was active in 2021 Jan and Feb, but not March 2021 and beyond.
p.s Hoping to get an answer for Redshift

p.s Hoping to get an answer in Postgres (I'm using Redshift)

Solution for PostgreSQL:
WITH cte AS ( SELECT '2021-01-01'::DATE AS month_start, '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-01', '2021-02-28' UNION ALL
              SELECT '2021-03-01', '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date <= month_end
  AND LEAST(end_date, closed_date) >= month_start
GROUP BY cte.month_end
ORDER BY cte.month_end;

or
WITH cte AS ( SELECT '2021-01-31'::DATE AS month_end UNION ALL
              SELECT '2021-02-28' UNION ALL
              SELECT '2021-03-31' )

SELECT cte.month_end, COUNT(*)
FROM roster
CROSS JOIN cte
WHERE start_date <= month_end
  AND LEAST(end_date, closed_date) >= DATE_TRUNC('month', month_end)
GROUP BY cte.month_end
ORDER BY cte.month_end;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=710260e9fa44a89cc9c2d536739f7c92
Replace cte code with the code which you need (generate first and last days of months for the months in interest in the first variant or only months last days in the second variant). For example, use dates generator, start from YYYY-MM-01 of the next month for a month in interest, step as a month, and substract - a month for first day of month and a day for the last day.


Is innodb_file_per_table advisable? We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with  innodb_file_per_table or leave just as one .ibd? I read some articles say do not go with it as you need more disk access when there joins to be performed? We will have join between this table and others for reporting generation purposes.

You must go with innodb_file_per_table and you need to do some how cleaning with InnoDB's current infrastructure.

I have seen many DB hosting clients setup MySQL and leave InnoDB in its default state. This causes the system tablespace (better known as ibdata1) grow wildly.

Even if you switched to innodb_file_per_table, the .ibd file would have to be extracted from ibdata1 and ibdata will never shrink. For example, if you have a table called mydb.mytable that is inside ibdata1 taking up 2GB, to extract it you have to the following:

STEP 01) Add this to /etc/my.cnf

[mysqld]
innodb_file_per_table

STEP 02) service mysql restart

STEP 03) ALTER TABLE mydb.mytable ENGINE=InnoDB;

That will make the file /var/lib/mysql/mydb/mytable.ibd

Unfortunately, the 2GB of space that was occupied by the table before the change cannot be reclaimed. I wrote past posts about how and why to cleanup InnoDB's infrastructure:

Why does InnoDB store all databases in one file?
Scheduled optimization of tables in MySQL InnoDB
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
My Original Post in StackOverflow

Once you make this major change, don't forget to increase innodb_open_files (default 300). Otherwise, disk access is very limited.

With regard to joins, make sure you have proper indexes that support the join criteria.

UPDATE 2012-04-02 11:30 EDT

Using innodb_file_per_table in a fresh installattion cause ibdata1 to grow very slowly because all DDL is done external to ibdata. You can shrink any InnoDB table as I mentioned before like this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

UPDATE 2012-04-02 16:50 EDT

When it comes to backups, be extremely careful making copies of .ibd files. Why?

Inside every .ibd file is special value known as the tablespace_id. There is a list of tablespace_id values in ibdata1. If you ever perform table maintenance that requires dropping and recreating the table, the tablespace_id will become different. Making a copy of such  a .ibd file can only be intergated back into the database for usage if you also make a copy of ibdata1. That endangers the tablespace_id of all other InnoDB tables. In light of this, it is preferable that you perform mysqldump backups because mysqldumps are logical copies of the data. In other words, the backup is independent of the point-in-time of the ibdata1 and you are free to reload without operability issues.


How do I query for leaf nodes in a hierarchy using a closure table? I have some hierarchical data (representing musical instruments and playing techniques), where each leaf in the hierarchy is an audio file.

The nesting depth of leaf nodes will vary because some playing techniques will have sub-articulations.

Example paths to leaf nodes could be

/philharmonia/trumpet/mute/muted_trumpet1.wav
/philharmonia/trumpet/legato/attack/legato_trumpet1.wav
/ircam/violin/pizz/violin_pizz1.wav

I have the data stored using a master table and closure table:

sqlite> PRAGMA table_info(as_node);
0|id|INTEGER|0||1
1|name|TEXT(1024)|1||0
2|file|INTEGER|0||0
3|parent|INTEGER|0||0

sqlite> PRAGMA table_info(as_node_closure);
0|id|INTEGER|0||1
1|ancestor|INTEGER|0||0
2|descendant|INTEGER|0||0
3|depth|INTEGER|0||0

How can I query the database to select all leaf nodes where an ancestor node matches a given string at given nesting level?

For example in plain English: "give me all leaf nodes where ancestor node is 'trumpet' at a nesting level of 2"

With the above data, this should return muted_trumpet1.wav and legato_trumpet1.wav.

UPDATE 1

Some example data:

sqlite> select * from as_node
1|BrassP||
2|4Horns||1
3|FT||2
4|DYN_02||3
5|RR_01||4
6|Mic_CL||5
7|4H_CL_DYN2_FT_01.wav|1|6
8|Mic_FM||5
9|4H_FM_DYN2_FT_01.wav|2|8
10|Mic_RM||5
11|4H_RM_DYN2_FT_01.wav|3|10
12|Mic_SUR||5
13|4H_SURR_DYN2_FT_01.wav|4|12
14|DYN_03||3
15|RR_01||14
16|Mic_CL||15
17|4H_CL_DYN3_FT_01.wav|5|16
...

sqlite> select * from as_node_closure limit 10;
1|1|1|0
2|1|2|1
3|2|2|0
4|1|3|2
5|2|3|1
6|3|3|0
7|1|4|3
8|2|4|2
9|3|4|1
10|4|4|0

UPDATE 2:

This gives me pretty much what I want, but I'm sure it it's not the right way:

SELECT name from as_node WHERE id IN (SELECT descendant FROM as_node_closure WHERE ancestor IN (SELECT id FROM as_node WHERE name = "trumpet") AND descendant IN (SELECT id FROM as_node WHERE name LIKE "%.wav"));

This uses the inner SELECT to get all of the leaf nodes and return only descendants in that list.

I think this is what you want (edited):

-- ml : short name for middle-to-leaf connection
-- rm : short name for root-to-middle connection

SELECT                                     -- show me
    leaf.*                                 -- all
FROM
    as_node AS leaf                        -- leaf nodes
  JOIN as_node_closure AS ml               -- which have an ancestor
    ON  ml.descendant = leaf.id            --
  JOIN as_node AS middle                   -- (let's call it middle)
    ON  ml.ancestor = middle.id            --
    AND middle.name = 'trumpet'            -- with the name 'trumpet'
  JOIN as_node_closure AS rm               -- and this middle
    ON  rm.descendant = ml.ancestor        -- has another ancestor
  JOIN as_node AS root                     -- (which we call root)
    ON  rm.ancestor = root.id              -- which is indeed a root node
    AND root.parent IS NULL                -- as it has no parent
WHERE                                      -- and the distance from root to middle
      rm.depth = 2                         -- is 2 (so middle's absolute level is 2)
  AND leaf.name LIKE '%.wav'
  AND NOT EXISTS                           -- and also the leaf is indeed a leaf node
      ( SELECT *                           -- as it has no children
        FROM as_node AS extra
        WHERE extra.parent = leaf.id
      ) ;

Test at SQL-Fiddle (where 'FT' is used instead of 'trumpet', to match your data.)


Why would SQL cast have to be expensive if used on a join column? I see that sql join internally functions always on some of the three basic operations : nested loops join, merge join and hash join.

From multiple articles on the internet I see SQL casting is said to be able to impact performances severely and almost always the reason is it disables index usage.

But to me it looks it could always be avoided:
Casting could disable merge join usage (and merge benefits from index usage) but hash join, that is said to be generally faster than merge, can still be utilized in the same way(looks to me). No matter in which phase of the hash join (build of probe) casting is applied, all that has to be additionally done is casting, before hashing, for the column casted.

Hash join, anyway, passes every row of the first table once, same as for the second table.
Hence, from my aspect, casting of join columns, applied to hash join, has not performance impact but the casting operation itself. And the guys on the internet say casting severely impacts performances since indexes cannot be used?

Is something wrong in my thinking above?
Thank you for the time

CAST or any other function is expensive when used in a JOIN condition because it makes the transformed column(s) be non-SARGable.

The database has no way to know what the output of the function will be until it runs the function.  This is as true for CAST as it is for a UDF that you write which does some fancy string manipulation logic.

The condition being non-SARGable means that the database can't use a search argument to find the value - it has to run it on every single row that matches the other criteria in the query.

For this reason, functions should be avoided in any filtering logic - JOINs, WHERE clauses, etc. wherever possible, as it essentially forces a table/index scan.


SQL Server Replication - Unable to start agent job - Username or Password Incorrect I have recently tried to setup replication. We are using the following topology:

Publisher Server -> Distributor Server -> Subscription Server

We currently have this setup using a different set of servers and it works without issue and i have used this set of servers as my template, so to speak.

With my new set of servers, i have been receiving the following SQL Agent error message when trying to run my initial snapshot from Publisher to Distributor:

Unable to start execution of step 2 (reason: Error authenticating proxy MyDomain\MyUserAccount, system error: The user name or password is incorrect.)

I have confirmed that i have the valid credentials regarding my user account and that it has sufficient access to the SQL instances in question. Additionally, it can also write to the snapshot folder share.

I have even ripped everything out and started a fresh, with a new user account.

I have tried to setup my publication using the existing distributor server we have and it went straight through without issue.

I have Googled this error message a lot, and nothing seems to have worked or be relevant to my issue at hand.

Any pointers would be greatly appreciated.

We have literally just solved this issue.

It turns out, the SQL agent was configured to run as my user in the following format:

Myuser@domain.com

I simply changed this to use the following format:

MyDomain\Myuser

It suddenly just started working once I changed that.


Parse a string to find data between delimiters, multiple occurrences I have a SQL Server table with one specific nvarchar column (let's assume it's called details) having data like below:

Item1: A100 ; Item2: B200; Item3:C300: Item4:D400; Item5:E500; Item6: F600600600; Item7: ;Item8: H800

I am looking to parse this specific column details in the table and store each item value in individual columns through a SQL view so that I can use it for querying individual elements/reporting.

The item labels/descriptions Item1, Item2 etc will remain the same, so I may not need to extract that. Their number is expected to be fixed as well.

However I need to extract each item value between the : and ;  and have them assigned to Item1value, Item2value in my view.
And sometimes some values may be simply null like shown in the sample above.

create view v_name as
select ___ as Item1Value, ___ as Item2Value...
from details

How can this be accomplished?

This is a pretty boring answer, but if you are guaranteed to always have your data in that format then you can get your desired results with a bunch of CHARINDEX and SUBSTRING calls. The idea is that if you find all of the positions of the labels you can take advantage of their fixed lengths to use those positions in the arguments of the SUBSTRING calls.

By the way, it looks like you have a few typos in your sample data. I've attempted to fix them so pay special attention to my test data.

CREATE TABLE #DETAILS (TEST_STRING VARCHAR(1000));

INSERT INTO #DETAILS VALUES ('Item1: A100 ; Item2: B200; Item3:C300; Item4:D400; Item5:E500; Item6: F600600600; Item7:; Item8: H800');
INSERT INTO #DETAILS VALUES ('Item1:; Item2:; Item3:; Item4:; Item5:; Item6:; Item7:; Item8:');

SELECT
  SUBSTRING(TEST_STRING, 7, P2 - 7) ITEM1VALUE
, SUBSTRING(TEST_STRING, P2 + 8, P3 - P2 - 8) ITEM2VALUE
, SUBSTRING(TEST_STRING, P3 + 8, P4 - P3 - 8) ITEM3VALUE
, SUBSTRING(TEST_STRING, P4 + 8, P5 - P4 - 8) ITEM4VALUE
, SUBSTRING(TEST_STRING, P5 + 8, P6 - P5 - 8) ITEM5VALUE
, SUBSTRING(TEST_STRING, P6 + 8, P7 - P6 - 8) ITEM6VALUE
, SUBSTRING(TEST_STRING, P7 + 8, P8 - P7 - 8) ITEM7VALUE
, SUBSTRING(TEST_STRING, P8 + 8, 8000) ITEM8VALUE
FROM
(
    SELECT
      TEST_STRING
    , CHARINDEX('; Item2:', TEST_STRING) P2
    , CHARINDEX('; Item3:', TEST_STRING) P3
    , CHARINDEX('; Item4:', TEST_STRING) P4
    , CHARINDEX('; Item5:', TEST_STRING) P5
    , CHARINDEX('; Item6:', TEST_STRING) P6
    , CHARINDEX('; Item7:', TEST_STRING) P7
    , CHARINDEX('; Item8:', TEST_STRING) P8
    FROM #DETAILS
) t;

Results (bottom row is all nulls as expected):

╔════════╦═══════╦═══════╦═══════╦═══════╦═════════════╦═══════╦═══════╗
║ ITEM1  ║ ITEM2 ║ ITEM3 ║ ITEM4 ║ ITEM5 ║    ITEM6    ║ ITEM7 ║ ITEM8 ║
╠════════╬═══════╬═══════╬═══════╬═══════╬═════════════╬═══════╬═══════╣
║  A100  ║  B200 ║ C300  ║ D400  ║ E500  ║  F600600600 ║       ║  H800 ║
║        ║       ║       ║       ║       ║             ║       ║       ║
╚════════╩═══════╩═══════╩═══════╩═══════╩═════════════╩═══════╩═══════╝

The above code will not work if you don't always have exactly eight items and you don't always have a space between ; and the next item label (except for label 1 of course). Also note that your item values cannot contain certain values or the code will throw an error. You said that you just want to get the data between : and ; and generally delimiters should not show up in data, but here's an example of data that would cause an issue:

INSERT INTO #DETAILS VALUES ('Item1:; Item7:; Item2:; Item3:; Item4:; Item5:; Item6:; Item7:; Item8:');


Change port number for SQL 2012 Cluster I have been trying to change the port number of a SQL 2012 cluster with little success.  I had thought that I could just do this the same way as normal through configuration manager however, even with the services down in the cluster and changing the port on both nodes the old port number remains.

I have started reading about the need to remove and then add in checkpoints for the cluster but have been unable to find anything relating to SQL 2012.

I am assuming I need to use the PowerShell commands for Remove-ClusterCheckpoint and Add-ClusterCheckpoint but if I am honest I am not clear what parameters I should be using.

Has anyone done this for SQL 2012 on Windows 2012 R2?

Cluster instances use registry keys checkpointing to ensure that all nodes in the cluster share the same configuration. Checkpointing works while the resource (the SQL Server Service in this case) is online. When the resource is offline, checkpointing doesn't happen.

In the context of your issue, just change the port from the configuration manager on the active node while the service is running.

As an alternative, you can edit the contents of the checkpoint file directly, as described in the section "Method 1" in this page.

As far as removing/adding checkpoints with PowerShell is concerned, the syntax is straightforward:

Get-ClusterCheckpoint #lists all checkpoints

#remove checkpoint
Remove-ClusterCheckpoint -ResourceName "SQL Network Name (InstanceName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceId\MSSQLServer"

#add checkpoint
Add-ClusterCheckpoint -ResourceName "SQL Network Name (InstanceName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceId\MSSQLServer"


Oracle alter table modify column from NVARCHAR2 to VARCHAR2 Having a column FIRSTNAME NVARCHAR2(30) converted from VARCHAR2(30) with

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- worked!

I tried to restore the original. However

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- fails!

gives an Ora-01439 error, that the values must be empty.

First introducing a new column FIRSTNAME2 VARCHAR2(30), copying and renaming did not succeed, maybe due to failing Oracle experience?

Please try this, its working for me.  Hope it helps you.

CREATE TABLE TEST_1
(
FIRSTNAME VARCHAR2(30)
);

INSERT INTO TEST_1(FIRSTNAME)
VALUES('NARENDRA');

COMMIT;

ALTER TABLE TEST_1
MODIFY FIRSTNAME  NVARCHAR2(30);

--Gives Error while modifying
/*ALTER TABLE TEST_1  MODIFY FIRSTNAME  VARCHAR2(30);8*/

ALTER TABLE TEST_1
ADD (FIRSTNAME2 VARCHAR2(30)
);

UPDATE TEST_1
SET FIRSTNAME2= substr(FIRSTNAME,1,30)

COMMIT;

ALTER TABLE TEST_1
DROP COLUMN FIRSTNAME;

ALTER TABLE TEST_1
RENAME COLUMN FIRSTNAME2 TO FIRSTNAME;


manual execution of specific remote query with postgresql_fdw In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g.

> explain verbose select max(col1) from remote_tables.table1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate  (cost=605587.30..605587.31 rows=1 width=4)
   Output: max(col1)
   ->  Foreign Scan on remote_tables.table1  (cost=100.00..565653.20 rows=15973640 width=4)
         Output: col1, col2, col3
         Remote SQL: SELECT col1 FROM public.table1

It would obviously be much more efficient to send SELECT max(col1) FROM public.table1 to the remote server and just pull the one row back.

Is there a way to perform this optimization manually? I would be satisfied with something as low-level as (hypothetically speaking)

EXECUTE 'SELECT max(col1) FROM public.table1' ON remote RETURNING (col1 INTEGER);

although of course a higher-level construct would be preferred.

I'm aware that I could do something like this with dblink, but that would involve rewriting a large body of code that already uses foreign tables, so I'd prefer not to.

EDIT: Here's the query plan for Erwin Brandstetter's suggestion:

=> explain verbose select col1 from remote_tables.table1
-> order by col1 desc nulls last limit 1;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Limit  (cost=645521.40..645521.40 rows=1 width=4)
   Output: url
   ->  Sort  (cost=645521.40..685455.50 rows=15973640 width=4)
         Output: col1
         Sort Key: table1.col1
         ->  Foreign Scan on remote_tables.table1  (cost=100.00..565653.20 rows=15973640 width=4)
               Output: col1
               Remote SQL: SELECT col1 FROM public.table1

This is better, in that it fetches only col1, but it's still dragging 16 million rows over the network and now it's also sorting them.  By way of comparison, the original query, applied on the remote server, doesn't even have to scan, because that column has an index.  (The core query planner isn't clever enough to do that for the modified query applied on the remote server, but that's minor.)

For the time being, it seems that the best available option is to create a view on the remote server that encapsulates the query needing to be "pushed down".  postgres_fdw is happy to define and use foreign tables backed by views on the remote, and regular old query optimization within the view does the Right Thing.  For instance, given

CREATE VIEW id_ranges AS
SELECT 'url_strings'::text AS tbl,
    min(url_strings.id)::bigint AS lo,
    max(url_strings.id)::bigint AS hi
   FROM url_strings
UNION
 SELECT 'captured_pages'::text AS tbl,
    min(captured_pages.url)::bigint AS lo,
    max(captured_pages.url)::bigint AS hi
   FROM captured_pages
UNION
 -- ... several more like that ...

on the remote, and a FOREIGN TABLE of the same name on the local server,

SELECT lo, hi FROM id_ranges WHERE tbl = 'url_strings';

the existing pushdown optimization will send the WHERE constraint to the remote, and the remote will scan only one table (making use of indexes if possible) and send back a single-row result.


Interview Question:- I have one table with following data in sql server CREATE TABLE [dbo].[T_COL](
    [COL1] [varchar](10) NULL,
    [COL2] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 1)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 2)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 3)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 1)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 2)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 3)

The desired output should look like this:-

col1     col2
A         1
A         1,2
A         1,2,3
B         1
B         1,2
B         1,2,3

You need a rolling string aggregate function. STRING_AGG doesn't allow OVER() clause, so a solution is to use a correlated subquery with FOR XML to generate the list.

SELECT
    T.COL1,
    RollingAggregate = STUFF (
        (
            SELECT
                ',' + CONVERT(VARCHAR(10), N.COL2)
            FROM
                [dbo].[T_COL] AS N
            WHERE
                N.COL1 = T.COL1 AND     -- Same letter
                N.COL2 <= T.COL2        -- Equal or lower number
            ORDER BY
                N.COL2
            FOR XML
                PATH('')
        ),
        1, 1, '') -- Remove leading comma
FROM
    [dbo].[T_COL] AS T
ORDER BY
    T.Col1,
    T.Col2

Result:

COL1    RollingAggregate
A       1
A       1,2
A       1,2,3
B       1
B       1,2
B       1,2,3


Stored Procedure in MySQL I am new to MySQL Stored Procedure, in my first attempt I got the following issues,

Stored Procedure:

Delimiter $$

create procedure accountstatus

(

    IN  inmode              varchar(27),
    IN  AccountStatus_id    int,
    IN  AccountStatus       varchar(255),
    IN  CreatedOn           datetime,
    IN  CreatedBy           varchar(255),
    IN  UpdatedOn           datetime,
    IN  UpdatedBy           varchar(255),
    IN  is_active           bit)

    Begin

    if inmode   =   'insert'
    then
        insert into accountstatus
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
        values
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active);
    end if;

    /*update*/

    if inmode   =   'update'
    then
        update accountstatus acc
        set
        acc.AccountStatus_id    =   AccountStatus_id,
        acc.Account_Status      =   Account_Status,
        acc.CreatedOn           =   CreatedOn,
        acc.CreatedBy           =   CreatedBy,
        acc.UpdatedOn           =   UpdatedOn,
        acc.UpdatedBy           =   UpdatedBy,
        acc.is_active           =   is_active
        where
        acc.AccountStatus_id    =   AccountStatus_id;

    end if;

    /*delete*/

    if inmode   =   'delete'
    then
        update  accountstatus acc
        set
        acc.AccountStatus_id    =   AccountStatus_id,
        acc.is_active           =   0
        where
        acc.AccountStatus_id    =   AccountStatus_id;

    end if;

    /*select*/

    if inmode   =   'select'
    then
        select * from accountstatus acc
        where
        acc.AccountStatus_id    =   AccountStatus_id;
    end if;
end

When I execute these statements I got the following output:

STATEMENT

call accountstatus ('insert', 1, 'accepted', current_date(),'rathish', current_date(), 'raj', 1);

OUTPUT

call accountstatus ('insert', 8, 'accepted', current_date(),'rathish', current_date(), 'raj', 1)
1 row(s) affected

Similarly I inserted some values to this table, but when I tried to execute the following update statement, values in the Account_Status became null as shown below,

STATEMENT

call accountstatus ('update', 8, 'accepted', current_date(),'raj', current_date(), 'raj', 1);

OUTPUT

call accountstatus ('update', 8, 'accepted', current_date(),'raj', current_date(), 'raj', 1)
1 row(s) affected

But, when I execute the following query statement I got the unexpected output as follows,

What is error in this procedure?
Is there any other ways to implement this procedure?
How it will effect the performance of the database?

Thanks in advance.

QUESTION #1

What is error in this procedure?

You seem to have some scope confusion on the variables
ANSWER TO QUESTION #1
PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.
SOLUTION : Change the names of the parameters so that they are distinct from the column names
create procedure accountstatus

(

    IN  inmode              varchar(27),
    IN  given_AccountStatus_id    int,
    IN  given_AccountStatus       varchar(255),
    IN  given_CreatedOn           datetime,
    IN  given_CreatedBy           varchar(255),
    IN  given_UpdatedOn           datetime,
    IN  given_UpdatedBy           varchar(255),
    IN  given_is_active           bit)

    Begin

    if inmode   =   'insert'
    then
        insert into accountstatus
        (AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
        values
        (given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
    end if;

    /*update*/

    if inmode   =   'update'
    then
        update accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id,  <- Not Needed for UPDATE
        acc.Account_Status      =   given_Account_Status,
        acc.CreatedOn           =   given_CreatedOn,
        acc.CreatedBy           =   given_CreatedBy,
        acc.UpdatedOn           =   given_UpdatedOn,
        acc.UpdatedBy           =   given_UpdatedBy,
        acc.is_active           =   given_is_active
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;

    end if;

    /*delete*/

    if inmode   =   'delete'
    then
        update  accountstatus acc
        set
--      acc.AccountStatus_id    =   given_AccountStatus_id, <- Not Needed for DELETE
        acc.is_active           =   0
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;

    end if;

    /*select*/

    if inmode   =   'select'
    then
        select * from accountstatus acc
        where
        acc.AccountStatus_id    =   given_AccountStatus_id;
    end if;
end

CAVEAT : Please note that I commented out two lines
QUESTION #2

Is there any other ways to implement this procedure?

ANSWER TO QUESTION #2
You could use triggers

Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
Apr 06, 2012 : How to speed up audit trigger?

QUESTION #3

How it will effect the performance of the database?

ANSWER TO QUESTION #3
Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 12, 2011 : Performance of a Trigger vs Stored Procedure in MySQL
Dec 23, 2011 : How to set up triggers in MySQL for copying a field

EPILOGUE
The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.
Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).


ER model for barber shop My team and I are designing a database for a barber shop. The main focus of the project is to allow customers to schedule appointments with barbers for some sort of hair service.

Our problem is that our professor is telling us to connect two relationships to each other and when we ask why he doesn't really do a good job of explaining why they need to connect. We told our professor that the book forbids the connection of two relationships to each other but he said "the book is wrong". The two relationships that our professor wants us to connect are performs to includes. My team and I think it would be better to connect performs to appointment. Does anyone understand why we would connect those two relationships to each other?

Diagram:

As far as a barber being connected to the appointment I'm with you guys on this. A foreign key [BarberID] in the appointments table links a barber to an appointment. The service being performed is also an attribute of that specific appointment. So the line from Performs should go to the appointment table. If anything, a foreign key relationship can exist between the barber table and the Services reference table, to create a list of services that a particular barber can perform (ex: not all barbers may be able to do highlights)

On a non-related note, I noticed that some of your tables are plural and some not. Try looking up some conventions for object naming and come up with something consistent, that would easily be followed by your future co-workers.


SQL Server : Job not reconnecting to restarted Linked Server The problem
To keep two tables across two servers in sync, we have a trigger that adds a change to a MergeTable. Every so often, a merging job runs on SQL Server to push the changes to the Progress linked server.
I was testing connectivity issues when I ran into this problem:

shut down Progress DB
job fails as expected
start up Progress DB
job continues to not connect to Progress DB until manually disabled and re-enabled

I get many multiples of these three messages:

OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server crash likely.". [SQLSTATE 01000]
OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Socket closed.". [SQLSTATE 01000]
OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Unexpected Network Error.  ErrNum = 10054". [SQLSTATE 01000]

What I've tried
I can execute sp_testlinkedserver and get an error, so I can detect WHEN it happens:

Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST".

I attempted to call sp_dropserver, sp_addlinkedserver, and sp_testlinkedserver in quick succession, which does not throw an error, but the job thinks it successfully completes when it didn't.
I'm quite new to SQL/T-SQL. There's no sp_reconnectlinkedserver, and I can't replicate this issue outside the job; running the individual procedures works when the database gets back up. I'm thinking this is a session issue, but I wouldn't know where to begin fixing it.

Querying the linked server seems to get it to reconnect. Calling sp_testlinkedserver doesn't always work, but a select statement will.

Sometimes there is a two minute lag from the start of the downed server to the next successful run of the job. In any case, it's simply a matter of waiting.


Is it reasonable to mark all columns but one as primary key? I have a table representing movies. The fields are:
id (PK), title, genre, runtime, released_in, tags, origin, downloads.

My database cannot be polluted by duplicated rows, so I want to enforce uniqueness. The problem is that different movies could have the same title, or even the same fields except tags and downloads. How to enforce uniqueness?

I thought of two ways:

make all the fields except downloads primary key. I'm keeping downloads out since it's JSON and it will probably impact the performance.
keep only id as primary key, but add a unique constraint with all the other columns (except, again, downloads).

I read this question which is very similar, but I didn't quite understand what should I do. Currently this table is not related to any other tables, but in the future could be.

At the moment I have slightly less than 20,000 records, but I expect the number to grow. I don't know if this is somewhat relevant to the issue.

EDIT: I modified the schema and here is how I would create the table:

CREATE TABLE movies (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    runtime     smallint NOT NULL CHECK (runtime >= 0),
    released_in smallint NOT NULL CHECK (released_in > 0),
    genres      text[] NOT NULL default ARRAY[]::text[],
    tags        text[] NOT NULL default ARRAY[]::text[],
    origin      text[] NOT NULL default ARRAY[]::text[],
    downloads   json NOT NULL,
    inserted_at timestamp NOT NULL default current_timestamp,
    CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

I also added the timestamp column, but that is not a problem as I won't touch it. So it will always be automatic and unique.

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?"

What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the star's names? Some combination of two or more?

The answer to my question and yours are the same.

However, I would not think that genre would be a good candidate. One reason, genre is much too subjective a criteria. Is 'The Three Musketeers' action? drama? adventure? comedy? action-adventure? romantic-comedy? I often see the same movie listed under different genres. Even when you allow for multiple genres, your user may select an entirely different one not listed with the actual movie they are looking for.

Even runtimes can differ, especially between theater and VCR/DVD/b-ray versions.

So you need hard, objective attributes which will not change from one media release to another. Unfortunately, that can exclude the name of the movie as movies have been known to be renamed, especially after the release of a sequel.

What about release date? The theatrical release of 1993? The VCR release of 1999? The DVD release of 2004? You get the idea.

Come to think about it, what of all those movies directed by Alan Smithee? Has the real director ever finally stepped forward to put his name on the project after the fact? I don't know.

Hmm, I'd better stop while there are still some criteria left.

Some additional points:

Yes, keep the surrogate key and create a unique index on the natural key fields (if you can finally nail those down). The surrogate key is best for foreign key references. You don't want to duplicate all the natural key fields in every table that contains a reference to a movie.
Drop the array fields (genres, tags, origins). Go ahead and properly normalize those attributes. I have never seen an array field that was not a great deal more trouble than it was worth, especially if you want them to be searchable ("...where genre = 'horror'..."). Note this will not automatically eliminate any issues with case differences and spelling ( "Science Fiction" vs "SciFi" ) -- unless you properly maintain the lookup tables. But it is a whole lot easier to check for such differences in one field of a small table than every array cell of every row of a large table.


How to DROP multiple tables from databases? I have situation where someone mistakenly execute script for creating tables under wrong database.

Let's say we have database DB1 which is some test database, and we have to create other database DB2. Script for creating tables is intended for DB2, but someone execute it under DB1.

Now my database DB1 has its own correct tables, and other incorrect tables. I know how to write a select query to view them, and I know that all wrongly created tables were created on a specific date, let's say 2009-12-12. But the tables are related with foreign keys, and I can't just drop them in the order that they appear, I must first to remove all foreign keys and then drop all of the tables.

Can someone suggest how to do that?

I can do it with cursor, but that is not elegant solution.

Since you only have to do it once, forget the fancy batch job and just get on with each task.  Assuming you know the table names, you just do this sort of thing.

For each table with foreign keys, drop the foreign key constraint on that field.  Then depending on your requirement, you can add records to the table that should have the data and add a new foreign key, or, simply delete those records.  Then you can drop the table.

Much of this work can be done with SSMS so you might not have to write that many queries.


SQL Server 2012: Monitoring SPID on unpredictable app Techies,

There's an application @my shop that gets executed through sql agent which works 95% of the time. The job completes within 10 minutes. The other 5% of the time, this same job stays active forever until one of us in the DBA team stop it.  The developers are working on a fix, however, in the meantime--the job will need to run as is with the current flaw.

Without a budget for tools, I need to find some way to monitor this job so that if it spins for more over 15 minutes, the DBA team is at least alerted.  Any suggestions?

I had the exact same situation and I created a SQL Agent job with the following script as the only step to monitor a job that occasionally runs long.

if exists(
        SELECT *
        FROM msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
        WHERE sja.start_execution_date IS NOT NULL
           AND sja.stop_execution_date IS NULL and
           sj.name ='Your job name'
        )
-- send an email
exec msdb.dbo.sp_send_dbmail
    @recipients ='email recipients',
    @subject='The job is running long',
    @body='Check the job on the server to see why it is running long'

In your case you would schedule this for 15 minutes after each execution of the job being monitored.


How SQL Server distributes queries between Database Engine and FTS Engine When a client submits a query to the database server, then SQL Server and its Database Engine parses, compiles, and execute those queries.

But, if the client's application submits a query which consists some of Full-Text-Search clause, than SQL Server uses FTS Engine to compiles and execute query.

What is tricky for me is whether the entire FTS query is executed by FTS Engine or divided somehow in two parts that are sent to Database Engine and FTS Engine, respectively.

Can someone explain to me what is really done at heart of the SQL Server in case of a SQL query that includes FTS clauses?

You might find the diagrams and explanation found in the SQL Server 2008 R2 documentation useful. (Nothing has fundamentally changed recently on Full Text processing.)

https://technet.microsoft.com/en-us/library/ms142541(v=sql.105).aspx

Quoting from that explanation: "The query processor compiles and executes SQL queries. If a SQL query includes a full-text search query, the query is sent to the Full-Text Engine, both during compilation and during execution. The query result is matched against the full-text index."

So you might view the Full Text search as a 'function' that returns its portion of the query from the Full Text Engine, then joins with any operators from the Database Engine that may also be part of the overall SQL Server query.


MySQL Temporary Tables I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this temporary tables? We have around 50 databases and many of the databases using create temporary table statement in its procedures. In /tmp directory I could see the .ibd, .frm and few .MYI & MYD files. Is it possible to find which database contributing more to creation of temporary tables? Please let me know, if you need any other information.

Thanks in advance.

Not quite what you asked for, but this query may help:

mysql> SELECT db, name FROM mysql.proc WHERE body LIKE '%temporary%';
+---------------+-----------------------------------+
| db            | name                              |
+---------------+-----------------------------------+
| common_schema | rdebug_compile_routine            |
| common_schema | _get_sql_dependencies_internal    |
| common_schema | query_checksum                    |
| common_schema | _split_cleanup_dependency_tables  |
| common_schema | _split_generate_dependency_tables |
+---------------+-----------------------------------+

If /tmp is in a separate filesystem, you are cruisin' for a bruisin'.  Change tmpdir to point to some directory in the main FS.


Always On Synchronous Commit Mode With Large Delays For SQL Server Enterprise's Always On availability groups in Synchronous Commit mode, is there some amount of latency that can't be exceeded?  For example, can it work between very distant data centers, such as between the US and Europe?

The answer you're looking for is in the docs

⚠ Note
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

When this happens, a synchronous secondary will fall back to be asynchronous (what some folks call "pseudo synchronous" because you set it to synchronous, but it's actually async). This is not specific to network latency, or physical distance, but can happen under a number of scenarios. As an example, if you restart a synchronous replica and that server is offline, your AG will fall back to asynch and continue to process transactions.
Synchronous AGs only pay a performance penalty on writing to the transaction log (which needs to write to the secondary replica). Write activity is often a minority of the database activity, with the majority of traffic being read operations, which are not affected by synch/async.
Running an AG in sync commit mode between US & EU is totally possible. Write transactions will be slower, as they need to write to the log on the other side of the ocean. Generally, a better design is to keep synch replicas closer to avoid the network latency. However, depending on the IO patterns of your database, your business needs, and your network backbone between geographies, it's possible. On the systems I support, we can tolerate synchronous commit latency across hundreds of miles--but when the distance gets longer than that the performance impact becomes too great. It's not that SQL Server can't support it--it's that the users will not tolerate that slowness.
Generally, I find most folks end up running synchronous commit within a data center/geography for high availability, and asynchronous commit between geographies/data centers for disaster recovery. For a planned failover across geographies, you can temporarily switch the remote replica to synchronous mode to ensure no data loss during the failover.
The easiest way to find out the actual impact on your application is to test it.


What's the point in SQL Server Central Management Server? Online MSDN article "Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" (SQL Server 2012) states:

"Note

Before you execute the following procedures, create a Central Management Server and server groups. For more information, see Create a Central Management Server and Server Group (SQL Server Management Studio) "

Though, I removed any CMS (Central Management Server) instance and still getting the same results over multiple SQL Servers on 2 differing machines

Seems like I am failing to find in online MSDN the illustration of the sense (or points) of an SQL Server Central Management Server.
Can you provide me with some?

The main difference between Registered Servers and Central Management Servers is where the server list is stored, and who it can be used by. There's no functional difference in multi-server queries -- at that point, the query window is connected to servers, regardless of which of the two features you used to get there.

It's unclear why that text is a warning in the documentation; I think it's just an advisory that you need to set up a server list before following any of the instructions (or they'd have to fork the instructions for Registered Servers vs. CMS). I think it should include language that indicates using Registered Servers (aka local server groups) is also a possibility, but I can see why they did what they did to simplify the instructions.

If you're interested in learning more about this topic, I have a two-part video series on my blog that you may like: Part 1, Part 2


Use BCP To Copy When Data Has Apostrophe In It Anytime my data has an apostrophe in it, the data is automatically split into the next field (which is skewing the result set).  Let's say this is my DDL

Create Table OneTwoThree
(
    userid varchar(max)
    ,name varchar(max)
    ,data varchar(max)
    ,data1 varchar(max)
    ,data2 varchar(max)
)

Insert Into OneTwoThree Values
('bb12', 'Hi Blee', 'Drop It Like It''s Hot', '', 'Boop')

And this is the BCP command I am using

Select @sql = 'bcp "SELECT ''userid'',''name'',''data'',
''data1'',''data2''
UNION ALL SELECT CAST([userid] As VARCHAR(MAX)),
CAST([name] As VARCHAR(MAX)),CAST([data] As VARCHAR(MAX)),
CAST([data1] As VARCHAR(MAX)),
CAST([data2] As VARCHAR(MAX)) dbo.OneTwoThree" queryout "'
SET @sql = @sql + 'C:\BCPTest.csv' + '" -c -t, -T -S '+ @@servername
exec master..xp_cmdshell @sql

This syntax will place "Drop It Like It" in data and "s Hot" in data1 when the entire phrase should go in data and data1 should be blank.

How should I alter this procedure so that the hyphen does not cause improper splitting?

Presumably you are expecting the .csv file to be opened using Excel or some equivalent.  I'd recommend wrapping the outputted text columns in double-quotes, such as:

declare @sql VARCHAR(1000);

Select @sql = 'bcp.exe "SELECT ''"userid"'',''"name"'',''"data"'',
''"data1"'',''"data2"''
UNION ALL
SELECT CHAR(34) + CAST([userid] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([name] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data1] As VARCHAR(MAX)) + CHAR(34)
, CHAR(34) + CAST([data2] As VARCHAR(MAX)) + CHAR(34)
FROM dbo.OneTwoThree" queryout "'
SET @sql = @sql + 'C:\temp\BCPTest.csv' + '" -c -t, -T -S '+ @@servername
PRINT @sql;
exec master..xp_cmdshell @sql

The output will look similar to:

If the BCPTest.csv is then opened with Excel, the double-quotes will be automatically interpreted as indicating those columns are text columns, and Excel will not show the quote marks.  Therefore, you only want to wrap text (CHAR, VARCHAR, NVARCHAR etc) columns in CHAR(34).

At that point, you'll start to see issues with columns that contain double-quotes; however you might decide to use the REPLACE() function to convert them into single-quotes or some other innocuous character.


Plan cache memory: parameterized SQL vs stored procedures In making a case to disallow parameterized SQL in my company's development environment the lead developer related a story about how the last time they used parameterized SQL the server had major performance issues. He said this was because the plan caching ate up almost all of the available memory on the server and switching to stored procedures cleared up the performance issues.

My question: is there a major difference in the memory footprint of a compiled/cached stored procedure and the cached plan for a parameterized SQL?

I have a guess that they also simplified the number of calls by going to procs and that probably had as much impact or more than just going to procs by itself, but I don't know.

No, there really isn't, for the same query taking the same parameters (though this hasn't always been the case - SQL Server wasn't always able to optimize at the statement level).

Now, if the ad hoc SQL you're sending to SQL Server varies by quite a bit (say a lot of similar but not identical queries), then you could have a lot of plan cache bloat due to single-use plans. You can reduce this somewhat by using the optimize for ad hoc workloads server setting. And depending on the parameterization techniques you're using, you may want to play with dynamic SQL vs. "kitchen sink" style queries (WHERE (c1 = @p1 OR @p1 IS NULL) AND (c2 = @p2 OR @p2 IS NULL) ... etc ...), and also parameterization forced vs. simple.

If you're looking for opinions with justification, I prefer stored procedures for a variety of other reasons - including the fact that you can drastically reduce the number of calls (and the amount of data) you send over the wire, you can also simplify the hardest parts of transaction management and error handling in a single layer, and the database people can optimize the SQL without having source code access and without having to re-compile and re-deploy the application code (be it the front end or middle tier - trust me, we're not good at that, just like developers are stereotypically not very good at writing good SQL queries :-)).


Galera - why can't I use port 4567 for replication? I have two Galera clusters in two different datacenters.
I want to replicate cluster c1(using c1-1 node as replication master) to cluster c2 (using c2-1 node as replication slave). Everything works well if I use port 3306 on c2-1for MASTER_PORT instead of the default port for replication 4567.
If I switch from port 3306 to 4567 for MASTER_PORT I get this error when I run SHOW SLAVE STATUS\G on c2-1:
Last_IO_Errno: 2013
Last_IO_Error: error connecting to master 'repl@x.x.x.x:4567' - retry-time: 60  maximum-retries: 86400  message: Lost connection to server at 'reading initial communication packet', system error: 0 "Internal error/check (Not system error)"

This is what I get when I run lsof -i:3306 on c1-1:
root@c1-1:~# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 1402 mysql   31u  IPv4  25689      0t0  TCP *:mysql (LISTEN)

And this is what i get when i run lsof -i:4567 on c1-1 (same node):
root@c1-1:~# lsof -i:4567
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 1402 mysql    8u  IPv4  25683      0t0  TCP *:4567 (LISTEN)

Also I allowed all traffic (using ufw) on all those ports.
If I run netcat x.x.x.x 3306 (x.x.x.x being c1-1's ip) on c2-1 I get this:
root@c2-1:~# netcat x.x.x.x 3306
q
5.5.5-10.5.9-MariaDB-1:10.5.9+maria~focal-log,)XbbFt8Z��-��:Srf(

And if I run netcat x.x.x.x 4567 (x.x.x.x being c1-1's ip) on `c2-1 I get this:
root@c2-1:~# netcat x.x.x.x 4567
$�-��\�цI�-Vk8�$

Why can't I use port 4567? Is it not using the right socket? If so what can be done?
Thank you!

4567 is the default Galera Replication Port for replication between nodes within a Galera cluster. That is not what you want to use here - you are doing primary-secondary replication (master-slave).
3306 is the default TCP/IP port you want to use for primary-secondary (master-slave) replication.


SQL Server 2012 database in suspect mode after upgrade from Developer to Standard Edition SQL Server database is in suspect mode after an upgrade from developer to standard edition 2012 version. In the log file there are two errors: 933 and error 905. Following error occurred when we tried to get the database back online.

  Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. Msg 905, Level 21, State 1, Line 1 Database 'VTSDB' cannot be started in this edition of SQL Server because it contains a partition function 'FNprocessedrawdata'. Only Enterprise edition of SQL Server supports partitioning. Msg 933, Level 21, State 1, Line 1 Database 'VTSDB' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

SQL Server Developer Edition is functionally equal to Enterprise Edition. The error is stating you are using features that only Developer or Enterprise support, such as Table Partitioning in this case. In order to use Standard, you cannot use any Enterprise features.

Check Features Supported by the Editions of SQL Server 2012 for more details.

One way to fix this issue would be to take the following steps:

  Take the database offline.
  Install a new named instance temporarily with Developer or Enterprise edition (database engine only as that is all that is needed
  for this).
  Copy the data and log files from current location to new location.
  On temporary instance, attach database and make sure you point to the copied files in the new location and not the original files. It
  should have no problems and bring the database up.
  Remove any Enterprise features from the link above. Use the following query to see what is used in that database "SELECT feature_name FROM sys.dm_db_persisted_sku_features"
  Detach database from temporary location.
  Rename original database files by appending some word (_orig as an example) to the current name.
  Move files from temporary location to same location as original files (assuming these files have same name as original files).
  Attach database to Standard instance by selecting the file you just copied
  Database should come up with no issues at this point.
  Turn off temporary SQL instance.
  After verifying everything is ok, uninstall the temporary instance.


Sql parent Child Query not working I want to get parent and their child data which means, parents first, then children.
Below is the query that I tried to convert to parent and their children

SELECT a.FactorTitle,
       a.FactorCode,
       a.parentId,
       c.FactorColumnCode,
       c.FactorColumnTitle,
       c.FactorColumnValue,
       c.isFactorValue,
       c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
FROM FactorSetup a
INNER JOIN PDModelSetup b ON a.PDModelCode=b.PDModelCode
LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
WHERE a.PDModelCode=2
  AND c.isFactorValue <> 'Y'
  AND a.FactorType='4'

I try below query

WITH EntityChildren AS
  (
   SELECT  a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
   FROM FactorSetup a
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   UNION ALL
   SELECT a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
   FROM FactorSetup a

   INNER JOIN EntityChildren e2 ON a.parentId = e2.FactorCode
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   )

SELECT * FROM EntityChildren

After executing  the query I got this error

  Msg 467, Level 16, State 1, Line 1
  GROUP BY, HAVING, or aggregate
  functions are not allowed in the recursive part of a recursive common
  table expression 'EntityChildren'.
  Msg 462, Level 16, State 1, Line 1
  Outer join is not allowed in the recursive part of a recursive common
  table expression 'EntityChildren'.

Then I change my query and remove count(*)

WITH EntityChildren AS
  (
   SELECT  a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType
   FROM FactorSetup a
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   UNION ALL
   SELECT a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType
   FROM FactorSetup a

   INNER JOIN EntityChildren e2 ON a.parentId = e2.FactorCode
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   )

SELECT * FROM EntityChildren

But I got this error

  Msg 462, Level 16, State 1, Line 1
  Outer join is not allowed in the
  recursive part of a recursive common table expression
  'EntityChildren'.

Define the outer join as a separate, non-recursive, CTE:

WITH FactorColumns AS
(
  SELECT  a.FactorTitle,
          a.FactorCode,
          a.parentId,
          c.FactorColumnCode,
          c.FactorColumnTitle,
          c.FactorColumnValue,
          c.isFactorValue,
          c.FieldType
  FROM FactorSetup a
  LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
),
...

Now just reference FactorColumns in EntityChildren – and syntactically your recursive CTE will have no outer join:

...
EntityChildren AS
(
  SELECT  f.FactorTitle,
          f.FactorCode,
          f.parentId,
          f.FactorColumnCode,
          f.FactorColumnTitle,
          f.FactorColumnValue,
          f.isFactorValue,
          f.FieldType
  FROM FactorColumns AS f

  UNION ALL

  SELECT f.FactorTitle,
         f.FactorCode,
         f.parentId,
         f.FactorColumnCode,
         f.FactorColumnTitle,
         f.FactorColumnValue,
         f.isFactorValue,
         f.FieldType
  FROM FactorColumns AS f
  INNER JOIN EntityChildren AS e2 ON f.parentId = e2.FactorCode
)
...


How to view full blocking postgres queries? I'm using a statement from this page to view blocking queries in Postgres

SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.query               AS blocking_statement,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.query                AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.granted;

However the queries are truncated so I typically can't see what's in the WHERE clause which makes debugging these blocking issues very hard. Is there a setting where I can tell postgres to log the full queries to pg_stat_activity

The length of the query tracked can be found in the parameter track_activity_query_size.

18.8.1. Query and Index Statistics Collector

track_activity_query_size (integer)

  Specifies the number of bytes reserved to track the currently executing command for each active session, for the
  pg_stat_activity.current_query field. The default value is 1024. This
  parameter can only be set at server start.

e.g. if you had track_activity_query_size=16384 in your config, then this would mean that query text up to 16k would be collected.


Installation of Oracle 9.2.4.0 stalled I'm installing Oracle 9.2.4.0 (I know, I know...) on Red Hat Enterprise Linux AS release 4 (Nahant Update 8) 64 bits using an Oracle 32 bit installer.

It appears to be stalled or hanged at 17% while "Installing Required Support Files 32 bit" indicating "linking pending".

This is what the log says as of now:

[oracle9@xxxx]$ tail -f installActions2007-09-24_07-34-38PM.log
    copyAsText = null
    JarLoc = /tmp/OraInstall2007-09-24_07-34-38PM/temp36
    gpEntries = [[libvsn_ee9.a ->%ORACLE_HOME%/lib32/libvsn9.a 2214 plats=2=>[46] langs=2=>[en,fr,ar,bn,pt_BR,bg,fr_CA,ca,hr,cs,da,nl,ar_EG,en_GB,et,fi,de,el,iw,hu,is,in,it,ja,ko,es,lv,lt,ms,es_MX,no,pl,pt,ro,ru,zh_CN,sk,sl,es_ES,sv,th,zh_TW,tr,uk,vi]]
, [libntcps9.a ->%ORACLE_HOME%/lib32/libntcps9.a 52696 plats=2=>[46] langs=2=>[en,fr,ar,bn,pt_BR,bg,fr_CA,ca,hr,cs,da,nl,ar_EG,en_GB,et,fi,de,el,iw,hu,is,in,it,ja,ko,es,lv,lt,ms,es_MX,no,pl,pt,ro,ru,zh_CN,sk,sl,es_ES,sv,th,zh_TW,tr,uk,vi]]
, [naeet.o ->%ORACLE_HOME%/lib32/naeet.o 6029 plats=2=>[46] langs=2=>[en,fr,ar,bn,pt_BR,bg,fr_CA,ca,hr,cs,da,nl,ar_EG,en_GB,et,fi,de,el,iw,hu,is,in,it,ja,ko,es,lv,lt,ms,es_MX,no,pl,pt,ro,ru,zh_CN,sk,sl,es_ES,sv,th,zh_TW,tr,uk,vi]]
]
    DllGroup = false

Llamando acción unixActions2.2.0.18.0  touchFile
    source = /folderA/oracle/folderB/OraHome1/lib/ldflags

We've successfully installed Ora9i 32 bits in a 64 bit servers before.

What do you think is the problem here?

This has worked for me:

Abort the installation an set the following environtment variable: LD_ASSUME_KERNEL=2.4.1, This will get you past the 17% mark.
Later during the installation you will get this error:

This happens because the installer is using the 64 bit bcc library

So solve that you must do this as root:

cd /usr/bin
mv gcc gcc_old
cp gcc32 gcc

Then click on Retry. Remember to revert that change after successfully installing.


Model a family tree database in Access 2007 I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have several N:M relationships.

A tribe has many clans (1:M)
A clan has many families (1:M)
A family has many Sims, and a Sim can belong to many families (M:N)
A lot can have many families living on it (1:M)
A Sim can be a member of many classes, and a class has many Sims (M:N)
An era has many classes, and a class can be in many eras (M:N)

This is what my model looks like so far:

SIM
----
Sim_id - unique PK
Sim_fname - First name of the Sim
#misc other details which will be filled in
fam_id - FK to FAMILY
Is_heir - yes/no

TRIBE
-----
tribe_id - unique PK
tribe_name
founder_id - FK to SIM who is the founder of the tribe

CLAN
----
clan_id - unique PK
clan_name
founder_id - FK to SIM who is the founder of the clan

FAMILY
------
fam_id - unique PK
partner1_id - FK to SIM who is the first partner in the relationship
partner2_id - FK to SIM who is the second partner in the relationship
start_day - Day the relationship began
clan_id - FK to CLAN the FAMILY belongs to
lot_id - FK to LOT on which the FAMILY lives
end_day - can be NULL
end_reason - reason why the relationship ended

ERA
---
era_id - unique PK
era_name

CLASS
-----
class_id - unique PK
class_name

LOT
---
lot_id - unique PK
lot_gen - generation number of the male head of the lot
tribe_id - FK to TRIBE
lot_number - number of the lot in the sequence

linkSIM_CLASS
-----------
sim_id - FK to SIM, composite PK
class_id - FK to ERA, composite PK

linkCLASS_ERA
-------------
class_id - FK to CLASS, composite PK
era_id - FK to ERA, composite PK

An example will help illustrate it:

Caleb is the founder of the Cullen tribe. He is the founder of the first clan, and the head of the first family (together with his wife Calliope). Their firstborn is daughter Bethany, followed by triplets (daughter Ferial, son Flint and daughter Feena). Flint is the firstborn son, so he is the heir. As he is a Generation 2 male, he is the founder of the clan Flintese.

Flint moves into his own lot and marries Ferial, forming his own family. They have several children, and then Ferial dies. Flint then marries Rahab (from the Swan tribe), forming another family. He is also given a concubine, Cullen-Fer, forming the another family. He has multiple children with both females, and they all live on the same lot. Flint was born in the Neanderthal Era as part of the Standard Class. He currently lives in the Roman Era as part of the Plebian Class. I will add a picture of the tables populated with this example data once I gain enough rep to post images.

(Please ignore the fact that he married his sister, it's part of the Sims 2 challenge I'm doing). Yes, I am creating a database to track my Sims, it's a fun way for me to use my SQL skills and to learn new things. I would like to maintain a historical log of where the Sim has lived, the multiple families they belong to, the different eras they have lived in etc.

There will be other tables also, but these are my core tables, so I would like to finalise these ones before implementing the other tables, as those relationships are simpler. Is my structure as normalised and efficient as possible?

Start using a ERD tool, or use paper to draw the relationships, those you have already written.

For example, "A tribe has many clans"  and "one clan, which belongs to one tribe" means there's a 1:N relationship between Tribe and Clan, so there should be a tribe_id in Clan table (FK to Tribe).

Another issue would be the "a family has many Sims" and "a Sim belongs to at least 2 families". You'll have to use a Sim_Family joint table for the many-to-many association between the Sim and Family but the "at least 2" is hard (or impossible?) to enforce with DRI alone.

After you have drawn all relationships between tables (entities), check if there are circular paths in the Foreign Keys. It's hard to deal with that and there's usually a way to avoid it by minor alterations in the design.


Error: Cannot Generate SSPI context When someone tries to connect to a SQL Server instance, the error shows up:

  It's not possible do generate SSPI context.

Yesterday we had a blackout (don't know how to say this expression in English) and I had to shut down our servers.

Looking for answers, I found this:

SQL Server 2008 connectivity issue : cannot generate SSPI context

But it doesn't help me, because they're working fine until yesterday. I don't want to change anything. But if a have to, I will change it.

Obs: I can't restart the server now.

Edit: Since I my answer, we haven't had any errors.

'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth.

There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. Applying one solution or another from random Internet resources, w/o understanding the cause, may or may not solve the issue, may or may not cause frustration, may or may not cause irrevocable damage.


How to improve multiple case when query performance? I have the following query which counts when the event_time is equal to the day of the month.

I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status.

Is there any more way I can optimise it?

I am running this against a database of at least 30 million records and this query will likely scan through at least 3 million records. I also have to run this query multiple times for different from_email_addresses.

Edited to include the explain query result.

select_type        tbl type   possible_keys                   key

PRIMARY            e   ref    PRIMARY, IDX_FROM_EMAIL_ADDRESS IDX_FROM_EMAIL_ADDRESS
PRIMARY            d   eq_ref PRIMARY                         PRIMARY
PRIMARY            s   ref    PRIMARY, IDX_EVENT_TIME         PRIMARY
DEPENDENT SUBQUERY ms  ref    PRIMARY, IDX_EVENT_STATUS       IDX_EVENT_STATUS
ref        rows       Extra
const      3,012,345  Using where, Using index, Using temporary, Using filesort
e.id       1
e.id,func  1          Using where; Using index
const,d.id 1          Using where; Using index

    SELECT
     d.template_name,
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 1 THEN 1 END) AS '1',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 2 THEN 1 END) AS '2',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 3 THEN 1 END) AS '3',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 4 THEN 1 END) AS '4',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 5 THEN 1 END) AS '5',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 6 THEN 1 END) AS '6',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 7 THEN 1 END) AS '7',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 8 THEN 1 END) AS '8',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 9 THEN 1 END) AS '9',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 10 THEN 1 END) AS '10',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 11 THEN 1 END) AS '11',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 12 THEN 1 END) AS '12',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 13 THEN 1 END) AS '13',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 14 THEN 1 END) AS '14',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 15 THEN 1 END) AS '15',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 16 THEN 1 END) AS '16',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 17 THEN 1 END) AS '17',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 18 THEN 1 END) AS '18',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 19 THEN 1 END) AS '19',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 20 THEN 1 END) AS '20',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 21 THEN 1 END) AS '21',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 22 THEN 1 END) AS '22',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 23 THEN 1 END) AS '23',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 24 THEN 1 END) AS '24',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 25 THEN 1 END) AS '25',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 26 THEN 1 END) AS '26',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 27 THEN 1 END) AS '27',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 28 THEN 1 END) AS '28',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 29 THEN 1 END) AS '29',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 30 THEN 1 END) AS '30',
     COUNT(CASE WHEN DAYOFMONTH(s.event_time) = 31 THEN 1 END) AS '31',
     COUNT(*) AS 'Grand Total'
FROM
  detail d
LEFT JOIN
    email e on d.id = e.id
LEFT JOIN
    status s on s.id = d.id
 AND s.event_time = (
                        SELECT
                          MINE(ms.event_time)
                        FROM
                          status ms
                        WEHRE
                          ms.id = d.id
                        AND
                            ms.event_status = 'SENT'
                    )
WHERE
    CASET(s.event_time AS DATE) BETWEEN '2020-03-01' AND '2020-03-31'
 AND
    e.from_email_address = 'abc@google.com'
GROUP BY
  d.template_name

this condition prevents database server from using index seek:

cast(s.event_time as date) between '2020-03-01'

first you need to change it to:

s.event_time >= '2020-03-01' AND s.event_time < '2020-04-01'


Dynamic Parameters In Pivot I know the premise of a PIVOT() this syntax produces it perfectly for me:

Select *
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot
(
  SUM(Amt)
  ,for annual IN ([2014],[2015],[2016])
) p

However, what if I am using a stored procedure and passing a year1 and year2 to the pivot as variables?  This will not work as incorrect syntax, but could this be achieved?

Create Procedure dbo.PivotParams
As
Declare @year1 varchar(100), @year2 varchar(100)

Set @year1 = '2014'
Set @year2 = '2016'

Select *
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot
(
  SUM(Amt) for annual BETWEEN @year1 and @year2
) p

Also the output should use COALESCE to prevent any NULL values in any of the pivot columns.

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose):

DECLARE @year1 smallint, @year2 smallint

SET @year1 = 2014;
SET @year2 = 2016;

DECLARE @years nvarchar(max) = N'', @select nvarchar(max) = N'';

SELECT  @years += N',' + QUOTENAME(y), @select += N',
  ' + QUOTENAME(y) + N' = COALESCE(' + QUOTENAME(y) + N',0)'
FROM
(
  SELECT TOP (@year2-@year1+1)
    y = @year1 - 1 + ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects ORDER BY [object_id]
) AS y;

DECLARE @sql nvarchar(max) = N'Select field1' + @select + N'
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM dbo.Testing -- ALWAYS use schema prefix!
) x
pivot
(
  SUM(Amt) for annual IN (' + STUFF(@years, 1, 1, N'') + N')
) p;';

PRINT @sql;

-- EXEC sys.sp_executesql @sql;

Slightly related:

Script to create dynamic PIVOT queries in SQL Server