How to send email from SQL Server || Using Database Mail to Send Emails in SQL Server || Configuring Email Notifications in SQL Server

 To send an email in a SQL Server trigger using SMTP2GO, you’ll need to create a stored procedure that sends emails through SMTP and call this procedure from within your trigger. Here’s a step-by-step guide to setting this up.

1. Enable Database Mail in SQL Server

First, ensure Database Mail is enabled on your SQL Server instance. Database Mail is required to send emails from SQL Server.

1. In SQL Server Management Studio (SSMS), go to Management > Database Mail.
2. Right-click and select Configure Database Mail to set up a mail profile if you haven't done so already.

2. Configure SMTP2GO Settings

Set up SMTP2GO as the SMTP server in SQL Server Database Mail:

• SMTP Server: mail.smtp2go.com
• SMTP Port: 2525 (or 587/465 for secure connections)
• Authentication: Use your SMTP2GO username and password for authentication.

In the Database Mail configuration, add these SMTP server details.

3. Create a Stored Procedure to Send Email

Now, create a stored procedure to send emails through Database Mail. This stored procedure will be called from your trigger.

CREATE PROCEDURE SendEmailUsingSMTP2GO

    @recipient NVARCHAR(255),

    @subject NVARCHAR(255),

    @body NVARCHAR(MAX)

AS

BEGIN

    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = 'SMTP2GOProfile', -- Use the profile name you configured

        @recipients = @recipient,

        @subject = @subject,

        @body = @body,

        @body_format = 'HTML';

END

Replace 'SMTP2GOProfile' with the actual profile name created for Database Mail.

4. Create the Trigger to Call the Stored Procedure

Now, create a trigger that calls the SendEmailUsingSMTP2GO stored procedure to send an email when a specific action occurs on the table.

CREATE TRIGGER trgSendEmailOnInsert

ON YourTableName

AFTER INSERT

AS

BEGIN

    DECLARE @recipient NVARCHAR(255);

    DECLARE @subject NVARCHAR(255);

    DECLARE @body NVARCHAR(MAX);

    -- Set the recipient, subject, and body of the email

    SET @recipient = 'recipient@example.com';

    SET @subject = 'New Record Inserted';

    SET @body = 'A new record has been inserted into the table.';

    -- Call the stored procedure to send the email

    EXEC SendEmailUsingSMTP2GO

        @recipient = @recipient,

        @subject = @subject,

        @body = @body;

END

Replace YourTableName with the name of your table, and customize the @recipient, @subject, and @body variables as needed.

5. Test the Trigger

Insert a row into YourTableName to test if the trigger works and if the email is sent via SMTP2GO.

sql

INSERT INTO YourTableName (Column1, Column2) VALUES ('Value1', 'Value2');

If everything is set up correctly, you should receive an email at the specified recipient address.

Important Notes:

• Avoid Heavy Email Load in Triggers: Triggers are synchronous with the transaction, so email sending will block the transaction until it completes. If you expect high frequency, consider logging events and using a separate job to send emails.
• Error Handling: Consider adding error handling in your stored procedure or trigger to handle email sending failures gracefully.

I have multiple databases with the same table structure, and each database name starts with 'U'. I want to find the row count of the Traces table in each of these databases. Could you provide a SQL script that loops through each database starting with 'U', checks if the Traces table exists, and retrieves the row count for it in each database?

 USE master;

DECLARE @DatabaseName NVARCHAR(128);

DECLARE @sql NVARCHAR(MAX);

-- Cursor to iterate over each database starting with "U"

DECLARE db_cursor CURSOR FOR

SELECT name 

FROM sys.databases

WHERE name LIKE 'U%'  -- Filters databases starting with "U"

  AND state_desc = 'ONLINE'; -- Only includes online databases

-- Open the cursor

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DatabaseName;

-- Loop through each database

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Construct the dynamic SQL for each database

    SET @sql = 'USE ' + QUOTENAME(@DatabaseName) + ';

                IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables WHERE name = ''Traces'')

                BEGIN

                    SELECT ''' + @DatabaseName + ''' AS DatabaseName, 

                           COUNT(*) as rowscount 

                    FROM ' + QUOTENAME(@DatabaseName) + '.dbo.Traces;

                END';

    

    -- Execute the dynamic SQL

    EXEC sp_executesql @sql;

    -- Move to the next database

    FETCH NEXT FROM db_cursor INTO @DatabaseName;

END

-- Close and deallocate the cursor

CLOSE db_cursor;

DEALLOCATE db_cursor;

Querystring Issue on C# with special characters

 If you're building a URL string dynamically in C#, you can use Uri.EscapeDataString to ensure proper encoding:

string searchTerm = "rock&roll";

string query = $"https://example.com/search?query={Uri.EscapeDataString(searchTerm)}";

This approach will automatically encode any special characters, including &, resulting in a safe and valid query string.

SQL Script to Terminate All Active Connections to a Specific Database || Kill Database connections

 -- Declare a cursor to iterate through the sessions

DECLARE @sessionId INT;

DECLARE kill_cursor CURSOR FOR

    SELECT session_id

    FROM sys.dm_exec_sessions

    WHERE database_id = DB_ID('DBName');

-- Open the cursor and loop through the sessions

OPEN kill_cursor;

FETCH NEXT FROM kill_cursor INTO @sessionId;

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Print the session ID to be killed (for verification purposes)

    PRINT 'Killing session: ' + CAST(@sessionId AS VARCHAR(10));

    -- Kill the session

    EXEC('KILL ' + @sessionId);

    FETCH NEXT FROM kill_cursor INTO @sessionId;

END

-- Close and deallocate the cursor

CLOSE kill_cursor;

DEALLOCATE kill_cursor;

How do I create a unique constraint on a column in SQL Server?

 You can create a unique constraint using the ALTER TABLE command:

ALTER TABLE YourTableName ADD CONSTRAINT Unique_Constraint_Name UNIQUE (ColumnName);

This enforces a unique constraint on ColumnName.

This returns the names of all databases on the server.

 To remove a column from a table, use the ALTER TABLE command:

ALTER TABLE YourTableName DROP COLUMN ColumnName;

This will permanently delete the column from the table.

How do I list all available databases on a SQL Server instance?

 You can list all databases on a SQL Server instance using:

SELECT name FROM sys.databases;

This returns the names of all databases on the server.

How do I check the SQL Server version and edition?

 Use the following query to get the SQL Server version and edition:

SELECT SERVERPROPERTY('ProductVersion') AS Version, 

 SERVERPROPERTY('ProductLevel') AS ProductLevel,

 SERVERPROPERTY('Edition') AS Edition;

This returns the version, product level (e.g., RTM, SP1), and edition of the SQL Server instance.

How do I rename a column in SQL Server?

 You can rename a column using the sp_rename system stored procedure:

EXEC sp_rename 'YourTableName.OldColumnName', 'NewColumnName', 'COLUMN';

This renames OldColumnName to NewColumnName in the specified table.

How do I grant a user access to a specific database in SQL Server?

 To grant access to a user for a specific database, use the following commands:

Create a login (if it doesn't already exist):

CREATE LOGIN YourLoginName WITH PASSWORD = 'YourPassword';

Grant access to the database:

USE YourDatabaseName; CREATE USER YourLoginName FOR LOGIN YourLoginName;

Grant a role or specific permissions:

EXEC sp_addrolemember 'db_datareader', 'YourLoginName';  -- Grants read access