T-SQL Error: An aggregate may not appear in the set list of an update statement

A reporting project contains a table with the product summary, the table has a lot of KPIs and specifically the "Last Sale Date" of a product.
According to the company rules, a product is sold when it is paid.
The following T-SQL code (shown here very simplified) can be used to update that column:

UPDATE Product
SET LastSalesDate = MAX(Orders.PaymentDate)
FROM Product
    INNER JOIN Orders ON (Orders.ProductID = Product.ProductID)

After executing the statement in Sql Server the following error occurs:

Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

The error indicates that a column of a table is being updated applying an aggregate function directly.
The solution is to rewrite the query, using for example a sub-query.
Example:

UPDATE Product
SET LastSalesDate = LastSaleDates.PaymentDate
FROM Product
    INNER JOIN 
    (
        SELECT MAX(Orders.PaymentDate) AS PaymentDate
            ,Orders.ProductID
        FROM Orders
        GROUP BY Orders.ProductID
    ) LastSaleDates ON (LastSaleDates.ProductID = Product.ProductID)

Another possible solution is to use a Common Table Expression (CTE).

;WITH [LastSaleDates] AS
(
    SELECT MAX(Orders.PaymentDate) AS PaymentDate
            ,Orders.ProductID
    FROM Orders
    GROUP BY Orders.ProductID 
)

UPDATE Product
SET LastSalesDate = LastSaleDates.PaymentDate
FROM Product
    INNER JOIN LastSaleDates ON (LastSaleDates.ProductID = Product.ProductID)

How to start SQL Agent job from Another server

A common scenario, when managing a server farm, is to have to sync the SQL Agent jobs in several servers.

You may want to start a job on a second server (SQL02) when a job completes on a primary server (SQL01).

The SQL02 may be a reporting or staging sever, and the SQL01 may be the transnational server.

Sql Server has a T-SQL statement - sp_start_job - to start a job on the server where it is executed.

From the Microsoft Documentation, sp_start_job has the following parameters:

sp_start_job
{[@job_name =] 'job_name' | [@job_id =] job_id }
[ , [@error_flag =] error_flag]
[ , [@server_name =] 'server_name']
[ , [@step_name =] 'step_name']
[ , [@output_flag =] output_flag]

The @server_name parameter is relative to the server where it is executed.
If the parameter supported executing a job from another server it would be great, but no such luck!!!v

The solution is to set a SQL02 as a linked server, this allow that Sql would recognize the remote server as being local.

To configure a linked server on Sql 2014 (It may change from Sql Sever version to Sql Sever Version), follow the next steps:
1) Open Sql Server Management Studio (SSMS)
2) Expand the SQL01 node name
3) Expand "Server Objects"
4) Press the right mouse button on the ""Server Objects"
Example:


5) Write the Linked Server Name: SQL02 in this example
6) Set the server type to SQL Server
7) In Security set the your security settings
In this example the Local login is "sa" and the "Impersonate" checkbox is active. Also the "For a login not defined in the list above, connection will:" option is set to "Be made using this security context", defining the Remote login and password.
Example:


8) On "Server Options" allow "RPC Out"
Example:


9) Press OK and if the everithing is correctly defined the linked server is configured


After configuring the linked server the job can be started on the SQL02 from the SQL01.

To test open a query window on SSMS in the SQL01 server and execute the SP to start the job:

EXEC [SQL02].[msdb].[dbo].[sp_start_job] @job_name = N'MyJob';

Where "MyJob" is the name of the job to start on SQL02

Note that the job is started, but the sp_start_job does not wait for the job "MyJob" to complete the execution.

High CPU usage in Visual Studio 2017

Visual studio 2017 has a High CPU usage after using it for some time.

The issue is very annoying, since the work computer starts to get very slow and unresponsive.

I fixed my problem by performing the following steps:

1. Close all the visual studio open instances
2. Delete all the .suo files from the visual studio solutions folders
3. Remove all the .vs hidden directories from the visual studio solutions folders

Use this solution with caution, so you only delete the referred files or folders.

T-SQL: How to raise an error on a user defined function (udf)

A common programming task is to create a user-defined function. The function can perform some tasks and raise an error if some validation fails.
Let us try to raise an error

CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS INT
AS
BEGIN
    IF (@op2 = 0)
    BEGIN
    RAISERROR ('Division by zero.', 16, 1);
    END

    RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2
END

But this approach gives the following error:

