Tuesday 27 September 2022

SQL "select where not in subquery" returns no results

 SQL "select where not in subquery" returns no results

 

These articles in my blog describe the differences between the methods in more detail:


There are three ways to do such a query:

  • LEFT JOIN / IS NULL:

    SELECT  *
    FROM    common
    LEFT JOIN
            table1 t1
    ON      t1.common_id = common.common_id
    WHERE   t1.common_id IS NULL
    
  • NOT EXISTS:

    SELECT  *
    FROM    common
    WHERE   NOT EXISTS
            (
            SELECT  NULL
            FROM    table1 t1
            WHERE   t1.common_id = common.common_id
            )
    
  • NOT IN:

    SELECT  *
    FROM    common
    WHERE   common_id NOT IN
            (
            SELECT  common_id
            FROM    table1 t1
            )
    

When table1.common_id is not nullable, all these queries are semantically the same.

When it is nullable, NOT IN is different, since IN (and, therefore, NOT IN) return NULL when a value does not match anything in a list containing a NULL.

This may be confusing but may become more obvious if we recall the alternate syntax for this:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

The result of this condition is a boolean product of all comparisons within the list. Of course, a single NULL value yields the NULL result which renders the whole result NULL too.

We never cannot say definitely that common_id is not equal to anything from this list, since at least one of the values is NULL.

Suppose we have these data:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL and NOT EXISTS will return 3, NOT IN will return nothing (since it will always evaluate to either FALSE or NULL).

In MySQL, in case on non-nullable column, LEFT JOIN / IS NULL and NOT IN are a little bit (several percent) more efficient than NOT EXISTS. If the column is nullable, NOT EXISTS is the most efficient (again, not much).

In Oracle, all three queries yield same plans (an ANTI JOIN).

In SQL Server, NOT IN / NOT EXISTS are more efficient, since LEFT JOIN / IS NULL cannot be optimized to an ANTI JOIN by its optimizer.

In PostgreSQL, LEFT JOIN / IS NULL and NOT EXISTS are more efficient than NOT IN, sine they are optimized to an Anti Join, while NOT IN uses hashed subplan (or even a plain subplan if the subquery is too large to hash)

 

concat the output of the subquery?

concat the output of the subquery?

select group_concat(id) as video_list
from videos 
where duration=0

 

Export array (table) into csv-file in Powershell

Export array (table) into csv-file in Powershell

 First of all, we need to define the array:

1
$export_array = @();

Then, fill in the data you need:

1
2
3
4
$export_array += ,@(""); # just an empty string
$export_array += ,@($a1, $b1, $c1);
$export_array += ,@($a2, $b2, $c2);
$export_array += ,@($a3, $b3, $c3);

Compose csv file name and path to it:

1
2
3
$datetime = Get-Date -Format "yyyy.MM.dd_HH-mm-ss";
$file_name = "audit_result_" + $datetime + ".csv";
$file_path = "./" + $file_name;

Actual export of array into csv-file:

1
2
3
4
5
6
7
8
9
foreach($item1 in $export_array) 
  $csv_string = "";
  foreach($item in $item1)
  {
    $csv_string = $csv_string + $item + ";";
  }
  Add-Content $file_path $csv_string;
}

In powershell how to output an ArrayList to CSV

 In powershell how to output an ArrayList to CSV

 

$CSVArrayList = new-Object System.Collections.ArrayList

[void]$CSVArrayList.Add(@('1','2','3'))
[void]$CSVArrayList.Add(@('4','5','6'))

Set-Content "./Output.csv" -Value $null

Foreach ($arr in $CSVArrayList) {
      $arr -join ',' | Add-Content "./Output.csv"
}

 

The STRING_SPLIT function in SQL Server

 The STRING_SPLIT function in SQL Server

 

Syntax:

The syntax is very simple as this table valued built-in function takes only two parameters. First one is a string and the second one is a single character.

STRING_SPLIT (string, separator)

