How can I use mysql to determine the Sundays for the last year? I have a table where I'm currently aggregating ~200 rows per day of stats.  These stats include things like # of users, how many people using X feature, etc.

What I'd like to do is query this table so summarize the data in this way:

Daily over the past X days
Weekly over the past year - Take the stats from each Sunday and pulling the current day (assuming today isn't Sunday)
Monthly over the past X years - Take the stats from the last day of each month and pulling the current day (assuming today isn't the last day of the month)

Columns:

date is the day the data was aggregated
value is the numeric value I need to show (e.g. - # of users)

I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available.  How can I use mysql to determine the Sundays for the last year?

Unfortunately I can't provide an example of what I have working at the moment, I'm still investigating potential solutions.  Most queries I come across are querying records by dates, not generating the dates themselves.

My table structure:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

You can use a Calendar table for this and other purposes. A calendar table is always useful.

I've set up a minimal example, but usually a Calendar table has more detailed fields like, hours, minutes, ect.

CREATE TABLE Calendar (
    cDate datetime,
    cDay int,
    cDayOfWeek int,
    cDayName varchar(20)
);

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    INSERT INTO Calendar (cDate, cDayOfWeek, cDayName)
    VALUES (dateStart, DAYOFWEEK(dateStart), DAYNAME(dateStart));
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

CALL filldates('2018-01-01','2018-12-31');

  I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available. How can I use mysql to determine the Sundays for the last year?

You can use the calendar table to build this empty table in this way:

INSERT INTO daily_stats (`date`, `stat`, `value`, `created_at`)
SELECT
    cDate, 0, 0, now()
FROM
    Calendar
WHERE
    cDayOfWeek = 7;

NOTE: cDayOfWeek could be different depending on first day of week in your system.

SELECT * FROM daily_stats LIMIT 10;

SELECT * FROM daily_stats LIMIT 10;

id | date       | stat | value | created_at          | updated_at
-: | :--------- | ---: | ----: | :------------------ | :---------
 1 | 2018-01-06 |    0 |     0 | 2019-01-19 18:16:49 | null
 2 | 2018-01-13 |    0 |     0 | 2019-01-19 18:16:49 | null
 3 | 2018-01-20 |    0 |     0 | 2019-01-19 18:16:49 | null
 4 | 2018-01-27 |    0 |     0 | 2019-01-19 18:16:49 | null
 5 | 2018-02-03 |    0 |     0 | 2019-01-19 18:16:49 | null
 6 | 2018-02-10 |    0 |     0 | 2019-01-19 18:16:49 | null
 7 | 2018-02-17 |    0 |     0 | 2019-01-19 18:16:49 | null
 8 | 2018-02-24 |    0 |     0 | 2019-01-19 18:16:49 | null
 9 | 2018-03-03 |    0 |     0 | 2019-01-19 18:16:49 | null
10 | 2018-03-10 |    0 |     0 | 2019-01-19 18:16:49 | null

db<>fiddle here


Stored Procedure which accept xml file as input <?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer's Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.</description>
   </book>
</catalog>

I have xml file as above.

In my SQL Server 2008 database there is table Book__Master In which all the columns are same as elements in above xml file i.e. there are columns like bookid, author, title etc.

Now I want to write a stored procedure which accepts this xml file as input parameter and stores the element's values as it is in their respective columns.

Can anyone help me on this that how I can write this type of stored procedure?

You can use X-Query for that:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE PROCEDURE dbo.ParseXML
   @XML XML
AS
BEGIN

  SELECT
    Book.value('@id','NVARCHAR(100)') AS id,
    Book.value('author[1]','NVARCHAR(100)') AS author,
    Book.value('title[1]','NVARCHAR(100)') AS title,
    Book.value('price[1]','DECIMAL(10,4)') AS price,
    Book.value('publish_date[1]','DATE') AS publish_date

    FROM @XML.nodes('catalog/book')Catalog(Book)

RETURN;
END;

Query 1:

DECLARE @FileXML XML = '<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer''s Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies,
      an evil sorceress, and her own childhood to become queen
      of the world.</description>
   </book>
</catalog>';

EXEC dbo.ParseXML @FileXML;

Results:

|    ID |               AUTHOR |                 TITLE | PRICE | PUBLISH_DATE |
|-------|----------------------|-----------------------|-------|--------------|
| bk101 | Gambardella, Matthew | XML Developer's Guide | 44.95 |   2000-10-01 |
| bk102 |           Ralls, Kim |         Midnight Rain |  5.95 |   2000-12-16 |

This shows you how to get to the values. From there you can easily insert them into your table.


Options for having same tables in 2 different data bases I need to know what options I have for my next problem:

I have 2 databases (sql server 2012)

first for an ERP
second for an website

For the website's database, I want to use a table (customer) which is created in ERP database.

I need a copy in real time of that table, in website's database. When a customer is entered by erp, it should appear in website too. Vice-versa is not necessary

How can I do this?

What you're looking for is Transactional Replication.

It allows you to replicate specific objects from one database to another.


Correct way to group by month and year? I need to group by month (and year) and I was thinking about:

GROUP BY CAST(YEAR(tDate) AS NVARCHAR(4)) + '-' + CAST(MONTH(tDate) AS NVARCHAR(2))

But I found in the web something like:

GROUP BY YEAR(tDate), Month(tDate)

Both are equivalent? It is better to use the second?

You should go with the second approach.  All of the string concatenation and type conversion just adds unnecessary CPU overheard to the query.

I can try this out on the Stack Overflow schema.  I'm going to query the user's table, and create a supporting nonclustered index on the date field:

CREATE NONCLUSTERED INDEX IX_CreationDate ON dbo.Users (CreationDate);

Here's an example of the first approach:

SELECT
    CAST(YEAR(u.CreationDate) AS NVARCHAR(4))
        + '-'
        + CAST(MONTH(u.CreationDate) AS NVARCHAR(2)),
    COUNT(*)
FROM dbo.Users u
GROUP BY
    CAST(YEAR(u.CreationDate) AS NVARCHAR(4))
        + '-'
        + CAST(MONTH(u.CreationDate) AS NVARCHAR(2));

And the plan and stats:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'Workfile'. Scan count 0, logical reads 0
Table 'Users'. Scan count 1, logical reads 675

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 258 ms.

Compare that to the second approach:

SELECT
    YEAR(u.CreationDate), Month(u.CreationDate),
    COUNT(*)
FROM dbo.Users u
GROUP BY YEAR(u.CreationDate), Month(u.CreationDate);

Here are the plan and stats for that one:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'Workfile'. Scan count 0, logical reads 0
Table 'Users'. Scan count 1, logical reads 675

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 229 ms.

As you can see, the string concat / cast approach uses slightly more CPU.  Everything else is the same.

By the way, the dbo.Users table only has about 300,000 rows in it.  Going through this same exercise on the dbo.Posts table, which has about 3,700,000 rows in it, the query goes parallel, and the CPU savings for the second approach become much more significant.

So not only is the 2nd approach better in terms of CPU in the general case, it also appears to scale better as data size increases.


How memory limit for backup process is calculated? How "memory limit" for backup process is actually calculated?

You must have got the output after using trace flags in command something like dbcc traceon(3605, 3004, 3014, 3213, -1). If you read the How It Works: How does SQL Server Backup and Restore select transfer sizes on this TF it says

WARNING: This trace flags should be used under the guidance of Microsoft SQL Server support.  They are used in this post for discussion purposes only and may not be supported in future versions.

The TF is undocumented so is its output parameters. The memory limit is probably calculated internally via code to which a normal Joe do not have information or access. So I would suggest not to waste time on finding how it is calculated. You can focus on MAXTRANSFERSIZE and the BUFFERCOUNT and play around with it.


How to export the output of sp_AskBrent? We have an instance that randomly spikes the CPU. I want to create an alert that fires on CPU over 90% and automatically calls a job that runs sp_AskBrent and emails me the output. However, the output is unreadable in either Text or HTML output. It doesn't even go well into an Excel spread sheet. How can I get the info in a readable format?

Make the alert run a job that runs the EXEC msdb.dbo.sp_send_dbmail with the @query parameter:

EXEC dbo.sp_send_dbmail @profile_name = 'mail_profile'
    , @recipients = 'some@mail.com'
    , @subject = 'some subject'
    , @body = 'the body text goes here'
    , @query = 'EXEC sp_AskBrent;'
    , @attach_query_result_as_file = 1;

The @attach_query_result_as_file = 1 parameter will send the results as an attachment in text format, which should certainly be readable.  If you want, you can export the results in comma-delimited format using the @query_result_separator parameter.

From the MSDN page for sp_send_dbmail:

  This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order Count, and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

There are many options for that stored proc; check the MSDN page and likely you'll get the output exactly how you want it.


using statistics in order to decide whether or not to add a filtered index I have many queries on table dbo.tblSAPDespatchItem, some of them
have the following condition in the where clause:

WHERE   b.lngDespatchItemStatusID = 2
AND     b.decvalue > 0

for example

Partial list of a query:

            ,despatchDate= CAST(CAST( a.dtmUpdated AS DATE ) AS DATETIME)
            ,strbxorderNo=A.strBxOrderNo

    FROM    dbo.tblSAPDespatch a
    INNER JOIN dbo.tblSAPDespatchItem b on a.lngDespatchID = b.lngDespatchID
    INNER JOIN [dbo].[tblBorder] C on C.strBxOrderNo = A.strBxOrderNo

    INNER JOIN dbo.tblBAccountParticipantEmail D on C.lngOrderedByParticipantID = D.lngParticipantID

    INNER JOIN dbo.tblBAccountParticipant E on d.lngParticipantID = e.lngParticipantID
    INNER JOIN dbo.vwtblItem F on f.strItemNo = b.strItemNo
    INNER JOIN @DIS DIS ON A.lngDespatchStatusID = DIS.lngDespatchStatusID
    INNER JOIN dbo.tblBMarket H on H.sintMarketID = c.sintMarketID

    WHERE   b.lngDespatchItemStatusID = 2
    AND     f.strStyle not in ( 'VOUCH', 'GG001', 'GG002' )
    AND     d.strEmail = @email
    AND     a.sintMarket = @marketId
    AND     a.[dtmUpdated] BETWEEN @BeginDt AND @EndDate
    AND     b.decvalue > 0

Can I use statistics to assess the benefit of adding a filtered index?

Using the scripts from this question here:

How to script statistics in Sql Server? (using T-SQL)

I get all stats on that table:

CREATE STATISTICS [_WA_Sys_00000006_33B97E11] ON [dbo].[tblSAPDespatchItem]( [strItemNo])
CREATE STATISTICS [_WA_Sys_00000007_33B97E11] ON [dbo].[tblSAPDespatchItem]( [strLocation])
CREATE STATISTICS [_WA_Sys_00000008_33B97E11] ON [dbo].[tblSAPDespatchItem]( [sintOrderItemSeqNo])
CREATE STATISTICS [_WA_Sys_0000000E_33B97E11] ON [dbo].[tblSAPDespatchItem]( [lngUserID])
CREATE STATISTICS [_WA_Sys_0000000F_33B97E11] ON [dbo].[tblSAPDespatchItem]( [dtmUpdated])
CREATE STATISTICS [_WA_Sys_00000010_33B97E11] ON [dbo].[tblSAPDespatchItem]( [decValue])
CREATE STATISTICS [_WA_Sys_00000011_33B97E11] ON [dbo].[tblSAPDespatchItem]( [sintOrderSeqNo])

then running the following command:

dbcc show_statistics ('tblSAPDespatchItem','_WA_Sys_00000010_33B97E11')

I get the following picture:

from the picture:

SELECT CONVERT(BIGINT,STR(CAST('1.9777E+07' AS REAL)))
SELECT CONVERT(BIGINT,STR(CAST('2.199613E+07' AS REAL)))

This gives me:

select 21996130 + 19777000
-- 41,773,130

Basically nearly 42 million rows I don't need to deal with

and when comparing to the actual values in the table, they approximately match:

sp_count 'tblSAPDespatchItem'
-- 175,918,697

select count(*)
from tblSAPDespatchItem with (nolock)
where decvalue <= 0
--19,396,110

select count(*)
from tblSAPDespatchItem with (nolock)
where decvalue <= 0
or decvalue is null
-- 43,238,039

This is probably not the sort of case where filtered indexes really shine - typically that is when the index represents only a small portion of rows in the table (like, a quarter or less) and in this case the filter predicate matches about three quarters of the table.

That's not to say that you wouldn't get gains here, but it might be a bit of a tricky research project to get good designs. (Then again, indexing is always like that.)

What about the current indexes on the table. Are they being used at the moment? Are they only being used in queries that have the predicate that you mentioned? If so, then it might be an easy win to convert some of your existing indexes to have a filter condition (instead of adding a new, filtered index).

You probably already knew this, but filtered indexes won't be used by where the predicate that matches the filter uses a variable or parameter. https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/


LocalDB v14 creates wrong path for mdf files Recently, I upgraded LocalDB from version 13 to 14 using the SQL Server Express installer and this instruction. After the installation, I stopped the existing default instance (MSSQLLOCALDB) of version 13 and created a new one, which automatically used the v14.0.1000 server engine.

I often use LocalDB for Database Integration tests, i.e. in my xunit tests, I create a (temporary) database which is deleted when the test finishes. Since the new version, unfortunately all my tests fail because of the following error message:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\kepflDBd0811493e18b46febf980ffb8029482a.mdf'

The odd thing is that the target path for the mdf file is incorrect, a backslash is missing between C:\Users\kepfl and DBd0811493e18b46febf980ffb8029482a.mdf (which is the random database name for a single test). The databases are created via the simple command CREATE DATABASE [databaseName] - nothing special here.

In SSMS, I see that the target locations for data, log, and backup are the following:

However, when I try to update the location, I get another error message:

How can I update the default locations so that LocalDB is able to create databases again? It's obvious that LocalDB does not correctly combine the default location directory and the database file name - is there a registry entry that I can edit? Or anything else?

Update after Doug's answer and sepupic's comment

According to this Stackoverflow question, the default location's should also be changeable via the registry. However, if I try to find the corresponding keys "DefaultData", "DefaultLog" and "BackupDirectory", I cannot find them in my registry. Did SQL Server v14 rename these registry keys, or moved these information out of the registry?

UPDATE

As of CU 6 for SQL Server 2017, this bug has been fixed. It is now possible to execute the following successfully:

CREATE DATABASE [CreateDatabaseTest];
DROP DATABASE [CreateDatabaseTest];

The problem, and the fact that it is fixed in CU6, is documented in the following KB article:
FIX: "Access is denied" error when you try to create a database in SQL Server 2017 Express LocalDB

To get the Cumulative Update, please go to the following page and grab the top (i.e. latest) build, which might be newer than CU6 depending on when you see this:

SQL Server 2017 build versions

BELOW INFO OBSOLETE AS OF SQL SERVER 2017 CU6 (Released 2018-04-17)

The lack of a backslash in the combined Path + File name appears to be a bug with SQL Server 2017. I just ran into it myself. I even tried editing the Registry to add a DefaultData string Value for C:\Users\MyAccountName\ in both of the following Keys (the 3 default paths are not in any of the LocalDB registry keys that I looked through):

Computer\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances\{some-GUID-value}
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14E.LOCALDB\MSSQLServer

And yes, I did shutdown and start up again the LocalDB instance in both attempts.

However, I am not convinced that not being able to change the default paths is a bug as it might just be poor documentation and poor error handling combined. I say this because I just tried editing the default locations for SQL Server LocalDB versions 2014, 2016, and 2017, and all resulted in the exact same error, which in itself is odd due to being from RegCreateKeyEx(), which should be dealing with the Registry and not the file system.

Not being able to change the path is unfortunate due to the lack of backslash when creating a new Database without specifying the files to use. However, I was able to create a new Database using the full CREATE DATABASE syntax as follows:

CREATE DATABASE [XXXXX]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'XXXXX_sys', FILENAME = N'C:\Users\MyAccountName\XXXXX_sys.mdf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
 FILEGROUP [Tables] DEFAULT
( NAME = N'XXXXX_data', FILENAME = N'C:\Users\MyAccountName\XXXXX_data.ndf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'XXXXX_log', FILENAME = N'C:\Users\MyAccountName\XXXXX_log.ldf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 COLLATE Latin1_General_100_CS_AS_KS_WS_SC;
GO


How to estimate/predict data size and index size of a table in MySQL I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer

For an example we have a table City with InnoDB engine,lets say in future (in next 1 year) it will have 1 million of records so what will be the estimated data size and index size of that table in this period.

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

UPDATE

What will be the estimated upper bound (Max size of table) with 1 million records and how can we estimate it.

Given the table description, I see

66 bytes per row of data
4 bytes per row for the primary key
7 bytes per row for country code index

3 bytes for the country
4 bytes for Clustered Key attached to the country code

Total of 77 bytes of data and keys
This does not factoring housekeeping for BTREEs or Tablespace Fragmentation

For a million rows, that would 77,000,000 bytes (73.43 MB)

As for measuring the table, for a given table mydb.mytable, you can run this query

SELECT
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

To measure all tables grouped by Database and Storage Engine

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

Run these queries and you can track changes in database/engine disk usage.

Give it a Try !!!


Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert? I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT
on a SQL Server 2008 R2.

A table could look like this:

CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] (
    [id] [int] NOT NULL,
    [description] [varchar](20) NOT NULL,
    [datetime] [datetime] NULL,
CONSTRAINT [pk_bulk_insert_datetime_issue] PRIMARY KEY CLUSTERED (
    [id] ASC
))

The insert batch:

BULK
INSERT [dbo].[tbl_bulk_insert_datetime_issue]
FROM 'C:\temp\bulkinsertsample.csv'
WITH
(
    FIELDTERMINATOR=';'
)

If I am to insert the following CSV content:

1;row01;
2;row02;20130401
3;row03;
4;row04;20130515

The table' content was parsed and inserted as expected:

id          description          datetime
----------- -------------------- -----------------------
1           row01                NULL
2           row02                2013-04-01 00:00:00.000
3           row03                NULL
4           row04                2013-05-15 00:00:00.000

But if the CSV file contains invalid data for the optional datetime

1;row01;
2;row02;20130401
3;row03;not_a_datetime
4;row04;20130515

SQL Server inserts the last valid datetime value instead of discarding the row with an error message:

id          description          datetime
----------- -------------------- -----------------------
1           row01                NULL
2           row02                2013-04-01 00:00:00.000
3           row03                2013-04-01 00:00:00.000
4           row04                2013-05-15 00:00:00.000

If I am to insert a row with an invalid datetime and no valid parsable datetime at all

1;row01;not_a_datetime

SQL Server inserts the default value of DATETIME:

id          description          datetime
----------- -------------------- -----------------------
1           row01                1900-01-01 00:00:00.000

Why does Sql Server insert the (for that specific row) invalid datetime when using bulk insert?
If I try to insert rubbish to an INT column the row will not be inserted by the bulk batch and an error will be thrown...

UPDATE:
I will share some information about the environment I used to reproduce the behaviour.

OS: Windows 7 Enterprise SP1 x64
regional settings: de-DE
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Enterprise Edition
database collation: Latin1_General_100_CS_AS
file encoding: UCS-2 Little Endian (with Windows CRLF)

As hinted by Max Vernon the file encoding seems to be at fault. Setting the encoding to UTF-8 without BOM the bulk insert will throw the error I expected and discards the invalid row.

On SQL Server 2005, and SQL Server 2012, I did the following:

USE tempdb;
CREATE TABLE ImpTest
(
    ImpTestID INT NULL
    , ImpTestDate DATETIME NULL
);
GO
BULK INSERT ImpTest FROM 'C:\SQLServer\ImportTest.txt' WITH (FIELDTERMINATOR=',');
SELECT * FROM ImpTest;
DROP TABLE ImpTest;

With the following data:

1,
2,20130406
3,NOT_DATE
4,
5,20130409

On both servers, I received the following:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified
codepage) for row 3, column 2 (ImpTestDate).

Perhaps, as Aaron noted, this is a bug that needs to be addressed through http://connect.microsoft.com

I'm wondering about some of the details of your system, including locale settings, collations, physical format of the import file (is it from a Unix system? etc).


Restoring a mysqldump loses user privileges for routines? When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql schema have no user privileges. (eg. Schema "abc" has the correct user privileges for routines but in schema "qrs" they are missing.)

The dump I am using is created by:

mysqldump --allow-keywords --all-databases --single-transaction --events --routines --flush-logs --flush-privileges --hex-blob

When I have run into this issue in the past I would load the dump and then reload just the mysql schema, which then gives the missing users privileges on the routines.

However recently I have started using GTIDs and as a result I can't reload the just the mysql schema from the dump as the GTIDs overlap, as when I try to do so i get this error:

@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

Is there a way to reload a dump made this way and have all the user permissions be exactly the same after reloading the dump on another server?

You need to add the --set-gtid-purged=OFF flag to your mysqldump statement. This will stop it from writing the set-gtid-purged line in the dump file.


Using Dynamic Data Masking in SQL Server set Custom String I need to mask the data in the following table.

For example, I need to mask the first_name of the user Roberto to something like NAME<<userID>>, So the end result should look like NAME1
Is this possible in SQL Server?
My SQL Server details below.

I have tried these methods from the documentation but still no luck
from documentation

You cannot refer to data in some other column so if that 1 should come from the MemberId column, then you are out of luck. What you have to play with are the prefix and suffix, and those need to be from the same column as the masking is defined on.


The best way to log access to SSRS reports We have a number of SSRS reports created using BIDS 2008 on SQL Server 2008 r2. The business wants us to log access to these reports. For example who and when run the Report A.

What is the best way of achieving that? Is it suitable if we use SQL Auditing feauture for  that?

Thank you.

SSRS has Execution Logs for this. http://msdn.microsoft.com/en-us/library/ms155836%28v=sql.100%29.aspx


What is Sql Server Clustered Index in Oracle terminology A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.

I would like a simple explaination for this concept for oracle savy users.

More information about Clustered Indexes in Sql Server.
What is a Clustered Index?

In Oracle terminology, it is an Index Organized Table, commonly referred to as an IOT.

  An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.


Microsoft SQL Server 2016 Restore command many files I have a question regarding proper syntax for restoring a 2016 SQL Server database.
One database 1 mdf no issue. But I need to restore a database that has 3 mdf files and 73 ndf files and then apply 2 differential back ups.
RESTORE DATABASE [Database]
FROM DISK = N'E:\Data\Server_Database_20210213221000_full_1_of_2.bak',
     DISK = N'E:\Data\Server_Database_20210213221000_full_2_of_3.bak'
     DISK = N'E:\Data\Server_Database_20210213221000_full_3_of_3.bak'
WITH FILE = 1, REPLACE,
MOVE 'Database' TO 'E:\Data\Database.mdf',
MOVE 'FactPart00' TO 'E:\Data1\FactPart\FactDatePart00.ndf',
MOVE 'FactPart01' TO 'E:\Data1\FactPart\FactDatePart01.ndf',
MOVE 'FactPart02' TO 'E:\Data1\FactPart\FactDatePart02.ndf',
... all the way to 73

The above part works my question is now once how do I then restore diff file
File Name
Server_Database_DIFF_20210214_220209.bak

To restore the diff will I also need to reference all the ndf files as well or just restore. What should my syntax look like? I don't have an error since I have not ran it as I am nervous about corrupting database.

to restore a differential backup please tale a look to the official doc here:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-sql-server?view=sql-server-ver15#using-transact-sql
You have to restore your full backup with the NORECOVERY clause. Then take your last differential backup and restore it.
Something like that:
RESTORE DATABASE [Database]
   FROM Disk = N'Database_backup_full.bak'
WITH FILE = 1, REPLACE, NORECOVERY,
MOVE 'Database' TO 'somepath\Database.mdf',
.....
   ;
GO
RESTORE DATABASE [Database]
   FROM Disk = N'Database_backup_differential.bak'
   WITH RECOVERY;
GO

You do not need to specify MOVE clause again in restoring differential backup files if that's already done when restoring full backup file


Error 976 Severity: 14, State: 1. Always on Clustering This has been happening in both the replicas (secondaries) at the same time ....Nothing shows on the primary replica.

Error: 976, Severity: 14, State: 1.
The target database is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online

Any ideas on where to start or what might be the problem?

You are attempting to connect to databases on secondary replicas when the availabilty group replica is not configured to allow connections on anything other than the primary replica.

The following script will tell you the state of the secondary replicas, and whether they will accept read connections:

SELECT ag.name,
       replica_server_name,
       secondary_role_allow_connections_desc
FROM sys.availability_replicas ar
    JOIN sys.availability_groups ag
        ON ag.group_id = ar.group_id;

The result of each replica with be:

NO - no connections allowed to the database (default behavior)
READ_ONLY - connections allowed when the ReadIntent flag is passed in a connection string
ALL - all connections to the databases are permitted.

To change the setting for a replica you can either use the GUI, or a script:

ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON 'ReplicaName'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = 'TCP://ReplicaName:Port'))

