SourceField: 2024

Thursday

test smtp server with powershell

Send-MailMessage -SMTPServer smtp.domain.com -To [email protected] -From [email protected] -Subject "This is a test email" -Body "Hi, this is a test email sent via PowerShell to test the STMP relay server"

Wednesday

add new fields to csv file using powershell


$z = Import-Csv zerotrac.csv
$nums= Import-Csv allleetcode.csv
$md=@{}
#converting one csv into hashmap for quicker search
foreach($r in $nums){
    $md.add($r.frontendQuestionId,$r)
}

foreach($r in $z){
    $nr=$md[$r.id]
    if ($nr){
     $r | Add-Member -MemberType NoteProperty -Name "isPaidOnly" -Value $nr.isPaidOnly
     $r | Add-Member -MemberType NoteProperty -Name "difficulty" -Value $nr.difficulty
     $r | Add-Member -MemberType NoteProperty -Name "topicTags" -Value $nr.topicTags

}

}


$z | Export-Csv -Path "C:\temp\newzerotrac1.csv" -NoTypeInformation

Saturday

how to configure server while Running sql and BizTalk on same server

XML viewer to see Biztalk XML messages and convert them into excel or word documents:

When running SQL Server and BizTalk Server on the same machine, it’s crucial to configure both to ensure optimal performance and avoid resource contention. Here are some best practices and configuration steps to follow:

1. Hardware Considerations

Memory (RAM): Ensure you have adequate memory, ideally 16 GB or more. SQL Server and BizTalk are both memory-intensive applications, so having sufficient RAM is essential.
CPU: Multi-core processors are recommended, ideally at least 4 cores. Assign CPU affinity if necessary to allocate specific cores to each application.
Disk Storage: Use separate physical disks (or separate logical partitions if physical separation isn’t possible) for BizTalk’s database files, log files, and BizTalk’s tracking data. This separation minimizes I/O contention.

2. SQL Server Configuration

Limit SQL Server Memory Usage: To prevent SQL Server from consuming all available memory, set a maximum memory limit for SQL Server. Go to SQL Server Properties -> Memory and set a reasonable cap (e.g., if you have 16 GB, allocate 8-10 GB for SQL).
Configure TempDB: TempDB can be heavily utilized by BizTalk, so ensure it has multiple data files (one per CPU core, up to 8 files). Each should be set to the same initial size and auto-growth increment.
Optimize Disk I/O: Place data files and log files on separate drives, if possible, to reduce contention. If the machine uses SSDs, this will significantly improve performance.
Backup and Maintenance Plans: Regularly back up BizTalk databases, and configure SQL Server Agent jobs to manage and maintain indexes and statistics, especially for message box and tracking databases.
Disable Unnecessary SQL Server Features: Turn off features that BizTalk doesn’t need, such as SQL Agent jobs unrelated to BizTalk, unnecessary SQL Server features, or components.

3. BizTalk Server Configuration

Optimize Host Instances: Separate processing, tracking, and adapter communication into different hosts to avoid performance bottlenecks.
- In-process hosts: For orchestration processing and message processing.
- Receive hosts: For receiving adapter processing.
- Send hosts: For sending adapter processing.
Configure Throttling Settings: Adjust BizTalk throttling settings in the Administration Console to optimize memory and CPU usage, particularly if the server is under heavy load.
Disable Tracking (if not required): Disable tracking for specific hosts if it’s not needed to save disk space and reduce CPU usage.
Limit Maximum Message Size: Set maximum message sizes for BizTalk to avoid excessively large messages slowing down processing or overwhelming SQL.

4. Operating System and Network Configuration

Power Settings: Set the server’s power plan to High Performance to avoid CPU throttling.
Disk Write Caching: Enable write caching on the disk but be cautious; ensure the server has UPS protection as write caching can cause data loss if there’s a sudden power failure.
Network Optimization: Use dedicated network interfaces for SQL Server and BizTalk network traffic if possible. This helps avoid network contention on a single interface.
Firewall and Security Settings: Ensure proper firewall rules are in place to allow only necessary traffic to SQL and BizTalk services.

5. BizTalk Database Maintenance

BizTalk Database Jobs: Enable and regularly monitor BizTalk’s built-in SQL jobs, such as Backup BizTalk Server (BizTalkMgmtDb), DTA Purge and Archive (BizTalkDTADb), and MessageBox_Message_Cleanup_BizTalkMsgBoxDb. These jobs help maintain database health and performance.
MessageBox and Tracking Database Size: Keep the BizTalkMsgBoxDb and BizTalkDTADb databases at optimal sizes. Purge data regularly, especially if tracking is enabled, to prevent database growth from impacting performance.

6. Monitor Performance Regularly

Use Performance Monitor (PerfMon) to track critical counters, such as:
- BizTalk: Messaging Database Size, Host Throttling State
- SQL Server: Buffer Cache Hit Ratio, Page Life Expectancy, Batch Requests/Sec
- System: CPU Usage, Available Memory, Disk I/O
Implement alerts for critical counters so you can take action before performance issues arise.

7. Consider Virtualization and Licensing