The following sample shows simplest usage of this function.

The following SELECT query will return an error because of the database compatibility level.

The reason for this error is that we decreased the database compatibility level under the 130 and SQL Server returns an error. Keep in mind, that if you are planning to use this function in your customer environment you have to be sure about their database compatibility level.

STRING_SPLIT and WHERE clause:

Through the WHERE clause, we can filter the result set of the STRING_SPLIT function. In the following select statement, the WHERE clause will filter the result set of the function and will only return the row or rows which start with “le”

Also, we can use the function in this form:

Now, we will look at the execution plan with help of ApexSQL Plan. We can see the Table valued function operator in the execution plan.

When we hover over the table-valued function operator in the execution plan, we can find out all the details about this operator. Under the object label, the STRING_SPLIT function can be seen. These all details tell us that this function is a table-valued function.

STRING_SPLIT and ORDER BY

Another requirement which we need in the SELECT statements is sorting functionality. We can sort the output of this function which looks like the other T-SQL statements.

Note: When I reviewed some customer feedback about SQL Server, I came across a suggestion about the STRING_SPLIT function which is “The new string splitter function in SQL Server 2016 is a good addition but it needs an extra column, a ListOrder column which denotes the order of the split values.” In my thought, this feature can be very useful for this function and I voted for this suggestion.

STRING_SPLIT and JOIN:

We can combine the function result set to the other table with the JOIN clause.

Also, we can use CROSS APPLY function to combine the STRING_SPLIT function result set with other tables. CROSS APPLY function provides us to join table value function output to other tables.

