SSIS : Null value for date keeps failing I have a sequence container and in that sequence container I have select that is passing through data into a foreach loop container. The problem arises with one of the columns that's a date datatype. When there is a date in that column it works fine, but when it's a null it fails. Is there anyway around this issue with null values?

This is the column

This is the error message below :

Below is the sql statement where I change the date from null to a date
SELECT
    cast(ISNULL([Submitted_Date],'01/01/1900') as datetime) as [Submitted_Date]
    FROM [dbo].[Application_Tbl]
WHERE
Application_Form_Id IN (12345)
processed = 0

Unfortunately, SSIS variables can't hold NULL values. NULL is allowed in data flow's columns but not on variables, this is a limitation of the underlying COM objects used by SSIS.

You will have to work-around this by supplying a default or sentinel value on your query, and later on check on this value to correctly assign a NULL. You can change your query to supply a non-null value of the correct type, for example:

SELECT
    T.Column1,
    T.Column2,
    DateColumn = ISNULL(T.DateColumn, '1900-01-01')
FROM
    YourTable AS T

And then whenever you are referencing this DateColumn inside your ForEach, make an expression to check against this value and replace with NULL.

YEAR( @[User::DateColumn]  ) == 1900 ?  NULL(DT_DATE) : @[User::DateColumn]

If you are issuing an UPDATE via OLE Command statement with the ForEach's variables then your current update should be something like:

UPDATE T SET
    col1 = ?,
    col2 = ?
FROM
    yourTableToUpdate T
WHERE
    primaryKeyID = ?

Or maybe an SP call:

EXEC dbo.UpdateSomeTable
    @ID = ?,
    @Col1 = ?,
    @Col2 = ?

You can use plain SQL to replace the value back to the NULL. I'd also recommend assigning the values to variables first then using the variables in any place you want inside the SQL. This way it's easier to see the proper order of the parameter assignation and you can also repeat them without trouble:

DECLARE @col1 DATETIME = ?
DECLARE @col2 VARCHAR(100) = ?
DECLARE @primaryKeyID INT = ?

SET @col1 = CASE WHEN YEAR(@col1) <> 1900 THEN @col1 END

UPDATE T SET
    col1 = @col1,
    col2 = @col2
FROM
    yourTableToUpdate T
WHERE
    primaryKeyID = @primaryKeyID

Or:

DECLARE @col1 DATETIME = ?
DECLARE @col2 VARCHAR(100) = ?
DECLARE @primaryKeyID INT = ?

SET @col1 = CASE WHEN YEAR(@col1) <> 1900 THEN @col1 END

EXEC dbo.UpdateSomeTable
    @ID = @primaryKeyID,
    @Col1 = @col1,
    @Col2 = @col2


PostgreSQL 10.6: Alter parent table will block ALL children? I want to add a column to near 74.000 child tables. These tables continually have insertions and reads from all the services in the company, in almost realtime. If I add a column to the parent table, it will block the 74.000 tables, or will be one-to-one?

Adding a column to the inheritance parent will only be completed once all children are modified. So yes, you will have to wait until all 74000 tables have been modified, and there will be an ACCESS EXCLUSIVE lock on all tables at the same time.

I hope you have set max_locks_per_transaction high enough that the locking table can hold all these locks. Having 74000 tables in one database is a problem, and having them all in an inheritance hierarchy is an even bigger problem. But that problem is with the database design.


Virtual Column - Using it in view (Oracle) I have made a virtual column in Oracle, using the following code.

CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as

lname varchar2 (256);
snumber varchar2 (256);
 email varchar2 (256);
    BEGIN
    select substr( p.name, instr( p.name, ' ', -1 ) + 1 ) into lname
    from person p where p.id = id_;

    SELECT regexp_replace(p.service_no, '[^0-9]*', '') into snumber
    FROM person p where p.ID = id_;

  email:= snumber||lname||'@met.af';

return email;
end email_address;

The virtual column works fine and it does populate what i want to acheive in the virtual column. But the problem arises when i make a view using virtual column's table; population of view takes hell lot of time (max 5 mins). Here, i would like to mention that the view works perfectly fine if i don't use the function (i.e empty column of email). The code for view is as under

select  distinct
    person.SERVICE_NO as Service_No,
    person.CNIC_NO as CNIC, person.NAME as NAME ,
    card.CPLC_SERIAL_NO as Card_Number,
    child_dc.NAME as Child_DC,
    root_dc.NAME as Root_DC, person.OU as OU,
    person.EMAIL as Email
from

 person_card inner join person
 on person_card.PERSON_ID = person.ID
 inner join card
 on person_card.CARD_ID = card.ID
    left outer join child_dc
 on person.CHILD_DC_ID = child_dc.ID
    left outer join root_dc
 on child_dc.ID = root_dc.ID;

What i presume is that when i make a virtual column, oracle forcibly asks me to keep the datatype length to 4000, therby, making it too large or heavy to populate. What should i do to make the view populate. I need to have a virtual column as email is not being entered by the application. Help required.