Msg 443, Level 16, State 14, Procedure Division, Line 7 [Batch Start Line 0]
Invalid use of a side-effecting operator 'RAISERROR' within a function.

So another approach is needed.
Let us try the THROW statement

CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS INT
AS
BEGIN
    IF (@op2 = 0)
    BEGIN
    ;THROW 51000, 'Division by zero.',1;
    END

    RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2
END

But the result is the same as the RAISERROR:

Msg 443, Level 16, State 14, Procedure Division, Line 7 [Batch Start Line 0]
Invalid use of a side-effecting operator 'THROW' within a function.

Having tried all the known solutions to throw an exception in Sql Server, an alternative is needed.
Let's try a cast conversion error:

CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT)
RETURNS DECIMAL(18, 8)
AS
BEGIN
    IF (@op2 = 0)
    BEGIN
    RETURN CAST('Division by zero.' AS INT);
    END

    RETURN CAST(@op1 AS DECIMAL(18, 8)) /  CAST(@op2 AS DECIMAL(18, 8))
END

The function is now created with success.
Let us see the result of calling the function with a division by zero:

SELECT dbo.Division(1, 0)

The following exception is thrown:

Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'Division by zero.' to data type int.


It's not the ideal solution, but it allows us to generate a exception on a T-SQL function.

How to backup all the user databases

The database administrator must backup periodically the databases on the Sql Servers.

The process is tedious and must be automated with a script to avoid forgetting any database.


The simple script can be scheduled on the Sql Server agent.

DECLARE @backupName VARCHAR(255) -- database backup name  
DECLARE @databaseName VARCHAR(255) -- database name  
DECLARE @path VARCHAR(256) -- Folder for the for backup files  
DECLARE @fileName VARCHAR(256) -- file name for generating the backup  
DECLARE @databaseExclusions TABLE (DatabaseName VARCHAR(255)) -- Databases to exclude the backup

-- Input parameters
SET @path = 'D:\Backup\2\'  
INSERT INTO @databaseExclusions (DatabaseName)
VALUES
('tempdb'),('master'),('model'),('msdb'),('AdventureWorks')


DECLARE database_cursor CURSOR FOR  
SELECT top 5 name
    FROM sys.databases 
    WHERE [state] <> 6 -- OFFLINE
    AND owner_sid != 1 -- User user database
    AND name NOT IN (SELECT DatabaseName FROM @databaseExclusions)
 
OPEN database_cursor   
FETCH NEXT FROM database_cursor INTO @databaseName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @databaseName;  
   SET @backupName = @databaseName + '-Full Database Backup';

   BACKUP DATABASE @databaseName TO DISK = @fileName WITH NOFORMAT, INIT,  NAME = @backupName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
   
   FETCH NEXT FROM database_cursor INTO @databaseName   
END   

 
CLOSE database_cursor   
DEALLOCATE database_cursor

How to convert a string to a byte array in C#

C# allow to easily convert a string to a byte array using the Encoding.ASCII.GetBytes function.
A sample usage is

string sample = "This is a simple string";
byte[] content = Encoding.ASCII.GetBytes(sample);

Note that this method only works if the string is with the ASCII format.

The framework supports other encodings (UTF8, Unicode, UTF32, ...) but a more generic method is needed.
The ideal solution if to don't need to worry about the encoding if the bytes don't need to be interpreted.
A use case for this solution is to transfer a file from a web server to a browser.

        static byte[] GetBytes(string value)
        {
            byte[] bytes = null;
            using (var memoryStream = new MemoryStream())
            {
                using (var streamWriter = new StreamWriter(memoryStream, Encoding.Default))
                {
                    streamWriter.Write(value);

                    streamWriter.Flush();
                    streamWriter.Close();

                    bytes = memoryStream.ToArray();
                }
            }

            return bytes;

        }

But this solution also uses the Encoding.Default for the StreamWriter.

The Enconding can also be performed as:

string sample = "This is a simple string";
byte[] content = Encoding.Default.GetBytes(sample);

Welcome the the encondig hell!!

T-SQL: How to split a path name in the path and file name

A common task in T-SQL is how to split a path name in the path and file name.

The following code will do the task:

DECLARE @file VARCHAR(200) = 'c:\temp\data\log.txt'

SELECT REVERSE(LEFT(REVERSE(@file), CHARINDEX('\', REVERSE(@file), 1) - 1)) AS [FileName]

SELECT LEFT(@file, LEN(@file) - CHARINDEX('\', REVERSE(@file), 1) + 1) AS [Path]