If SQL Server and BizTalk are sharing a virtualized environment, consider separating them into different VMs to allow easier scaling and isolation. Also, review SQL and BizTalk licensing requirements to ensure compliance when running both on the same server.

By following these steps, you can run SQL Server and BizTalk Server on the same machine in a way that minimizes resource contention, ensuring smooth operations.

Tuesday

BinBuilder - equivalent of StringBuilder for binary objects

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


public class BinBuilder
{

    public BinaryWriter writer;
    public MemoryStream requestMemoryStream = new MemoryStream();

    public BinBuilder(string a)
        : this(ASCIIEncoding.Default.GetBytes(a))
    {
    }

    public  BinBuilder(byte[] init):this()
    {
        writer.Write(init);
    }

    public void Append(ushort us)
    {
        this.Append(BitConverter.GetBytes(us));
    }

    public void AppendCRC(ushort us)
    {
        var a=BitConverter.GetBytes(us);
        writer.Write(a[1]);
        writer.Write(a[0]);
    }

    public void Append(char us)
    {
        this.Append(BitConverter.GetBytes((ushort)us));
    }


    public void Append(byte[] init)
    {
        writer.Write(init);
    }


    public BinBuilder()
    {
        writer = new BinaryWriter(requestMemoryStream);
    }

    public byte[] ToArray()
    {
        writer.Flush();
        return requestMemoryStream.ToArray();
    }

}

tsql SEQUENCE create usage and list of current values


CREATE SEQUENCE dbo.GenericSequenceNumber
AS INT
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 999999
 CYCLE ;
GO

--usage
SELECT NEXT VALUE FOR dbo.bbGenericSequenceNumber


--to list all of sequesnces inlcuding their values do:
SELECT
  name,
  cast(start_value AS NUMERIC)   AS start_value,
  cast(increment AS NUMERIC)     AS increment,
  cast(current_value AS NUMERIC) AS current_value
FROM sys.sequences;

Friday

Fix Error 0x800F0954 Installing .NET Framework 3.5 or Any Optional Feature

[Fix] Error 0x800F0954 Installing .NET Framework 3.5 or Any Optional Feature

If the error 0x800f0954 occurs installing optional Windows features, it may be because the system is unable to access the Windows Update server. This is especially true in case of domain-joined computers which is configured to downloads updates from a WSUS server. It could also be possible that your computer was once a part or a corporate or domain network and the group policy setting is still in place.

Method 1: Bypass WSUS to Install Features/Updates Directly from Windows Update

To fix the problem, temporarily bypass WSUS server using the following registry edit (requires administrator privileges).

Right-click Start, and click Run
Type regedit.exe and click OK

Go to the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU

On the right-pane, if the value named UseWUServer exists, set its data to 0
Exit the Registry Editor
Restart Windows.

Sunday

imagemagic add text to image

rem different types of text annotations on existing images
rem cyan yellow orange gold 
rem -gravity SouthWest
rem draw text and annotate
rem C:\me\tools\magic\convert.exe  -fill cyan -pointsize 120 -gravity center -draw "text 0,300 '%~1' "   %2  out%2
rem  C:\me\tools\magic\convert.exe %2  -undercolor white -pointsize 120 -gravity SouthWest -fill cyan  -annotate 0      %1 out%2

rem - klasnij horizontal append
rem C:\me\tools\magic\convert.exe %2  -background white -pointsize 120 label:%1  -gravity Center -append    out%2
rem C:\me\tools\magic\convert.exe %2 ( -size 600x -background red -fill black label:%1 -rotate 90 -trim +repage ) -gravity west -geometry +20+0 -composite out%2 
rem wow pashe vertical append 
rem C:\me\tools\magic\convert.exe %2 ( -size 1000x -background white -fill black label:%1 -rotate 90 -trim +repage ) -gravity east  +append out%2 

rem changing image size with horisontal append 
rem C:\me\tools\magic\convert.exe %2 ( -size 1500x -background white -fill black label:%1  -trim +repage ) -gravity east  +append out%2 

rem cool annotation with changing image size 
C:\me\tools\magic\convert.exe %2 ( -size 1500x -background white -fill black label:%1  -trim +repage ) -gravity center -append out%2

note that %~1 is command line argument without double quotes.

Wednesday

powershell compare two csv files and output differences

I had to compare data returned from two APIs - one JSON another XML so

1. I used this viewer to convert json to csv and this app to save xml to csv

2. I used this PowerShell to show difference between two csv files:

Start-Transcript -Path result-10.csv


$mas = Import-Csv 1.csv
$marko = Import-Csv 2.csv

$md=@{}
#converting one csv into hashmap for quicker search
foreach($r in $mas){
    foreach($a in $r.psobject.properties){
        $fn=$a.name
        $val=$a.value
        $md.Add($fn.ToLower(),$val)
    }
}

echo "fieldName,csv1,csv2"

    foreach($r in $marko){
        foreach($a in $r.psobject.properties){

            $k=$a.name.replace("_","").toLower()
            if ($md.ContainsKey($k)){
                if ($md[$k] -ne $a.value){
                  echo "$($a.name),$($a.value),$($md[$k])"
                }
            }
        }
    }
Stop-Transcript