For more details see the Microsoft documentation.


How to Restore Postgresql Cluster from data directory in our office we have a development database server that runs Debian 5, on this server we had a postgresql 8.1 running but because of the lack of internet access from the server we backed up with pg_dump_all the entire cluster, but the dump file is lost then we installed many versions and neither 8.1 version itself recognize the database cluster, so my question is, theres a way to reinstall the same postgres 8.1 and tell him to take the old cluster from data folder? or the backup should be via the dump file only? i only have the data folder but neither the 9.2, 8.4, or the same 8.1 can recognize the cluster. this is the error im getting while trying to initialize the same cluster with pg 8.1

postgres@Desarrollo-SDQ-VE:/opt/postgres/8.1/bin$ ./postgres -D  /databases/postgresql/8.1/ve/ -c config_file=/etc/postgresql/8.1/ve/postgresql.conf
[2012-11-13 10:42:22 AST:18359id]     2012-11-13 10:42:22 AST    2012-11-13 10:42:22.137 AST  FATAL:  database files are incompatible with server
[2012-11-13 10:42:22 AST:18359id]     2012-11-13 10:42:22 AST    2012-11-13 10:42:22.138 AST  DETAIL:  The database cluster was initialized with HAVE_INT64_TIMESTAMP but the server was compiled without HAVE_INT64_TIMESTAMP.
[2012-11-13 10:42:22 AST:18359id]     2012-11-13 10:42:22 AST    2012-11-13 10:42:22.138 AST  HINT:  It looks like you need to recompile or initdb.

