Hi All,

Often we try to copy the files from sql server tables and we end up renaming the copied file names manually.

Here is simple sql server statement, where it pull the message archive location and name of the file will be with some GUID and you want to name with PO number column , use this simple select statement

select ‘copy ‘+MessageArchiveLocation +’,’+’"’+PONumber+’.xml’+’"’ from Ordertransactions (nolock) where PONumber in

(‘12344′,’112112′,’11231232’)

order by 1 desc

you will get the results, then copy them to batch file and run it.

Regards,

Chaitanya

Hi All,

Want to delete the GUID named files in your folders?

Here is the simple script that takes the regex for GUID and compares the file name and remove it.

get-childitem |? {$_.basename -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")} |Remove-Item

if you want to Include subfolders too, use the recurse option like below

get-childitem -Recurse |? {$_.basename -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")} |Remove-Item

Note: please test it in lower environments before implementing in production.

Regards,

Chaitanya

Hi All,

If you got bunch of DLLs and want to check the .net version for those, this is simple script you can use to find out the version.

# check .net frame work for the DLLs

$filepath=’D:\Builds\\AutomatedDeployment\Deployment\Add-PreDlls’

$files=Get-ChildItem -Path $filepath -Recurse -filter *.dll

foreach($file in $files)

{

$version = [System.Reflection.Assembly]::ReflectionOnlyLoadFrom($file.FullName).ImageRuntimeVersion;

echo "$($file) is using Framework: $version"

}

Happy scripting….

Regards,

Chaitanya

As you are hearing now a days about lot about bit coin and its value reaching all-time high, but Bit coin actually works using block chain model.

Most of them predicting this is the new internet and also it is a new technology revolution.

This is the TedEx video , you could find the information on Block chain in a simplified way. I could not agree more on that.

https://www.youtube.com/watch?v=k53LUZxUF50

Please watch it and welcome to new technology advancement.

regards,

Chaitanya

Hi All,

When I am trying to create a scheduled task in windows server 2016, I was getting the below error.

The task XML contains a value which is incorrectly formatted or out of range.

(8,42):Duration:P99999999DT23H59M59S

+ CategoryInfo : NotSpecified: (PS_ScheduledTask:Root/Microsoft/..

.S_ScheduledTask) [Register-ScheduledTask], CimException

+ FullyQualifiedErrorId : HRESULT 0x80041318,Register-ScheduledTask

+ PSComputerName : AZWEPSSCBTXPFE1.partners.extranet.microsoft.com

After looking online, found this issue with [timespan]::MaxValue property that used in the script

This was my script.

$mycredentials = Get-Credential

Invoke-Command -ComputerName “Server1” -Credential $mycredentials -ScriptBlock {

$Action = New-ScheduledTaskAction -Execute ‘powershell.exe’ -Argument ‘-command “D:\PS_Jobs\PS_Job1.ps1” -ExecutionPolicy RemoteSigned -NoProfile’

$Trigger = New-ScheduledTaskTrigger -Once -At 9am -RandomDelay (New-TimeSpan -Minutes 30) -RepetitionDuration ([timespan]::MaxValue) -RepetitionInterval (New-TimeSpan -Minutes 60)

$Task = New-ScheduledTask -Action $Action -Trigger $Trigger -Settings (New-ScheduledTaskSettingsSet)

$Task | Register-ScheduledTask -TaskName ‘Job1 Task’

}

As the issue with [timespan]::MaxValue, I made some changes to include the time span for only 3 years to run the script.

This is the updated and working script.

Invoke-Command -ComputerName “Server1” -Credential $mycredentials -ScriptBlock {

$dt= ([DateTime]::Now)

$timespan = $dt.AddYears(3) -$dt;

$Action = New-ScheduledTaskAction -Execute ‘powershell.exe’ -Argument ‘-command “D:\PS_Jobs\PS_Job1.ps1” -ExecutionPolicy RemoteSigned -NoProfile’

$Trigger = New-ScheduledTaskTrigger -Once -At 9am -RandomDelay (New-TimeSpan -Minutes 30) -RepetitionDuration $timespan -RepetitionInterval (New-TimeSpan -Minutes 60)

$Task = New-ScheduledTask -Action $Action -Trigger $Trigger -Settings (New-ScheduledTaskSettingsSet)

$Task | Register-ScheduledTask -TaskName ‘Job1 Task’

}

Regards,

Chaitanya

Hi All,

I have prepared the below script to fetch the cricket scores. It will take ESPN CricInfo feed and run for every 2 minutes.

You have to pass the team that you are interested to look for scores. Here I took India and it will give the balloon notifications in your desktop.

It is Useful when doing the work and wanted to know the score for every 2 minutes and if any wicket falls, it will give the notification.

Script :

param(

[Parameter(Position=0, Mandatory=$false)][String]$Team = "india"

)

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

#region WriteLog function

function WriteLog($LogMessage, $LogDateTime, $LogType)

{

write-host

"$LogType, ["+
$LogDateTime +"]: "+ $LogMessage | Add-Content -Path $LogFilepath

}

#endregion

# Get Start Time

$startTime = (Get-Date)

$RunTime =get-date -Format "MMdyyyhhmmss"

# Get build folder parent directory

$scriptpath = $MyInvocation.MyCommand.Path

$ScriptDir = Split-Path $scriptpath

# Get Application folder path in the build folder

$LogFolderPath = $ScriptDir + "\" + "Logs"

# Check if Log folder already exists. If not create a folder for logging purposes

if(!(Test-Path $LogFolderPath))

{

New-Item -ItemType directory -Path $LogFolderPath

}

[string] $logdate =get-date -Format "yyyyMMdd"

$LogFolderFilepath =$LogFolderPath + "\" + "$logdate"

if(!(Test-Path $LogFolderFilepath))

{

New-Item -ItemType directory -Path $LogFolderFilepath

}

# creating logfile path string

$LogFilepath =$LogFolderFilepath +"\"+ "Logfile.txt"

$LogDateTime = get-date

WriteLog "***Getting the Scores for $Team" $LogDateTime "Information"

write-host "***Getting the Scores for $Team" -ForegroundColor Yellow

TRY

{

$PreviousScore = $null;

while($true)

{

[xml]$data = (Invoke-WebRequest ‘http://static.cricinfo.com/rss/livescores.xml‘).Content

if($?)

{

$CurrentScore = "";

$finalScore = "";

$wickdown = "";

$currWicket = "";

$PrevWicket = "";

foreach ($xnode in $data.rss.channel.childnodes)

{

if(($xnode.title -like "*$($Team)*") -and ($xnode.title -match "[0-9]"))

{

$CurrentScore = $CurrentScore + $xnode.title + "`n"

}

}

function WicketDown($CurrentScore,$PreviousScore)

{

$currWicket = ($CurrentScore.Split("/")[1]).split(" ")[1]

$PrevWicket = ($PreviousScore.Split("/")[1]).split(" ")[1]

if($currWicket -ne $PrevWicket)

{

return 1;

}

else

{

return 0;

}

}

if ([string]::IsNullOrEmpty($PreviousScore))

{

$PreviousScore = $CurrentScore;

$finalScore = $CurrentScore;

}

elseif (($PreviousScore -eq $CurrentScore ) -and (-not([string]::IsNullOrEmpty($PreviousScore))))

{

$finalScore = $null;

}

elseif (($PreviousScore -ne $CurrentScore ) -and (-not([string]::IsNullOrEmpty($PreviousScore))))

{

$PreviousScore = $CurrentScore;

$finalScore = $CurrentScore;

$wickdown = WicketDown($CurrentScore,$PreviousScore)

}

else

{

}

$objNotifyIcon = New-Object System.Windows.Forms.NotifyIcon

$objNotifyIcon.Icon = [System.Drawing.SystemIcons]::Information

$objNotifyIcon.BalloonTipIcon = "Info"

if($finalScore -and $wickdown -ne ‘1’)

{

$objNotifyIcon.BalloonTipText = $finalScore

$objNotifyIcon.BalloonTipTitle = "Cricket Score:"

$objNotifyIcon.Visible = $True

$objNotifyIcon.ShowBalloonTip(30000)

}

if($finalScore -and $wickdown -eq ‘1’)

{

$objNotifyIcon.BalloonTipText = $finalScore

$objNotifyIcon.BalloonTipTitle = "Cricket Score:"

$objNotifyIcon.BalloonTipIcon = "Error" ;

$objNotifyIcon.Visible = $True

$objNotifyIcon.ShowBalloonTip(30000)

}

else

{

$LogDateTime = get-date

WriteLog "***Could not find the Score for $team" $LogDateTime "Information"

write-host "***Could not find the Score for $team" -ForegroundColor Yellow

}

}

$objNotifyIcon.Dispose();

Start-Sleep -seconds 120

}

}

Catch

{

$ErrorOccured = $true

#region log exception in log file

$LogMessage = $_.Exception.Message

$LogDateTime = get-date

WriteLog $LogMessage $LogDateTime "Error"

WriteLog "$Action failed with Error" $LogDateTime "Error"

$ErrorActionPreference="Continue"

#endregion

}

Finally

{

$LogDateTime = get-date

WriteLog "*** Script exection stoppped " $LogDateTime "Information"

}

Happy scripting

Regards,

Chaitanya

Hi All,

Below is the query for that.

SELECT rs.[restore_history_id]

,rs.[restore_date]

,rs.[destination_database_name]

,bmf.physical_device_name

,rs.[user_name]

,rs.[backup_set_id]

,CASE rs.[restore_type]

WHEN ‘D’ THEN ‘Database’

WHEN ‘I’ THEN ‘Differential’

WHEN ‘L’ THEN ‘Log’

WHEN ‘F’ THEN ‘File’

WHEN ‘G’ THEN ‘Filegroup’

WHEN ‘V’ THEN ‘Verifyonlyl’

END AS RestoreType

,rs.[replace]

,rs.[recovery]

,rs.[restart]

,rs.[stop_at]

,rs.[device_count]

,rs.[stop_at_mark_name]

,rs.[stop_before]

FROM [msdb].[dbo].[restorehistory] rs

inner join [msdb].[dbo].[backupset] bs

on rs.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

order by rs.[restore_date] desc

GO

Hi All,

CU 2 is now available for BizTalk Server 2016. Below is the official Microsoft link for the details.

https://support.microsoft.com/en-us/help/4021095/cumulative-update-2-for-microsoft-biztalk-server-2016

regards,

Chaitanya

Here is the below query to identify.

# Sql server orphaned transactions

select * FROM master..syslockinfo WHERE req_spid=-2

Regards,

Chaitanya