SELECT multiple sensor values in one query Background

I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of these values (the latest logged) one by one and displays them to the user. But currently this takes too long time because there are too many values that needs to be fetched, the fetch takes about 8ms per value and in total we talk about around 300ms increase in total page load time - for a relatively good page.

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL, --- stored as unixtime
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
);

The table is partitioned weekly on the Date column.

What I do now

So, what I do is the following. I select the largest value in each partition that is before the current date/time. and pick out the largest value.

SELECT TOP (1) ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId = @fSensorId
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;

What I want to do

I want to select all values in one query. E.g. select the latest value for DeviceId=1 and SensorId=1,2,3,4,5. I have come up with the following so far, where I select with the IN keyword to get values for multiple sensors. However, I still need to group them and sort out the one with the highest date. I'm thinking of adding a GROUP BY clause, but don't know how to get it right (the ones I've tried has failed so far).

SELECT ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId IN (@fSensorId1, @fSensorId2, @fSensorId3)
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;

First things first, I notice that your 'what I do now' query:

SELECT TOP (1)
    ca.SensorValue,
    ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
    SELECT TOP (1)
        v.Date,
        v.SensorValue
    FROM SensorValues AS v
    WHERE
        $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
        AND v.DeviceId = @fDeviceId
        AND v.SensorId = @fSensorId
        AND v.Date <= @fDate
    ORDER BY
        v.Date DESC
) AS ca
WHERE
    p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
    AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
    AND p.index_id = 1
ORDER BY
    p.[partition_number] DESC,
    ca.Date DESC;

...produces an execution plan like this:

This execution plan has an estimated total cost of 0.02 units.  Over 50% of this estimated cost is the final Sort, running in Top-N mode.  Now estimates are just that, but sorts can be expensive in general, so let's remove it without changing the semantics:

SELECT TOP (1)
    ca.SensorId,
    ca.SensorValue,
    ca.Date
FROM
(
    -- Partition numbers
    SELECT DISTINCT
        partition_number = prv.boundary_id
    FROM
        sys.partition_functions AS pf
    JOIN sys.partition_range_values AS prv ON
        prv.function_id = pf.function_id
    WHERE
        pf.name = N'SensorValues_Date_PF'
        AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
) AS p
CROSS APPLY
    (
    SELECT TOP (1)
        v.Date,
        v.SensorValue,
        v.SensorId
    FROM dbo.SensorValues AS v
    WHERE
        $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
        AND v.DeviceId = @fDeviceId
        AND v.SensorId = @fSensorId
        AND v.Date <= @fDate
    ORDER BY
        v.Date DESC
  ) AS ca
ORDER BY
    p.partition_number DESC,
    ca.Date DESC

Now the execution plan has no blocking operators, and no sorts in particular.  The estimated cost of the new query plan below is 0.01 units and the total cost is distributed evenly over the data access methods:

With the improvement in place, all we need to produce a result for each Sensor ID is to make a list of Sensor IDs and APPLY the previous code to each one:

SELECT
    PerSensor.SensorId,
    PerSensor.SensorValue,
    PerSensor.Date
FROM
(
    -- Sensor ID list
    VALUES
        (@fSensorId1),
        (@FSensorId2),
        (@FSensorId3)
) AS Sensor (Id)
CROSS APPLY
(
    -- Optimized code applied to each sensor
    SELECT TOP (1)
        ca.SensorId,
        ca.SensorValue,
        ca.Date
    FROM
    (
        -- Partition numbers
        SELECT DISTINCT
            partition_number = prv.boundary_id
        FROM
            sys.partition_functions AS pf
        JOIN sys.partition_range_values AS prv ON
            prv.function_id = pf.function_id
        WHERE
            pf.name = N'SensorValues_Date_PF'
            AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
    ) AS p
    CROSS APPLY
        (
        SELECT TOP (1)
            v.Date,
            v.SensorValue,
            v.SensorId
        FROM dbo.SensorValues AS v
        WHERE
            $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
            AND v.DeviceId = @fDeviceId
            AND v.SensorId = Sensor.Id--@fSensorId1
            AND v.Date <= @fDate
        ORDER BY
            v.Date DESC
      ) AS ca
    ORDER BY
        p.partition_number DESC,
        ca.Date DESC
) AS PerSensor;

The query plan is:

Estimated query plan cost for three Sensor IDs is 0.011 - half that of the original single-sensor plan.


Getting error while running dbca command as oracle user on linux, please help me on this could someone please help me on the below error to get ride of it:

[oracle@localhost ~]$ dbca
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /u01/app/oracle/product/11.2.0/db_home/lib/libnjni11.so: libaio.so.1: cannot open shared object file: No such file or directory
Exception in thread "main" java.lang.UnsatisfiedLinkError: get
        at oracle.net.common.NetGetEnv.get(Native Method)
        at oracle.net.config.Config.getNetDir(Unknown Source)
        at oracle.net.config.Config.initConfig(Unknown Source)
        at oracle.net.config.Config.<init>(Unknown Source)
        at oracle.sysman.assistants.util.NetworkUtils.<init>(NetworkUtils.java:286)
        at oracle.sysman.assistants.util.step.StepContext.<init>(StepContext.java:332)
        at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:811)
        at oracle.sysman.assistants.dbca.ui.UIHost.<init>(UIHost.java:258)
        at oracle.sysman.assistants.dbca.ui.InteractiveHost.<init>(InteractiveHost.java:54)
        at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:164)
        at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:112)
        at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:180)
[oracle@localhost ~]$

Start with (use the appropriate install syntax for your distro)

sudo apt-get install libaio1

and if that doesn't do the trick, do

sudo apt-get install libaio libaio-dev

Paul...


psql command output to print error via -L output.log wanted to know how to print error output via psql command
using below :
output of Insert_log.log shows only query statement not the error :
psql -h ${hostname} -p 5432 -U postgres -d ${DB_NAME} -a  -f ${DIR}/log.sql -L Insert_log.log

[ec2-user@scripts]$ ./log.sh
\o
INSERT INTO mig.qb_tax_rates (id, xxx"}}', 18977);
psql:/home/ec2-user/scripts/log.sql:2:
ERROR:  column "q_id" of relation "qb_tax_rates" does not exist
LINE 1: INSERT INTO mig.qb_tax_rates (id, q_id, sync_token, qb_updat...

[ec2-user@ip-10-99-99-999 scripts]$ cat Insert_log.log
********* QUERY **********
INSERT INTO mig.qb_tax_rates (id, xx}}', 18977);
**************************

The file specified to -L cannot be made to include errors.
To have errors in the same log file than queries and results, both outputs should be redirected to the same file. For example:
$ cat >script.sql
select 1/0;

select count(*) from pg_class;

$ psql -e -f script.sql &> output.log

$ cat output.log
select 1/0;
psql:script.sql:1: ERROR:  division by zero
select count(*) from pg_class;
 count
-------
   352
(1 row)

See psql --help for more options:

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)


How to find inputs that caused different query plans in Query Store Query store shows that a completely parameterized query has multiple query plans:

How can I find the input that is causing such long response times?

Why would the same parameterized query end up with different query plans?  Where can I learn more about that?

If you want to do this within the Query Store user interface, you can look at the compiled value for each of the plans to see which parameter values led to different plans:

click on one of the colored circles in the plan summary from your screenshot
right-click the graphical execution plan that appears at the bottom and choose "Properties"
expand the "Parameter List" node in the properties pane

Here I'm looking at the compiled values that led to the "green" plan.  Clicking on the "orange" plan will show the compiled values for that one.

If you prefer not to use the user interface, you can get the plans directly from the Query Store tables using the method described by Max Vernon here: Does SQL Server Query Store capture parameter values?

As Aaron Bertrand mentioned in the comments:

  And plans can change for a variety of reasons, such as a significant data change that invalidates statistics. Some other relevant info here and here.

A new plan can get compiled for a number of different reasons (including statistics updates, manual plan clearing, adding up or rebuilding indexes, etc).

In the case in my screenshot, I can see that the new plan for this query showed up at 12:18 pm on 2/9.  Looking at the OptimizerStatsUsage node of the execution plan properties, I can see that several of the statistics used by this query were updated just before that, so this is why I got the new plan:

  which is the process for which SQL Server chooses different plans depending on the parameters values? I would like to read more about that.

The process that SQL Server uses to generate a specific plan based on different input parameters is called "parameter sniffing."  It's a really complex topic that you can read a lot more about it in Erik Darling's answer here:

Why is my query suddenly slower than it was yesterday?

There are some very comprehensive discussions about the topic of parameter sniffing in the "learn more" links at the bottom of that answer.


Where clause in a XML column I have the following XML column in my Oracle 11 G DB:

<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
  <odo-reading latest="false">
     <reading value="391264">393,264</reading>
     <reading-date value="2018-12-04">4-Dec-2018</reading-date>
     <reading-unit code="K">Km</reading-unit>
  </odo-reading>
   </vehicle>
</l-analysis>

I need to query the reading value but only where odo-reading latest="true".

How can I do that in a CLOB column?