I don't think it's the size of the column (btw: don't "presume", test).

My bet is that the function gets called over and over again for each row from the result. So for each row in the result you make two(!) selects on the person table.

Edit: I was wrong with that above statement. The function for a virtual column is only called when the column it is based on is updated.

You can slightly improve that by only doing a single SELECT in your function:

select substr( p.name, instr( p.name, ' ', -1 ) + 1 ),
       regexp_replace(p.service_no, '[^0-9]*', '')
   into lname, snumber
from person p where p.id = id_;

But I would recommend to simply put that logic into the view and construct the email string inside the view:

select  distinct
        person.SERVICE_NO as Service_No,
        person.CNIC_NO as CNIC, person.NAME as NAME ,
        card.CPLC_SERIAL_NO as Card_Number,
        child_dc.NAME as Child_DC,
        root_dc.NAME as Root_DC, person.OU as OU,
        regexp_replace(p.service_no, '[^0-9]*', '')||substr( p.name, instr( p.name, ' ', -1 ) + 1 )||'@met.af' as email
from person_card
inner join person
 on person_card.PERSON_ID = person.ID
inner join card
 on person_card.CARD_ID = card.ID
    left outer join child_dc
 on person.CHILD_DC_ID = child_dc.ID
    left outer join root_dc
 on child_dc.ID = root_dc.ID;

That way no additional select is necessary and it should run just fine.


Change user password on slave Our users can only access the slave database.

How can I let them change their passwords on their own or when their passwords expire?

We maintain this very same setup. In order to handle read only on the replicas, we have a cron job with SUPER permissions that synchronizes their password with the one on the database.

Alternatively, you could handle passwords on an external authentication mechanism, such as LDAP, so passwords are not handled on MySQL. We don't do it like this because there are MySQL-specific changes (different grants) that we still need to handle on the database. To facilitate such changes, we use roles so that global permissions can be handled easily.

Don't let the users execute commands to the database as SUPER, even if wrapped appropriately, or that could be exploited. Have an API at least in between which simplifies the executable actions. If you don't want to handle external code, you could even have stored procedures with definer permissions, although those are prone to mistakes/SQL injection.


PostgreSQL remove numbers before first comma I am new to SQL however, I am looking to update a table where column called "scrap_value" and rows contains a set of numbers in an array e.g.
{100000,125000,150000,175000,200000}

I want to remove the first number and comma of all rows in "scrap_value", so the end result looks like this
{125000,150000,175000,200000}

How would I got about doing this?

That looks like an array. If that is the case, you can remove the first element of the array using an UPDATE statement:

update the_table
  scrap_value = scrap_value[2:];

[2:] selects all elements of the array starting with the second. The result of that is then used to override the existing array.


using $Partition function to improve query performance I have tables which are partitioned based on a INT column.

I see some queries that are using $Partition function to compare partition number instead of comparing the actual field data.

For example instead of saying:

select *
from T1
    inner join T2 on T2.SnapshotKey = T1.SnapshotKey

they have been written like below:

select *
from T1
    inner join T2 on $Partition.PF_Name(T2.SnapshotKey) = $Partition.PF_Name(T1.SnapshotKey)

where PF_Name is the name of partition function.

I see comments on those queries that this has been done to improve performance, and when I run both queries I see different in execution time and different execution plan. I'm not sure how these two queries are different.

This is a real query:

-- this takes about 9 seconds
select sum(PrinBal)
from fpc
    inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703

-- this takes about 5 seconds
select sum(PrinBal)
from fpc
    inner join gl on $Partition.SnapshotKeyPF(gl.SnapshotKey) = $Partition.SnapshotKeyPF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703

And below is the execution plan of the real query:

Sorry I can't upload even a sanitized execution plan as uploads are monitored by our network and it might be a policy violation.

Question is: Why execution plans are different and why the second query is faster.

I appreciate if someone can share any idea on this. Just interested to know why they are different. Faster is better though.

This is happening on SQL Server 2014. If I run the same on SQL Server 2012, the result will be different and the first query will perform faster!

Why execution plans are different

First query

select sum(PrinBal)
from fpc
    inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703

The optimizer knows:

gl.SnapshotKey = fpc.SnapshotKey; and
fpc.SnapshotKey = 201703

so it can infer:

gl.SnapshotKey = 201703

Just as if you had written:

select sum(PrinBal)
from fpc
    inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703
and gl.SnapshotKey = 201703

The literal value 201703 can also be used by the optimizer to determine the partition id. With both SnapshotKey predicates (one given, one inferred) this means the optimizer knows the partition id for both tables.

Going further, with a literal value (201703) for SnapshotKey now available on both tables, the join predicate:

gl.SnapshotKey = fpc.SnapshotKey

simplifies to:

201703 = 201703; or simply
true

Meaning there is no join predicate at all. The result is a logical cross join. Expressing the final execution plan using the closest available T-SQL syntax, it is as if you wrote:

SELECT
    CASE
        WHEN SUM(Q1.c) = 0 THEN NULL
        ELSE SUM(Q1.s)
    END
FROM
(
    SELECT c = COUNT_BIG(*), s = SUM(GL.PrinBal)
    FROM dbo.gl AS GL
    WHERE GL.SnapshotKey = 201703
    AND $PARTITION.PF(GL.SnapshotKey) = $PARTITION.PF(201703)
) AS Q1
CROSS JOIN
(
    SELECT Dummy = 1
    FROM dbo.fpc AS FPC
    WHERE FPC.SnapshotKey = 201703
    AND $PARTITION.PF(FPC.SnapshotKey) = $PARTITION.PF(201703)
) AS Q2;

Second query

select sum(PrinBal)
from fpc
    inner join gl on $Partition.PF(gl.SnapshotKey) = $Partition.PF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703

The optimizer can no longer infer anything about gl.SnapshotKey, so the simplifications and transformations made for the first query are no longer possible.

Indeed, unless it is true that each partition holds only a single SnapshotKey, the rewrite is not guaranteed to produce the same results.

Again, expressing the execution plan produced using the closest available T-SQL syntax:

SELECT
    CASE
        WHEN SUM(Q2.c) = 0 THEN NULL
        ELSE SUM(Q2.s)
    END
FROM
(
    SELECT
        Q1.PtnID,
        c = COUNT_BIG(*),
        s = SUM(Q1.PrinBal)
    FROM
    (
        SELECT GL.PrinBal, PtnID = $PARTITION.PF(GL.SnapshotKey)
        FROM dbo.gl AS GL
    ) AS Q1
    GROUP BY
        Q1.PtnID
) AS Q2
CROSS APPLY
(
    SELECT Dummy = 1
    FROM dbo.fpc AS FPC
    WHERE
        $PARTITION.PF(FPC.SnapshotKey) = Q2.PtnID
        AND FPC.SnapshotKey = 201703
) AS Q3;

This time there is no logical cross join. Instead, there is a correlated join (an apply) on the partition id.

  why the second query is faster.

This is hard to assess from the information given. Using mock data and tables based on the queries and plan image provided, I found the first query outperformed the second in every case.

The same query expressed using different syntax can often produce a different execution plan, simply because the optimizer started from a different point, and explored options in a different order before it found a suitable execution plan. Plan search is not exhaustive, and not every possible logical transformation is available, so the end result is likely to be different. As noted above, the two queries do not necessarily express the same requirement anyway (at least given the information available to the optimizer).

On a separate note, be aware that the initial columnstore implementation in SQL Server 2012 (and to a somewhat lesser extent, 2014) has many limitations, not least on the optimization side of things. You will likely get better, and more consistent, results by upgrading to a more recent release (ideally the very latest). This is particularly true if you're going to be using partitioning.

I certainly would not recommend you get into the habit of rewriting joins using $PARTITION, except as a very last resort, and with a very deep understanding of what you are doing.

That's about all I can say without being able to see the schema or plan detail.


Updating column from another table - PostgreSQL 9.2 There are two tables:

ja_jobs
junk.test_test1

I need to update the time_job column from junk.test_test1 to ja_jobs.

Here is what I'm doing now:

UPDATE ja_jobs t2
SET time_job = t1.time_job
FROM junk.test_test1 t1
WHERE t2.id = t1.id
AND t2.time_job IS DISTINCT FROM t1.time_job;

But I'm getting empty/null updates..

Why is that happening and how can I solve the problem?

UPDATE 1:

When I run the query above, I get:

UPDATE 2202

So I check if the data has been changed:

select * FROM public.ja_jobs WHERE id = 14574527

And the time_job field is null - So no changes have been made

14574527 = Is an ID from a random ROW that has the time_job field null

UPDATE 2:

select count(*) FROM junk.test_test1 a
JOIN ja_jobs as b ON a.id = b.id
WHERE b.clientid = 23856 AND deleted = false

I get: 770 - So it seems that there are data to be updated.. But no update is happening...

QUERY:

UPDATE public.ja_jobs t2 SET time_job = t1.time_job FROM junk.test_test1 t1 WHERE t2.id = 14571727 AND t2.time_job IS DISTINCT FROM t1.time_job;

select id,clientid,time_job FROM public.ja_jobs WHERE id = 14571727

select id,clientid,time_job from junk.test_test1 WHERE id = 14571727

AS you can see, the test_test1.time_job is not null. That data should be updated into the public.ja_jobs.time_job column - But it's not

UPDATE 3:

Triggers:
    "b_iu_timestamps_from_visits" BEFORE INSERT OR UPDATE ON "ja_jobs" FOR EACH ROW EXECUTE PROCEDURE "tf_ja_jobs_timestamps_from_visits"()
    "ct_a_iu_jobs_same_account_only" AFTER INSERT OR UPDATE ON "ja_jobs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "dm"."tf_jobs_same_account_only"()
    "ct_a_u_jobs_block_immutable_columns_changes" AFTER UPDATE ON "ja_jobs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "dm"."tf_jobs_block_immutable_columns_changes"()
    "update_modified_date" BEFORE UPDATE ON "ja_jobs" FOR EACH ROW EXECUTE PROCEDURE "update_lastmodified_column"()

The statement looked perfectly fine in the first place. After excluding other options (like tables referenced without schema), it seems like the problem is caused by the 4 UPDATE triggers on the table (or one of them).

You should inspect the trigger code and understand what they are doing and which one of them is causing this behaviour.

After that, it's really a business decision. If the triggers are not supposed to do this, you could decide to modify/correct the trigger code.

If they are doing what they are supposed to do, you might decide to not run the updates or - if these changes have to done absolutely - run the updates by disabling temporarily the triggers, bypassing the business rules.

You could disable one by one with:

ALTER TABLE public.ja_job_users
    DISABLE TRIGGER trigger_name ;

or all of them with:

ALTER TABLE public.ja_job_users
    DISABLE TRIGGER USER ;

which disables all user defined triggers (but not FK triggers), then run the wanted statements and then re-enable them (with the same syntax: ... ENABLE TRIGGER ...;.

But I would strongly suggest to first inspect what these triggers do. It might be possible that only one is the root cause and the others are crucial and have to be kept enabled.

If the data are critical (can you afford to lose them?), I would also strongly suggest you take a backup now, before making any changes and do the actions manually, and inside a transaction. So, you can BEGIN; a transaction, run the updates, checking the data that have changed, and only when you are absolutely sure then COMMIT; the transaction.

Then take a backup immediately after the changes as well. This will allow later - and in your own time - to check thoroughly any side effect that this operation may have done by comparing the two backups.


How to stop the SQL Server Agent Job Scheduler with T-SQL? I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)

There are several jobs scheduled to run with increments of minutes to days.  I want to "turn off" the scheduler so nothing will run on schedule until my manual job is done.  I realize that missed jobs will not run again until their next scheduled time, after the scheduler is restarted.

First Step: stop scheduler
Several Heavy lifting steps: Do the hard work
Last Step: start scheduler

I found An Oracle command but I am 100% Microsoft SQL Server

I know that if I Turn off the SQL agent, jobs will not run but that is not what I am looking for.

The jobs that exist for today, are not expected to be the same jobs next time.  Disabling jobs with EXEC dbo.sp_update_job is not a viable solution.

Additional information: There may also be jobs in place that are intentionally disabled for some reason, that do want to be re-enabled. Stopping the scheduler seems like the best choice.

I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along with the database.schema.table_name_you_want_to_create and it will create the table for the first time for you. After that you can change it into an insert.)

SELECT * FROM
MSDB.dbo.sysschedules ss
INNER JOIN msdb.dbo.sysjobschedules jss
   ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1

That code returns the schedules for all schedules and it also performs an inner join to limit the data returned to only schedules that are paired to jobs currently that are enabled.

You can then create a loop or cursor or something similar to loop through the staging table and execute the sp_update_schedule procedure. This will disable all schedules that are enabled and paired to a job. Once your maintenance is complete, you can run the loop once more, but this time enabling the schedules you had disabled earlier.

If you would like an example of a loop, you can see an example I have created in the past on stack overflow.

I have gone back and used the suggestions to create a complete code solution. Please see below, there are two scripts in total. The first will disable your jobs and the second will re-enable them. TEST THIS IN A TEST ENVIRONMENT BEFORE PUTTING IT IN PRODUCTION. Source for completeness.

Script one:

USE MSDB;

/*************************************************************
 Checking for history table. Creating it if it doesn't exist.
*************************************************************/

IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[JobsEnabledTracker](
[Id] [INT] IDENTITY(1, 1) NOT NULL,
[job_id]      [UNIQUEIDENTIFIER] NULL,
[schedule_id] [BIGINT] NULL,
[enabled]     [BIT] NULL);
END;
IF EXISTS
(
    SELECT
           1
    FROM [dbo].[JobsEnabledTracker]
    WHERE [enabled] = 1
)
   OR
(
    SELECT
           COUNT(*)
    FROM [dbo].[JobsEnabledTracker]
) = 0
    BEGIN
        PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.';

/***********************
 Clear out history table
***********************/

        PRINT 'Truncating history table: dbo.JobsEnabledTracker';
        TRUNCATE TABLE [dbo].[JobsEnabledTracker];

        PRINT 'Inserting records into history table: dbo.JobsEnabledTracker';

