SQL Server DBA and Azure SQL Insights

Automate Emailing of Latest Excel Reports from Shared Folders Using PowerShell

Automatically send the latest Excel file from a shared folder via email, along with the file's creation date, using a simple PowerShell script. No more manual checks

# Configuration

$sharedFolder = "\\YourSharedServer\SharedFolderPath"

$smtpServer = "smtp.yourdomain.com"

$from = "sender@yourdomain.com"

$to = "recipient@yourdomain.com"

$subject = "Latest Excel File from Shared Location"

# Get the latest Excel file

$latestFile = Get-ChildItem -Path $sharedFolder -Filter *.xls* |

Sort-Object LastWriteTime -Descending |

Select-Object -First 1

if ($latestFile) {

$filePath = $latestFile.FullName

$fileName = $latestFile.Name

$fileDate = $latestFile.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss")

$body = @"

Hello,

Please find the latest Excel file attached.

**File Name:** $fileName

**Generated On:** $fileDate

Regards,

Your DBA Automation

# Send email with attachment

Send-MailMessage -From $from -To $to -Subject $subject -Body $body `

-SmtpServer $smtpServer -BodyAsHtml -Attachments $filePath

} else {

Write-Host "No Excel file found in the shared folder."

}

Unable to Drop a User from SSISDB?

I ran into an issue when trying to drop a user from SSISDB (the Integration Services Catalog database).

The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.

The transaction ended in the trigger. The batch has been aborted.

How to solve it?

Find where the user has permissions

USE SSISDB

SELECT *

FROM catalog.object_permissions

WHERE grantee_sid = SUSER_SID('YourUserName');

--change YourUserName to your actual username

Revoke permissions

REVOKE READ ON OBJECT::[folder_name] TO [YourUserName];

REVOKE MODIFY ON OBJECT::[project_name] TO [YourUserName];

Drop the user

DROP USER [YourUserName];

SQL Server Config Manager Error "MMC could not create the snap-in

Hi, I have seen this error elsewhere online. I have gone to mmc to enable the snap in and I still have had no fix. My computer is running Windows Server 2022, SQL Server Express 2022, and SSMS. I have reinstalled, repaired, and all of the other tricks. Help!

Solution: Re-register the Configuration Manager Snap-In Manually

-->Open Command Prompt as Administrator

-->Run the following command:(For SQL Server 2022, the version folder is 160. Adjust the path if yours is different.)

-->cmdmofcomp "C:\Program Files (x86)\Microsoft SQL Server\160\Shared\sqlmgmproviderxpsp2up.mof"

-->Restart your machine

How to Fix: 'Microsoft.ACE.OLEDB.12.0' Provider is Not Registered on the Local Machine

If you encounter the error:

'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

while importing Excel or CSV files into SQL Server, don't worry! This is a common issue caused by a missing or mismatched version of the Microsoft Access Database Engine.

Download the Microsoft Access Database Engine 2016 Redistributable

Go to the official Microsoft download page:
Microsoft Access Database Engine 2016 Redistributable
Select the version based on your system:

32-bit version (recommended even on 64-bit systems if you're using 32-bit SQL Server tools).
64-bit version (only if you're sure all tools are 64-bit).

Note: Using the 32-bit version resolves compatibility issues with tools like SQL Server Management Studio (SSMS), which often run in 32-bit mode.

Install Using Command Prompt (Silent Install)

Once downloaded, follow these steps to install properly:

Open Command Prompt as Administrator.
Navigate to the folder where the .exe file is downloaded.

Example Command: cd C:\Test\AccessDatabaseEngine.exe /quiet

If your file is named differently (like AccessDatabaseEngine_X64.exe), use the exact name.

This will install the provider quietly without UI interruptions.

Restart Your Tools

After installation:

Restart SQL Server Management Studio (SSMS).
Try your import or export task again.

You should no longer see the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine error!

T-SQL SQL Server Backup Report: Last Full, Differential, and Log Backup Details

Monitoring your SQL Server backups is crucial for disaster recovery planning.

Below is a powerful SQL query that retrieves the last full, differential, and log backup details for each database.

SELECT
db.name AS DatabaseName,
-- Last Full Backup Details
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS LastFullBackupTime,
MAX(CASE WHEN bs.type = 'D' THEN bmf.physical_device_name END) AS FullBackupLocation,
-- Last Differential Backup Details
MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS LastDifferentialBackupTime,
MAX(CASE WHEN bs.type = 'I' THEN bmf.physical_device_name END) AS DifferentialBackupLocation,
-- Last Log Backup Details
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS LastLogBackupTime,
MAX(CASE WHEN bs.type = 'L' THEN bmf.physical_device_name END) AS LogBackupLocation,
-- Backup Status
CASE
WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL THEN 'No Full Backup Found'
ELSE 'Full Backup Available'
END AS FullBackupStatus,
CASE
WHEN MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) IS NULL THEN 'No Differential Backup Found'
ELSE 'Differential Backup Available'
END AS DifferentialBackupStatus,
CASE
WHEN MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL THEN 'No Log Backup Found'
ELSE 'Log Backup Available'
END AS LogBackupStatus
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
JOIN sys.databases db ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name;