drop table t1 purge;
create table t1 (id number, cxml clob);
insert into t1 values (1,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
  <odo-reading latest="false">
     <reading value="391264">393,264</reading>
     <reading-date value="2018-12-04">4-Dec-2018</reading-date>
     <reading-unit code="K">Km</reading-unit>
  </odo-reading>
   </vehicle>
</l-analysis>');
commit;

select extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading[@latest="true"]/reading/@value') as value from t1;

VALUE
----------
653463

Answer to the original question before it was edited:

drop table t1 purge;
create table t1 (id number, cxml clob);
insert into t1 values (1,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
   </vehicle>
</l-analysis>');
insert into t1 values (2,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="false">
         <reading value="653463">653,464</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
   </vehicle>
</l-analysis>');
commit;

select id, extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading/reading/@value') as value from t1
where extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading/@latest') = 'true';

        ID VALUE
---------- ----------
         1 653463


Data type and Operator type Errors i am following learn sql the hard way tutorial, in tutorial tutor suggest to use sqllite 3 but im using MSSQL 2008 and i have a problem, tutor ask from me to find something using AND or OR operators, her is the query i wrote;

select * from pet where name like 'Flu%' and age = 1000

but i receive:

  Msg 206, Level 16, State 2, Line 13 Operand type clash: text is
  incompatible with smallint

i try this one

select * from pet where name like 'Flu%' and age = '1000'

i receive :

  Msg 402, Level 16, State 1, Line 13 The data types text and varchar
  are incompatible in the equal to operator.

and when i convert age to varchar it works;

select * from pet where name like 'Flu%' and convert (varchar, age) = 1000
select * from pet where name like 'Flu%' and convert (varchar, age) = '1000'

it returns the correct answer

Here is the table design (as tutor wants);
id=int, name=text, breed=text, age=text, dead=int

why it doesnt work before converting to varchar?

Looking forward to your reply.

Kinds.

You got problem because sqllite and sql server datatype are different.

In the specific, you use a text datatype. This is bad, in sql server, for many reasons:

Text will be removed in future versions of sql server: https://msdn.microsoft.com/it-it/library/ms187993.aspx
You cannot compare with the equal operator
Always choose appropriate data type. Store a number in a text field will lead you to problems.

So, I suggest you to build your table with appropriate datatypes. In your case name must be a varchar and age an integer (if it is ok).

Also you must not learn starting on a book for a specific rdbms and use another one, or you will get a lot of problems like this.


Using select distinct with IF condition How can I ignore the is_enrolled field from distinct? This is the query:

select DISTINCT
`courses`.*, IF(course_enrollments.user_id=12, TRUE, FALSE) as is_enrolled
from `courses`
inner join `course_time_periods` on `course_time_periods`.`course_id` = `courses`.`id`
inner join `org_time_periods` on `time_period_id` = `org_time_periods`.`id`
left join `course_groups` on `course_time_periods`.`id` = `course_groups`.`course_period_id`
left join `course_enrollments` on `course_groups`.`id` = `course_enrollments`.`group_id`
where `org_time_periods`.`started_at` <= "2017-08-12 12:39:05"
    and `org_time_periods`.`finished_at` >= "2017-08-12 12:39:05"
    and `courses`.`is_active` = true
    and `courses`.`organization_id` = 2
    and `course_enrollments`.`deleted_at` is null

This duplicating one course because it's enrolled, so it returns the first row with is_enrolled as trueand next one as false.

It looks like you are trying to generate a list of all courses, plus a note as to which of those courses a specific user is enrolled in.

If that's the case, try changing the query as follows:

    select DISTINCT
    `courses`.*, IF(`course_enrollments`.`group_id` IS NOT NULL, TRUE, FALSE) as is_enrolled
    from `courses`
    inner join `course_time_periods` on `course_time_periods`.`course_id` = `courses`.`id`
    inner join `org_time_periods` on `time_period_id` = `org_time_periods`.`id`
    left join `course_groups` on `course_time_periods`.`id` = `course_groups`.`course_period_id`
    left join `course_enrollments` on (    `course_groups`.`id` = `course_enrollments`.`group_id`
                                       AND `course_enrollments.user_id` = 12
                                      )
    where `org_time_periods`.`started_at` <= "2017-08-12 12:39:05"
        and `org_time_periods`.`finished_at` >= "2017-08-12 12:39:05"
        and `courses`.`is_active` = true
        and `courses`.`organization_id` = 2
        and `course_enrollments`.`deleted_at` is null

As far as I can tell, your left joins only exist to check to see if the user_id in question showed up in a course_enrollments row. So, I modified the join to course_enrollments so that only rows with the indicated user_id would be returned by the join.

Then, instead of checking for user_id = 12, we simple test for whether a row was returned from course_enrollments. If a row was returned, then the user you're interested in is enrolled; otherwise, they are not enrolled.

Note: the code is untested.


Dirty buffer pages after issuing CHECKPOINT I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a "cold" read a well as I can. Part of that is clearing the buffer cache before performing the queries. From everything I can find dirty buffer pages are supposed to be written during a checkpoint. However, even after issuing a CHECKPOINT, there still seem to be 169 dirty pages of my database in the buffer pool (assessed via SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id=7 AND is_modified=1).
Is there anything I am misunderstanding about checkpoints or the content of sys.dm_os_buffer_descriptors? If not, why do I still have dirty pages after they were supposedly written away?

A manual CHECKPOINT should write all dirty pages to disk.  It does this for the database that is active for your connection, not for all databases.  Make sure the current database is the name of whatever database_id=7 is:
USE [YourDatabaseName];
GO

CHECKPOINT;
GO

SELECT *
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 7 AND is_modified = 1;

See How do checkpoints work and what gets logged:

All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

And Database Checkpoints (SQL Server):

Name
Transact-SQL Interface
Description

Manual
CHECKPOINT [checkpoint_duration]
Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection...

Note that a bunch of things can cause dirty pages show up in sys.dm_os_buffer_descriptors after all explicit (user initiated) write operations have completed.  Some of these could be in response to SELECT queries:

sync or async stats updates from plan compilation
query store data collection

Or due to background tasks related to specific SQL Server features:

columnstore tuple mover
deferred drop (if tables were truncated or dropped, but extents are still being deallocated in the background
Change Tracking autocleaup
PVS cleanup (if using Accelerated Database Recovery on SQL Server 2019)

That's not an exhaustive list, but gives you an idea that you might see dirty pages for many reasons beyond just inserts, updates, and deletes.


MySQL: How to clean MySQL from RHEL5? I have installed MySQL 5.1.73 community edition through RPM. It was working fine.
I uninstalled this version by using yum remove mysql-server command.
I wanted to upgrade MySQL from 5.1.73 to 5.6.19 community edition.
Then I installed it by sudo rpm -i /u000/MySQL-server-5.6.19-1.rhel5.x86_64.rpm command.
After installation, I was able to start mysql by issuing sudo /etc/init.d/mysql start.
After this, I tried to install MySQL-shared-compat-5.6.19-1.rhel5.x86_64.rpm using RPM command. But, it is giving conflict message with mysql 5.1.73 - install of MySQL-server-5.6.19-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.73-4.el6.x86_64.
It is same for MySQL-client-5.6.19-1.rhel5.x86_64.rpm. Again, I got the similar conflict message.
After this, I tried to connect MySQL by issuing below command:

$ mysql -uroot -p3pkvFtaWM_PTCwJ8
-bash: mysql: command not found

How can I fix this issue and run the MySQL 5.6 as normally?
Do I need to clean MySQL from server?

Search for mysql-related packages that are now installed: rpm -qa | grep -i mysql

Delete any packages like mysql, mysql-server, mysql-client, mysql-shared, mysql-shared-compat, mysql-libs. yum remove mysql-server You do not need to remove mysql library bindings like python-mysqldb, php-mysql, etc., unless they had a dependency with some of the previous packages.

I recommend you to install the Oracle MySQL yum repositories, that will make the installing and maintenance much easier, unless you know what you are doing and want more control over your packages. An installation guide is detailed here: http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/index.html Basically, it involves creating a .repo file and yum update.


Create User, Tablespace Replication in Oracle GoldenGate How to replicate - Create User, Procedure and Tablespace using Oracle GoldenGate.

I am able to replicate DDL under schema, however above is not getting replicated i.e. when I create an user, procedure or tablespace.

GoldenGate Version - 12.2.0.1
Database Version - 11.2.0.4

Extract Conf:

GGSCI (sourceGG) 36> view params ext4

extract ext4
userid ogguser, password ogguser
exttrail /u01/app/oracle/product/11.2.0.4/oggsrc/dirdat/mt
DDL INCLUDE ALL
table ogguser.*;

GGSCI (sourceGG) 37>

Dump Conf:

GGSCI (sourceGG) 37> view params dpump2

extract dpump2
userid ogguser, password ogguser
rmthost targetGG, mgrport 7809
rmttrail /u01/app/oracle/product/11.2.0.4/oggtrg/dirdat/pt
passthru
DDL INCLUDE ALL
table ogguser.*;

GGSCI (sourceGG) 38>

Replicat Conf:

GGSCI (targetGG) 37> view params rep4
replicat rep4
assumetargetdefs
handlecollisions
userid ogguser, password ogguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
map ogguser.*, target ogguser.*;

GGSCI (targetGG) 38>

Thanks!

DDL replication in Oracle Goldengate is disabled for the extract process by default and enabled for the replicat process.

To enable the DDL support, you need to specify the DDL parameter for the extract process.

  DDL

  Use the DDL parameter to:

  enable DDL support
    filter DDL operations
    configure a processing action based on a DDL record

  Syntax

DDL [
{INCLUDE | EXCLUDE}
  [, MAPPED | UNMAPPED | OTHER | ALL]
  [, OPTYPE type]
  [, OBJTYPE 'type']
  [, SOURCECATALOG catalog | ALLCATALOGS]
  [, ALLOWEMPTYOBJECT]
  [, ALLOWEMPTYOWNER]
  [, OBJNAME name]
  [, INSTR 'string']
  [, INSTRWORDS 'word_list']
  [, INSTRCOMMENTS 'comment_string']
  [, INSTRCOMMENTSWORDS 'word_list']
  [, STAYMETADATA]
  [, EVENTACTIONS (action)
]
[...]

In order to replicate CREATE USER, ALTER TABLESPACE or CREATE ROLE kind of statement the DDL scope should be OTHER or ALL to include all kind of DDLs.

Some other examples of OTHER scope:

CREATE USER joe IDENTIFIED by joe;
CREATE ROLE ggs_gguser_role IDENTIFIED GLOBALLY;
ALTER TABLESPACE gg_user TABLESPACE GROUP gg_grp_user;

References:
Oracle GoldenGate - DDL Replication
DDL: Oracle Goldengate 12c
Understanding DDL Scopes


Theil-Sen estimator function in T-SQL Does anyone have a Theil-Sen regression function written in T-SQL?

I found one written in Perl, but I'm not able to recode it into SQL.

I was lying when I said, I'm not able to recode it into SQL. I was just too lazy. Here is the code with an example of usage.

The Code is based on a TheiSen perl library, using QuickMedian.
Let's define a new table type to easily pass our data to the procedure.

CREATE TYPE dbo.TheilSenInputDataTableType AS TABLE
(
    ID INT IDENTITY(1,1),
    x REAL,
    y REAL
)

Please note the ID column, which is important here as our solution uses CROSS APPLY statement in order to achieve correct interpretation of the inner loop found in TheilSen.pm.

my ($x1,$x2,$y1,$y2);
foreach my $i(0 .. $n-2){
    $y1 = $y->[$i];
    $x1 = $x->[$i];
    foreach my $j($i+1 .. $n-1){
        $y2 = $y->[$j];
        $x2 = $x->[$j];

We will also need a new data type for storing an array of real type values.

CREATE TYPE [dbo].[RealArray] AS TABLE(
    [val] [real] NULL
)

Here is the f_QuickMedian function, returning median for given array. Credit for this one goes to Itzik Ben-Gan.

CREATE FUNCTION [dbo].[f_QuickMedian](@RealArray RealArray READONLY)
RETURNS REAL
AS
BEGIN
    DECLARE @Median REAL;
    DECLARE @QMedian REAL;

    SELECT @Median = AVG(1.0 * val)
    FROM
    (
        SELECT o.val, rn = ROW_NUMBER() OVER (ORDER BY o.val), c.c
        FROM @RealArray AS o
        CROSS JOIN (SELECT c = COUNT(*) FROM @RealArray) AS c
    ) AS x
    WHERE rn IN ((c + 1)/2, (c + 2)/2);

    SELECT TOP 1 @QMedian = val FROM @RealArray
    ORDER BY ABS(val - @Median) ASC, val DESC

    RETURN @QMedian
END

And the p_TheilSen Estimator:

CREATE PROCEDURE [dbo].[p_TheilSen](
      @TheilSenInput TheilSenInputDataTableType READONLY
    , @m Real OUTPUT
    , @c Real OUTPUT
)
AS
BEGIN
    DECLARE
        @m_arr RealArray
      , @c_arr RealArray;

    INSERT INTO @m_arr
        SELECT m
        FROM
        (
            SELECT
                t1.x as x1
                , t1.y as y1
                , t2o.x as x2
                , t2o.y as y2
                , t2o.y-t1.y as [y2 - y1]
                , t2o.x-t1.x as [x2 - x1]
                , CASE WHEN (t2o.x <> t1.x) THEN  CAST((t2o.y-t1.y) AS Real)/(t2o.x-t1.x) ELSE NULL END AS [($y2-$y1)/($x2-$x1)]
                , CASE WHEN t1.y = t2o.y THEN 0
                  ELSE
                    CASE WHEN t1.x = t2o.x THEN NULL
                        ELSE
                        -- push @M, ($y2-$y1)/($x2-$x1);
                        CAST((t2o.y-t1.y) AS Real)/(t2o.x-t1.x)
                    END
                  END as m
            FROM @TheilSenInput t1
            CROSS APPLY
                    (
                    SELECT  t2.x, t2.y
                    FROM    @TheilSenInput t2
                    WHERE   t2.ID > t1.ID
                     ) t2o
        ) t
        WHERE m IS NOT NULL

    SELECT @m = dbo.f_QuickMedian(@m_arr)

    INSERT INTO @c_arr
        SELECT y - (@m * x)
            FROM @TheilSenInput

    SELECT @c = dbo.f_QuickMedian(@c_arr)

END

Example:

DECLARE
      @in TheilSenInputDataTableType
    , @m Real
    , @c Real

INSERT INTO @in(x,y) VALUES (10.79,118.99)
INSERT INTO @in(x,y) VALUES (10.8,120.76)
INSERT INTO @in(x,y) VALUES (10.86,122.71)
INSERT INTO @in(x,y) VALUES (10.93,125.48)
INSERT INTO @in(x,y) VALUES (10.99,127.31)
INSERT INTO @in(x,y) VALUES (10.96,130.06)
INSERT INTO @in(x,y) VALUES (10.98,132.41)
INSERT INTO @in(x,y) VALUES (11.03,135.89)
INSERT INTO @in(x,y) VALUES (11.08,139.02)
INSERT INTO @in(x,y) VALUES (11.1,140.25)
INSERT INTO @in(x,y) VALUES (11.19,145.61)
INSERT INTO @in(x,y) VALUES (11.25,153.45)
INSERT INTO @in(x,y) VALUES (11.4,158.03)
INSERT INTO @in(x,y) VALUES (11.61,162.72)
INSERT INTO @in(x,y) VALUES (11.69,167.67)
INSERT INTO @in(x,y) VALUES (11.91,172.86)
INSERT INTO @in(x,y) VALUES (12.07,177.52)
INSERT INTO @in(x,y) VALUES (12.32,182.09)

EXEC p_TheilSen @in, @m = @m OUTPUT, @c = @c OUTPUT

SELECT @m
SELECT @c

Returns:

m = 52.7079
c = -448.4853

Just for a comparision, perl version returns following values for the same data set:

m = 52.7078651685394
c = -448.484943820225

I use TheilSen estimator to calculate DaysToFill metric for filesystems.
Enjoy!


SQL Server is started but didn't listen on any port I newly installed SQL Server 2012. No database created yet. And I can open the Management Studio with sa/password. The Binn\sqlservr.exe is started as a service and I can see it started in the service list. However, it didn't listening the 1433 port, or even, not listening any port by default, as I checked as below:

tasklist|find /I "sql"

I got:

sqlservr.exe                  5668 Services                   0     40,112 K

in which 5668 I think is the PID. And then to get PID = "5668" is listening to which port:

netstat -ano | find /I "5668"

but I got nothing except blank. On the other hand, after searching posts in this site, I double-checked my SQL Server's configuration: start->All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager-> SQL Server SQL Native Client 11.0 Configuration -> Client Protocols -> TCP/IP ->Default Port 1433. There're 3 services, SQL Server(MyInstanceName), SQL Server Agent(MyInstanceName) and SQL Server Browser. The last two is stopped.

SQL Server Configuration Manager-> SQL Server Network Configuration->Protocols for MyInstanceName -> TCP/IP is disabled by default, after I enabled it and restart the service and repeat the netstat -ano | find /I "newPIDNum" command I found the

TCP    0.0.0.0:52395          0.0.0.0:0              LISTENING  5668(newPIDNum)

Why 52395 instead of 1433? And I failed to create the DSN using Windows ODBC Data Source administrator which returns me an error: specified SQL server not found.
Any help for me? Thanks.

What made my SQL server listen for TCP connections on port 1433 was the following...

Configuration Tools / SQL Server Configuration Manager
click the instance name in SQL Server Network Configuration
enable TCP communication
Right-click the TCP communication entry, choose Properties
Click the IP addresses tab
Enable all non-VMnet IP addresses and enter 1433 in the Port entry for "IPAll".
Restart the SQL Server on SQL Server Services tab
Check your configuration with netstat -an:

C:\>netstat -an | find "1433"
  TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
  TCP    [::]:1433              [::]:0                 LISTENING


Get 2 Most Recent Records By Product I am stumped.  I need to return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different.

PRODUCTID  CREATEDATE    STATUS    LEVEL
ABC        1/3/2016      fail      1
ABC        1/4/2016      pass      2
ABC        1/5/2016      pass      3
DEF        1/1/2016      pass      1
DEF        1/2/2016      pass      1
DEF        1/10/2016     pass      1
DEF        1/11/2016     fail      1
GHI        12/29/2015    pass      1
GHI        12/30/2015    fail      1
JKL        1/1/2016      pass      1
JKL        1/2/2016      pass      1

Based on values above the following would be returned:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE

Any help is greatly appreciated.

UPDATED POST with additional logic.

I added LEVEL column.  The query needs to return as described above.

Return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different.

If a ProductID has only one record total, and it is PASS, it would be FALSE.  There needs to be two 'pass' in a row.

The below query works like this:

It first uses the ROW_NUMBER window function to partition it by PRODUCTID and ORDER them by CREATEDATE from 1 to n
For STATUS = 'pass' you give 1 (0 otherwise)
You then only keep the first 2 values with n <= 2 (-> 1 or 2)
Finally if the sum of the 2 rows for a given PRODUCTID is equal to 2 and the count of distinct LEVELs is 2, it returns TRUE.

Query:

SELECT PRODUCTID
    , CASE WHEN SUM(STATUS) = 2 AND COUNT(DISTINCT LEVEL) = 2
          THEN 'TRUE'
          ELSE 'FALSE'
      END
FROM (
    SELECT PRODUCTID, LEVEL
        , STATUS = CASE WHEN STATUS = 'pass' THEN 1 ELSE 0 END
        , n = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC)
    FROM data
) agg
WHERE n <= 2
GROUP BY PRODUCTID

See SQL Fiddle.

Output:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE


SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds Ultimately, what I am trying to do is create an SSIS package in the QA environment for another team which truncates the destination tables and inserts from the source tables.  However, on one table, ITEMMAST, I am receiving an error due to a unique constraint violation when trying to copy the data using a data flow task.

What is odd is that a SELECT...INSERT using a linked server succeeds!  However, I can't use linked servers because this other team wants to be able to specify the environment for the source so I have to parameterize the connection strings.

Here is the definition of the unique constraint:

ALTER TABLE [dbo].[ITEMMAST]
  ADD CONSTRAINT [ITESET1]
  PRIMARY KEY CLUSTERED
  (
      [ITEM_GROUP] ASC,
      [ITEM] ASC
  ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
  ON [PRIMARY];

I've tried dropping the primary key constraint, copying the data, and then recreating the primary key constraint but that also produces an error.

  Msg 1505, Level 16, State 1, Line 46
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ITEMMAST' and the index name 'ITESET1'. The duplicate key value is (SALO ,                                 ).
  Msg 1750, Level 16, State 0, Line 46
  Could not create constraint. See previous errors.
  The statement has been terminated.

A developer gave me the following query to run which helped me identify the culprits, one row with 32 spaces in the ITEM column and another row with the ASCII character of 0, a null character, in the ITEM column.

SELECT TOP 10
patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM) as [Position],
 substring(ITEM,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM),1) as [InvalidCharacter],
 ascii(substring(ITEM,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM),1)) as [ASCIICode]
,*
 FROM Lawson.dbo.ITEMMAST
WHERE ITEM_GROUP = 'SALO'

Here is more information about the QA environment.  These settings match our PROD environment so I'm unable to modify QA to make this work.  I've thought about altering the unique constraint definition and changing the destination database collation.

Source Database Collation: Latin1_General_BIN
Collation for the ITEM Column in the Source Table: Latin1_General_BIN

Destination Database Collation: SQL_Latin1_General_CP1_CI_AS
Collation for the ITEM Column in the Destination Table: Latin1_General_BIN

I checked that the codepage for the Latin1_General_BIN collation is 1252 which matches the codepage for the SQL_Latin1_General_CP1_CI_AS collation.

Does anyone have any suggestions for other things that I can try?  I'm new to SSIS so there might be a setting that I did not set correctly.

[UPDATE #1]

Screen shot showing the result of the following query from the source table.

SELECT TOP 2
 CONVERT(VARBINARY(500), [ITEM])
,ITEM COLLATE SQL_Latin1_General_CP1_CI_AS AS [ITEM_Collated]
,*
FROM PROD.dbo.ITEMMAST
WHERE ITEM_GROUP = 'SALO'

I had the O.P. check for duplicates in the destination table. The following query was used:

SELECT ITEM_GROUP, ITEM, COUNT(*)
FROM Lawson.dbo.ITEMMAST
GROUP BY ITEM_GROUP, ITEM
HAVING COUNT(*) > 1;

The result was:

  0x2020202020202020202020202020202020202020202020202020202020202020

The row starting with 7 "nul" characters (i.e. CHAR(0) or 0x00, see image #2 in the question) was replaced with a row of all spaces.

The problem is that the first character is 0x00, which is the null terminator for strings. Pulling that value into SSIS (i.e. .NET) probably viewed it as a simple empty string. And inserting an empty string into a CHAR() column (i.e. a blank-padded datatype) will naturally leave you with all spaces. Hence your duplicate row.

At this point, I would say that it's simply bad data, unless you feel a need to preserve an invalid string (no string should ever have anything after a 0x00, nor should it really even have the 0x00, but if it does, it would be the last character).

So, you can correct that row at the source, but it can't be an empty string since that would get the same PK violation that you are running into now. Is there a default dummy value?

Or, if that row is truly invalid / not used, then I would remove it, OR maybe find out how that row got there in the first place. Did someone manually enter it, did the app create it? Was it a botched import? You might take this opportunity to add a CHECK constraint to ensure that no row has the 0x00 character in it in the future (this would help identify the culprit in the future). Then delete the row from QA and move on... :)

I suppose you can always try to exclude that row if need be, but it would be best to prevent it from ever existing. It will never appear correctly when selected. And there might be other negative impacts downstream in the future. How much time have you (and I ;) spent on finding / fixing this? Do you really want to do it again, or have someone else hunt this down? If that row could never show up in prod, then it should be removed from QA.


Replicating database structure locally In my setup for a web application using PHP and MySQL, I am using separate databases for each user's private data. So in essence, every authenticated user will have access to only two different databases myappdb_public and myappdb_user_###. User credentials will be saved in a separate database myappdb_creds. The idea behind this setup is to increase the security of private data while maintaining a shared data resource.

Is there a way to create a new database from a "template" of another database structural setup.
Is there a way to update all "child" databases structures when the template is updated?

I have used replication before, but that was a totally different setup and not sure if there is a way to replicate structure only across multiple databases on the same local server.

Replication

"Database Replication" refers to dynamically copying any incoming insert/update/delete statements being made to one database to another identical database. The primary and secondary databases are considered "replicas" because at all times they contain the same data produced by the same history, though the secondary may be delayed by a small stretch of time. The secondary may be used for read-only queries in place of the primary. And the secondary may be transformed into a primary in case of fail-over.

Migration

What you are seeking is loosely called  "Database Migration", also called schema migration. The idea here is to reproduce the same structure (DDL) as another database. But each produced database is independent of the others, each having different users’ data stored in them.

A prime example example is multi-tenancy.
Another use might be as part of a vertical-market app being licensed to various customers where on each new sale you need to establish a fresh database in a certain configuration as expected by the app.

Migration tools

Developers and DBAs used to do this migration work manually, devising their own ad-hoc solutions or habits. Fortunately we now have a choice of tools to help with this tedious though crucial chore.

Flyway

My favorite tool is Flyway. Built in Java, but wrapped as command-line  executables so you can use from scripts or other tools.

Flyway runs through a collection of SQL scripts, and optionally certain Java libraries you may write. The order of execution is determined by a certain file naming scheme.

A table is automatically created within the database to track scripts run. When new scripts are encountered, Flyway detects them and appropriately runs those that have not yet been applied to that particular database.

Flyway or other such tools are extremely helpful not only in production but also in testing and development. You can recreate a throw-away database automatically, and even bring it up to any particular point in the history of its schema evolution.

Anyone considering Flyway should also consider another quite similar product: Liquibase.

Other choices exist as well, such as the commercial tool Redgate.

Templates

Some databases such as Postgres provide for templates where you can create some tables and indexes and so in a database. After that, you can create a fresh new database whose initial state is copied from that state.

Indeed, Postgres always works that way, where template0 and template1 databases are used transparently by default when you ask for a database without explicitly specifying any other template.

Templates only solve the genesis problem, getting started. You will almost certainly evolve your database later. You'll add or drop tables, columns, indexes, and so on. You may need to run code to convert or update existing data en masse. For this you'll want to use migration tools, as the templates cannot help you after that initial genesis. So in my opinion, I would not bother with defining a template, instead using migration scripts for all that initial setup.


How to get Identity Column to jump ahead (by say 1000) on SQL Server 2012 I am using SQL Server 2012 and I would like to keep my pre-existing data but to cause the ID (identity) column to skip by 2000 rows.

Why is this happening? I have a scenario where I may have transactions happening on a standby server which I will need to copy to current server. I want the IDs to match. Therefore I do not want any new transactions on this server that would have the same ID as my standby server.

Hopefully this makes sense!! Thank you

You could do this by running something like

declare @newid int;

select @newid = IDENT_CURRENT('YourTable') + 2000;

dbcc checkident('YourTable', reseed, @newid);

This would reseed your table identity to be 2000 higher than it currently is and seems like it should resolve what you are looking to do.


Rewriting a query to be more efficient Currently I'm using:

SELECT t.CustomerLastName
FROM (
    SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName
    FROM OrderLine, Product, Orders, Customer
    WHERE OrderLine.ProductId = Product.Id
    AND Orders.Id = OrderLine.OrderId
    AND Customer.Id = Orders.CustomerId
    GROUP BY Orders.Id, Customer.Lastname
    ) AS t
WHERE t.OrderLinePrice =
(
    SELECT MAX(s.OrderLinePrice) AS MaxOrderPrice
    FROM (
        SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName
        FROM OrderLine, Product, Orders, Customer
        WHERE OrderLine.ProductId = Product.Id
        AND Orders.Id = OrderLine.OrderId
        AND Customer.Id = Orders.CustomerId
        GROUP BY Orders.Id, Customer.Lastname
        ) AS s
)
ORDER BY CustomerLastName

As my query to fetch the list of names for which the price of their order is equal to the price of the most expensive order. Now having 2x more or less the same subquery in one query seems horribly inefficient, so I'm wondering if, how and where to start with optimising this. Any tips?

You could try to use top (1) with ties.

SELECT TOP (1) WITH TIES
  SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice,
  Orders.Id,
  Customer.Lastname AS CustomerLastName
FROM OrderLine, Product, Orders, Customer
WHERE OrderLine.ProductId = Product.Id
  AND Orders.Id = OrderLine.OrderId
  AND Customer.Id = Orders.CustomerId
GROUP BY Orders.Id, Customer.Lastname
ORDER BY OrderLinePrice DESC


How do I create a view with new snake_case columns (replacing camelCase) and remaining original columns? (using the output of `FORMAT`) Unfortunately I am in a position where I have to use camelCase column names for my database, as the source data dictates so.

However, I am using an application where I can only query for snake_case columns (as discussed here).

I want to create a view table that has all the same columns, except those with camelCase names replaced with a snake_case name, so this query

SELECT * FROM view_name;

will return

| id  | name | column_one |...|
|-----|------|------------|---|
| ... | ...  | ...        |   |

I can do this manually like this:

CREATE VIEW view_name AS SELECT id, name, schema.table."columnOne" AS column_one FROM schema.table

but I have too many columns, is there a way to do this automatically for all columns?

UPDATE 1:
Made progress on this, I am able to get the string of all the columns I need from one table like this, but I still don't know how to use it with CREATE VIEW:

(SELECT FORMAT(
  '%I.%I.%I."%I",',
  table_catalog,
  table_schema,
  table_name,
  column_name
)
FROM information_schema.columns
WHERE column_name !~ ' |[[:lower:]][[:upper:]]'
AND table_name = 'example_table_name')
UNION
(SELECT FORMAT(
  '%I.%I.%I.%I AS %I,',
  table_catalog,
  table_schema,
  table_name,
  column_name,
  lower(
    -- replace all spaces with _, xX and Xx becomes x_x
    regexp_replace(
      -- First, replace spaces with an _
      replace(column_name, ' ', '_'),
      '([[:lower:]])([[:upper:]])',
      '\1_\2',
      'g'
    )
  )
)
FROM information_schema.columns
WHERE column_name ~ ' |[[:lower:]][[:upper:]]'
AND table_name = 'example_table_name')

which results in

                                    format
------------------------------------------------------------------------
 db.schema.table."id",
 db.schema.table."name",
 db.schema.table."columnOne" AS column_one,
 db.schema.table."columnTwo" AS column_two

I want to CREATE VIEW from the output of format, how can I do this?

If you're the TL:DR; type, please find the solution here

with snakes as (
    select
        ordinal_position,
        table_catalog,
        table_schema,
        table_name,
        format('%I.%I.%I',table_schema,table_name,column_name) as full_name,
        lower(
            -- replace all spaces with _, xX and Xx becomes x_x
            regexp_replace(
                -- First, replace spaces with an _
                replace(table_name, ' ', '_'),
                '([[:lower:]])([[:upper:]])',
                '\1_\2',
                'g'
            )
        ) as snake_case_table,
        lower(
            -- replace all spaces with _, xX and Xx becomes x_x
            regexp_replace(
                -- First, replace spaces with an _
                replace(column_name, ' ', '_'),
                '([[:lower:]])([[:upper:]])',
                '\1_\2',
                'g'
            )
        ) as snake_case_column
    from information_schema.columns
    where table_catalog =  current_database()
      and table_schema in ('public')
), tables as (
    select
        format('%I.%I',table_schema,'vw_'||snake_case_table) as view_name,
        string_agg(
                full_name||' as '||snake_case_column,
                ','||chr(10)||'        ' order by s.ordinal_position

        ) as column_list,
        format('%I.%I',table_schema,table_name) as base_table
    from snakes s
    group by
        s.table_catalog,
        s.table_schema,
        s.table_name,
        s.snake_case_table
)
select
    'create or replace view '||view_name||' '||chr(10)||
    'as '||chr(10)||
    '    select '||chr(10)||
    '        '||column_list||chr(10)||
    '    from '||base_table||';'||chr(10)||chr(10)
    as create_view_command
from tables;

As you can see on the fiddle, the output of this should be one create view command for every table in the current database. Please note I haven't worked with postgres foreign data wrappers, so I've removed the cross-database references (but they should be rather trivial to add back in).

The only material additions I've made to your code are the use of string_agg() and group by. However, re: format('%I."%I"',...), you can lose the double quotes here. As per the docs

  I treats the argument value as an SQL identifier, double-quoting it if necessary. ...


Upgrading using pg_upgrade on windows fails When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails:

RUNAS /USER:postgres "CMD.EXE"

After some investigation, it seems there is no postgres windows user on the machine.  This causes the pg_upgrade command to fail as well.

How should I run the upgrade process?

Open an administrator cmd window as described in the link above.

Make sure you cd into an empty writable folder (for example, c:\temp\somewhere).

Execute the pg_upgrade command, and make sure to add an -u postgres parameter to it:

"C:\Program Files\PostgreSQL\9.3\bin\pg_upgrade.exe" -u postgres
    --old-datadir "C:/Program Files/PostgreSQL/9.2/data"
    --new-datadir "C:/Program Files/PostgreSQL/9.3/data"
    --old-bindir "C:/Program Files/PostgreSQL/9.2/bin"
    --new-bindir "C:/Program Files/PostgreSQL/9.3/bin"


Installing Oracle XE 11gR2 on Fedora 15 - linux group permission deadlock During installation of Oracle XE 11gR2 on Fedora 15, I am presented with:

  The install cannot proceed because ORACLE_BASE directory (/u01/app/oracle) is not owned by "dba" group.  You must change the ownership of ORACLE_BASE directory to "dba" group and retry the installation.

This is frustrating because I have set oracle:oinstall as the owner, and dba is in the oinstall group.

When I change the owner to dba it just complains about the oracle user.

  dba gid=oinstall groups=oinstall,root,oper
  oracle gid=oinstall groups=oinstall,vboxsf,dba  (running in virtualbox)

What am I doing wrong?

You've got the distinction between users and groups mixed up. Putting the dba user in the oinstall group will not mean that the dba group will also have permission to write to /u01/app/oracle when the owning group is oinstall - this is because the dba user and the dba group are completely separate entities.

The Oracle user should be in the oinstall and dba groups. There shouldn't be a dba user, only an Oracle user.

chown oracle:dba /u01/app/oracle

... will fix it.


How to get count of each column data I am trying the fetch the data of the each user of each month. The table looks like below

----------------------------------------------------------------------------------
| user         |  pm_jan      |       actual_jan    |       pm_feb  | actual_feb |
----------------------------------------------------------------------------------
|   u1         |  w5           |     6               |   0           |    0       |
|   u2         |  w2           |     5               |   0           |    0       |
|   u3         |  w6           |     2               |   w7          |    7       |
|   u4         |   0           |     0               |   w6          |    2       |
|   u5         |   0           |     0               |   w2          |    3       |

In the above table now I want to fetch the total count of  pm and actual data of each month until december.

I tried the below but I am able to fetch only the count of jan month but I need it for every month count

    SELECT COUNT(pm_jan),COUNT(actual_jan)
    FROM pm
    WHERE  pm_jan<>'' AND actual_jan<>''

What is the best query to approach this method

SELECT SUM(pm_jan > 0) count_pm_jan,
       SUM(actual_jan > 0) count_actual_jan,
       SUM(pm_feb > 0) count_pm_feb,
       SUM(actual_feb > 0) count_actual_feb
FROM pm


Add additional rows using an SQL query SELECT distinct [product_group]
      ,[product_type_name]
  FROM [Yield_Reporting].[dbo].[d_product]

For every product_group entry I want to have another product_type_name = AllType

So the resulting table should look like this,

I can't figure out how to make that result using an SQL query.

You can hard-code AllType string for the product_type_name value and  UNION this with your existing DISTINCT values you can get your expected result.

Working Demo with the given sample data:

DECLARE @d_product TABLE (product_group VARCHAR (200), product_type_name VARCHAR (200));

INSERT INTO @d_product( product_group, product_type_name) VALUES
('BARE_SILICON', 'BARE_SILICON'),
('Black_Mamba', 'Black_Mamba'),
('BURIN-IN_WAFER', 'BURIN-IN_WAFER'),
('CALVIN', 'CALVIN_BLK');

SELECT DISTINCT [product_group], [product_type_name] FROM @d_product
UNION
SELECT DISTINCT [product_group], 'AllType' FROM @d_product

Output:

product_group      product_type_name
---------------------------------------
BARE_SILICON       AllType
BARE_SILICON       BARE_SILICON
Black_Mamba        AllType
Black_Mamba        Black_Mamba
BURIN-IN_WAFER     AllType
BURIN-IN_WAFER     BURIN-IN_WAFER
CALVIN             AllType
CALVIN             CALVIN_BLK


Conversion failed when converting the varchar value 'Hours' to data type int Conversion failed when converting the varchar value 'Hours' to data type int. This is my query:

Sum(Cast(Cast(DATEDIFF(HH, ld_time_tracker_start_time,
    fld_time_tracker_end_time)%3600/60 As int) +' Hours'+
Cast(DATEDIFF(MINUTE, ld_time_tracker_start_time,
 fld_time_tracker_end_time)As int )+'Minutes' As Int))
As [Time Spent(In hrs)]

I modified the above Code To:

+Cast('Hours' As Int)
+Covert(int,'Hours')

... and got the error message:

  Conversion failed when converting the varchar value ' Hours' to data
  type int

This is not clear what output is needed.
You are trying to convert text strings to int. This obviously does not work. Besides this type of formatting might fit better outside of SQL Server.

If you want something like 5 Hours 15 Minutes you can try this query:

[Time Spent(In hrs)] =
    CAST(
        SUM(
            DATEDIFF(hour, ld_time_tracker_start_time, ld_time_tracker_end_time)
        )
    as varchar(10))
    +' Hours '+
    CAST(
        SUM(
            DATEDIFF(minute, ld_time_tracker_start_time, ld_time_tracker_end_time) % 60
        )
    as varchar(10))
+' Minutes'

Note that I have replaced HH by hour. This is easier to read and understand and it avoid mistakes. See DATEDIFF (Transact-SQL).


Triggers on Insert or Update SQL Server (Spatial) I am looking at creating a trigger on a live SQL Server spatial table that will update a column when a user either:

Creates a new feature
Updates the geometry of a feature in a GIS application

Unfortunately I have only been able to work out updating a column when a new feature is created, or updating a column when the geometry column is changed, but not both within one trigger.

Ideal world - attribute Updated populates current date for the particular feature that has been created or modified.

This is what I have so far that works only when creating a new feature:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER *[triggername]* ON *[table]*
AFTER INSERT,UPDATE
AS BEGIN
    DECLARE @ID VARCHAR(8)

    SELECT @ID = [ID] FROM *[table]*

    UPDATE *[table]*
    SET Updated = GETDATE()
    WHERE [ID] = @ID
END
GO

This works when the geometry is changed

CREATE TRIGGER [triggername] ON [table]
FOR INSERT, UPDATE
AS BEGIN
    UPDATE [table]
    SET [table].Updated = Getdate()
    FROM [table]
    INNER JOIN deleted d ON [table].id = d.id
END
GO

Isn't this what you're looking for ?

CREATE TRIGGER [triggername] ON [table]
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE [table]
    SET [table].Updated = Getdate()
    WHERE ID IN (SELECT ID FROM INSERTED)
END
GO


Cannot allocate memory for the buffer pool when using remote access MySQL I tried to separate the webserver and mysql server.
MySQL server = 16GB RAM and the config of Innodb_buffer_pool_size = 10GB. And
my Web server = 4GB RAM

My website connect to my database server using remote access with IP assigned. When I running a website it feels very slowly and then web goes down with the log "Cannot allocate memory for the buffer pool"

Does the webserver need more RAM for Innodb_buffer_pool_size too?
Because what I know is that the webserver only run httpd not mysqld. And mysqld handled by remote mysql server.

In my case the web server still need to increase size for Innodb_buffer_pool_size.

Already add "skip-name-resolve" but no luck.

Is anyone here encounter same issue with me?

"Separate" -- meaning they are on 2 different servers?

If so, then 10GB for the buffer_pool should be fine for a mysql-only server with 16GB of RAM.

If you have both on the same server, set the buffer_pool to

70% * (16G - (ram needed for web server))

  down with the log "Cannot allocate memory for the buffer pool"

Something is hogging memory.  It could be:

Other processes
Settings in mysql that are too high.  Did you change some other settings?

Or are you getting that error on the separate web server?  If so, then you have MySQL there!


Apply operation on all schemas I have an Oracle instance, with several schemas (all starting with the same prefix).

PATT_A
PATT_B
PATT_C
...

All of them have the same table, and I'd like to update all of them in one shot (with SYSTEM account).

I know I can get these schemas doing so :

SELECT username FROM dba_users WHERE username LIKE 'PATT_%' ;

And I would like to apply the same query on them, like this one :

UPDATE table1 SET field1 = 'new_value' WHERE field2 = 'filter' ;

I assume something like a loop could do the job, but I don't know how to use it here.

You can do it with Native dynamic SQL ( http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011 ) using the EXECUTE IMMEDIATE statement.
Here's an example:

DECLARE
 v_sql varchar2(128);
 v_username varchar(30);
 cursor c1 is
    select username from dba_users where username like ('PATT%');
BEGIN
    for REC in c1 loop
        v_sql := 'UPDATE ' || REC.username || '.table1 SET field1 = ''new_value'' WHERE field2 = ''filter'''  ;
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/

Otherwise, you can make a script with each needed statement with a simple query like

SELECT '''UPDATE ' || username || '.table1 SET field1 = ''new_value'' WHERE field2 = ''filter'';' from dba_users where username in ( 'PATT_%' );


How to call a stored procedure in an Oracle Scheduler job I want to call a stored procedure in an Oracle Scheduler job. My procedure and job are shown below:

Stored procedure

create or replace PROCEDURE emp_test
IS
begin

delete from  emp;
insert into emp(empid,empname)
select empid1,empname1 from emp_temp;
end;

Job

BEGIN
dbms_scheduler.create_job (
job_name => 'emp_test_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
CALL emp_test();
END;',
start_date => SYSTIMESTAMP,
enabled => true,
repeat_interval => 'FREQ=DAILY');
END;

I am able to call procedure like CALL emp_test() with out using a job.

When I am using same thing in a job like above, the job was created successfully but the procedure is not running, rows are not inserted. I need run this job and rows should be inserted; can any one help me please?

You don't need CALL when creating a job (and parenthesis for a procedure that takes no arguments ).  I'd also specify  INTERVAL=1 explicitly.

BEGIN
   dbms_scheduler.create_job (
    job_name => 'emp_test_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'emp_test;',
    start_date => SYSTIMESTAMP,
    enabled => true,
    repeat_interval => 'FREQ=DAILY;INTERVAL=1'
   );
END;


Table partitioning and future values I have a clustered columnstore table with approximately 1.4 billion rows.
I figured it being a clustered columnstore would best suit my need for various aggregations on many different splits/columns.
There is currently just a single partition, however I would like to split the data into multiple partitions.  I haven't been using partitions before, so I am not quite sure what the best practice is.
The table contains monthly financial information for our customers and will increase with approximately 35 million new rows each month. Each row will have a YearMonth_Id and a Customer_Id as well as a lot of columns containing financials.
I am thinking to do the partition by YearMonth_Id (maybe grouped by quarters?).
I'm no DBA, just a finance analyst.  Since we don't have qualified DBA to maintain this table regularly, it comes down to me to do it; hence I want to have as little maintenance on the table as possible.
My question therefore is as follows. When working with partitions and incremental values (YearMonth_Id), how should I structure the partitions on the table?
I read that usually you would leave an empty partition to the far right (if working with left range) that you can split each month (or quarter).   However, I am thinking of creating, say 12 empty partitions for future values, so the next time I need to do a split will be in a years time; so that the table is not dependent on me adding a new partition every month.
Is this a big no-no or would it be an okay way to go?

Empty partitions have negligible effect on query performance, so having up to 12 empty partitions, one per month, should not be a problem.

Having said that, you could simply automate the creation of partitions, the way I detail in this answer.  That means you'd only ever have a single empty partition, assuaging any fears about performance impacts from too many empty partitions.

Table partitioning is not a panacea for increasing performance and can result in lower performance than a non-partitioned table.  The primary use-case for partitioning is to enable the easy swapping out of old data, and swapping in of new data, using the ALTER TABLE ... SWITCH syntax.  This answer shows the advantages of using ALTER TABLE ... SWITCH to ease data management.

In order to get some performance improvement via partitioning you need to use the partitioning key in queries against the partitioned table.  Queries that don't use the partitioning key in the WHERE clause or in a JOIN clause must inspect all partitions for rows that match the selection criteria.


SQL Server 2012 Performance - Reads per second I am sure this has been asked multiple times - but I could not find a good answer anywhere. Hence this question.

I have a azure cloud service that reads data from a SQL Server 2012 Database. The primary use of this data base is Read-Only. The data gets populated once every month and the cloud service only reads from it (SELECT) (Yes - I could move this to a key-value store - but as of now I cannot change the way things are)

What would be a good way to estimate the number of SELECT operations that this database can handle per second. The machine on which this runs is a 4 core, 7GB ram azure vm. I am just looking for some ballpark numbers to reasonably estimate the number of users my service can handle. The table is clustered on the column which I will filter (WHERE) results on.

Would greatly appreciate help with this.

--EDIT--

Specifying more details based on a reply below:

What level of performance is acceptable?

My end-user SLA should be around 2 seconds. So I expect the DB to return in approx 100 - 500 milliseconds

How many rows are being returned from these Selects?

On an average I might be returning around 50 rows per select call. Total data being < 200KB

Size of the database

Around 15M records totalling around 150GB

I understand that this is a vague question - would be great to get some numbers from folks based on their experiences.

The question itself is too nebulous. You asked how many Selects per second the database can handle? Well, can handle before what happens? What level of performance is acceptable? Do you need results returned in milliseconds, seconds, minutes? What is acceptable? How many rows are being returned from these Selects? What does the query plan look like?

Ultimately no one here is going to be able to tell you what you want to know. There are too many details you didn't tell us. But you can certainly find out for yourself. It's not at all difficult to stress test your database, to find this out for yourself. If you are a developer, you could easily write a C# program that creates threads that performs these Selects. It would be quite easy to measure performance as you gradually increase the number of threads. If you are not a developer, I have to believe there are stress test apps you can buy, or find open source, that will enable you to measure this. If determining a rough idea of how many users your database can accept is that important, you need to be doing this anyway.


Find most frequent values for a given column I have a table that I would like as a leader-board for invitations as described below. I would like to create a query that counts the number of duplicate rows in a given month and order in a descending fashion.

Reading through some questions, this query seems to work:

SELECT COUNT(invite_code) AS counted
FROM invite_table
GROUP BY invite_code
ORDER BY counted DESC
LIMIT 10;

But it doesn't consider the month. What I am looking for is to get the most frequently appearing user_code where the month is specified. Also any criticism about the table design is welcome as I have deliberately designed it such that there are repeating rows with duplicate values. I am trying to track users whose invite code is used the most in a given month, I also have codes that indicate which channel a user comes from (maybe seeing an ad in FB for example), is this a valid table design?

Table "public.invite_table"
   Column    |         Type         | Collation | Nullable | Default
-------------+----------------------+-----------+----------+---------
 user_code   | character varying    |           | not null |
 invite_code | character varying    |           |          |
 month       | character varying(3) |           | not null |
 points      | integer              |           | not null |
Indexes:
    "invite_table_pkey" PRIMARY KEY, btree (user_code)
Foreign-key constraints:
    "invite_table_user_code_fkey" FOREIGN KEY (user_code) REFERENCES user_table(user_code)

get the most frequently appearing user_code where the month is specified

Since user_code is the primary key, that question would be nonsense. There can never be more than one. I assume you meant invite_code?

Just add a WHERE clause. And since the column can be NULL, also consider excluding NULL values:

SELECT invite_code, COUNT(*) AS counted
FROM   invite_table
WHERE  month = 'May'  -- or whatever is stored in your varchar(3) column
AND    invite_code IS NOT NULL -- exclude NULL
GROUP  BY invite_code
ORDER  BY counted DESC, invite_code  -- to break ties in deterministic fashion
LIMIT  10;

Month, date, timestamp?

A month column as varchar(3) doesn't seem very useful if there can be data for more than a single year. I would use data type date for it. You can format that with to_char() any way you like for presentation. Like:

SELECT to_char(date '2017-12-01', 'Mon');  -- 'Dec'

The column could look like this (also addressing your comment):

...
, inserted_at date DEFAULT CURRENT_DATE
...

The default value is entered when the column is omitted in an INSERT statement.

Or, if really only the month is relevant:

... DEFAULT date_trunc('month', now())::date

Or store the complete timestamptz (8 bytes, that's what I would probably do):

...
, inserted_at timestamptz DEFAULT now()
...

Read the manual here and here.

And be aware that date and timestamp depend on your current time zone setting. Details:

Ignoring timezones altogether in Rails and PostgreSQL


MongoDB - Should I separate the journal and the data to different drives? I have seen and heard from multiple sources that it can be a good idea, performance-wise, to write your db journal and your data files to separate disks.

What's the recommended way to do this (e.g. on EC2)?

If we write our data files to RAID (e.g. of EBS drives), where would most people write the journal to?
Should I just use completely separate (RAIDed) set of drives
Should I use a single non-RAID drive?
Should I just leave data and journal on the same RAID?

The only time that moving the journal is an absolute recommendation is if you have to use a direct NFS mount - NFS is not recommended for MongoDB in general but in particular it does not play well with the journal.

In general, the journal will have quite a different access pattern to the rest of your data (sequential versus random access).  Hence it is often a good idea to separate the journal and the data from a performance perspective.  Note that this is a very broad generalization and will vary depending on your usage of MongoDB, but will generally be true.

Since your questions are largely about EC2 and EBS then NFS will not play a part here, but I thought it worth mentioning in a broader context, onto specifics.

For EC2/EBS, you will generally only need to separate the journal out if you are seeing write IO contention (or overall IO contention given the nature of EBS) - it will move IO to a different disk and free up some capacity on the data disk.  Of course, with EBS your IO is also dependent on the network IO available on the instances and that is dependent on your instance size (and whether you have opted for P-IOPS), hence a lot of variables.

If you are seeing high IOWait times and your disk looks write bound (see IOStat), this is something you should consider, but only as a temporary measure, because it will only be a small tweak compared to increasing available IO.  There are plenty of options available there depending on your starting point, like adding more EBS drives to the RAID or by taking advantage of P-IOPS provisioning.  You can also occasionally get better performance by changing instance type so that you have less network contention.  Each of these may be more effective than moving the journal and have less headaches.

To explain, there are other considerations here - snapshots for one.  Thanks to the journal you no longer have to fsync and lock the database to get a consistent snapshot (be it EBS or LVM or other).  However the journal has to be included in the snapshot for that to be the case.  Hence whatever node you use for backing up, if you intend to snapshot without taking downtime for that node, then you need to make sure the journal is included.

Finally, one of the uses of the journal is to facilitate recovery from an unclean shutdown, such as an OS level crash/reboot.  If the journal is on the ephemeral disk in EC2, it will be blown away by such a reboot and hence not be useful in that context.  Any such crash/reboot with such a configuration would therefore require a resync from scratch or a restore from backup/snapshot should it occur.

Overall, like most configuration decisions, you have to weigh the pros and cons and pick the solution most relevant for your use case.  Hopefully this will give you enough information to make an informed choice.


Get rows only joined to certain types of row in another table I have rows in a person table.  Each can be linked to multiple rows in a thing table via a link table.  Each thing has an objectType.  I want to get all rows in the person table which are ONLY linked to three specific objectTypes and I only want one row per person.  I'm a SQL newbie and I don't know where to start.  I've set up a sample:

http://sqlfiddle.com/#!18/56bd69/2

Just using INNER JOINS and WHERE doesn't work because it returns all the person rows.

Untested (seemed to be some technical problem with fiddle), but you might be looking for the Exist predicate

SELECT p.*
FROM person as p
WHERE EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE t.objectType IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

EDIT:
With info from the comment, it is clear that what you want persons that have all 3 object types (I assumed any). In SQL there is no ALL quantifier (contrary to EXISTS). It is, however, possible to do the transformation:

ALL x:p(x) <=> NOT EXISTS x:NOT x

which as noted by you in the comment would result in:

SELECT p.*
FROM person as p
WHERE NOT EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE t.objectType NOT IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

Another option is to count which persons that have exactly 3 objects among the 3:

 SELECT p.rowkey
 FROM person as p
 JOIN link as l
     ON p.objectId = l.personObjectId
 JOIN thing as t
     ON t.objectID = l.thingObjectId
 WHERE t.objectType IN ('fork','knife','eraser')
 GROUP BY p.rowkey
 HAVING COUNT( DISTINCT t.objectType ) = 3;

These are the two most commonly used techniques for relational division that I know of.


SQL query to return a columns with given range I have a table tblItems with records having the same UniqueID and Description but with different dates and amount.
Now I want to show the records by grouping them together using the UniqueIDs and then display the Amount under a dynamically created Date column using the same record.
Date range will be provided as parameter like start date and end date and based on the date range there might be N number of columns.
Described with the example....
tblItems table
╔════════════╦════════════════════╦══════════╦═════════╗
║ UNIQUECODE ║    DESCRIPTION     ║   DATE   ║ AMOUNT  ║
╠════════════╬════════════════════╬══════════╬═════════╣
║ ABCD       ║ Item Decsription 1 ║ 1/3/2014 ║ 2068    ║
║ PQRS       ║ Item Decsription 2 ║ 1/5/2014 ║ 412.104 ║
║ PQRS       ║ Item Decsription 2 ║ 1/5/2014 ║ 635.448 ║
║ UVWX       ║ Item Decsription 3 ║ 1/5/2014 ║ 214.52  ║
║ UVWX       ║ Item Decsription 3 ║ 1/7/2014 ║ 827.2   ║
╚════════════╩════════════════════╩══════════╩═════════╝

I want to display this result as:
╔════════════╦════════════════════╦══════════╦══════════╦══════════╦══════════╗
║ UNIQUECODE ║    DESCRIPTION     ║ 1/3/2014 ║ 1/5/2014 ║ 1/7/2014 ║  Total   ║
╠════════════╬════════════════════╬══════════╬══════════╬══════════╬══════════╣
║ ABCD       ║ Item Decsription 1 ║     2068 ║ 0        ║ 0        ║ 2068     ║
║ PQRS       ║ Item Decsription 2 ║        0 ║ 1047.552 ║ 0        ║ 1047.552 ║
║ UVWX       ║ Item Decsription 3 ║        0 ║ 214.52   ║ 827.2    ║ 1041.72  ║
╚════════════╩════════════════════╩══════════╩══════════╩══════════╩══════════╝

Your best bet is probably to use dynamic SQL to pivot your data. Here try this out.

IF OBJECT_ID('tempdb..#tblItems') IS NOT NULL
    DROP TABLE #tblItems;
IF OBJECT_ID('tempdb..##tblItemsTotal') IS NOT NULL
    DROP TABLE ##tblItemsTotal;

CREATE TABLE #tblItems
(
    UniqueCode CHAR(4),
    [Description] VARCHAR(100),
    [Date] Date,
    Amount Decimal(18,3)
);

INSERT INTO #tblItems
VALUES  ('ABCD','Item Description 1','01/03/2014',2068),
        ('PQRS','Item Description 2','01/05/2014',412.104),
        ('PQRS','Item Description 2','01/07/2014',635.448),
        ('UVWX','Item Description 3','01/05/2014',214.52),
        ('UVWX','Item Description 3','01/07/2014',827.2);

--Now that I've set up the problem, here's for the actual code

--Declare your date range and variables to hold column lists
DECLARE @startDate DATE = '01/03/2014',
        @endDate DATE = '01/07/2014',
        @SelectCols VARCHAR(MAX),
        @PivotCols VARCHAR(MAX);

--Caluclate column lists
SELECT  @SelectCols =  COALESCE(@SelectCols + ',','') + 'ISNULL(pvt.' + QUOTENAME(CAST([Date] AS VARCHAR(25))) + ',0) AS ' + QUOTENAME(CAST([Date] AS VARCHAR(25))),
        @PivotCols =  COALESCE(@PivotCols + ',','') + QUOTENAME(CAST([Date] AS VARCHAR(10)))
FROM    #tblItems
WHERE   [Date] BETWEEN @startDate AND @endDate
GROUP BY [Date];

--Calculate your total within data range
SELECT  UniqueCode,
        [Description],
        SUM(amount) Total INTO ##tblItemsTotal
FROM #tblItems
WHERE [Date] BETWEEN @startDate AND @endDate
GROUP BY UniqueCode,[Description];

--Use dynamic SQL to pivot your data and join to ##tblItemsTotal for the Total column
EXEC
(
    'SELECT pvt.UniqueCode,
            pvt.[Description],' +
            @selectCols +
            ',B.Total
     FROM #tblItems A
     PIVOT
     (
        SUM(Amount) FOR [Date] IN (' + @PivotCols + ')
     ) pvt
     INNER JOIN ##tblItemsTotal B
     ON pvt.UniqueCode = B.UniqueCode'
)


Builtin\Administrators doesn't permit access I have created a SQL 2016 (Developer) instance on a new build machine. For ease of use, when we do this, we grant Builtin\Administrators sysadmin rights on the instance. This allows all developers on the machine to be able to have full control over their local instance.

In previous versions of SQL (e.g. 2014). This works fine. However, when I try it on SQL 2016, this does not appear to work.

Whenever I attempt to log into the machine with Windows Authentication, I receive the following error:

  Login failed for user 'xxxx'. (.Net SqlClient Data Provider)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

  Server Name: xxxx

  Error Number: 18456

  Severity: 14

  State: 1

  Line Number: 65536

Below are shots of the Builtin\Administrators group within SQL:

As you can see, it's set up and enabled.

The server is configured for Mixed Mode authentication. If I add a Login for the user directly, Windows Authentication will work successfully.

Below is the error log from SQL, which includes a bit more information:

For clarity, the user is in a group which is a member of the Administrators group on the machine. Running whoami /groups on the commandline confirms that the local Administrators group is listed.

What do I need to do to get Builtin\Administrators to allow successful logins?

As commented, UAC is blocking your connection to SQL Server. Run it as administrator and it should be fine.


Getting Time Duration I have a table like below: I want to get time duration for each record which SPEED is greater than 100. I written  a function for that, it’s working fine , but my problem was i have millions of records these table so while i am calling these function from a stored procedure it is taking too much time.

Can you please suggest any other option for this..

Here is my table structure and data

---------------------------------------
ID            SPEED          TRACKTIME
---------------------------------------
1              50        2014-10-01 00:20:00
2              80        2014-10-01 00:21:00
3              110       2014-10-01 00:21:30
4              105       2014-10-01 00:22:10
5              97        2014-10-01 00:23:00
6              65        2014-10-01 00:25:00
7              107       2014-10-01 00:26:00
8              101       2014-10-01 00:27:00
9              41        2014-10-01 00:29:00
10             70        2014-10-01 00:30:00
-------------------------------------

My function:

CREATE FUNCTION [dbo].[udf_GetOverspeedMinute]
(
    @vehicleID      INT,
    @starttime      DATETIME,
    @endtime        DATETIME,
    @speedlimit     DECIMAL(18, 2)
)
RETURNS @tmp_overspeed TABLE(ID INT IDENTITY(1, 1), TimeInMinute INT)
AS
BEGIN
    DECLARE @tmp_tracking TABLE (
                ID INT IDENTITY(1, 1) PRIMARY KEY,
                TrackingID INT,
                Speed DECIMAL(18, 2),
                TrackTime DATETIME
            )

    INSERT INTO @tmp_tracking
      (
        TrackingID,
        Speed,
        TrackTime
      )
    SELECT Tracking.ID,
           Speed,
           TrackTime
    FROM   Tracking WITH(NOLOCK)
    WHERE  VehicleID = @vehicleID
           AND TrackTime BETWEEN @starttime AND @endtime

    ;WITH cte_speed AS (
        SELECT RN = ROW_NUMBER() OVER(ORDER BY TrackTime),
               *
        FROM   @tmp_tracking
    )

    INSERT INTO @tmp_overspeed
      (
        TimeInMinute
      )
    SELECT SUM(
               DATEDIFF(MINUTE, [Current Row].TrackTime, [Next Row].TrackTime)
           )
    FROM   cte_speed [Current Row]
           LEFT JOIN cte_speed [Next Row]
                ON  [Next Row].RN = [Current Row].RN + 1
    WHERE  [Current Row].Speed > @speedlimit

    RETURN
END

You don't say what DB you are using, but that syntax looks like MS SQL Server.

Also you do not properly state the table structure - the function refers to VehicleID which doesn't appear in you sample data and you don't say what the data types or what indexes and keys are present. All of this could be significant.

You are pulling a bunch if data into a table variable and processing it there which could be a problem - if there are many rows per vehicle this will hit disk and cause slowness due to that IO.

With SQL Server 2012 or later this is quite easy to do using window functions:

SELECT ID, DATEDIFF(MINUTE
            , TrackTime
            , LEAD(TimeTrack) OVER (ORDER BY TimeTrack)
            )
FROM Tracking
WHERE VehicleID = @VehicleID
AND TrackTime BETWEEN @starttime AND @endtime
AND Speed > @speedlimit
ORDER BY TimeTrack

For this to be efficient you'll at very least need an index covering VehicleID. Better still either one covering VehicleID & TrackTime (perhaps including speed & ID), or have VehicleID & TrackTime be your clustered index (though for a definite recommendation we need to know a lot more about the other queries the table sees: picking your clustered index to optimise this query might show others).

I'd your indexes are right this should pass over the data once, with and index seek rather than a scan (or worse, a table scan) and no spooling to disk unless the data per vehicle is really massive.

If you want results for multiple vehicles then you need to tell the lead function to split by VehicleID so it becomes LEAD(TimeTrack) OVER (PARTITION BY VehicleID ORDER BY TimeTrack), and add VehicleID to the main ordering clause too.


What are the best practices for restricting write-access on a specific table? I have a table, which I'd like to be read-only, except for a few specific stored procedures which need permissions to UPDATE rows in the table.

After that, I want only a select group of additional stored procedures to have permissions to EXECUTE the aforementioned UPDATE procedures.

I've been reading about GRANT and DENY on MSDN/TechNet, and I've also been reading about schemas, but the documentation is so vague and non-relatable that I'm having trouble figuring out how to accomplish real-world security goals based on the descriptions provided.

I can see that you can GRANT or DENY permissions for individual principles, one at a time - but I need to deny INSERT UPDATE and DELETE permissions for ALL principles, EXCEPT for the few stored procedures to which I am granting UPDATE permissions (as well as sa and/or db_owner I suppose).
I don't want any new users to have UPDATE permissions on this table by default, and I would really like to avoid having to manually deny permissions every time we create/modify users or other objects.

By default, no direct access is permitted to either tables or stored procedures.  One must explicitly grant access in order for non-privileged users to have access.  The best practice to simplify security administration is to grant permissions to roles, and control access via role membership.

If you want to restrict access only via stored procedures, take a look at ownership chaining (https://technet.microsoft.com/en-us/library/ms188676).  This allows you to grant execute permissions to individual roles/users without allowing direct access to underlying tables as long as the object involves have the same owner (default is dbo).

Using ownership chaining in your scenario, you could grant only SELECT permission on the tables, with updates allowed only by users with execute permission on the update procs.  No direct table modifications would be allowed.

Edit:

Below is an ownership chaining example.  Note that neither user can update the table directly and the write user can update the table only using the stored procedure.  The implication of ownership chaining is that when you grant execute permissions on a stored procedure, users can access all of the encapsulated objects without direct object permissions as long as the objects have the same owner.

--create objects
CREATE TABLE dbo.MyTable(
      MyTableID int IDENTITY NOT NULL
        CONSTRAINT PK_MyTable PRIMARY KEY
    , SomeData int
    );
GO

CREATE PROC dbo.UpdateMyTable
      @MyTableID int
    , @SomeData int
AS
UPDATE dbo.MyTable
SET SomeData = @SomeData
WHERE MyTableID = @MyTableID;
GO

--create roles
CREATE ROLE TableReaderRole;
CREATE ROLE TableWriterRole;
GO

--grant permissions to roles
GRANT SELECT ON dbo.MyTable TO TableReaderRole;
GRANT EXECUTE ON dbo.UpdateMyTable TO TableWriterRole;
GO

--create user with only read permissions
CREATE USER SomeReadOnlyUser FOR LOGIN SomeReadOnlyUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeReadOnlyUser;
GO

--create user with both read and writer permissions
CREATE USER SomeAllowedWriteUser FOR LOGIN SomeAllowedWriteUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeAllowedWriteUser;
ALTER ROLE TableWriterRole
    ADD MEMBER SomeAllowedWriteUser;
GO

Be aware that it is the owner rather than the schema name that is relevant for ownership chaining.  If the table and proc were in different schemas, the ownership chain would still be unbroken as long as the schemas had the same owner (AUTHORIZATION).  The object owner is typically inherited from the schema owner.


How to display MS SQL results vertically? Here is what I've got currently when executing queries in sqlcmd:

1> SELECT 1,2,3,4
2> GO

----------- ----------- ----------- -----------
          1           2           3           4

(1 rows affected)

Although in case of longer results this output format is not human-readable, as it gets wrapped in the terminal.

How do I display the results vertically (an equivalent of \G in mysql)? Or in another way which can be human-readable?

The expected result when you use SELECT F1, F2, F2 is to obtain one column for each field in the SELECT sentence.

SELECT '1' + CHAR(13) + '2' + CHAR(13) + '3' + CHAR(13)

------
1
2
3

(1 row(s) affected)


Messaging:- Need to find last child of its parent with in the same table I am making a messaging module.

I need to find the all parents with their last child message.

Like:

+--------------------+----------------------+---------+
| pkUserDiscussionID | fkDiscussionParentID | message |
+--------------------+----------------------+---------+
|                  1 |                    0 | a       |
|                  2 |                    1 | b       |
|                  3 |                    1 | c       |
|                  4 |                    0 | a       |
+--------------------+----------------------+---------+

I need to find the last child of parent node (fkDiscussionParentID=0) with last child (pkUserDiscussionID=1 last child pkUserDiscussionID=3) with message.

Below I found a link close to my answer but not completely:

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

Because of my hard work I got the answer:-

SELECT
    parent.pkUserDiscussionID,
    child.UserDiscussionMessage AS childmsg,
    parent.UserDiscussionTitle,
    parent.UserDiscussionMessage AS parentmsg
FROM user_discussion parent
LEFT JOIN user_discussion child
    ON child.fkDiscussionParentID = parent.pkUserDiscussionID
    AND child.pkUserDiscussionID =
    (
        SELECT max( pkUserDiscussionID )
        FROM user_discussion AS c
        WHERE c.fkDiscussionParentID = parent.pkUserDiscussionID
    )
WHERE
    parent.fkDiscussionParentID =0
GROUP BY
    parent.pkUserDiscussionID
ORDER BY
    parent.pkUserDiscussionID ASC
LIMIT 0 , 30

Can anyone suggest anything better than this?


Login failed for user 'domain\Administrator'. (Microsoft SQL Server, Error: 18456) On my newly installed SQL Server 2014, I get the following error when trying to connect to the SQL Server:

2015-05-13 12:09:38.40 Logon   Error: 18456, Severity: 14, State: 5.
2015-05-13 12:09:38.40 Logon   Login failed for user 'INTRA\Administrator'.
Reason: could not find a login matching the name provided. [CLIENT: <local machine>]

I reinstalled the sql server three times.

The user for which the login fails is the AD user with which I am logged into the server itself. When logging in via SSMS, I don't have to give any credentials (it is auto-filled), so this can't be a typo error.

How can I resolve this issue?

PS: I also had problems with the SSL cert which are already resolved, so the underlying problem may be bigger.

I reinstalled the sql server three times.

During the installation you are asked what users to be added as instance administrators. You should add yourself. You probably added the built-in Administrators group and now you're running a LUA process and had lost the admin token and cannot access the instance. If yes, then is simply a mater of running the client process elevated ('Run as administrator').

If you have omitted to add the built-in Administrators group as SQL instance admins then you must follow the steps described at Connect to SQL Server When System Administrators Are Locked Out.


How group with min(date) and select an another column in the same table I have a query who return something like it :

Name   Gender Job         date of hire
John   M      mechanic    2012-05-08
John   M      electrician 2010-01-01
Vicky  F      scientific  2012-11-11
Bob    M      NULL        NULL

I need to have the name, gender and jobname of first job of each person. But i don't know how.
My query look like this :

select name,gender,jobname,hiredate
from person p
left join job j on p.personid = j.personid

I'm on Microsoft SQL Server 2000

I need this as result :

Name   Gender Job
John   M      electrician
Vicky  F      scientific
Bob    M      NULL

I infer that your data looks like this:

Person Table

╔══════════╦═══════╦════════╗
║ PersonID ║ Name  ║ Gender ║
╠══════════╬═══════╬════════╣
║        1 ║ John  ║ M      ║
║        2 ║ Vicky ║ F      ║
║        3 ║ Bob   ║ M      ║
╚══════════╩═══════╩════════╝

Job Table

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        1 ║ Mechanic    ║ 2012-05-08 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

The first task is to find the first job (by hire date) for each person.  One neat way to do that is by using a correlated subquery:

SELECT j.*
FROM dbo.Job AS j
WHERE
    j.HireDate =
    (
        SELECT MIN(j2.HireDate)
        FROM dbo.Job AS j2
        WHERE j2.PersonID = j.PersonID
    );

Notice the correlation WHERE j2.PersonID = j.PersonID between the inner and outer queries there.  The output of that query is:

╔══════════╦═════════════╦════════════╗
║ PersonID ║   JobName   ║  HireDate  ║
╠══════════╬═════════════╬════════════╣
║        1 ║ Electrician ║ 2010-01-01 ║
║        2 ║ Scientific  ║ 2012-11-11 ║
╚══════════╩═════════════╩════════════╝

The execution plan (given a clustered PRIMARY KEY on PersonID, HireDate) is:

The interesting thing about that plan is the Job table is only scanned once, despite there being two references to it in the original query.  The plan uses an optimization that I call Segment Top.  Essentially the execution engine takes advantage of the index order to detect the start of a new group (segment) and take just the first row from each group (top).

Now that we have that result, all we need do is join it back to the Person table:

SELECT
    p.PersonName,
    p.Gender,
    j.JobName
FROM dbo.Person AS p
LEFT JOIN
(
    -- Previous query
    SELECT j.*
    FROM dbo.Job AS j
    WHERE
        j.HireDate =
        (
            SELECT MIN(j2.HireDate)
            FROM dbo.Job AS j2
            WHERE j2.PersonID = j.PersonID
        )
) AS j ON
    j.PersonID = p.PersonID
OPTION (MERGE JOIN);

The execution plan is:

The OPTION (MERGE JOIN) is not required; I just added it to show the plan you are likely to get when the tables contain a larger number of rows than in this small example.

Table definitions and sample data:

CREATE TABLE dbo.Person
(
    PersonID    integer NOT NULL,
    PersonName  varchar(30) NOT NULL,
    Gender      char(1) NOT NULL,

    PRIMARY KEY (PersonID)
);

CREATE TABLE dbo.Job
(
    PersonID    integer NOT NULL,
    JobName     varchar(30) NOT NULL,
    HireDate    datetime NOT NULL,

    PRIMARY KEY (PersonID, HireDate)
);

INSERT dbo.Person
    (PersonID, PersonName, Gender)
SELECT 1, 'John', 'M' UNION ALL
SELECT 2, 'Vicky', 'F' UNION ALL
SELECT 3, 'Bob', 'M';

INSERT dbo.Job
    (PersonID, JobName, HireDate)
SELECT 1, 'Mechanic', '20120508' UNION ALL
SELECT 1, 'Electrician', '20100101' UNION ALL
SELECT 2, 'Scientific', '20121111';


SSIS - Setup OLEDB Connection to SQL Server Always On Listener I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).

I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I specify MultiSubnetFailover? I don't see an option for it. And do I have to add Server=TCP in the connection?

When I run the package the connection string looks like this...

Data Source=myTestServer\mssql;User ID=TestUser;Initial Catalog=TestDB;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

In short, the OLEDB v11 doesn't support the MultiSubnetFailover parameter.  Check out this helpful article.  You can instead set the ConnectionTimeout to 30 seconds, which seems to let it failover to the other node, but then every OLEDB step in your package could take 30 seconds to fire up.