/******************************
 Add in values to history table
******************************/

        INSERT INTO [dbo].[JobsEnabledTracker]
        (
               [job_id],
               [schedule_id],
               [enabled]
        )
        SELECT
               [jss].[job_id],
               [jss].[schedule_id],
               1 AS 'enabled'
        FROM [msdb].[dbo].[sysschedules] AS [ss]
             INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id]
        WHERE [ss].[enabled] = 1;

/**********************************************************************************
 Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/

        DECLARE @JobsEnabled TABLE
        ([Id]          INT
         PRIMARY KEY IDENTITY(1, 1),
         [job_id]      UNIQUEIDENTIFIER,
         [schedule_id] BIGINT,
         [enabled]     BIT
        );

/*****************************************
 Insert schedules that we need to disable.
*****************************************/

        INSERT INTO @JobsEnabled
        (
               [job_id],
               [schedule_id],
               [enabled]
        )
        SELECT
               [job_id],
               [schedule_id],
               [enabled]
        FROM [dbo].[JobsEnabledTracker];

/********************************
 Holds the job id and schedule id
********************************/

        DECLARE @jobid UNIQUEIDENTIFIER;
        DECLARE @scheduleid BIGINT;

/***********************************
 Holds the ID of the row in the loop
***********************************/

        DECLARE @ID INT= 0;

/**********************
 Check if records exist
**********************/

        IF EXISTS
        (
            SELECT
                   [Id]
            FROM @JobsEnabled
        )
            BEGIN
                PRINT 'Loop mode, jobs found enabled.';

/**********
 Begin loop
**********/

                WHILE(1 = 1)
                    BEGIN

/***************************************
 Grab jobid, scheduleid, and id of rows.
***************************************/

                        SELECT
                               @jobid =
                        (
                            SELECT TOP 1
                                   [job_id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );
                        SELECT
                               @scheduleid =
                        (
                            SELECT TOP 1
                                   [schedule_id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );
                        SELECT
                               @ID =
                        (
                            SELECT TOP 1
                                   [Id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );

/************************************
 Re-enable schedule associated to job
************************************/

                        PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255));
                        EXEC [sp_update_schedule]
                             @schedule_id = @scheduleid,
                             @enabled = 0;

/*********************
 Removes row from loop
*********************/

                        DELETE FROM @JobsEnabled
                        WHERE
                              [Id] = @ID;

                        UPDATE [dbo].[JobsEnabledTracker]
                          SET
                              [enabled] = 0
                        WHERE
                              [job_id] = @jobid
                              AND [schedule_id] = @scheduleid;

/****************************
 No more rows, stops deleting
****************************/

                        IF
                        (
                            SELECT
                                   COUNT(*)
                            FROM @JobsEnabled
                        ) <= 0
                            BEGIN
                                BREAK
                            END;

/********
 End Loop
********/
                    END;
                PRINT 'Exiting loop, disabling schedules paired to jobs complete.';

/**********
 End elseif
**********/
            END;
            ELSE
            BEGIN
                PRINT 'All done';
            END;
    END;
    ELSE
    BEGIN
        PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.';
    END;

Script two

USE MSDB;

/*******************************************************************************
 Check for history table. This physical table tells us what jobs we are going to
 enable the scheduler for.
*******************************************************************************/

IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NOT NULL
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[JobsEnabledTracker]
        WHERE [enabled] = 0
    )
    BEGIN
        PRINT 'Jobs disabled in history table: dbo.JobsEnabledTracker found.';

/**********************************************************************************
 Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/

        DECLARE @JobsEnabled TABLE
        (
        [Id] int PRIMARY KEY IDENTITY(1, 1)
        , [job_id] uniqueidentifier
        , [schedule_id] bigint
        , [enabled] bit
        );

/*******************************************************************************
 Insert schedules that we had disabled that we need to go back in and re-enable.
*******************************************************************************/

        INSERT INTO @JobsEnabled( [job_id], [schedule_id], [enabled] )
               SELECT [job_id], [schedule_id], [enabled]
               FROM [dbo].[JobsEnabledTracker];

/********************************
 Holds the job id and schedule id
********************************/

        DECLARE @jobid uniqueidentifier;
        DECLARE @scheduleid bigint;

/***********************************
 Holds the ID of the row in the loop
***********************************/

        DECLARE @ID int= 0;

/**********************
 Check if records exist
**********************/

        IF EXISTS
        (
            SELECT [Id]
            FROM @JobsEnabled
        )
        BEGIN
            PRINT 'Loop mode, jobs found disabled.';

/**********
 Begin loop
**********/

            WHILE 1 = 1
            BEGIN