any Idea?

Here's what I'd do:

Download postgresql-8.1_8.1.8-1.diff.gz, postgresql-8.1_8.1.8-1.dsc and postgresql-8.1_8.1.8.orig.tar.gz from http://archive.debian.org/debian/pool/main/p/postgresql-8.1/ on a machine running the same OS as what you intend to install on.
dpkg-source -x postgresql-8.1_8.1.8-1.dsc
Examine debian/rules for configure options.

This shows:

DEB_CONFIGURE_EXTRA_FLAGS := --mandir=\$${prefix}/share/postgresql/8.1/man \
                    --with-docdir=\$${prefix}/share/doc/postgresql-doc-8.1 \
                    --datadir=\$${prefix}/share/postgresql/8.1 \
                    --bindir=\$${prefix}/lib/postgresql/8.1/bin \
                    --includedir=\$${prefix}/include/postgresql/ \
                    --enable-nls \
                    --enable-integer-datetimes \
                    --enable-thread-safety \
                    --enable-debug \
                    --disable-rpath \
                    --with-tcl \
                    --with-perl \
                    --with-python \
                    --with-pam \
                    --with-krb5 \
                    --with-openssl \
                    --with-gnu-ld \
                    --with-tclconfig=/usr/lib/tcl$(TCL_VER) \
                    --with-tkconfig=/usr/lib/tk$(TCL_VER) \
                    --with-includes=/usr/include/tcl$(TCL_VER) \
                    --with-pgport=5432 \
                    $(ARCH_OPTS) \
                    CFLAGS='$(CFLAGS)' \
                    LDFLAGS='$(LDFLAGS)'

This should help you do your own source build. In particular I think you probably need --enable-integer-datetimes.

Alternately, you could dpkg-buildpackage -rfakeroot -uc -b to build the package, then copy it to the target server and install it.


Column Concatenation Is it possible to concatenate the rows from two/more different columns into one new column ?
Example:

 C1     C2      C3
---- + ---- => ----
 a      d       a
 b      e       b
 c      f       c
                d
                e
                f

this should do the trick if you are using T-SQL

select C1, C2, a.Chuck
from testa
right join  (select C1 as Chuck from testa UNION ALL select C2 as Chuck from testa) as a  ON a.Chuck = testa.C1


What's the difference between Replicate_Wild_Ignore_Table & Replicate_Ignore_Table? In my site's master/slave setup:

Replicate_Wild_Ignore_Table = %.cache%, &
Replicate_Ignore_Table is empty.

What's the difference between the two?

The Replicate_Wild_Ignore_Table option allows you to specify table names using wildcard characters. The Replicate_Ignore_Table option does not (only complete table names may be used).

See the MySQL manual for more details.