In the following sample, we will create two tables and first table (#Countries) stores name and the continent of countries and second table (#CityList) stores city of countries table but the crucial point is #CityList table stores the city names as a string array which is separated by a comma. We will join this to the table over country columns and use the CROSS APPLY function to transform city array into a column. The below image can illustrate what will we do.

More details about STRING_SPLIT

After all the basic usage methodology of this function; we will delve into more detail. In the previous examples we always used a comma (,) as a separator for the function, however, we may need to use other symbols as a separator. The STRING_SPLIT function allows us to use other symbols as a separator, but it has one limitation about this usage. According to the MSDN; separator is a single data type and this parameter data types can be nvarchar (1), char (1), and varchar (1). Now, we will make a sample about it. The following SELECT statement will run without error. As well as we will use at (@) instead of a comma (,).

However, the following SELECT statement will return an error because of the data type declaration.

“Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’”. The definition of error is very clear and it indicates a problem that is related to the data type of separator. After this sample, a question can appear in your mind. Can we assign NULL value to separator? We will test and learn.

We cannot assign NULL value to separator as a value.

In addition, when we use this function for numerical values, the result set will be in string data types. When we execute the following query, we can see all details and result in set table data type.

Now we will analyze the following query execution plan with ApexSQL Plan.

In the select operator, you are seeing a warning sign and now find out the details about this warning.

The reason for this warning is that we tried to join integer data type to varchar data type, so this type of usage causes implicit conversion. Implicit conversions affect the performance query.

MySQL Query GROUP BY day / month / year

 MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

 

How to edit pdf metadata from command line?

How to edit pdf metadata from command line?

Give exiftool a try; it is available from the package libimage-exiftool-perl in the repositories.

As an example, If you have a pdf file called drawing.pdf and you want to update its metadata, use the utility, exiftool, in this way:

exiftool -Title="This is the Title" -Author="Happy Man" -Subject="PDF Metadata" drawing.pdf

For some reason the Subject entered ends up in the keywords field of the metadata in the pdf file. not a problem in some cases, even desirable, however, this may be problematic: evince and the nautilus metadata previewer do not show this, but Adobe Acrobat viewer and PDF-XChange viewer do.

The program will create a backup of the original file if you do not use the -overwrite_original switch. This means a duplicate will exist in the folder where the updated pdf is. From the example above, a file named drawing.pdf_original will be created.

Use the overwrite switch at your own risk. My suggestion is not to use it and script something to move this file to a better location just in case.

 

https://exiftool.org/gui/

How to Get Data in Descending order using group by in mysql

How to Get Data in Descending order using group by in mysql


SELECT player_messages.*

FROM     player_messages
WHERE    (receiver_user_id, sender_user_id, insertdate) IN (
           SELECT   receiver_user_id, sender_user_id, MAX(insertdate)
           FROM     player_messages
           WHERE    receiver_user_id='33'
           GROUP BY receiver_user_id, sender_user_id)
ORDER BY player_messages.insertdate DESC

 

Format date and time in a Windows batch script

 Format date and time in a Windows batch script

 

set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
echo hour=%hour%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
echo min=%min%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
echo secs=%secs%

set year=%date:~-4%
echo year=%year%

:: On WIN2008R2 e.g. I needed to make your 'set month=%date:~3,2%' like below ::otherwise 00 appears for MONTH

set month=%date:~4,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
echo month=%month%
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
echo day=%day%

set datetimef=%year%%month%%day%_%hour%%min%%secs%

echo datetimef=%datetimef%

Powershell Start-Sleep

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/start-sleep?view=powershell-7.2&viewFallbackFrom=powershell-7.1 

Start-Sleep

Suspends the activity in a script or session for the specified period of time.

Syntax

PowerShell
Start-Sleep
     [-Seconds] <Double>
     [<CommonParameters>]
PowerShell
Start-Sleep
     -Milliseconds <Int32>
     [<CommonParameters>]

Description

The Start-Sleep cmdlet suspends the activity in a script or session for the specified period of time. You can use it for many tasks, such as waiting for an operation to complete or pausing before repeating an operation.

Examples

Example 1: Pause execution for 1.5 seconds

In this example, the execution of commands pauses for one and one-half seconds.

PowerShell
Start-Sleep -Seconds 1.5

Example 2: Pause execution at the command line

This example shows that execution is paused for 5 seconds when run from the command line.

PowerShell
PS> Get-Date; Start-Sleep -Seconds 5; Get-Date

Friday, May 13, 2022 9:38:15 AM
Friday, May 13, 2022 9:38:20 AM

PowerShell cannot execute the second Get-Date command until the sleep timer expires.

Parameters

-Milliseconds

Specifies how long the resource sleeps in milliseconds. The parameter can be abbreviated as m.

Type:Int32
Aliases:ms
Position:Named
Default value:None
Accept pipeline input:True
Accept wildcard characters:False

-Seconds

Specifies how long the resource sleeps in seconds. You can omit the parameter name or you can abbreviate it as s. Beginning in PowerShell 6.2.0, this parameter now accepts fractional values.

Type:Double
Position:0
Default value:None
Accept pipeline input:True
Accept wildcard characters:False

Inputs

Int32

You can pipe the number of seconds to Start-Sleep.

Outputs

None

This cmdlet does not return any output.

Notes

  • You can also refer to Start-Sleep by its built-in alias, sleep. For more information, see about_Aliases.
  • Ctrl+C breaks out of Start-Sleep.
  • Ctrl+C does not break out of [Threading.Thread]::Sleep. For more information, see Thread.Sleep Method.

Friday 20 May 2022

How to migrate FRS to DFSR Replication Method

 https://itsimple.info/?p=1059

How to migrate FRS to DFSR Replication Method 

 

When join windows 2016/2019 to domain 2008/2003 you can get error like :

 

The specified domain %1 is still using the File Replication Service (FRS) to replicate the SYSVOL share. FRS is deprecated.

The server being promoted does not support FRS and cannot be promoted as a replica into the specified domain.

You MUST migrate the specified domain to use DFS Replication using the DFSRMIG command before continuing.

For more information, see https://go.microsoft.com/fwlink/?linkid=849270.

 

To do that first make sure all Domain Controllers are replicated :

Repadmin /syncall /force /APed

The start migration , Migrate to Prepared State :

Dfsrmig /setglobalstate 1

Make sure the process has finished :

Dfsrmig /getmigrationstate

You should get :

Only then continue with :

Dfsrmig /setglobalstate 2

You should get

Only then continue with :

Dfsrmig /setglobalstate 3

You should get

 

Then stop the NTFRS File Replication service :

Transferring Active Directory FSMO Roles

 https://9to5it.com/transferring-active-directory-fsmo-roles/

Transferring Active Directory FSMO Roles

Check Active Directory FSMO role placement

Before we get going, I thought it would be a good idea to document how to determine the current location of each FSMO role. To do this, the easiest way is to:

  1. Launch Command Prompt (Start >> Run >> cmd)
  2. From the Command Prompt, run the following:

netdom query fsmo
  1. This will list all of the FSMO roles and on what domain controller they are currently running on.

 

To move AD FSMO Roles using the command line, do the following:

  1. Log into the DC you want to transfer the role to as an Enterprise Administrator (if transferring the Domain Naming Master or the Schema Master roles). Otherwise you can log in as a Domain Administrator
  2. Launch Command Prompt (Start >> Run >> cmd) Note: You might need to run command prompt in admin mode depending on the Windows version you are running
  3. From the command prompt, type ntdsutil and press Enter
  4. This will enter the ntdsutil command set
  5. Type roles and then press Enter
  6. Type connections and then press Enter
  7. Type connect to server <SERVER_NAME> where <SERVER_NAME> is the name of the domain controller you want to transfer the role(s) to. See example below:

connect to server WP-DC-V02.testlab.com
  1. This will connect you to the server you want the role(s) to be transferred to
  2. Type q and press Enter
  3. Type transfer <ROLE_NAME> where <ROLE_NAME> is the name of the FSMO role you want to transfer. Below is an example of how to transfer each role:

transfer infrastructure master
transfer naming master
transfer PDC
transfer RID master
transfer schema master
  1. Type q and press Enter to quit. Repeat until you have exited the ntdsutil command set

Here is the full set of commands you would type to transfer the PDC Emulator role to the WP-DC-V02 domain controller (Note: At the end of each line you would press Enter):


ntdsutil
roles
connections
connect to server WP-DC-V02.testlab.com
q
transfer PDC

Active Directory FSMO Roles

 https://9to5it.com/active-directory-fsmo-roles/

Active Directory FSMO Roles

 

First off, a quick intro into how Active Directory works:

Active Directory is a multi-master operations enabled database. In other words, it accepts changes from multiple domain controllers (masters) at the same time. Because of some well developed algorithms conflicts are usually avoided, however there are some instances in where multi-master operations simply wouldn’t work properly and hence there are a few “single-master operations” (FSMO) roles.

FSMO = Flexible Single Master Operations.

The Active Directory FSMO roles are a number of roles or tasks which are required to be run in a single-master operations mode within an Active Directory environment (forest & domain). These tasks run on a specific domain controller, however they can be split so that certain roles run on different DCs (and this is best practise).

There are 5 different types of FSMO roles and each of them are explained below. Some of them are required per domain and some are required per forest.

Forest Based Roles

There are 2 roles which are required per Microsoft Windows Forest. These are:

Schema Master

As the name suggest, this role is used to replicate the schema between all domain controllers within the forest. Because of this, this role is found within the root domain of the forest only – and therefore there is only one per forest.

Domain Naming Master

This role also resides within the root domain of the forest (and there can only be one of them). Its job is to process all changes to the directory namespace. In other words the addition or removal of child domains within the forest. It also takes care of any additions or removals of cross references to domains within other forests (e.g. domain or forest trusts).

Domain Based Roles

There are 3 roles which are required within each domain. These are:

Relative ID Master

This is a really important role, as it manages the Relative ID (RID) pool for the domain. A RID is used every time a security principal object is created within the Active Directory domain (e.g. a user, group, computer, etc). Each security principal object within the domain has its own unique Security Identifier (SID), which is made of the domain’s SID + a unique RID from the domain controller’s assigned pool.

When a domain controller’s unused RID pool is running low, it contacts the Relative ID Master to get some more. The Relative ID Master then allocates more to the domain controller from the domain’s unallocated RID pool – which is manage and maintained by the Relative ID Master. Therefore there can only be one of these per domain.

Infrastructure Master

Used to handle all cross domain object references (i.e. when an object in Domain A is referenced by another object in Domain B). A common example of this would be if you add a user in Domain A to a security group within Domain B. There can only be one of these per domain.

When the AD Recycle Bin feature is not enabled, the Infrastructure FSMO role owner is the DC responsible for updating a cross-domain object reference in the event that the referenced object is moved, renamed, or deleted. In this case, the Infrastructure Master role should be held by a domain controller that is not a Global Catalog server. If the Infrastructure Master runs on a Global Catalog server, it will not update object information, because it does not contain any references to objects that it does not hold. This is because a Global Catalog server holds a partial replica of every object in the forest.

When the AD Recycle Bin feature is enabled, every DC is responsible for updating its cross-domain object references in the event that the referenced object is moved, renamed, or deleted. In this case, there are no tasks associated with the Infrastructure FSMO role, and it is not important which domain controller owns the Infrastructure Master role.

For more information about the Global Catalog, see this article >> The Active Directory Global Catalog Server.

PDC Emulator

The PDC Emulator is the “worker” of all the Active Directory FSMO roles, as it does the most work by far. There can only be one of these per domain. The PDC Emulator performs the following functions:

  1. Time syncronisation for all computers within the domain. This is very important as time syncronisation is required by the Kerberos authentication protocol.
  2. Password changes performed by other domain controllers in the domain are replicated preferentially to the PDC Emulator.
  3. If a logon authentication fails at a given DC in the domain due to a bad password, the DC will forward the authentication request to the PDC Emulator to validate the request against the most current password. If the PDC Emulator reports an invalid password to the DC, the DC will send back a bad password failure message to the user.
  4. Account lockouts are processed on the PDC Emulator.
  5. Editing or creation of Group Policy Objects (GPO) is always done from the GPO copy found in the PDC Emulator’s SYSVOL share, unless configured not to do so by the administrator.
  6. Emulates the functions of a Windows NT 4.0 PDC in a mixed mode environment where NT 4.0 BDCs still exist (not that this would be very common anymore).

Considerations for placement of FSMO Roles

When deciding where and how to place each of the FSMO roles, take into consideration these recommendations:

  • The Schema Master should be placed on the PDC of the forest root domain
  • The Domain Naming master should be placed on the PDC of the forest root domain
  • Place your PDC on your best hardware and in a location that contains replica Domain Controllers within the same AD site and domain
  • Place the RID master on the domain PDC
  • In a multi-domain forest, place the Infrastructure Master on a non-global catalog domain controller. If every domain controller is a global catalog, then there is no work for the Infrastructure Master so you can place it anywhere
  • In a single-domain forest, there is no work for the Infrastructure Master so therefore it can be placed on any domain controller

For more information regarding best practises for the placement of the Active Directory FSMO roles, see Microsoft KB223346.

Transferring Active Directory FSMO Roles

If you want to know how to move one or all of the AD FSMO roles between domain controllers, then check out my Transferring Active Directory FSMO Roles article.

Thursday 19 May 2022

Regex Match All Except a Specific Word, Character, or Pattern

https://regexland.com/regex-match-all-except/

Regex Match All Except a Specific Word, Character, or Pattern

 

A regular expression that matches everything except a specific pattern or word makes use of a negative lookahead. Inside the negative lookahead, various unwanted words, characters, or regex patterns can be listed, separated by an OR character.

For example, here’s an expression that will match any input that does not contain the text “ignoreThis”.

/^(?!.*ignoreThis).*/

Note that you can replace the text ignoreThis above with just about any regular expression, including:

  • A word (e.g. apple or password)
  • A set of unwanted characters in square brackets (e.g. [aeiou])
  • A regex pattern (e.g. mis{2}is{2}ip{2}i)
  • A list of regex patterns separated by the OR sybmol |(e.g. (cats?|dogs?)

Before we dive into each of these, let’s first discuss how the whole thing works:

ALSO READ: Regex Match Everything After A Specific Character

How The Main Expression Works

To begin our expression, we first start by allowing everything to be matched. This is done by the dot symbol . which matches any character, followed by a zero-or-more quantifier *. This allows us to match zero or more of any character:

/.*/

Next, we add a negative lookahead, written in the form (?!abc). The negative lookahead looks ahead into the string to see if the specified expression (abc in this case) is present. It work by only checking whether the abc expression is present, without actually matching or returning the expression.

/(?!abc).*/

Note that we place the negative lookahead at the start of the expression to ensure that it is validated before anything else is checked.

The expression above will now start from the first character in the string, checking every substring for abc, and won’t match if it finds this expression. However, upon validating the substring starting with the second character, bc, the test will fail since bc is not equal to abc. Therefore, the remainder of the string will be matched. To prevent this from happening, we need to provide a start-of-string anchor ^:

/^(?!abc).*/

This anchor forces the matched expression to start at the beginning of the string and ensures that no subsequent sub-strings can be matched.

Finally, this expression above will reject any string starting with abc but will accept any string that starts with a different character followed by abc. In other words, it will accept aabc or xabc.

To prevent this from happening, we need to provide an additional expression that will notice the characters at the start of the string, together with the unwanted expression. To do this, we need to add another dot character . and zero-or-more quantifier * that will notice zero-or-more characters in front of the unwanted expression.

/^(?!.*abc).*/

Notice that we place the .* inside the negative lookahead. If we placed it in front of the negative lookahead, the entire string will be matched before the negative lookahead is even checked.

And this completes the general expression required. We can now tweak it to suit specific use-cases.

Let’s look at some examples.

Match All Except a Specific Word

To match everything except a specific word, we simply enter the unwanted word inside the negative lookahead. The following expression will not match any string containing the word foo:

/^(?!.*foo).*/

We can list multiple unwanted words by separating them with the OR symbol |. The following expression will ignore strings that contain any of the words dollar, euro, or pound:

/^(?!.*(dollar|euro|pound)).*/

Notice that we need to enclose the list of unwanted words in round brackets () for this to work correctly. If the round brackets are ignored, the .* at the front of the negative lookahead will work together with dollar but not with euro or pound, causing sentences that contain other characters before these unwanted words to be matched.

Match All Except a Specific Character

To match everything except a specific character, simply insert the character inside the negative lookahead. This expression will ignore any string containing an a:

/^(?!.*a).*/

If the character you want to exclude is a reserved character in regex (such as ? or *) you need to include a backslash \ in front of the character to escape it, as shown:

/^(?!.*\?).*/

For a set of characters, one can include them in square brackets. Note that special characters inside square brackets don’t need to be escaped. The following expression will not match any string that contains a vowel:

/^(?!.*[aeiou]).*/

Match All Except a Specific Pattern

In addition to unwanted words or characters, one can specify a pattern that must be avoided in all matches. The pattern must be placed inside the negative lookahead:

For example, this expression will not match any string that contains three consecutive digits \d:

/^(?!.*\d{3}).*/

The following expression will not match any spelling of the word grey or gray:

/^(?!.*gr(e|a)y).*/

Match All Except a List of Patterns

Finally, patterns can be combined by enclosing them in parentheses () and separating them using the OR symbol |.

The following expression will not match any string containing three consecutive digits \d, nor a string containing a vowel:

/^(?!.*(\d{3}|[aeiou])).*/

Lookahead Support

It should be noted that some programming languages does not support lookaheads in their regex implementations and will therefore not be able to run the expressions above.