/***************************************
 Grab jobid, scheduleid, and id of rows.
***************************************/

                SELECT @jobid =
                (
                    SELECT TOP 1 [job_id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );
                SELECT @scheduleid =
                (
                    SELECT TOP 1 [schedule_id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );
                SELECT @ID =
                (
                    SELECT TOP 1 [Id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );

/***************************************
 Re-enable schedule associated to job
***************************************/

                PRINT 'Enabling schedule_id: '+CAST(@scheduleid AS varchar(255))+' paired to job_id: '+CAST(@jobid AS varchar(255));
                EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 1;

/*********************
 Removes row from loop
*********************/

                DELETE FROM @JobsEnabled
                WHERE [Id] = @ID;

/***********************
 Set job back to enabled
***********************/

                UPDATE [dbo].[JobsEnabledTracker]
                  SET [enabled] = 1
                WHERE [job_id] = @jobid AND
                      [schedule_id] = @scheduleid;

/****************************
 No more rows, stops deleting
****************************/

                IF
                (
                    SELECT COUNT(*)
                    FROM @JobsEnabled
                ) <= 0
                BEGIN
                    BREAK;
                END;

/********
 End Loop
********/
            END;
            PRINT 'Exiting loop, enabling schedules paired to jobs complete.';

/**********
 End elseif
**********/
        END;
        ELSE
        BEGIN
            PRINT 'All done';
        END;
    END;
    ELSE
    BEGIN
        PRINT 'dbo.JobsEnabledTracker has no disabled jobs currently.';
    END;
END;
ELSE
BEGIN
    PRINT 'dbo.JobsEnabledTracker is NULL, you may need to run the first script to create and populate this table.';
END;


Reporting Service and Application Role First poster, long time lurker here.
What is the best way to activate application role in a report ?

I've tried different things and so far the only method that works is to embed the call to the application role like so :-

EXEC sp_setapprole 'REPORTZ', 's3cr3t';
select *
from mytable
where ID < 10000

in the Dataset.
It does work... but not to my liking (certainly not in the shape I'd want to roll into production environment).

I'd prefer if I could somehow 'hijack' or 'inject' the application role activation line at runtime either through custom assemblies or probably some sort of 'server hook' in the Reporting Service (which in both cases, I have no idea how)

Much appreciated for your time + kind attention.

YS.

I see a couple of ways this could be done without resorting to anything overly fancy.

The first would be to use Windows integrated authentication and assign permissions to the groups which represent users of the application.
You could create roles which could be granted, and then use set role to assume the role in your code in the db.  This way you aren't passing in a password and you are authenticating the application first.

The advantage of the first is that it is easy to manage, in an AD domain, who has access to the application.  The management would be relatively simple.  The major disadvantages would be that it would be limited to cases where integrated auth makes sense, and you'd have to support integrated auth all the way through.  Additionally every hop ends up requiring 3-way authentication (between server, client, and KDC).

The advantage of the second is that it is probably easiest to shim in, and it exposes no security info over the network, and it works in cases where integrated auth is not going to work well.

I would try both of those approaches before trying to extent the reporting service.  As the comment suggested, http://msdn.microsoft.com/en-us/library/aa237582%28v=SQL.80%29.aspx may be helpful and may work, but it is conceptually more complex than trying to manage roles directly.


SQL Server: unable to copy database I'm trying to create a dev database so I don't create triggers and such on the production database. I use the right click -> Taks -> Copy, followed the wizard and the process starts.

After maybe a minute, it stops and says Error.

The Event Viewer says this:

Package "ShellPackage" failed.

Event Name: OnError
 Message: ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [dbo].[VIEWNAME]
AS
SEL..." failed with the following error: "Invalid column name 'C_27'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}
StackTrace:   Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: DOMAIN\administrator
 Source Name: SERVER_SERVER_Transfer Objects Task
 Source ID: {47D1A40A-A6CF-4A5C-897E-80CB9C1D6151}
 Execution ID: {3E979DF7-C5DC-4583-8613-CB9AC9153D56}

What am I doing wrong?

Thank you!

Best and most reliable way is to use backup / restore

If your database is big and you are on sql server 2008 R2 and up, standard edition also has compression feature to allow you to compress your backups.

Dont forget to sync up users after the restore :-)


System Database Migration and Security - Log Shipping or DB Mirroring I've setup log shipping between two servers (they used to be mirrored).

What is the simplest or most efficient way to mirror the system databases between the primary and secondary?

Is it just a matter of backing these databases on the Primary and then restoring them on the Secondary?

Further, does anyone know of a TSQL script or a (free) program that'll script all logins from the primary server?

System databases cannot be mirrored or logshipping.

The best way is to script and sync up  desired objects e.g. logins, jobs, etc.

The best tool that I have used so far for migrating databases, jobs, agent schedules, operators, logins, etc is powershell based dbatools.io


How to run a cold backup with Linux/tar without shutting down MySQL slave? I run the following before tar-ing up the data directory:

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;

However, tar will sometimes complain that the ibdata* and ib_logfiles* files are updated during the process. What am I missing?

The slave machine is in a cold standby machine so there are no client processes running while tar is running.

CentOS release 5.6 64bits, MySQL 5.1.49-log source distribution.

FLUSH TABLES WITH READ LOCK will not halt writes to InnoDB.

FLUSH TABLES WITH READ LOCK does NOT work with InnoDB (MySQL Forums)
How FLUSH TABLES WITH READ LOCK works with Innodb Tables (mysqlperformanceblog.com)
How fast is FLUSH TABLES WITH READ LOCK? (mysqlperformanceblog.com)

It may block access to writing to tables, but InnoDB will allow writes to ibdata1 to provide MVCC info for redo and undo logs.
Check out Map of InnoDB's Infrastructure

Please note the physical independence of a table from the ibdata1, and how log files are related.
Since the box is a Slave, you have two options :
OPTION #1 (Warm Backup)

Run STOP SLAVE;
Run FLUSH TABLES;
Run your Linux/tar
Run START SLAVE;

OPTION #2 (Cold Backup of Fully Committed Data)

Run SET GLOBAL innodb_fast_shutdown = 0;
Run service mysql stop
Run your Linux/tar
Run service mysql start

OPTION #3 (Cold Backup)

Run service mysql stop
Run your Linux/tar
Run service mysql start

EPILOGUE
Options #2 and #3 would be the better choices by far. What is the difference between them ?

OPTION #2 flushes all transactional changes out of ibdata1 and the transaction logs (ib_logfile0, ib_logfile1). This makes for a longer shutdown. However, you will have a faster mysql startup because InnoDB Crash Recovery would not need to be performed.
OPTION #3 lets you shutdown faster, leaving InnoDB in suspended animation (all transactional changes in ibdata1 and the transaction logs (ib_logfile0, ib_logfile1)). The transactional changes are applied on mysql startup during the InnoDB Crash Recovery phase.


understanding (and modelling) grocery shopping lists I am having a hard time understanding how multiple product orders are mapped out.

What is the best way of modelling a shopping list for multiple customers?

I am developing an C# application that mocks the system we use at work for processing customer grocery orders. After a fair amount of schema research, I am looking at something very simple, like this:

Products {
ProductID,
Name,
Price
}

Customers {
CustomerID,
FirstName,
SecondName
}

Customer_Orders {
OrderID
CustomerID
ProductID
Quantity
}

This does make sense to me, but what seems weird is the Customer_Orders table. As I understand, if a customer places an order with 10 different items, I'll have 10 rows, with the same OrderID and CustomerID yet different ProductIDs, representing the shopping list.

This seems counter-intuitive to me, I imagine ProductID should be made up of a collection of values (how I'd program it) and not separate rows.

Is my thinking way off here, or am I supposed to model it this way?

Your Order should be Primary Key which you can link to another table where order details will be given.

Products {
ProductID,
Name,
Price
}

Customers {
CustomerID,
FirstName,
SecondName
}

Customer_Orders {
OrderID (Primary Key)
CustomerID
...
}

Order_details {
OrderID
ProductID
Quantity
}


How to connect to oracle 12c from sqlplus without password in command line This may seem as weird question:

If i have user called demo with password Pass1234

When connecting to oracle 11g I can run sqlplus like this with password in command line:
on DB host:

C:\> sqlplus demo/Pass1234

Or remote machine

C:\> sqlplus demo/Pass1234@<ip>:1521/orcl

I can do so without give the password in command line and expose it like this:

C:\> sqlplus demo

Then I get:

SQL*Plus: Release 11.1.0.7.0 bla bla bla
Copyright (c) bla bla bla
Enter password:

Then I can enter the password manually.

When connecting to Oracle 12c I can connect like this from any machine (with password):

C:\> sqlplus demo/Pass1234@<ip>:1521/pdborcl

My question is: How to connect to Oracle 12c without tnsnames.ora edit and without write the password in the command line?

I also havn't figured out yet if it is possible to connect to Oracle 12c from sqlplus on the DB host machine without give the IP or localhost, and without editing the tnsnames.ora, like I can do in Oracle 11g?

And more thing, can I do so from a remote machine (even to Oracle 11g) without using a full connect?

(means to give user and db connection in command line and password manually, not like use /NOLOG in command and then: SQL> connect demo/Pass1234@<ip>:1521/pdborcl)

Thanks!

This answer based on a1ex07 comment:

given the folowing detailes:

user name: demo
password: Pass1234
ip: 1.2.3.4
listener port: 1521
oracle SID: orcl
pdb service name: pdborcl

connect to core db from db host as sysdba:

without need of password: sqlplus / as sysdba
password manually: sqlplus sys as sysdba
password in command:  sqlplus sys/Pass1234 as sysdba

connect to core db from db host or remote as sysdba:

password manually:  sqlplus sys@\"1.2.3.4:1521/orcl\" as sysdba
password in command: sqlplus sys/Pass1234@1.2.3.4:1521/orcl as sysdba

connect to pdb from db host or remote as sysdba:

password manually:  sqlplus sys@\"1.2.3.4:1521/pdborcl\" as sysdba
password in command: sqlplus sys/Pass1234@1.2.3.4:1521/pdborcl as sysdba

connect to pdb from db host or remote as demo (regular user):

password manually:  sqlplus demo@\"1.2.3.4:1521/pdborcl\"
password in command: sqlplus demo/Pass1234@1.2.3.4:1521/pdborcl


MySQL - Creating a user and specifying a password How do I create a user and specify a password in one line:

I thought CREATE USER 'foo'@'2.2.2.2' IDENTIFIED BY 'bar'; would do it but apparently it doesn't.

Any thoughts? I'm on version 5.1.49 of MySQL.


Unable to open .sps.bak database backup file with SQL Server 2012 Express edition I am using SQL Server 2012 Express edition. Client sent me the SQL Server backup file with the extension .sps.bak. I am not able to open it with SSMS. Please somebody help me here.

I get the following error message.

  Restore of database failed.
  System.Data.SqlClient.SqlError: the
  database was backed up on a server running version 10.50.1600. That
  version is incompatible with this server, which is running 10.00.1600.
  Either restore the database on a server that supports the backup or
  use a backup that is compatible with this server.

I cannot open the database that was developed with a professional edition on an Express edition?

That's probably a Sharepoint backup. Restore it in Sharepoint, or ask for a SQL server backup

Your error message shows that you are connected to a SQL 2008 server, not SQL 2012, and that the backup was performed on a SQL 2008 R2 server. You need an R2 server, or SQL 2012, to restore it.


Select random database records between subset of records having the same count(*) in a query I have this simple query:

  SELECT `value`, count(*) as `noHits`
    FROM `topic`
    WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."')
    GROUP BY `value`
    ORDER BY count(*) DESC LIMIT 150

It generates a list of records such as:

keyword1, 100
keyword2, 90
keyword3, 40
keyword4, 40
keyword5, 40
keyword6, 40
...
keyword1500, 40

My challenge is to select a list of top 150 records ordered by count(*) DESC however when there are more than 1 value having the same count(*) as in the list above keyword3 to keyword1500 select them randomly from the database.

It seems as if the selection is not random from the tests I made.

The result is used to generate a wordcloud and I do not want the same keywords to always be selected (out of thousands) when they have the same count(*)

To solve this I did the following (see the fiddle)

Created a table:

CREATE TABLE test  -- 22 records
(
  the_word TEXT,
  no_of_times INTEGER
);

Populated it with the data at the end of this answer.

Then ran the following query:

SELECT the_word, no_of_times FROM test
ORDER BY no_of_times DESC, RAND() LIMIT 15;

I tested this several times and obtained results like the following. The first 6 records were always the same - keywords1-6. The remaining 9 records were always randomly from keyword7-22.

Result (sample - first 10 shown):

 the_word   no_of_times
keyword1            100
keyword2             90
keyword3             80
keyword4             70
keyword5             60
keyword6             50
keyword17            40
keyword19            40
keyword22            40
keyword18            40

This is considerably more elegant than my first answer - guess I overcomplicated things!:-)

=========================== Data (22 records) ================

INSERT INTO test VALUES ('keyword1',  100);
INSERT INTO test VALUES ('keyword2',  90);
INSERT INTO test VALUES ('keyword3',  80);
INSERT INTO test VALUES ('keyword4',  70);
INSERT INTO test VALUES ('keyword5',  60);
INSERT INTO test VALUES ('keyword6',  50);
INSERT INTO test VALUES ('keyword7',  40);
INSERT INTO test VALUES ('keyword8',  40);
INSERT INTO test VALUES ('keyword9',  40);
INSERT INTO test VALUES ('keyword10', 40);
INSERT INTO test VALUES ('keyword11', 40);
INSERT INTO test VALUES ('keyword12', 40);
INSERT INTO test VALUES ('keyword13', 40);
INSERT INTO test VALUES ('keyword14', 40);
INSERT INTO test VALUES ('keyword15', 40);
INSERT INTO test VALUES ('keyword16', 40);
INSERT INTO test VALUES ('keyword17', 40);
INSERT INTO test VALUES ('keyword18', 40);
INSERT INTO test VALUES ('keyword19', 40);
INSERT INTO test VALUES ('keyword20', 40);
INSERT INTO test VALUES ('keyword21', 40);
INSERT INTO test VALUES ('keyword22', 40);


How do I add keyboard shortcuts in SSMS 2012 to run a specific query? How do I add keyboard shortcuts in SSMS 2012 to select all the data from table?

In SSMS 2008 we could assign a shortcut in the following way:

  TOOLS-->OPTIONS-->ENVIRONMENT-->KEYBOARD--->CTRL+3-->Select * from

This doesn't appear to be possible in SSMS 2012.

The option is still there (although this is from SQL Server 2014):

You don't normally have to restart SSMS, just open a new query window.


MySQL partitioning table by date and hour I have a table with a timestamp field. This table will have millions of rows every day so the idea is to create partitions by date and hour. Every day I will delete partitions older than 10 days and create the partitions for next day.
   Can you someone tell me how to define partitions for this case? I could not figure it out.
   Thanks in advance.

Use 12 partitions -- 10 days, plus one at each end.  REORGANIZE PARTITION to split the empty, future, partition at the end before the new day; drop the oldest.  All the details, including code, are spelled out in http://mysql.rjweb.org/doc.php/partitionmaint .

Hourly partitions would lead to over 240 partitions; that large a number slows things down.


Unique index on 2 columns in mysql I have one table in mysql named 'UserFriends' where I am updating my websites user's friends details.

here is the schema of the table (UserFriends)

id  int,
Userid int,
friendid int,
createdate timespan

now, I want to create unique index on userid & friendid. that i have created unique index well. so, right now i am not able to insert same userid and friendid as duplicate. but if i am inserting same value in opposite field it accept without generating error.

example :

insert into userfriends ( userid, friendid )
select 1, 2  --- insert perfect
insert into userfriends ( userid, friendid )
select 1, 2  --- show error because unique index comes in a picture

now i am inserting

insert into userfriends ( userid, friendid )
select 2, 1  --- records insert here (i don't want this)

How do i prevent this?

One way to avoid your issue is to make it a business rule that the userid and friendid values be inserted in a specific order. You could add a row constraint (either by adding INSERT/UPDATE triggers, or by applying your changes via a Stored Procedure) to ensure that userid<friendid so it would be okay to insert (userid, friendid) VALUES (1, 2) but any attempt to add (userid, friendid) VALUES (2, 1) would fail.

Example using TRIGGER:

DELIMITER $$
DROP TRIGGER IF EXISTS `userfriends_INSERT` $$
CREATE TRIGGER `userfriends_INSERT`
BEFORE INSERT ON `userfriends`
FOR EACH ROW
BEGIN
    IF new.`userid` >= new.`friendid` THEN
        SIGNAL SQLSTATE '45000';  # unhandled user-defined exception
    END IF;
END $$
DELIMITER ;

Test:

mysql> SHOW VARIABLES LIKE "version";
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| version       | 5.5.29-0ubuntu0.12.04.1 |
+---------------+-------------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE userfriends;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| userid   | int(11) | NO   | PRI | NULL    |       |
| friendid | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = "userfriends";
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

mysql> SELECT trigger_name FROM information_schema.triggers;
+--------------------+
| trigger_name       |
+--------------------+
| userfriends_INSERT |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM userfriends;
Empty set (0.00 sec)

mysql> INSERT INTO userfriends (userid, friendid) VALUES (2, 1);
ERROR 1644 (45000): Unhandled user-defined exception condition
mysql> INSERT INTO userfriends (userid, friendid) VALUES (1, 2);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO userfriends (userid, friendid) VALUES (2, 1);
ERROR 1644 (45000): Unhandled user-defined exception condition
mysql> INSERT INTO userfriends (userid, friendid) VALUES (1, 2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'


Extracting two particular values from a multi valued column I have a table SUBSCRIBER and in it the data is stored in a way that bars is a multi-valued column . Different values of bars are stored in different rows with the ID as following :

ID BARS
1 , BAR1
1 , BAR2
1 , BAR3
2 , BAR4
2 , BAR3
3 , BAR3
4 , BAR1
4 , BAR4

OUTPUT for the data set should be the following:

1,BAR1,
2,,BAR4
3,,,
4,BAR1,BAR4

So the query should be extracting 2 particular BAR values from the multi valued column(BARS) and gives the result for a report based on the conditions as following:

ID,BAR1,BAR4 : WHEN BOTH BAR1 AND BAR4 EXIST
ID,BAR1,, : WHEN BAR1 EXISTS AND BAR4 IS NOT PRESENT
ID,,BAR4 : WHEN BAR4 EXISTS AND BAR1 IS NOT PRESENT
ID,,, : WHEN BOTH BAR1 AND BAR4 ARE NOT PRESENT

If you don't need the extra commas, then:

SELECT CONCAT(id, ',',
          GROUP_CONCAT(bars ORDER BY bars))
    FROM subscriber
    WHERE bars IN ('bar1', 'bar4')
    GROUP BY id;

If you do need the commas, then search for "pivoting".


Race condition in DELETE with nested query? Is there a race condition if two processes both execute this query at (nearly) the same time?

delete
    table
from
    table with (readpast)
    join (
        select top (1)
            notPK
        from
            table with (readpast)
        order by
            table.id
    ) as selected on
        table.notPK = selected.notPK

Assume there are a variety of duplicate notPK's in table, that id is (of course) an identity and primary key, and that there is no shortage of records in this table. Also assume the default isolation level (read committed).

In other words, is there a chance that one of those processes will not get to delete any records from table even when there's no shortage of records there?

In other words, when does SQL Server apply row locks on the records that will get deleted by the query--before or after they've been SELECTed in that subquery?

I mentioned this in a comment, but the design is to process a First-In-First-Out queue that contains duplicates. In the real world notPK uniquely identifies a unit so in the ideal world it would be the primary key, but here in the real world there are duplicate entries in the queue for each unit. So when one unit gets dequeued then all the duplicates need to be dequeued as well, because each unit should be processed only up to once.

So the question I'm wondering is whether this query guarantees that a unit (and its duplicates) will always be deleted (when there are records in the table), or if it is sometimes possible for zero records to be deleted. For example, does it do this?

Read a notPK
Get references to all records with that notPK and that aren't locked, and place locks on them
Delete those records
Release the locks

...or does it do this?

Read a notPK and get references for all records with that notPK and place locks on all of those records, all in an atomic operation
Delete those records
Release the locks

My gut feeling is that it does the former. Therefore two processes could both read the same notPK, but only one of those processes would "win" the race and acquire locks to delete. The other would end up deleting zero records.

Is that correct?

Here's the same query written a little different with the help of @SlavaMurygin:

delete
    table
from
    table with (readpast)
where
    notPK in (
        select top (1)
            notPK
        from
            table with (readpast)
        order by
            table.id
    )

It appears from empirical evidence that a race condition does exist.

I've seen in the logs of an application running a query such as this where two threads each got to delete a record at the same time, and those records had the same notPK. If a race condition did not exist then only one thread would have gotten to delete a record.

Note that when I asked the above question I didn't think that it would be possible for both threads to each dequeue a notPK; I thought it would only be possible for one thread to dequeue any particular notPK.

Since I observed that there is at least this one kind of race condition, then it must also be possible for a process to fail to dequeue anything even when there is no shortage of records in the queue.

I'm not sure how to concisely put something here that readers can take and validate on their own, so I guess everyone will just have to trust me on this.


Storage order vs Result order This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.

@Catcall says this on the subject of storage order (clustered index) and the output order

  A lot of people believe that a clustered index guarantees a sort order on output. But that's not what it does; it guarantees a storage order on disk.
  See, for example, this blog post.

I've read the blog post by Hugo Kornelis and understands that an index doesn't guarantee that the sql server reads the records in a specific order. Yet I have a hard time accepting that I can't assume this for my scenario?

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

My original query was this:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

But I suggest that I could as well use this one (read below for my explanation):

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

As you can see, my table rows are small (16bytes) and I've got only one index, a clustered. In my scenario, the table consists of 100.000.000 records at this moment (and this will most likely increase tenfold).

When the database server queries this table it has two ways of finding my rows, either it seeks the primary key and thereby reading and returning my values in desc. order of Date, or it has to do a full table scan. My conclusion is that a full table scan on all those records will be way too slow and the database server will therefore always seek the table via its primary key and thereby returning the values sorted by Date DESC

Let me try to explain why you should not do that, why you should never assume that an SQL-product will return a result set in a specific order, unless you specify so, whatever indices - clustered or non-clustered, B-trees or R-Trees or k-d-trees or fractal-trees or whatever other exotic indices a DBMS is using.

Your original query tells to the DBMS to search the SensorValues table, find rows that match the 3 conditions, order those rows by Date descending, keep only the first row from those and - finally -  select and return only the SensorValue column.

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC ;

These are very specific orders you have given to the DBMS and the result will most probably be the same every time you run the query (there is a chance it might not, if you have more than one row that match the conditions and have the same max Date but different SensorValue but lets assume for the rest of the conversation that no such rows exist in your table).

Does the DBMS have to do this, to run this query, the exact way I describe it above? No, of course not and you know that. It may not read the table but read from an index. Or it may use two indexes if it thinks it's better (faster). Or three. Or it may use a cached result (not SQL Server but other DBMS cache query results). Or it may use parallel execution one time and not the next time it runs. Or ... (add any other feature that affects execution and execution plans).

What is guaranteed though is that it will return the exact same result, every time you run it - as long as no rows are inserted, deleted or updated.

Now lets see what your suggestion says:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010 ;

This query tells to the DBMS to search the SensorValues table, find rows that match the 3 conditions, order those rows by Date descending,, don't care about the order, keep only one row and - finally -  select and return only the SensorValue column.

So, it basically tells the same as the first one, except that it tells that you want one result only that matches the conditions and you don't care which one.

Now, can we assume that it will give always the same result because of the clustered index?
- If it does use this clustered index every time, yes.

But will it use it?
- No.

Why not?
- Beacuse it can. The query optimizer is free to choose a path of execution every time it runs a statement. Whatever path it sees fit at that time for that statement.

But isn't using the clustered index the best/fastest way to get results?
- No, not always. It might be the first time you run the query. The second time, it may use a cached result (if the DBMS has such a feature, not SQL Server*). The 1000th time the result may have been removed from the cache and another result may exist there. Say, you had executed this query just before:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date ASC ;         --- Notice the `ASC` here

and the cached result (from the above query) is another, different one that still matches your conditions but is not the first in your (wanted) ordering. And you have told the DBMS not to care about the order.

OK, so only cache can affect this?
- No, many other things, too.

other indexes were considered, at that time by the DBMS as better for this query.
a developer changed or completely removed this clustered index you had.
you or some other developer added another index that the optimizer decided it's more efficient to use than the CI.
you updated to a new version and the new optimizer has a minor bug or a change in how it ranks and chooses execution plans.
statistics were updated.
parallel execution was chosen instead.

*: SQL Server does not cache query results but the Enterprise Edition does have an Advanced Scanning feature which is kind of similar in that you may get different results because of concurrent queries. Not sure exactly when this kicks in though. (thnx @Martin Smith for the tip.)

I hope you are convinced that you should never rely that an SQL query will return results in a specific order, unless you specify so. And never use TOP (n) without ORDER BY, unless of course you just want n rows in the result and you don't care which ones are returned.


Left join where there is no match on the left (IS NULL):

SELECT campaign_id as id, tbl1.nickname as name
FROM tbl1
LEFT JOIN tbl2 ON nickname=name
WHERE tbl2.id IS NULL

Alternately there is a MariaDB-10.3 syntax:

select version();

| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |

create table tbl1 (name varchar(20))

✓

insert into tbl1 values ('n1'),('n2'),('n6')

✓

create table tbl2 (name varchar(20))

✓

insert into tbl2 values ('n1'),('n2'),('n12')

✓

select name from tbl1 except select name from tbl2

| name |
| :--- |
| n6   |

db<>fiddle here


Is it possible to write (A OR B) IN(X, Y, ..., W)? Is it possible to write (A OR B) IN(X, Y, ..., W) ?

I need to fill two IN() expr. with same values by hand. Like:

A IN(X, Y, ..., W) OR B IN(X, Y, ..., W)

What is the best option to have only one IN() to fill?

No, there isn't a simple solution, unless the values are already in respective (normal or temporary) tables. If they are,

A IN(X, Y, ..., W) OR B IN(X, Y, ..., W)

is easy to rewrite. It's equivalent to:

EXISTS ( SELECT *
         FROM List1 JOIN List2 ON List1.item = List2.item )

If the A and B are not supplied values/parameters but columns/expressions from the tables involved in the query, we can use a mixed approach, having only the X, Y, ..., W values in a (temp) table:

A IN (SELECT item FROM List2)
OR
B IN (SELECT item FROM List2)

-- alternative 1
EXISTS (SELECT * FROM List2 WHERE List2.item = A)
OR
EXISTS (SELECT * FROM List2 WHERE List2.item = B)

-- alternative 2
EXISTS (SELECT * FROM List2 WHERE List2.item IN (A,B))

If the values are not already in tables, then a way to write this would be using VALUES clause but this has not been implmented in mySQL:

EXISTS ( SELECT *
         FROM (VALUES (A), (B)) AS List1 (item)
           JOIN
              (VALUES (X), (Y), ... (W)) AS List2 (item)
           ON List1.item = List2.item )

In MySQL, you'll have to rewrite the VALUES with rather long subqueries, using UNION

EXISTS ( SELECT *
         FROM (SELECT A AS item UNION ALL
               SELECT B
              ) AS List1
           JOIN
              (SELECT X AS item UNION ALL
               SELECT Y         UNION ALL
               ...
               SELECT W
              ) AS List2
           ON List1.item = List2.item )


MySQL: Is there a way to find out which user called stored procedure? Server: MySQL 5.7.14
I am following the Suggestion 1 suggested here https://dba.stackexchange.com/a/109416/126860

I am trying to find out which user called the stored procedure. But since I am also using a definer. The CURRENT_USER(); function returns the "definer" user name.

I have tried using SHOW PROCESSLIST; to find out who is running the stored procedure. but it doesn't show stored procedures as a separate process.

Is there a way to find the actual user who invoked the stored procedure.

Back on Jan 10, 2012, I answered MySQL error: Access denied for user 'a'@'localhost' (using password: YES) where I show two function calls:

USER() reports how you attempted to authenticate in mysqld
CURRENT_USER() reports how you were allowed to authenticate by mysqld

Since CURRENT_USER() shows the definer, try using USER(). It might display the MySQL user and the host where that user was coming in from. Please note that this may not work if USER() is an internal thread running the Stored Procedure.


Update primary SQL Server in mirrored environment using transaction logs from old environment I'm currently migrating a large (~40gb) database to a new environment. The new environment is a HA/mirrored set up with primary, mirror and witness nodes.

I'm backing up the database in the old environment, restoring to primary & mirror, and turning on mirroring. During this time the old environment is still active, and data is changing. When we are ready to go live with the new environment I plan to take another transaction log from the old environment and restore that to the new primary server. Is that possible? Will this be successfully synchronised to the mirror?

When we are ready to go live with the new environment I plan to take another transaction log from the old environment and restore that to the new primary server. Is that possible? Will this be successfully synchronized to the mirror?

You can set up mirroring as normal. But when you want to failover, then use SYNCHRONOUS - High safety without automatic failover.

using T-SQL :

ALTER DATABASE <<DB NAME>> SET PARTNER FAILOVER

Doing so, only the role reversal happens and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN.

Now once you verify everything is OK, then you can break mirroring with below steps :

Turn mirroring off (break mirroring) on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Migrate Logins as well as any sql agent jobs to secondary. You might have to fix orphaned users.

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’


How to handle duplicates in an UPDATE on a primary key in Postgres? Let's assume a scenario with the entities Person and Company and a table PersonCompanyStocks that models how many stocks a persons owns of a certain company (N:M cardinality). For example:
person | company | num_stocks
-----------------------------
Alice  | foo     | 300
Bob    | foo     | 100
Bob    | bar     | 200

This table uses (person, company) as a primary key to guarantee unique entries, and foreign keys to the respective person/company table (I'm only using string IDs for simplicity).
Now let's assume company bar buys company foo. We want to update the table in a way that it becomes:
person | company | num_stocks
-----------------------------
Alice  | bar     | 300
Bob    | bar     | 300

Looking only at Alice's record suggests to use a naive approach like:
UPDATE
  PersonCompanyStocks
SET
  company = "bar"
WHERE
  company = "foo"

However this update fails with duplicate key value violates unique constraint ... because for Bob there already is a row with the key ("Bob", "bar"). For INSERT's Postgres supports ON CONFLICT DO ..., but it looks like there is no equivalent for UPDATE. And clearly we also have to deal with properly merging the num_stock value of the two rows.
What is the best strategy to approach this problem? I only see a relatively ugly solution:

One query to determine the duplicates.
One UPDATE to merge the duplicates into the final row.
One DELETE to remove the offending duplicates.
The above UPDATE to do the renaming in rows without duplicates.

This feels complex and is probably prone to race conditions. Does Postgres offer any trick to solve this more elegantly?

We need move stocks between companies, right? What means we need add stocks to existing users and change company for new users. Or, what the same, we could delete all stocks of company "foo" and insert .. on conflict new rows for company "bar"
with rows as (
  delete from PersonCompanyStocks
    where company = 'foo'
    returning person, num_stocks
)
insert into PersonCompanyStocks (person, company, num_stocks)
  select person, 'bar', num_stocks from rows
  on conflict(person,company) do update set
    num_stocks = PersonCompanyStocks.num_stocks + excluded.num_stocks;

Transactional, no race conditions here thanks to row locking during delete.


Performance: Select count(*) where... VS select column_name where I'll keep the scenario simple:

There is an app, and there is a database.
In the database, there is a table with a large amount of records
I feed the app these records regularly

Now, one reload can imply some of these changes:
  i. New records in set
  ii. Updated records in set
  iii. Deleted records in set

Finally, for records that remain the same I can make use of a diferent process that works as much as 100 times faster than reading the table through ODBC.

Knowing this, I am trying to reduce the reloading time by using the ODBC as little as possible and midway through my algorithm I encounter a question that I have not been able to solve.

**How much faster is a select count(*) where [condition] opposed to select columnName where [condition]

Why? Because just by reloading the new and updated records, I can calculate whether there have been deletions or not by counting records depending on [condition]. Then, if there have been deletions, I'll have to read from the database those that remain. Nornally, the amount of new and updated records will be a small portion of the whole set.
If a count is considerably faster than a normal select, I might want to use it as a fast check, since deleting records is not a common activity.

It really depends on a database you use while executing the query and gathered statistics.

Oracle DB.  If stats have already been gathered , the first query would return the amount of rows in 0.00001 sec. If have not been, then it will be faster in five more times than while executing the second query as it is retrieving large amount of rows instead of just one.
For MS SQL will be almost the same thing.
For MySQL or MariaDB it depends on the engine. But I assume these databases always try caching the value of amount of rows as the optimizer will do queries faster.

In addition, columns in WHERE clause and in COUNT() do matter as well. They could be indexes or not and etc. There are lot of criteria considering perfomance between the queries but in the most cases the first one is recommended for using to get amount of rows. I could tell you more precisely if I had certain example.


How to improve performance of a query matching on the same table I have a requirement to find potentially matching customer records in the same table. The logic is as per below. However this seems to perform at O(N²). Is there anyway to improve the performance here? I've tried setting indexes, hashing the columns and comparing on that, etc but performance remains terrible over a large dataset. I've also added the query plan below.

SELECT
    C1.CustomerId AS Customer1,
    C2.CustomerId AS Customer2
FROM Customer C1
INNER JOIN Customer C2
    ON
    C1.CustomerId != C2.CustomerId
    AND
    (C1.FirstName = C2.FirstName OR C1.BirthDate = C2.BirthDate)
    AND
    (
        C1.EmailAddress = C2.EmailAddress
        OR
        C1.MobilePhoneNumber = C2.MobilePhoneNumber
        OR
        (
            C1.HomeAddressLine1 = C2.HomeAddressLine1
            AND
            (
                C1.HomePostCode = C2.HomePostCode
                OR
                C1.HomeSuburb = C2.HomeSuburb
            )
        )
    )

You can split your query into two different queries allowing for two different covering indexes to help you find the rows faster.

One query that checks FirstName and another that checks BirthDate.

select C1.CustomerId,
       C2.CustomerId
from dbo.Customer as C1
  inner join dbo.Customer as C2
    on C1.CustomerId <> C2.CustomerId and
       C1.FirstName = C2.FirstName and
            (
              C1.EmailAddress = C2.EmailAddress or
              C1.MobilePhoneNumber = C2.MobilePhoneNumber or
              (
                 C1.HomeAddressLine1 = C2.HomeAddressLine1 and
                (C1.HomePostCode = C2.HomePostCode or C1.HomeSuburb = C2.HomeSuburb)
              )
            );

select C1.CustomerId,
       C2.CustomerId
from dbo.Customer as C1
  inner join dbo.Customer as C2
    on C1.CustomerId <> C2.CustomerId and
       C1.BirthDate = C2.BirthDate and
            (
              C1.EmailAddress = C2.EmailAddress or
              C1.MobilePhoneNumber = C2.MobilePhoneNumber or
              (
                 C1.HomeAddressLine1 = C2.HomeAddressLine1 and
                (C1.HomePostCode = C2.HomePostCode or C1.HomeSuburb = C2.HomeSuburb)
              )
            );

The index you need to support those queries:

create nonclustered index IX_FirstName on dbo.Customer(FirstName)
  include(EmailAddress, MobilePhoneNumber, HomeAddressLine1, HomePostCode, HomeSuburb);

create nonclustered index IX_BirthDate on dbo.Customer(BirthDate)
  include(EmailAddress, MobilePhoneNumber, HomeAddressLine1, HomePostCode, HomeSuburb);

In my rather limited test case I saw an improvement from more time than I care to wait down to 4 seconds*.

The query plans I got for the two queries.

*I actually started the original query and forgot about it. Scratched my head trying to figure out why my computer was sluggish and found that the query had been running for 40 minutes.


Multiple subqueries in columns with joins to return multiple rows I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there is one more column joining year. Now I want to return the data like Name(actual name of user), Department(actual name of the department) and year by using subqueries. I tried but I am not getting any results.

main table
--------------------------
| user | department| year|
--------------------------
| 1    |  1        | 1992|
--------------------------
|  2   | 12        | 2007|
--------------------------
users table
--------------------
| pkey | name      |
--------------------
| 1    |  someone  |
--------------------
|  2   |  someName |
--------------------
department table
--------------------
| pkey | name      |
--------------------
| 1    |  Dep1     |
--------------------
|  12  |  Dep2     |
--------------------
expected result from query for main table with subquery for user and department by doing inner joins
--------------------------
| user     | department| year|
--------------------------
| someone  |  Dep1     | 1992|
--------------------------
| someName |  Dep2     | 2007|
--------------------------

why you need use subqueries ?

You can use joins for get data

SELECT u.name AS user, d.name AS departement, m.year
FROM main AS m
INNER JOIN department AS d ON m.departement_id = d.id
INNER JOIN users AS u ON m.user_id = u.id
;

I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1


Methods to identify/recognise 'bad' parameter sniffing in SQL Server There's lots of superb information out there about the certain approaches that can be used to mitigate bad parameter sniffing, but there's not a lot of information about how to go about identifying a bad parameter sniffing problem.

Assuming you're not using SQL Server 2016 so can't leverage Query Store and you don't have users complaining about a problematic stored procedure that intermittently performs poorly, what methods can be used to proactively identify a bad parameter sniffing problem?

Utilising Extended Events, RML Utilities and simply querying/polling the DMVs all seem like viable approaches, does anyone have a methodology using either of these or know of any good resources that describe how to go about doing this?

Identifying parameter sniffing is hard work! It may not be apparent using monitoring tools or queries, because it typically requires some analysis of other executions of the query plan.

This is where the DMVs, and a plan cache analysis tool like sp_BlitzCache can help. You can download it here. Full disclosure: I work for Brent Ozar, and contribute to the project.

It ONLY uses DMVs that would be available by your guidelines to perform analysis of the plan cache, etc. It's also free. If you can't install the stored proc, the relevant bit of code to attempt to identify parameter sniffing is here:

 parameter_sniffing = CASE WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND min_worker_time < ((1.0 - (@parameter_sniffing_warning_pct / 100.0)) * AverageCPU) THEN 1
                                 WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND max_worker_time > ((1.0 + (@parameter_sniffing_warning_pct / 100.0)) * AverageCPU) THEN 1
                                 WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND MinReturnedRows < ((1.0 - (@parameter_sniffing_warning_pct / 100.0)) * AverageReturnedRows) THEN 1
                                 WHEN AverageReads > @parameter_sniffing_io_threshold
                                      AND MaxReturnedRows > ((1.0 + (@parameter_sniffing_warning_pct / 100.0)) * AverageReturnedRows) THEN 1 END

The variables are configurable if you use the stored procedure. If you want to roll your own, how you assign the values is up to you. What we look at is if the average of certain plan attributes are wildly exceeded or, uh... underceeded by the min/max values of those attributes.

These are usually good indicators of parameter sniffing. Thinking about a 'small' value plan that performs a key lookup 5 times (and works well for most values over a number of executions). If a 'large' value comes along next and uses the cached plan, forcing the key lookup to execute hundreds of thousands of times, it would use more cpu/do more reads/run longer than average.

Hope this helps!


How do I drop all constraints from all tables? I want to drop all default constraints, check constraints, unique constraints, primary keys and foreign keys from all tables in a SQL Server database. I know how to get all the constraint names from sys.objects, but how do I populate the ALTER TABLE part?

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types.
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  ALTER TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N' DROP CONSTRAINT '
  + QUOTENAME(c.name) + ';'
FROM sys.objects AS c
INNER JOIN sys.tables AS t
ON c.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.[type] IN ('D','C','F','PK','UQ')
ORDER BY c.[type];

PRINT @sql;
--EXEC sys.sp_executesql @sql;

PRINT is just there for eyeballing, not for copying and pasting the output (if you want to run it, that's what the commented-out EXEC is for) - if you have a lot of constraints, it may not show the entire script because PRINT is limited to 4,000 characters (8kb). In those cases, if you need to validate the entire script, see this tip for other ways to validate the script before running. For example:
SELECT CONVERT(xml, @sql);

Once you are happy with the output, uncomment the EXEC.


Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation I've developed a timesheet recording system, that allows users to record:

A Period of time (9:00 to 12:00)
What they did during that time (DutyActivity: 1 hour report writing, 2 hours training)

When they submit a timesheet, the system also records their GeographyId, RoleId, CategoryId (single ids) and AttributeIds (multiple ids) - which is a snapshot of the user at that point in time.

This recorded timesheet information can then be queried for reports. In it's simplest form the system will return a list of UserIds with a SUM of the PeriodMinutes for each recorded DutyActivityId. Which would look like the following:

However what I also want to do is group by and return the recorded AttributeIds recorded with each timesheet (in the TimesheetAttribute table). However because there are multiple AttributeIds recorded for each timesheet, it's a little more complex.

My attempt at this creates the following report:

However the problem with this is that the SUM(PeriodMinutes) for each DutyCategoryId returned is now too high. It appears my SQL to include AttributeIds for each timesheet is causing the SUM calculation to be made incorrectly.

I've created a ZIP containing SQL for the database schema plus data, along with the SQL for both queries I've screenshot'd here: ZIP Timesheet example database

The information below is included in the ZIP, but I'm linking them here for ease of access:

Report on timesheets, without attributes grouping

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total
FROM
Timesheet AS T

LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

ORDER BY UserId ASC

Report on timesheets, with attempted attributes grouping

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TA.AttributeId * -1 AS AttributeId, /* Multiply AttributeId by -1 in order to create negative Ids, so that two PIVOT operations can be used */
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total

FROM
Timesheet AS T

LEFT JOIN
TimesheetAttribute AS TA ON
TA.TimesheetId = T.TimesheetId

LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
AttributeId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

/* Also PIVOT against the known AttributeIds */
PIVOT
(
SUM(AttributeId)
FOR AttributeId IN ([-1],[-2],[-3],[-4],[-5])
)
AS PivotAttribute

ORDER BY UserId ASC

I've managed to get the required result by dropping the second PIVOT and AttributeId GROUP BY argument and instead using a LEFT JOIN on the attributes.

This answer was provided by visahk16 on the SQL Team forums:

SELECT
    *
    FROM
    (
    SELECT
    T.UserId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TA.[-1],TA.[-2],TA.[-3],TA.[-4],TA.[-5],
    TDA.DutyActivityId AS TypeId,
    SUM(TDA.PeriodMinutes) AS Total

    FROM
    Timesheet AS T

    LEFT JOIN
    (SELECT TimesheetId,
     SUM(CASE WHEN AttributeId ='1' THEN -1 ELSE 0 END) AS [-1],
     SUM(CASE WHEN AttributeId ='2' THEN -1 ELSE 0 END) AS [-2],
...
     SUM(CASE WHEN AttributeId ='5' THEN -1 ELSE 0 END) AS [-5]
     FROM TimesheetAttribute
     GROUP BY TimesheetId
     )AS TA ON
    TA.TimesheetId = T.TimesheetId

    LEFT JOIN
    TimesheetDutyActivity AS TDA ON
    TDA.TimesheetId = T.TimesheetId

    GROUP BY
    T.UserId,
    AttributeId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TDA.DutyActivityId
    ) AS SourceTable

    /* PIVOT against the known DutyActivityIds */
    PIVOT
    (
    SUM(Total)
    FOR TypeId IN ([1],[2],[3],[4],[5])
    )
    AS PivotType

    ORDER BY UserId ASC


Making Switches Work in SSRS I'm trying to get my report to display different color cells, depending on the condition. This is my current code:

=Switch(Fields!DaysToExpiration.Value > 31, "Green",
        Fields!User_Certification_Status.Value = 8, "Firebrick”,
        Fields!User_Certification_Status.Value = 13, “Orange”,
        Fields!User_Certification_Status.Value = 11, “Orange”,
        Fields!User_Certification_Status.Value = 10, “Orange”,
        Fields!User_Certification_Status.Value = 12, “Orange”,
        Fields!DaysToExpiration.Value <= 30, "Yellow",
        1=1, "Gray")

So, my problem lies with the last part of the code. I'm using a matrix view, which means there are some cells where there is no data. SSRS is treating these like they have a value though (I'm assuming 0). This means that all the blank ones automatically turn to yellow, rather than gray. How can I get around this?
Thank You.

Yeah, SSRS can be a little cumbersome with its handling of empty/null values. Try adding an explicit isnothing() before the yellow option:

=Switch(Fields!DaysToExpiration.Value > 31, "Green",
    Fields!User_Certification_Status.Value = 8, "Firebrick”,
    Fields!User_Certification_Status.Value = 13, “Orange”,
    Fields!User_Certification_Status.Value = 11, “Orange”,
    Fields!User_Certification_Status.Value = 10, “Orange”,
    Fields!User_Certification_Status.Value = 12, “Orange”,
    IsNothing(Fields!DaysToExpiration.Value), "Gray",
    Fields!DaysToExpiration.Value <= 30, "Yellow",
    1=1, "Gray")


postgresql set variable not working with command option Has anyone a solution for this issue (tested on postgres v12)

I would like to use the psql variable option in conjunction with command like this:

psql -v env=test -c "SELECT :'env';"

but this will fail

on the contrary this one won't echo "SELECT :'env';" |psql -v env=test

Has anyone an idea about how to solve this or do you think this is a bug ?
Thanks for your insights

It's not a bug, as the psql documentation warns about this:

  -c command
  ...

  command must be either a command string that is completely parsable
             by the server (i.e., it contains no psql-specific features), or a
             single backslash command.

The expansion of :'env' in -c "SELECT :'env';" is a psql-specific feature.

I would suggest that the solution is to use heredoc rather than -c:

$ psql -v env=test <<END_OF_SQL
select :'env';
END_OF_SQL

Output:

?column?
----------
 test
(1 row)


Transaction log queries I have used these queries to access transactions from an SQL Server database

Use Action
Go
Select * from fn_dblog(null,null)

This query works but only loads 7 lines when one table in database holds over 100,000 records
Next I used this query to recover history

SELECT  * FROM fn_dump_dblog(NULL,NULL,'DISK',1
,'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Action.bak'
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL)

This query worked without error but only recovered 37 lines. If I restore the backup there are more than 100,000 records in one table. I would expect to see hundreds or thousands of lines when I run these queries.

The only logical answer would be that the transactions in the file are limited. But the file size for the transaction log is 1.5 GB. Is there any command I can run to check the number of transactions in the log file?

Can anyone explain how I can make these queries work to recover every transaction in the transaction log?

This query works but only loads 7 lines when one table in database holds over 100,000 records

The thing with sys.fn_dblog is that it only shows you the active portion of the log. If you wanted every transaction that ever happened through means such as this, I might suggest something more useful of your time.

  This query worked without error but only recovered 37 lines. If I restore the backup there are more than 100,000 records in one table. I would expect to see hundreds or thousands of lines when I run these queries.

A single log backup doesn't hold everything that ever happened, you'll need every log backup ever taken. If your database was ever in the simple recovery model, or if it was in the full recovery model and anything was accomplished in it before a full backup was taken then you're not going to get everything back. Period.

Just because there are 100k records in the table doesn't mean there were 100k transactions, either. It could have been one big transaction, it could have been 2 transactions that inserted 200k and then a 3rd transaction that removed 100k, etc. ad nauseam.

  The only logical answer would be that the transactions in the file are limited.

Yes in terms of what you can read at any given point as the log file can be thought of as a giant ring buffer that writes over the older inactive portions. It's circular and will continue re-using inactive portions unless it fills up and must grow because all of the current space is active.

  Is there any command I can run to check the number of transactions in the log file?

You already have them, you just need to do this for every log backup ever taken across all of the time the database has been in existence.

Also, this doesn't take into account actually reading the log structure which isn't publically documented. This doesn't even include the newer log structures for things like Hekaton which have their own structure inside of the log structures or any of the trace flags you'd need to set certain behaviors.

All-in-all you're better off doing something else with your time. If this is needed as part of database recovery then I'd employ some professionals. If this is purely for academic then I would suggest creating a very small database, putting it in the full recovery model, taking a full backup, and not to take a log backup until you're done looking at that part of the log.


How to drop column named ` I managed to create a column in my MySQL database named ` (the backtick character).  How can I delete it?  Is there a way to escape the backtick character in a query?

You could try setting the sql_mode to use ANSI_QUOTES and then dropping the column that way:

SET sql_mode = 'ANSI_QUOTES';

ALTER TABLE "schema"."table" DROP COLUMN "`";


Is it possible to write a select * view that includes new columns added to table? I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them.

If I do the following:

create database viewtest;
\c viewtest
create table a (x text);
create view b as select * from a;
alter table a add y text;
select * from b;
 x
---
(0 rows)

select * from a;
 x | y
---+---
(0 rows)

\d+ b
                  View "public.b"
 Column | Type | Modifiers | Storage  | Description
--------+------+-----------+----------+-------------
 x      | text |           | extended |
View definition:
 SELECT a.x
   FROM a;

I want to essentially create a view where the definition includes * rather than an explicit list of the columns in the table when I created the view. Maybe I'm crazy, but this seems like a practical use case and I can't figure out how to do it for the life of me.

The basic problem for your idea is that a view (or materialized view) uses "early binding", meaning the query is parsed at the time of creation. SELECT * is expanded to the list of columns visible at that time. Postgres registers a row type for the view. Later additions to the table are not cascaded to any views. (And existing columns cannot be deleted due to the registered dependency, unless you delete the view first.)

A function taking a polymorphic input type is a limited workaround, but only for registered row types. Example:

How to create function(table_name) that returns a set of the table

For your case: SELECT a + b AS ab, * FROM tbl, just use the simple query as given. If a + b is just a placeholder for a more complex calculation, I would suggest to encapsulate the calculation in a function like:

CREATE OR REPLACE FUNCTION f_ab(a int, b date)  -- use actual data types
  RETURNS int AS   -- use actual data type
$func$
SELECT a + b  -- placeholder for more complex calculation
$func$  LANGUAGE sql STABLE;

And adapt your query to:

SELECT f_ab(a, b) AS ab, * FROM tbl;


Does Azure SQL Data Sync support MySQL or SQLExpress? I did see that it supports SQL Server (2005 and later) but does it support syncing between a SQLExpress server or MySQL server and an Azure Server?

I'm trying to use it with a SQLExpress server but it isn't connecting. Is this because it's not supported?

The error I'm getting when trying to connect with SQL Data Sync 2.0 on my server computer is: Unable to connect to the Database. Please check if service account has connectivity, credentials, and permissions to connect.

I've opened up port 1433 in the firewall and can connect to the server via SSMS. However, the SQL Data Sync 2.0 application won't connect.

I'll be trying this with a MySQL server later as well so I just wanted to know if it's supported or not as well.

Azure SQL Data Sync does not support MySQL for synchronization but it does support SQL Server Express as you can read here.

One of the troubleshooting steps is to exit local sync agent, then open the services.msc applet in Control Panel, make sure SQL Data Sync service is started, if the service is started please restart the service. If the service cannot start change the service account to a local Administrator of the computer. Reopen the local sync agent.

Try also to disable any security software (Avast, Windows Firewall). Start SQL Server Browser Service on the computer.

Another possible reason, the agent key that you have entered is invalid. The agent key being submitted is not the most recent; you may have regenerated a newer key on Azure Sync portal. Try regenerating on Azure portal and resubmitting.


Is it possible to change ENUM() lists? I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').

Then I executed...

ALTER TABLE  `test`
CHANGE  `bool`  `bool` ENUM(  'yes',  'no',  'maybe' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

...and it worked.

Have I done anything wrong? Is it dependent on db engine?Why everybody says changing a ENUM() list isn't possible? eg. here http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

As long as the table is empty, there is no problem. As long as new values for ENUM is appended and not renamed given a populated table, again no problem.

The ENUM you redefined in your question actually kept the original internal values for yes and no as the test table last remembered it.

The following applies to populated tables:

What about this?

ALTER TABLE  `test`
CHANGE  `bool`  `bool` ENUM(  'no',  'yes',  'maybe' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Now you have a problem. The ENUM values in a fully populated table would have their internal values reversed so that yes is now no and no is now yes.

What about this?

ALTER TABLE  `test`
CHANGE  `bool`  `bool` ENUM(  'maybe', 'no',  'yes' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Big Problem. In a populated table, yes is now maybe. New rows inserted with yes are disconnected from the previous yes rows because they now mean maybe.

SUMMARY

There are very high-risk, bait-and-switch techniques to do this very quickly in MyISAM. I would strongly advise against doing this in InnoDB because of its tablespace id interaction with ibdata1.