Insert statement performance in SQL Server I have the below Insert statement query as part of an stored procedure that is taking some time to be complete (20-30) minutes, when normally in another server with same hardware specifications takes 1-2 minutes

Query:

INSERT INTO [DBO].[cond]
    (
       [LAST_NAME]
     , [FIRST_NAME]
     , [MIDDLE_NAME]
     , [BUSINESS_NAME]
     , [TIN]
     , [date_of_birth]
     , [OCCUPATION]
     , [CTR_TRANSACTION_FK]
     , [CREATED]
     , [CREATED_BY]
     , [LAST_MODIFIED]
     , [LAST_MODIFIED_BY]
     , [CTR_FK]
     , [CUSTOMER_FK]
     , [cust_type_FK]
     , [gender]
     , [naics_code]
     , [name_suffix]
     , [alternate_name]
     , [extra_type]
     , [ID_ISSUING_COUNTRY_FK]
     , [id_issuing_state]
     , [ID_TYPE]
     , [ID_NUMBER]
     , [FLEX_1]
     , [FLEX_2]
     , [FLEX_3]
     , [FLEX_4]
     , [ID_TYPE_OTHER_DESCRIPTION]
     , [person_involved_source]
     , [person_involved_date]
     , [DEPOSITOR]
    )
    OUTPUT
   [INSERTED].[ID]
     , [INSERTED].[customer_FK]
     , [INSERTED].[CTR_TRANSACTION_FK]
     , [INSERTED].[CASHTX_CUSTOMER_FK]
       INTO [#COND_INSERTED]
    SELECT
   [cust].[last_name]
     , [cust].[first_name]
     , [cust].[middle_name]
     , [cust].[COMPANY_NAME]
     , [foi].[identification_number]
     , [cust].[birth_incorporation_date]
     , [cust].[occupation]
     , NTRX.[CTR_TRX_ID]
     , @FilingDateTime
     , @Filer
     , @FilingDateTime
     , @Filer
     , NTRX.[CTR_ID]
     , [cust].[id]
     , [cust].[customer_type_FK]
     , [cust].[GENDER]
     , [cust].[NAICS_CODE]
     , [cust].[SUFFIX]
     , NULL
     , CASE
       WHEN [cashmap].[cb_type] = 'C'
       THEN 'Conductor'
       WHEN [cashmap].[cb_type] = 'O'
       THEN 'Common Carrier'
       ELSE ''
   END
     , [ctry1].[id]
     , [cust].[id_issuing_state]
     , [cust].[id_type]
     , [cust].[id_number]
     , [cashmap].[FLEX_1]
     , [cashmap].[FLEX_2]
     , [cashmap].[FLEX_3]
     , [cashmap].[FLEX_4]
     , [cust].[ID_TYPE_OTHER_DESCRIPTION]
     , 1
     , [cust].[LAST_MODIFIED]
     , [cashmap].[DEPOSITOR]
    FROM  [DBO].[cashcust] AS [cashmap]
      INNER JOIN [DBO].cust AS [cust] ON [cashmap].[CUSTOMER_FK] = [cust].[id]
      LEFT JOIN [DBO].custf AS [foi] ON [foi].[customer_FK] = [cust].[id]
      LEFT JOIN [DBO].country AS [ctry1] ON [cust].[id_issuing_country] = [ctry1].[fincen_code]
      INNER JOIN [DBO].trans AS [TXN] ON [txn].[UNIQUE_TRANS_ID] = [cashmap].[transaction_id]
               AND txn.INSTITUTION_FK=cashmap.INSTITUTION_FK
      LEFT JOIN [DBO].custacc AS [custmap] ON [txn].[account_FK] = [custmap].[account_FK]
                AND [custmap].[mapping_type] = 1
                AND [custmap].[IS_ACCOUNT_HOLDER] = 1
                AND [cashmap].[customer_FK] = [custmap].[customer_FK]
      JOIN [#TMP_INSERTED] AS NTRX ON NTRX.TRX_ID=[txn].[id]
    WHERE  [cashmap].[conductor_bene] = 'C'
      AND @SourceConBenFromTellerFile = 0
      AND ISNULL([cashmap].[has_error], 0) = 0;

Execution Plan:
I noticed that the heaviest operator is the Clustered Index Insert.

Note: First set of inserted row was: 4810
      Second set of inserted row was: 4886
      Third set of inserted row was: 62

So not sure why is taking that time to insert them.

Important Server Info:
MaxDOP = 4.
Cost Treshold of Parallelism = 5

Top Waittypes:

Page life expectancy  =                                                                                60

Also I noticed some implicit conversion warnings in some of the insert statements:

Full Execution Plan:
Part 1:

Part 2:

Part 3:

Memory Clercks for Blocking:

It's hard to be sure what's happening without a sample of data, but I can throw a few ideas out there:

Have you isolated whether the problem is with the INSERT or the SELECT?  It could be that the query is running slower on the one machine, and it's not the insert per se that's the problem.
Do the two servers have the same schema?  I once spent a week tracking a poor performance issue until discovering that I had temporarily deleted an index and forgot to restore it (oops).  There are several good tools for automatically comparing schemas.  For example, if they have the same fields in their clustering index, but in a different order, that could make a big difference.
Do the two tables have similar record counts, fragmentation, and fill factors?  Those shouldn't make a huge difference, but they will affect insert speed.
To avoid the implicit insert, consider writing the result of your SELECT into a temp table, with data types and clustering fields like the target.  Then INSERT from there into your target.  This will let you separate the cost of the INSERT vs the SELECT, and will minimize the time the target table is locked.


SQL profiler not showing RPC calls When I am running SQL profiler to trace the sql queries being executed, the trace is showing only Audit logs, not the sql commands being executed.
Can somebody tell me how to get the sql commands also?
Thanks

In the "Events Selection" tab of the trace definition, make sure that the TextData column is checked for RPC:Completed events.

(This is unchecked in the default trace definition)


Need help with a table relationships and over-all design My design is a Netflix clone which has movies and shows just like Netflix. There is also a table called transcodes which will have information on a movie or episode video transcode.

My problem is, I don't know how the I should connect the transcodes table to the movies and episodes table. I thought about having a movie_id and episode_id foreign key in transcodes but then I was struggling with coming up with the query. I think adding a transcode_id to the movies and episodes tables could work, but I feel there is a more efficient way of handling it.

What would be the best way to connect transcodes to the movies and episodes tables?

Maybe my design is bad? If anyone sees any possible improvements, I would appreciate feedback on that as-well.

- EDIT -

I've updated my design to reflect the feedback. The only question I have is, some tables have both movie_id and show_id which means one of those will always be NULL, is that bad design? Or is that acceptable? Also, take a look at the titles_posters table. Something seems a little off about how everything is associated, seems like there will be quite a bit of nulls as-well, but maybe that is fine?

The question to ask yourself is simple: Can a single Transcode record be associated with more than one Movie or Episode?

If Yes, you need a linking table that links Movies and Transcodes and one that links Episodes and Transcodes to create a Many-To-Many relationship.

If No, you simply add transcode_id and a foreign key to the Movies and Episodes tables.

You need to apply that same logic to some of your other tables/relationships. For example, can a Movie really only be associated with a single user as per the current design? What is that relationship - the user who uploaded it, a user who watched it, a user who recommended it?

A few others:

Shouldn't poster_id on Episodes actually be on Seasons? A season may have a different poster, but surely not an individual episode?
Can users have multiple roles? If so, you need a linking table.
Can Movies or Shows have multiple Genres (Action & Thriller, Drama & Comedy for example)? Again, if so, linking table.

Check out this Wikipedia page which covers off some basics on database/ERD design. You need to identify the various entities within your data model (a Movie, for example, is an entity, as is a user) then define the relationship between each entity (Entity A can have only one Entity B associated, and Entity C can have many associated Entity D records etc) and that will help you determine and design your ERD.


Enable SQL Server FILESTREAM - Windows Based Docker Container (not linux) I have been struggling for a few days to spin up a windows based docker container running SQL Server 2017 on Windows Server Core 2016. I am using the following image as a starting point:
https://hub.docker.com/r/microsoft/mssql-server-windows-developer
I managed to successfully install the container and get it running perfectly. When I attempt to restore my database, I get an error stating that the FILESTREAM feature is not enabled. I understand that FILESTREAM is not supported on Linux containers but this is a windows based container.
I have been spending the last few days trying to enable filestream. As this is a server core OS, there is now UI. So using SQL server configuration manager is not an option. At least not locally. I tried connecting to the machine remotely using MMC to manage it but I get an error saying that the computer cannot be remotely managed due to COM+ Network Access (DCOM-In) and all rules in the Remote Event Log Management group not being enabled on the Windows Firewall. Trying to add these rules in the docker container just results in a bunch of errors. I read somewhere that the container actually using the firewall on the host machine. I tried adding the required firewall rules on both the container and the host machine with the following command:
Invoke-Command -ComputerName localhost {Set-NetFirewallRule -DisplayGroup 'Remote Event Log Management' -Enabled True -PassThru | select DisplayName, Enabled}

This only results in Access Denied errors on both machines. I tried enabling FILESTREAM on the container using the following script:
# Enable FILESTREAM

$username = "LocalOSUserAccount"
$password = ConvertTo-SecureString "LocalOSUserAccountPassword" -AsPlainText -Force
$psCred = New-Object System.Management.Automation.PSCredential -ArgumentList ($username, $password)

$instance = Get-SqlInstance -MachineName "localhost" -Credential $psCred
$wmi = Get-WmiObject -Namespace "ROOT\Microsoft\SqlServer\ComputerManagement11" -Class FilestreamSettings | where {$_.InstanceName -eq $instance}
$wmi.EnableFilestream(3, $instance)
Get-Service -Name $instance | Restart-Service

Set-ExecutionPolicy RemoteSigned
Import-Module "sqlps" -DisableNameChecking
Invoke-Sqlcmd "EXEC sp_configure filestream_access_level, 2"
Invoke-Sqlcmd "RECONFIGURE"

but this also ended in tears with a instance cannot be found error.
Out of pure frustration, I finally ended up referencing the docker file for this image and creating my own. The original file can be found here:
https://github.com/microsoft/mssql-docker/tree/master/windows/mssql-server-windows-developer
The only thing that I changed was to add the /FILESTREAMLEVEL and the /FILESTREAMSHARENAME to the command line install of SQL Server as per https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver15 in the dockerfile.
With this strategy, I managed to create the image successfully with /FILESTREAMLEVEL=1 and omitting the /FILESTREAMSHARENAME parameter (not required for /FILESTREAMLEVEL=1). When I run the image though, the container is created, but the MSSQLSERVER service fails to start and the container shuts down. I need to find a way to trace this error somehow which I will investigate. It has to be FILESTREAM causing the service start to fail.
When I use /FILESTREAMLEVEL=2 and a /FILESTREAMSHARENAME=MSSQLSERVER, the image fails to build saying that the share MSSQLSERVER could not be validated. I investigated this and it seems you have to ensure that the "Server" service is started, I don't see that service in my container. Another way to enable it is to enable "Printer and File Sharing". Attempting to do this also results in a bunch of Access Denied errors.
Am I missing something very basic? I am fairly new to docker but this is a huge amount of struggle to enable FILESTREAM? Any help would be very much appreciated.

Not sure if this is the problem but the link you gave in the question says that the image was last updated 3 years ago:

Plus it says that:
"This image is compatible with Windows Server 2016 (Core, with Desktop)...
I'm concerned about that "with Desktop" but I cannot tell if this is the problem.
I would rather suggest you to use this one by running:
docker pull mcr.microsoft.com/mssql/server:2017-latest

Let us know if it works.


Microsoft SQL Server v13 bulk add users from active directory I would like to import all users from Active Directory into Security/Users.

What I do is the following:

Go to Security -> Users
Right click Users -> New User...
User type: Windows user, click on '...' at User Name
Define object type to User and location to my Active Directory one
Click on Find Now

Now I can see all users in this specific domain. What I cannot do is select more than one user from this list (e.g. by holding shift). So basically I would have to do it user by user. Since there are many users that would take a long time. And I am also wondering that there should be a 'smarter' way to do it.

Am I missing something? Am I doing something wrong?

Thanks for the help!

Am I missing something? Am I doing something wrong?

It's unusual to add a large number of individual active directory logins to a SQL Server.  Instead add logins for Active Directory Groups to take care of most of your users.

See, eg: https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server


Options for keeping two SQL Server Compact databases in sync? Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports.  Both run SQL Server Compact databases.

Currently, we pull (and back up) the database to the back. But now it takes too long due to network latency and database size, so we are evaluating our options. One of them is to add a timestamp/rowversion column to each table. We can then just pull updated/inserted records and apply them accordingly.

Is using the timestamp to grab all new/updated records from the front to update/add them to the back a viable solution? Will the timestamps stay in sync, or will the back get new timestamps when the records are applied?

Or is there another option I am not aware of?

From the development perspective, you could try the Microsoft Sync Framework.

For further information about Microsoft Sync Framework, refer to the following links:

http://msdn.microsoft.com/en-us/library/bb726015(v=sql.105).aspx
https://stackoverflow.com/questions/581110/using-ms-sync-framework-to-synchronize-two-sql-ce-dbs


Configure Connection String to Use Target Machine Credentials I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one machine.
An example connection string is below:
Data Source=myPC;User ID=Admin;Initial Catalog=TestLocal;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;
When I create a new connection in visual studio it asks for the server name...I want the server name to be equal to whatever the server name is where my package is being run. Is this possible?
edit:
The package is being executed as a multiserver sql server agent job. My machine is the master(msx). Should I be looking at making changes to the actual ssis package in visual studio, or focusing on changes in the ssms job? My intuition points me towards editing the package but this is where I have been left scratching my head.
following this guide: https://www.sqlshack.com/parameterizing-database-connection-sql-server-integration-services/ still has the package being called by the user on the target machine, but the package is executed on my machine... headscratcher..
This screenshot below we can see that the job is started on My Machine and is called by Machine2. My thought process here is that now Machine2 should be running this job ON Machine2. You can see that I have included a parameter called ServerName and have set the value to localhost thinking that this would cause the package to be executed on the local machine of the user that called it (in this case Machine2)?..

The result of the run in the image above has the database of Machine1 being updated, when we really want Machine2 to be receiving the update.

The dtsx package runs where you install and execute it. Usually in SSISDB.
Then you can create a sql server agent job to run it.
The connection string specifies where the action (executing some sql script, or as a data source/destination in a dataflow as examples) will be executed.
You can install the package in every server you want and then refer in the connection string for example to localhost to run it against the same server.
You can also manage parameters and design a parametric connection string as you pointed out.
If you have a list of servers to contact with the connection string, you have to loop into the list and place the values in the connection string and then execute the task you want using parameterized connection string. So you will contact every server you want and execute what you need.
But there will be always only one server hat runs the solution.
If you have a job in every of your servers that point to a local SSIDB with a local dtsx to execute against a local instance you have to manage them with a local environment that define a good connection string for each one of them.


SQL Query logging in extended events deadlock xml report is too short How can I tell the sql server to log the whole query in the xml, and not truncate it?

example:

   <inputbuf>
(@0 nvarchar(20),@1 nvarchar(10),@2 decimal(38,20),@3 int,@4 datetime,@5 nvarchar(10))IF EXISTS(SELECT TOP 1 NULL FROM "MySQLDB".dbo."MyTable$Reservation Entry" WITH(UPDLOCK)  WHERE ("Item No_"=@0 AND "Location Code"=@1 AND "Quantity (Base)"&gt;@2 AND "Reservation Status"=@3 AND "Expected Receipt Date"&lt;=@4 AND "Variant Code"=@5)) SELECT "timestamp","Entry No_","Positive","Item No_","Location Code","Quantity (Base)","Reservation Status","Description","Creation Date","Transferred from Entry No_","Source Type","Source Subtype","Source ID","Source Batch Name","Source Prod_ Order Line","Source Ref_ No_","Item Ledger Entry No_","Expected Receipt Date","Shipment Date","Serial No_","Created By","Changed By","Qty_ per Unit of Measure","Quantity","Binding","Suppressed Action Msg_","Planning Flexibility","Appl_-to Item Entry","Warranty Date","Expiration Date","Qty_ to Handle (Base)","Qty_ to Invoice (Base)","Quantity Invoiced (Base)","New Serial No_","New Lot No_","Disallow Cancellation","Lot No_","Vari   </inputbuf>
  </process>

this is taken from sql server management studio 17.4, management, extended events, sessions, system_health, package0.event_file event detail xml_report value. As you can see the query shown is truncated. Is there a way to tell SQL to log all of the query without truncating it?

If there isn't a way to tell sql not truncate, what is the best way of retrieving the whole query?

Thank you

If there isn't a way to tell sql not truncate, what is the best way of
retrieving the whole query

Every deadlock graph contains sqlhandle for every process that partecipates and it can be used to retrieve sql_text:
select *
from sys.dm_exec_sql_text(0x01000c001247710230b2bfa34800000000000000000000000000000000000000000000000000000000000000)

where the value for parameter for sys.dm_exec_sql_text is the sqlhandle from deadlock graph.
This way you may get your statement if its execution plan is still in cache.
You need the VIEW SERVER STATE permission to query sys.dm_exec_sql_text.


Partition Fact Table In one of my fact table which has close to 25 million records in it and now when a cognos team try to run the report on top of that it takes lot of time, So i am thinking of partitioning the table we store the records in fact table based on daily basis and we do have have the id calaendarday dma in the fact table as data type int.So is there a way i can partition this fact table based on  id calaendarday dma?

Please help me on the above query as it is impacting the cognos reports

I have an answer in a previous question that talks about the different forms of partitioning.

Not knowing if you have Database Paritioning Feature (DPF) enabled (since it is a separate license for DB2), your biggest bang for the buck would be to have table partitioning and/or a multidimensional clustering table. MDC's you want to cluster on things with low cardinality, so don't use your date field for that. But you may be able to use other fields used in the query for it.

As for partitioning, you will first need to create your table with partitions defined. (You unfortunately can't partition a non-partitioned table). In your definition, you could define all the partitions you want. After that you can always attach new ones. Once they are defined you can move the data from the old table to the new table, or just reload your fact table (depending on your business case).

Now, here is another thought on defining partitioning. IBM does recommend putting each partition in its own tablespace (for performance and disaster recover reasons). To do this you would have to define the table multiple times each with its partition range and assign it to a tablespace. Then do ALTER TABLE ADD PARITION and attach the table to the "base table". If you are using one day at a time for data, this perhaps could get costly, so maybe you would rather wish to partition quarterly or monthly or something like that.


Check and trigger constraints Suppose a DB contains two tables A and B as given below:

 A
---------------------
 id        SERIAL
 id_b      INTEGER -- NOT NULL reference to B.id
 ts_update TIMESTAMP

 B
--------------------------
 id             SERIAL
 ts_last_update TIMESTAMP

Now let's say that we'd want Postgres to automatically update ts_last_update in B such that B.ts_last_update is always equal to MAX(A.ts_update) WHERE A.id_b=B.id whenever A receives new rows, updates to existing rows or rows are deleted.  Similarly, it would be desirable to ensure that any updates to ts_last_update in B conform to the same rule above.

I thought of introducing a trigger that is executed on insert, update and delete operations on A.  But it seems that there'd be a need to also introduce a CHECK constraint to ts_last_update in B, however this would mean that whenever the trigger in A runs, the check constraint in B would also run which means that two queries would be executed.

Is my reasoning above correct or is there a better way of enforcing this strange rule?  Is there perhaps a way of specifying some sort of exclusivity where when the trigger runs, the check constraint is ignored?

The requirements are not clear in at least three places:

What should happen if B is updated but no related row in A is updated? Should B.ts_last_update be updated with current timestamp or not?
What should happen if a row in B has 0 related rows in A (due to that it was just inserted or the related rows from A were deleted? Should B.ts_last_update hold the latest update timestamp of B/A rows or be NULL?
I can't see how a CHECK constraint would help in your case. CHECK constraints are row constraints, they can only be used for checking data from one table and each row independently of other rows, not from two tables.

If the aim is for that column to always hold the MAX(A.ts_update) WHERE A.id_b = B.id value and not to be affected by updates in table B, then you don't really need a trigger at all. You can query both tables every time you need the value or have a view:

--------------------------
CREATE TABLE B
  ( id        SERIAL PRIMARY KEY,
    -- more columns
    -- but no timestamp
  ) ;

---------------------
CREATE TABLE A
  ( id        SERIAL PRIMARY KEY,
    id_b      INTEGER NOT NULL REFERENCES B (id),
    ts_update TIMESTAMP
  ) ;

-- View: B with  ts_last_update
--------------------------
CREATE VIEW BB
    AS
    SELECT B.*,
           ( SELECT MAX(A.ts_update)
             FROM A
             WHERE A.id_b = B.id
           ) AS ts_last_update
    FROM B ;


Publishing stored proc execution in transactional replication In SQL 2008 R2, I am going to update a table which will affect 25 million rows, table is currently replicated (transactional replication). To minimize the impact on replication can I create a stored procedure, wrap the update statement inside the stored proc and add this stored procedure for the replication?

By doing this when stored proc executed it will replicate the execution of the stored procedure. My question is how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being  updated on the publisher (i.e. actually replicating 25 million update statement)?

how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being updated on the publisher (i.e. actually replicating 25 million update statement)?

SQL Server knows that when you set up article property as below :

You can explicitly set only to replicate SP defination, execution of SP or Execution in a serialized transaction of the SP while setting up Article property.

Refer here. When you select "serialized transaction of the SP", the entire execution is replicated as a single transaction there by reducing the Transactional Replication overhead i.e. With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber

Test it before hand as performing such a large update (25 million rows) will be way too much for replication to handle (depending on your replication topology - same distribution server as publisher, geographically separate publisher and subscribers, etc) and some caveats as described in BOL.

You can probably thing of another approach like --

Remove the current table from replication.
Do you data update and (To speed up things, you can truncate the table on Subscriber if the table is massive - so when you do a snapshot, replication wont have to perform delete on subscriber table)
Add the table back to replication with nosync option
Snapshot the publication, but this time only the snapshot of the table that was added will be generated.


Renaming the target table of a trigger We have a table that serves as an audit trail table for an app. Let's call it ProductAudit, and let's call the data table for this audit table Products. ProductsAudit got polluted with about 150 million rows due to a bug. There are about 2 million valid rows that need to remain.

So for cleanup, I want to:

create a new table, ProductAudit_TEMP
copy over the 2 million good rows
drop table ProductAudit
rename ProductAudit_TEMP to ProductAudit.

This will be much faster than deleting the 150 million invalid rows - even in batches.

My question is, table Products has INSERT, UPDATE, and DELETE triggers, which write into table ProductAudit. Will my plan break the triggers? I'll have all users out of the system, and even disable the triggers for safety when performing the cleanup.

EDIT:

Just to clarify, ProductAudit does not have triggers, Products does. ProductAudit is being populated by the INSERT, UPDATE, and DELETE triggers that belong to table Products.

As @AaronBertrand pointed out in his comment, objects are attached to other objects by object_id, not by object name.  Having said that, there is a way to move data from one table to another without the need to use sp_rename.

Since example code is the cleanest way to show how something works, I've created the following test-bed code to illustrate how to use ALTER TABLE ... SWITCH syntax to enable data migration.

First, we'll create an "existing" Product and ProductAudit table, with a trigger on the Product table, that inserts rows into the ProductAudit table:

USE tempdb;

IF OBJECT_ID('dbo.Product') IS NOT NULL
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
(
    ProductKey int NOT NULL
        CONSTRAINT PK_Product
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(4000) NOT NULL
    , TriggerUpdated bit NOT NULL
        CONSTRAINT df_Product_TriggerUpdated
        DEFAULT (0)
);
GO
IF OBJECT_ID('dbo.ProductAudit') IS NOT NULL
DROP TABLE dbo.ProductAudit;
CREATE TABLE dbo.ProductAudit
(
    ProductAuditKey int NOT NULL
        CONSTRAINT PK_ProductAudit
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductKey int NOT NULL
    , SomeData varchar(4000) NOT NULL
);
GO

CREATE TRIGGER dbo.ProductAuditInsertTrigger
ON dbo.Product
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ProductAudit (ProductKey, SomeData)
    SELECT i.ProductKey
        , i.SomeData
    FROM inserted i
    UPDATE dbo.Product
    SET TriggerUpdated = 1
    FROM dbo.Product p
        INNER JOIN inserted i ON p.ProductKey = i.ProductKey;
END
GO

Here, we'll insert 100,000 rows into the Product table:

;WITH src AS (
    SELECT *
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1
    CROSS APPLY src s2
    CROSS APPLY src s3
    CROSS APPLY src s4
    CROSS APPLY src s5;

SELECT ProductCount = COUNT(*)
FROM dbo.Product pa
WHERE pa.TriggerUpdated = 1;

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Results of the SELECT queries above:

ProductCount
------------
100000

ProductAuditCount
-----------------
100000

This will create a "temporary" table to hold the rows we want to retain:

IF OBJECT_ID('dbo.ProductAuditNew') IS NOT NULL
DROP TABLE dbo.ProductAuditNew;
CREATE TABLE dbo.ProductAuditNew
(
    ProductAuditKey int NOT NULL
        CONSTRAINT PK_ProductAuditNew
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductKey int NOT NULL
    , SomeData varchar(4000) NOT NULL
);

This code "migrates" the data from the existing table into the new table, retaining the triggers present on the original table, without recreating them.  The ALTER TABLE ... SWITCH syntax is a meta-data-only operation that is extremely quick, making this a great way to migrate data.  The code below sets the transaction isolation level to lock the tables involved to prevent other processes inserting or updating data in any of the involved tables.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --lock all row-ranges affected by
                                              --the migration
DECLARE @msg varchar(1000);
BEGIN TRANSACTION
BEGIN TRY
    --disable the trigger temporarily
    DISABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
    --copy the rows we want to retain from the old table to the new table
    INSERT INTO dbo.ProductAuditNew (ProductKey, SomeData)
    SELECT pa.ProductKey
        , pa.SomeData
    FROM dbo.ProductAudit pa
    WHERE pa.ProductAuditKey % 5 = 0; --retain 20% of the rows
    --truncate the "old" table
    TRUNCATE TABLE dbo.ProductAudit;
    --switch the single partition belonging to the "new" table
    --into the "old" table
    ALTER TABLE dbo.ProductAuditNew
    SWITCH TO dbo.ProductAudit;
    --remove the "new" table, which is actually empty now
    DROP TABLE dbo.ProductAuditNew;
    --re-seed the table's identity object; only necessary if an IDENTITY column is present
    DBCC CHECKIDENT('dbo.ProductAudit', RESEED);
    --re-enable the trigger
    ENABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SET @msg = ERROR_MESSAGE();
    PRINT (@msg);
    ROLLBACK TRANSACTION
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --return to default mode

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Results:

ProductAuditCount
-----------------
20000

Here, we'll insert another 10 rows into the dbo.Product table to prove the trigger still works:

;WITH src AS (
    SELECT *
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1;

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Results:

ProductAuditCount
-----------------
20010


Is a clustered index locked while a (clustered index) scan is in progress? The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.

The problem leading me to this question is that I have a view joining several other views and tables. One of these tables (let's call it table A) did not have a covering non-clustered index on columns in join conditions and a clustered index scan was issued instead.

The effect was that all other queries that also used table A AND used a clustered index scan to be made were waiting after the first query (no deadlock yet, since I have a large timeout). As soon as I created a non-clustered index on table A for the join columns, the clustered index was no longer used and queries started working fine in parallel.

Anyway, if my assumption is correct, is there a way to detect the lock? I haven't tried yet enabling any locking-related trace flags? I only used 'sp_who2' and a few other queries found around here for now.

If you have stumbled across this before, then please share your thoughts.

There are many many many more factors at play.

isolation level. locking behavior differs wildly between isolation levels. Some don't lock at all (read uncommitted, snapshot, rcsi). The default read committed transiently locks rows it reads as long as is necessary. Repeatable read and Serializable hold on to locks and end up locking everything, and many developers deploy serializable without ever realizing they do so.
scan purpose. Scans for read are compatible with each other so they don't block, no matter what they lock. If you have  scans blocking other scans they must be scans for update, which are incompatible with each other. Again, snapshot isolation level (including rcsi) do not block even when doing a scan for update.
lock granularity, based on cardinality estimates. Scans may choose row, page or rowset level granularity. A table scan will likely choose page locks.
index page locks/row locks configuration, which you so inadvertently changed. You should revert the change, since is not based on any measurement and root cause analysis. Guts feeling has no role in investigation.
lock escalation
row stability requirements when off-row LOB data is present
other

I suggets you follow the procedure described in Capturing wait stats for a single operation to capture the wait stats of the scans you observed as blocking. Se if indeed they block on locks held by the table scan operation. IF the scenario is truly as you described (read scan vs. other read operations) then there is no reason for blocking so something else will be at play. You can also give sp_whoisactive  a shot.


I can not copy database MongoDB I created a mongo cluster in ubuntu and run the copy in the sharding with a small database, everything works fine.

The problem is that when I try to copy a database 300gb, is not getting done. The host where I have the mongos instance, run the following:

db.runCommand ({copydb: 1, fromdb: "tranches" todb: "tranches" fromhost: "mongoserver: 27025"})

And the following appears:

{ "code": 13398,"ok": 0,"errmsg": "exception: cant copy to sharded DB" }

What could be the problem ?, any idea for the problem?.

The copydb command cannot be run against a database in which sharding has been enabled.

In this situation I would recommend using the mongodump and mongorestore tools to extract your collection data and to restore to a different collection / database / cluster. These tools can be run against mongos and will respect shard boundaries.


Finding The Actual Application User ID In The Transaction Logs I am trying to make any row in the database auditable and transaction logs seem to capture what changed when. With regard to who made the change, this link explains how to extract the database user ID from trace logs: How to find out who deleted some data SQL Server.

This would be OK for SMSS users since they would all have different database user IDs. But because the web application has a single database user ID in its connection string, every transaction coming from the web application will refer to the same user. I want to be able to find out the application user who made the change, not the database user.

Is there a way that I can pass this actual user ID information as some kind of meta data so that it will be logged as well for each transaction?

I want to be able to find out the application user who made the change, not the database user.

Then you're going to need to fundamentally change the way your application behaves in terms of security and auditing. You'll also need to setup some sort of auditing, either in the application or database.

  Is there a way that I can pass this actual user ID information as some kind of meta data so that it will be logged as well for each transaction?

No, the log was not meant to be a developer tool.


This query shifts the LogIn date by the number of minutes between midnight (0=00:00) and the start of the shift:

17:30 (Start) becomes 00:00
2:30 becomes 9:00.
2016-01-06 03:45:16.0000000 becomes 2016-01-05 10:15:16.0000000
...

It is used to get the date of the shift and it can then be used in a GROUP BY.

Query:

WITH s AS (
    SELECT s.UserID, e.LogIn, e.LogOut
        , [DATE] = CAST(DATEADD(minute, DATEDIFF(minute, s.[Start], 0), e.LogIn) as date)
    FROM @Shifts s
    INNER JOIN @Employees e ON s.UserID = e.UserID
)
SELECT s.UserID, [DATE]
    , FirstIN = CAST(MIN(LogIn) as time)
    , LastOut = CAST(MAX(LogOut) as time)
FROM s
GROUP BY s.UserID, [DATE];

See SQL Fiddle.

Output:

UserID  DATE        FirstIN             LastOut
2267    2016-01-04  20:52:08.0000000    03:57:16.0000000
2267    2016-01-05  18:43:50.0000000    03:45:16.0000000

Arrive early, leave late:

If someone is leaving a little late, it should work fine.

If someone arrive early, you can add some time to DATEADD such as:

[DATE] = CAST(DATEADD(minute, 90 + DATEDIFF(minute, s.[Start], 0), e.LogIn) as date)

Here it adds 90 minutes. This means that if someone arrives up to 90 minutes  early it will be counted on the current day. It will be for LogIn starting at 16:00 with your sample (17:30 - 90mins)
If an employee arrives 2h early (> 90min), it is counted as the previous day.

Working hours:

The number of hours between FirstIn and LastOut in hours is calculated like this:

DATEDIFF(hour, MIN(LogIn), MAX(LogOut))

Or like this:

DATEADD(minute, DATEDIFF(minute, MIN(LogIn), MAX(LogOut)), CAST('' as time))

Working hours output:

UserID  DATE        FirstIN             LastOut             WorkHours   WorkTime
2267    2016-01-04  20:52:08.0000000    03:57:16.0000000    7           07:05:00.0000000
2267    2016-01-05  18:43:50.0000000    03:45:16.0000000    9           09:02:00.0000000


SQL Server Join not returning records I have a query I am trying to return all users from with their join_date based on a LEFT JOIN. The query is like so:

SELECT users.name,
MIN(history.time_out) as join_date
FROM flexlm_user users
LEFT JOIN flexlm_history history ON users.id=history.user_id
GROUP BY users.name
ORDER BY 2

However, the results are returning Null for join_date where history.time_in is Null. The history table has no Nulls for the time_out field. Any help is much appreciated.

current results are like this

name    join_date
user1   NULL
user2   NULL
user3   NULL
user4   48:00.0
user5   31:00.0
user6   59:00.0
user7   15:00.0

I want results like this:

name    join_date
user1   13:00.0
user2   5:00.0
user3   7:00.0
user4   4:00.0
user5   31:00.0
user6   59:00.0
user7   15:00.0

History Table:

CREATE TABLE [dbo].[flexlm_history](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [server_id] [int] NOT NULL,
    [user_id] [int] NOT NULL,
    [product_id] [int] NOT NULL,
    [update_id] [int] NOT NULL,
    [time_out] [datetime] NOT NULL,
    [time_in] [datetime] NULL
)

Sample Data:

+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+
| id  | server_id | user_id | product_id | update_id |        time_out         |         time_in         |
+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+
| 615 |         2 |       9 |          8 |      4670 | 2017-07-28 14:10:00.000 | 2017-07-28 14:43:00.000 |
| 616 |         2 |       9 |         10 |      4670 | 2017-07-28 14:10:00.000 | 2017-07-28 14:43:00.000 |
| 617 |         2 |      24 |          9 |      4672 | 2017-07-28 14:13:00.000 | NULL                    |
| 618 |         2 |       2 |          9 |      4672 | 2017-07-28 14:14:00.000 | NULL                    |
| 619 |         2 |      76 |          4 |      4676 | 2017-07-28 14:42:00.000 | 2017-07-28 16:53:00.000 |
| 620 |         3 |      18 |         20 |      4685 | 2017-07-28 15:22:00.000 | 2017-07-28 15:53:00.000 |
| 621 |         2 |      21 |          4 |      4686 | 2017-07-28 15:29:00.000 | 2017-07-28 16:33:00.000 |
| 622 |         2 |      24 |          4 |      4690 | 2017-07-28 15:48:00.000 | 2017-07-28 16:33:00.000 |
| 623 |         2 |       9 |          2 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 624 |         2 |       9 |          8 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 625 |         2 |       9 |         10 |      4692 | 2017-07-28 15:53:00.000 | 2017-07-28 16:43:00.000 |
| 626 |         2 |      43 |          2 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 627 |         2 |      43 |          5 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 628 |         2 |      43 |          6 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 629 |         2 |      43 |          8 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 630 |         2 |      43 |          9 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 631 |         2 |      43 |         10 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
| 632 |         2 |      43 |         11 |      4694 | 2017-07-28 16:10:00.000 | NULL                    |
+-----+-----------+---------+------------+-----------+-------------------------+-------------------------+

User Table:

CREATE TABLE [dbo].[flexlm_user](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](25) NOT NULL,
)

Sample Data:

+----+-------+
| id | name  |
+----+-------+
|  9 | user1 |
| 24 | user2 |
| 76 | user3 |
|  2 | user4 |
| 18 | user5 |
| 21 | user6 |
| 24 | user7 |
|  9 | user8 |
| 43 | user9 |
+----+-------+

how about going the OUTER APPLY route. You can modify the inner query to handle nulls differently, i've just disregarded them but if you want to handle them you could use ISNULL or CASE in the future.

SELECT users.name,
       H.join_date
FROM flexlm_user users
    OUTER APPLY
(
    SELECT MIN(history.time_out) AS join_date
    FROM flexlm_history history
    WHERE history.user_id = users.id
          AND history.time_out IS NOT NULL
) H
ORDER BY H.join_date;


Insert row wise different images in SSRS I am working on SSRS in witch i have data set with [First, Last, Email and Image]
image contains name of image and when ever i use this image i have to put www.something.com/images/ at start of this field

Now I am using this in SSRS something like this

Is it possible that what i am doing if yes some one kindly help

I am using following expression in first column

="www.something.com/images/"+Fields!Image_Path.Value

FYP: I am using SQL server 2012 and Visual Studio 2013

What I am trying to ask: I have image name in database and i want to insert that image in report something like here is my name Taimur and i want to insert image of taimur in first field the problem is, i have only image name in database and when ever i have to use that image i have to put www.something.com/images before image name because i have physically existed images on that path
Hopefully you understand my question

Use http:// and join your strings using & not +


Database syncronization I am working on a student project and I need some guidance on what solution I can use.

The goal of the project is to create a dummy web page that would send
dummy data to 2 databases(or read from one of them).
If at any point one of them is unavailable it should keep sending
data to the one that still works.
When the unavailable database returns to function it should check
with the one that was still working, synchronize all changes made to
data and continue on. It should work both ways.
Both DB1 and DB2 can shutdown but when they return to function they
should synchronize with the one that was working.

Professor recommended MS SQL server 2019 Developer, so far I managed to make database replication so any change made to DB1 is replicated on DB2, but I can only send data to DB1 because it doesn't work the other way. I followed this How to Configure MS SQL Server Replication: Complete Walkthrough tutorial. Basically it is transactional replication between 2 SQL Server dbs that run on 2 VMs.
I can't figure out the way to do this so I would appreciate if anyone has any suggestions or tutorials that could help me. One thing that I found and I think would work is MS SQL peer to peer replication, but it is only available for Enterprise edition.

Check out Basic Availability Groups. These are available in Standard Edition (and Developer) and offer exactly the functionality you're after.
You have two replicas, a Primary and Secondary. The client application connects to a shared listener name, and read and Write workload between the client application and database occurs on the primary replica through this listener. Data changes are synchronised to the secondary replica behind the scenes.
When the primary replica fails, if you have configured automatic failover then the availability group moves to the secondary replica, and the read/write workload begins working off the secondary replica. As this still occurs through the listener name, no application change is required.
After failover, the secondary replica becomes the new primary and once the old primary is online again, it becomes the new secondary. Data synchronisation resumes, but in the other direction to ensure that you can failover back to the original primary at some point if needed.
Check out this Docs article for an overview of Availability Groups. Note that Basic AGs function just like normal AGs but for Standard Edition only, so they have some limitations.


Why PostgreSQL remove or add WAL files? I have experiences of administration with MySQL and Oracle Database.
I started to work with PostgreSQL recently and cannot find the reason why PostgreSQL managing WAL files by removing or adding
according to setting with wal_keep_segments, max_wal_size and min_wal_size.

MySQL just keeps on generaing new transaction files and Oracle rotates the trancation files with REDO log files and also archiving.
I don't know why PostgreSQL managing WAL by changing the number of WAL files. Why not just keep the fixed number of WAL files?

Postgres temporarily adds WAL segments beyond max_wal_size to absorb peak transaction loads without forcing a checkpoint -- unlike Oracle.

It removes unneeded WAL seqments to avoid using up the log file system unnecessarily.

There's always a trade-off between performance and resource (in this case log disk space) utilization, and different DBMSes use different approaches to addressing this issue.  In this case PostgreSQL lets you find the balance that works best for you by allowing you to play with the values of wal_keep_segments, max_wal_size and min_wal_size.


Increasing max server memory to deal with System.OutOfMemoryException on SQL Server I am not very experienced so please bear with me.

I have been attempting to import a 2500 MB and 3800 MB CSV into SQL Server 2016. Unfortunately I keep getting the System.OutOfMemoryException error. My computer has 8.00 GB RAM so I figured I would just increase the max server memory from the default up to 4000 MB. For some reason though each time I try to change the max server memory to make it higher it changes back to default the moment I press OK. How do I fix this problem?

I am using BULK INSERT to import the data. The BATCHSIZE, KILOBYTES_PER_BATCH, and ROW_PER_BATCH arguments are not set.

UPDATE

I have decided to split up my CSV document so that I can import less of it at a time. Unfortunately by the time I try to import the last piece of my CSV the error shows up again.

UPDATE

I have managed to substantially increase the max server memory to 4 GB. However this has not fixed the problem.

The System.OutOfMemoryException error that you are seeing means there was a problem with SQL Server Management Studio, not with the amount of memory on the server itself. One easy way to trigger this error is to simply run a SELECT query on a large table. If the table is too large then the results grid will take up too much memory and you'll see the error.

It's not clear to me why you would experience this error when importing data, but raising max server memory is not the right fix. I recommend trying BULK INSERT after setting the BATCHSIZE, KILOBYTES_PER_BATCH, or ROW_PER_BATCH arguments. That will limit the amount of data sent in a single transaction. You could also try importing the data through bcp. You don't even need SSMS open for that.


DBA Tasks ( SQL Server 2008 ) - Recommendation - Maintenance plan I'm a JR DBA on a big company. I was hired 6 months ago, and I'm learning things really fast (I'm the only DBA here).

The old DBA left servers and SQL Server instances for me, to take care of it.

It's a really awesome structure (cluster and etc).

But, he was using scripts and a lot of programming to make backups, cleanup tasks, and all other DBA tasks. Everything is good. I am fixing some things here and there but everything is really good.

I'm learning SQL Server all by myself, and I was reading about MAINTENANCE PLAN.

It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA).

Thanks.

(For complement, he uses a script to put database names on a table, and then he uses scripts to seek that table and use it on jobs. I can do this with a simple maintenance plan. But he was a Senior DBA. he told me that he likes it, and it's not a problem to use maintenance plan. I just want some opinions)

It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA).

Maintenance plans are not bad, but when your environment grows, the limited flexiblity and functionality that maintenance plans provide wont be sufficient.

For e.g Maintenance plans suffer from below drawbacks :

There is no CHECKSUM option when taking backup of databases or there is no option of verifying backups using CHECKSUM.
There is not much logic you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes... and many more :-)

I highly recommend to use Ola's backup solution (and Index maintenance  solution as well).


How to select/count rows where the same value exists in a column but not in other column all at the same table I have this table structure and data:

CREATE TABLE IF NOT EXISTS `default_relations_users` (
  `id_user_rq` int(11) NOT NULL,
  `id_user_ap` int(11) NOT NULL,
  UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 2),
(2, 3);

I need to get only the rows where the value is on the left but not on the right, meaning for example: 1,2 and 2,1 will not be selected but 1,3 and 1,4 yes. I try this query:

SELECT * FROM default_relations_users
WHERE default_relations_users.id_user_rq = 1
 AND NOT EXISTS (SELECT id_user_ap FROM default_relations_users WHERE id_user_ap = 1)

Maybe I don't understand what Subqueries mean or I'm using in the wrong way. So any help?

SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON  A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;

I loaded you sample data (I added an additional index)

mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
    ->   `id_user_rq` int(11) NOT NULL,
    ->   `id_user_ap` int(11) NOT NULL,
    ->   UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
    ->   UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
    -> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          2 |
|          2 |          3 |
+------------+------------+
6 rows in set (0.00 sec)

mysql>

Here is the result of my answer

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
|          2 |          3 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL
    -> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.01 sec)

mysql>


Implementing a query, two types of strategies Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id.

For instance

CREATE TABLE groups (
          gid int,
          iid int,
          primary key(gid,iid)
);
CREATE TABLE items (
          iid int primary key,
          label char(5)
);

and let put some data in these, to help visualize the coming problem

insert into items values (1,"pen"),(2,"gum"),(3,"cat"),(4,"dog"),
                         (5,"hug"),(6,"art"),(7,"fun");

insert into groups values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,1),
                         (3,4),(4,2),(4,3),(4,5),(4,6),(4,7);

Retrieving items of multiple groups (e.g. groups 1,2,3,4) uses a DISTINCT to remove duplicates

(1) SELECT DISTINCT i.iid,i.label FROM groups g JOIN items i ON g.iid=i.iid
    WHERE g.gid in (1,2,3,4) ORDER BY i.iid;

But since the tables will eventually contain more data, would that query using a subquery be more efficient

(2) SELECT i.iid,i.label FROM items i
    JOIN (SELECT DISTINCT iid FROM groups WHERE gid IN (1,2,3,4)) AS s
    ON i.iid=s.iid ORDER BY i.iid;

since distinct applies only to the items ids, despite the overhead of using a subquery?

Or this one suggested by @hypercube (see comments):

(3) SELECT i.iid,i.label FROM items AS i
    WHERE EXISTS
       (SELECT * FROM groups AS g WHERE g.gid IN (1,2,3,4) AND i.iid=g.iid)
    ORDER BY i.iid

For information, eventually

the items table will have between 50~100k rows
a group will likely have 5~10k items
groups ids to be selected in a single query are in the 5~20 range
probability that an item is part of 2 selected groups: 50%
probability that an item is part of 3 selected groups: 30%
probability that an item is part of 4 selected groups: 10%

(1) is simple and straightforward.  It may be the most efficient.

(2) is a common speed-up.  But it shines when the non-indexed part (just i.label in your case) is bulky and/or the key into the table (i.id) is not the PRIMARY KEY.  So, if your example is watered down from the real code, (2) may outperform (1).

(3) is unlikely to be efficient, since it needs to reach into items 50~100K times.

Side issues:

Don't use CHAR unless the column really is fixed length; instead, use VARCHAR.

I assume you are using InnoDB.  (MyISAM, because of different handling of PRIMARY KEY, will be less efficient.)

If groups is a many:many mapping table, see my tips.  It will probably help (3) some.


mysql workbench - Couldn't find option to remove DROP TABLE IF EXIST Recently I've been looking into export options to find an option to remove:

DROP TABLE IF EXISTS and replace it with CREATE TABLE IF NOT EXISTS.

I've seen on other MySQL clients like HeidiSQL or sequelPro (for MAC) where you can tick a option to include drop table or not, but couldn't find any related thing in the MySQL Workbench.

It's very dangerous as usually when exporting and importing to exchange data with other tables, it simply DROPS tables when they exist rather then just updating them.

I've did research about the issue, and all I'm getting is an explanation about drop table method itself.

I've been using MySQL Workbench for quite sometime, and now I'm really thinking to switch to another MySQL client because of this.

Im using MySQL Workbench Community edition version 8.0.12.

Thanks in advance.

This is not a limitation of MySQL Workbench, but one of mysqldump (which is used by Workbench to create the data export). See also Create table if not exists from mysqldump.


Are these two statements the same performance wise? Setup

I have a combination index in table SubmissionStatus as show below.  And running a simpl query 2 different ways.

 KEY `Combo_Status_Type_BidID` (`fk_ActivityStatus`,`ActivityType`,`fk_BidIDAssigned`),

Query 1

SELECT *
FROM SubmissionStatus SS
WHERE fk_ActivityStatus IN (5,12)
AND ActivityType = 61

Query 2

SELECT *
FROM SubmissionStatus SS
WHERE ActivityType = 61
AND fk_ActivityStatus IN (5,12)

My understanding

From my understanding the index I have in place here should benefit Query 1 because it has fk_ActivityStatus first, and ActivityType second, just as the Where/And conditions are listed in the statement.

Question

But, Is query 2 also getting to take full advantage of this index?  Or does the fact that it has ActivityType listed first prevent it from being able to use the combo Index?  (Making it having to do a full table scan, or look for an index that has ActivityType as the first.)

P.S.

The data table are currently to small to be able to get much reliable feedback from the EXPLAIN, and execution plan.

According to the official MySQL Documentation (How MySQL Optimizes WHERE Clauses) MySQL will optimize the WHERE clause as follows:

Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

(Please read the full documentation)

There is a small disclaimer at the top of the article which states:

Note:
Because work on the MySQL optimizer is ongoing, not all of the optimizations that MySQL performs are documented here.

Based on the quoted article MySQL should run both statements using the same execution plan and the supplied index.