In this scenario, I'll have a table that has column that is Fruit, this column will have about 1000 + distinct entries in it an a entry on a per day basis. What I'm trying to do is for each distinct type of Fruit, I want to find where the day has sold 2x the amount of fruit from the previous two days combined.

So for example, if on day 1&2, there was 1 apple sold each day I would have a total of 2 sold, I want to know whenever the following day has sold 2x the previous, so if day 3 has 4 apples sold then this is a positive result, however if 3 were sold, then this would be a negative result. I've put together a very clunky and slow cursor that I'm not confident in the results. One of the things to take into consideration is the data always need to be ordered by date desc, sometime the data isn't populated everyday an the ID will be out of order.

Here is a test query that has the final results I'm looking for.

declare @testTable table(Id int not null identity(1,1),Fruit nvarchar(10),Sold int, DateSold date)

DECLARE @StartDate datetime = '2024-01-22';
DECLARE @EndDate   datetime = Dateadd(day,100,@StartDate);

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
     insert into @testTable(Fruit,Sold,DateSold)
SELECT 'Apple',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
  union 
  SELECT 'Orange',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
    union 
  SELECT 'Pears',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
    union 
  SELECT 'Plums',90+ROW_NUMBER() OVER(ORDER BY theDate),theDate as theValue
  FROM theDates
OPTION (MAXRECURSION 0)

Fiddle with sample data:

https://dbfiddle.uk/coq20xzk

declare @appleRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Apple' ORDER BY NEWID());
declare @ornageRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Orange' ORDER BY NEWID());
declare @pearRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Pears' ORDER BY NEWID());
declare @plumRow int = (SELECT TOP 1 Id FROM @testTable where fruit = 'Plums' ORDER BY NEWID());

update @testTable
set Sold = Sold + 250
where Id in (@appleRow,@ornageRow,@pearRow,@plumRow);

select * from @testTable;

--these are the ids am looking for
select * from @testTable
where Id in (@appleRow,@ornageRow,@pearRow,@plumRow);

I would like to use the INSERT INTO clause to add additional data to a table.

I would like to insert the following Car data into table called Car_data.

Chrysler,Fiat Chrysler,32000,31591,32831,29544,33006,29945,13857,31869,0 Dodge,Fiat Chrysler,29000,110517,117582,104146,90643,88656,43756,71935,0 Ferrari,Ferrari,300000,2585,2585,2585,,2147,2147,2147, Fiat,Fiat Chrysler,26000,2214,2889,2361,1740,1128,1339,1102,0

Can someone show me how to INSERT INTO dbo.[Car_data]. The insert script is a follows:

INSERT INTO dbo.[Car_data]
(
  Brand
 ,Autogroup
 ,[Avg Price]
 ,[Q1 2019]
 ,[Q2 2019]
 ,[Q3 2019]
 ,[Q4 2019]
 ,[Q1 2020]
 ,[Q2 2020]
 ,[Q3 2020]
 ,[Q4 2020]
)
VALUES
(
  N'' -- Brand - nvarchar(50)
 ,N'' -- Autogroup - nvarchar(50)
 ,0 -- Avg Price - int
 ,0 -- Q1 2019 - int
 ,0 -- Q2 2019 - int
 ,0 -- Q3 2019 - int
 ,0 -- Q4 2019 - int
 ,0 -- Q1 2020 - int
 ,0 -- Q2 2020 - int
 ,0 -- Q3 2020 - int
 ,0 -- Q4 2020 - int
);
GO

So I would like to know if there is straight forward to insert the Car Data into the Car_data table?

I created my database in SQL Server with the code first method in ASP.NET Core. My intention is to build a simple news site. I have two tables in one of which I create news groups and it is successful in the other. I have to select that news group from my tables and add descriptions, photos, etc.

When I click on record, I get this error:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Pages_PageGroups_PageGroupGroupID". The conflict occurred in database "dbNews", table "dbo.PageGroups", column "GroupID".

I suppose it is for the subject of dependency injection, but I did not try it.

using System.ComponentModel.DataAnnotations;

namespace toplern18.Models
{
    public class Page
    {
        [Key]
        public int PagrID { get; set; }

        [Display(Name = "عنوان گروه")]
        [Required(ErrorMessage = "لطفا{0} را وارد نمایید")]
        public int GroupID { get; set; }

        [Display(Name = "عنوان")]
        [Required(ErrorMessage = "لطفا{0} را وارد نمایید")]
        [MaxLength(250)]
        public string Title { get; set; }

        [Display(Name = "توضیح مختصر")]
        [Required(ErrorMessage = "لطفا{0} را وارد نمایید")]
        [MaxLength(350)]
        [DataType(DataType.MultilineText)]
        public string ShortDescription { get; set; }

        [Display(Name = "متن")]
        [Required(ErrorMessage = "لطفا{0} را وارد نمایید")]
        [MaxLength(400)]
        [DataType(DataType.MultilineText)]
        public string text { get; set; }

        [Display(Name = "بازدید")]
        public int visit { get; set; }

        [Display(Name = "تصویر")]
        public string ImageName { get; set; }

        [Display(Name = "اسلاید")]
        public bool ShowDlider { get; set; }

        [Display(Name = "تاریخ ایجاد")]
        public DateTime CreateDate { get; set; }

        //الان این اون یکه است
        public virtual PageGroup PageGroup { get; set; }

        public virtual List<PageComment> PageComment { get; set; }

        public Page()
        {
        }
    }
}

-------------

using System.ComponentModel.DataAnnotations;

namespace toplern18.Models
{
    public class PageGroup
    {
        [Key]
        public int GroupID { get; set; }

        [Display(Name = "عنوان گروه")]
        [Required(ErrorMessage = "لطفا{0} را وارد نمایید")]
        [MaxLength(150)]
        public string GroupTitle { get; set; }


        //رابطش با پیج یک به چند است  الان لیست اون چنده است
         public virtual List<Page> Page { get; set; } //برقراری رابطه

        public PageGroup()
        {
            //سازنده خالی
        }
    }
}

I need to create a temporary table in one of my stored procedures. The data to be inserted into the temp table is derived from a dynamic pivot query - hence I am tied to dynamic sql. So it becomes something like this -

set query = 'select ....'+ pivotcols + 
            ' into ##temp_table
              from base_table
              pivot (
              max(col1)
              for col2 in 
              (' + pivotcols +' as final'

 exec(query)

Here I cannot use local temp table (#temp_table), since the table created within the dynamic sql won't be available to the rest of the stored procedure. So I end up using a global temp table (##temp_table).

Now the problem is if there is unforeseen exit from the stored proc where the table does not get dropped properly, it can raise exceptions when someone else tries to use the same sp. Also even without exceptions, if two people run the same procedure, there could be problems. Is there any solution to this problem? Any alternatives I can use?

Note: I have to use dynamic sql - the pivot query cannot be made dynamic in any other way, since the columns that will be pivoted are decided at runtime. But I am flexible to how the data actually goes into the temp table.

EDIT: edited 'variable' in the question heading to 'table'

I am currently trying to use the methods 'FromSql' and 'FromSqlInterpolated' from the namespace Microsoft.EntityFrameworkCore and these methods are not properly working as expected.

The result is supposed to be the following:

But when i parameterize the query in a controller action, the result is skipping the last result:

The way in which I execute the command is with the following code:

SqlParameter id= new("@ID", 200013);
SqlParameter inicio = new("@Inicio", "2023011");
SqlParameter fin= new("@Fin", "2024004");
FormattableString formattableString = FormattableStringFactory.Create(query, id, inicio, fin);
var calculoInpcs = await db.CalculoInpcs.FromSql(formattableString).ToListAsync();

With the query being:

SELECT per_anioa1 Anio, per_nummes Mes, ISNULL(i.Valor, c.Valor) InpcCorrespondiente, c.Valor InpcActual, CAST(c.Valor / ISNULL(i.Valor, c.Valor) AS FLOAT) FactorActualizacion, SUM(his_import) ServicioDescontado, ROUND((c.Valor / ISNULL(i.Valor, c.Valor)) * SUM(his_import),2) ServicioActualizado,((c.Valor / ISNULL(i.Valor, c.Valor)) * SUM(his_import)) - SUM(his_import) Diferencia
FROM nmlohism
INNER JOIN nmloperi ON per_keyper = his_keyper AND per_keypro = his_keypro AND his_keynom = per_keynom
LEFT JOIN SANOP_Catalogo_INPC i ON i.Año = CASE WHEN per_nummes=1 THEN per_anioa1-1 ELSE per_anioa1 END AND i.Mes = CASE WHEN per_nummes=1 THEN 12 ELSE per_nummes-1 END
LEFT JOIN (
    SELECT TOP 1 Valor FROM SANOP_Catalogo_INPC ORDER BY Año DESC, Mes DESC
) c ON 1 = 1
WHERE his_keycon IN ('402','407','413','560','565','566') AND his_keyemp = @ID AND his_keyper >= @Inicio AND his_keyper <= @Fin
GROUP BY per_anioa1, per_nummes, i.Valor, c.Valor

My guess is that something is preventing LEFT JOIN from working properly, and is not including the last result from the table nmlohism.

When executing the query with the method 'FromSqlRaw' and replacing the values directly in the string (without string interpolation), the result is the correct once again.

I have used the same approach for other DbSets and all have worked as expected, except for this case. I need help finding the reason for this and if there is any workaround.

To authenticate mail server from an application we use OAuth authentication. Application uses java mail API for SMTP mail communication.We create a app in Azure and use it's client ID and client secret to generate access token and refresh token. Use that in mail server to send scheduled mails via smtp with OAuth authentication.

The generated access token is 6000 chars and when we encrypt it and save in MSSQL database in varchar field, encrypted char length exceeds 8000 and so unable to save the access token and use them

Is there a way to control access token length? what could be the maximum length? How could we encrypt and save such long token in database?

I have the following query that takes minutes to get executed. When I started writing this query, uptil TVVA5.VVA_VAL it was OK but when I introduced TVVA6 it became slow and when I introduced TVVA7 it became slower and keeps becoming slower as I add TVVA columns. I found that until any 5 TVVA columns it works well. Any idea to optimize this query.

SELECT 
                [TCRD].[CRD_REQ_ID] AS [requestId],
                [TCTP].[CTP_CDE] AS cardType,
                [TCHD].[CHD_COD_EXT] AS codeCardHolder,
                [TCHD].[CHD_FRST_NAMES] AS firstNames,
                [TCHD].[CHD_INI] AS initials,
                [TCHD].[CHD_PFX_LST_NAME] AS prefixLastName,
                [TCHD].[CHD_LST_NAME] AS lastName,
                [TCHD].[CHD_TTL_PFX] AS titlePrefix,
                [TCHD].[CHD_TTL_SFX] AS titleSuffix,
                [TCHD].[CHD_DOB] dateOfBirth,
                [TCRD].[CRD_VAL_DTE] AS cardExpiryDate,
                [TCRD].[CRD_ISS_DTE] AS cardIssueDate,
                [TCHD].[CHD_NAT_CODE] AS natCode,
                [TCHD].[CGD_GDR_CDE] AS genderCode,

                [TPIC].[PIC_VAL] AS picture,
                [TSIG].[SIG_VAL] AS [signature],

                [TCRD].[CRD_NAME_ON_CARD] AS nameOnCard,
                [TORG].[ORG_CDE] AS organizationCode,
                [TNAT].[NAT_DESC_AR] AS nationalityArabic,
                TORG.ORG_FULL_NAME issuingAuthority,

                TVVA1.VVA_VAL nameArabic,
                TVVA2.VVA_VAL docmentType,
                TVVA3.VVA_VAL docmentNumber,
                TVVA4.VVA_VAL passportNumber,
                TVVA5.VVA_VAL phoneNumber,
                TVVA6.VVA_VAL professionEnglish,
                TVBV1.VBV_VAL passportImage,
                TVVA7.VVA_VAL cardPersonalizationDate,
                TVVA8.VVA_VAL printerSerialNumber,
                TVVA9.VVA_VAL placeOfBirthArabic,
                TVVA10.VVA_VAL addressInQatarArabic,
                TVVA11.VVA_VAL sponsorNameEnglish,
                TVVA12.VVA_VAL sponsorNameArabic,
                TVVA13.VVA_VAL residencyType
            FROM        
                TCHD                
            INNER JOIN
                TCRD
            ON
                [TCHD].[CHD_ID] = [TCRD].[CRD_CHD_ID]
            INNER JOIN
                TCTP
            ON
                [TCRD].[CRD_CTP_ID] = [TCTP].[CTP_ID]
            INNER JOIN
                TNAT
            ON
                [TCHD].[CHD_NAT_CODE] = [TNAT].[NAT_CODE]
            INNER JOIN
                TORG
            ON
                [TCRD].[CRD_ORG_ID] = [TORG].[ORG_ID]
            INNER JOIN
                TPIC                        
                cross apply (select [TPIC].[PIC_VAL] AS '*' for xml path('')) P ([picture])
            ON
                [TCRD].[CRD_ID] = [TPIC].[PIC_CRD_ID]
            INNER JOIN              
                TSIG
                cross apply (select [TSIG].[SIG_VAL] AS '*' for xml path('')) S ([signature])
            ON
                [TCRD].[CRD_ID] = [TSIG].[SIG_CRD_ID]
            INNER JOIN
                TVVA TVVA1
            ON
                TVVA1.VVA_PK_VAL = TCHD.CHD_ID 
            AND
                TVVA1.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'NAME_ARABIC' )
            INNER JOIN
                TVVA TVVA2
            ON
                TVVA2.VVA_PK_VAL = TCHD.CHD_ID 
            AND 
                TVVA2.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'DOCUMENT_TYPE' )
            INNER JOIN
                TVVA TVVA3
            ON
                TVVA3.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA3.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'DOCUMENT_NUMBER' )
            INNER JOIN
                TVVA TVVA4
            ON
                TVVA4.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA4.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PASSPORT_NUMBER' )
            INNER JOIN
                TVVA TVVA5
            ON
                TVVA5.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA5.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PHONE_NUMBER' )
            INNER JOIN
                TVVA TVVA6
            ON
                TVVA6.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA6.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PROFESSION_ENGLISH' )
            INNER JOIN
                TVVA TVVA7
            ON
                TVVA7.VVA_PK_VAL = TCRD.CRD_ID
            AND 
                TVVA7.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'CARD_PERSONALIZATION_DATE' )
            INNER JOIN
                TVBV TVBV1
                cross apply (select TVBV1.VBV_VAL AS '*' for xml path('')) PP ([passportImage])
            ON
                TVBV1.VBV_PK_VAL = TCHD.CHD_ID      
            AND 
                TVBV1.VBV_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PASSPORT_IMAGE' )
            INNER JOIN
                TVVA TVVA8
            ON
                TVVA8.VVA_PK_VAL = TCRD.CRD_ID
            AND 
                TVVA8.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PRINTER_SERIAL_NUMBER' )
            INNER JOIN
                TVVA TVVA9
            ON
                TVVA9.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA9.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PLACE_OF_BIRTH_ARABIC' )
            INNER JOIN
                TVVA TVVA10
            ON
                TVVA10.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA10.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'ADDRESS_IN_QATAR_ARABIC' )
            INNER JOIN
                TVVA TVVA11
            ON
                TVVA11.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA11.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'SPONSOR_NAME_ENGLISH' )
            INNER JOIN
                TVVA TVVA12
            ON
                TVVA12.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA12.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'SPONSOR_NAME_ARABIC' )
            INNER JOIN
                TVVA TVVA13
            ON
                TVVA13.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA13.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'RESIDENCY_TYPE' )
            WHERE                                       
                [TCRD].[CRD_REQ_ID] = 10720

I have created a form to collect data from students who are registering. However, I am encountering an issue where the data is automatically inserting into the SQL Server database, even though I have disabled the button that sends the data to the controller for processing and insert into the database.

I'm unsure why this automatic insert is happening and would appreciate if someone could take a look at it. Additionally, this error arises unexpectedly. My program was functioning properly, but suddenly, between 100 to 300 entries are being inserted into the database without any apparent reason. While registration continues to work as expected, this issue is frustrating and baffling.

[HttpPost]
public ActionResult SubmitRegistration(Tbl_Registration Registration, HttpPostedFileBase Picture, HttpPostedFileBase B_Form, HttpPostedFileBase B_Certificate, HttpPostedFileBase P_Report)
{
    try
    {
        if (activeAcademicID.HasValue)
        {
            Tbl_Registration newRecord = new Tbl_Registration
                    {
                        Registration_ID = Registration.Registration_ID,
                        Class_ID = Registration.Class_ID,
                        Academic_ID = activeAcademicID.Value,
                
                        Picture = Path.GetFileName(picturePath),
                        Picture_Path = Path.Combine("Applicant_Photo", Path.GetFileName(picturePath)),
                        B_Certificate = Path.GetFileName(bCertificatePath),
                        B_Certificate_Path = Path.Combine("Birth_Certificate", Path.GetFileName(bCertificatePath)),
                        B_Form = Path.GetFileName(bFormPath),
                        B_Form_Path = Path.Combine("Bay_Form", Path.GetFileName(bFormPath)),
                        P_Report = Path.GetFileName(pReportPath),
                        P_Report_Path = Path.Combine("Progress_Report", Path.GetFileName(pReportPath)),

                        Is_Active = true,
                        Created_On = DateTime.Now,
                    };

            int selectedClassID;

            if (int.TryParse(Request.Form["Class_ID"], out selectedClassID))
            {
                newRecord.Class_ID = selectedClassID;
            }

            dbContext.Tbl_Registration.Add(newRecord);
            dbContext.SaveChanges();

            string fatherName = Registration.Father_Name;
            string selectedClassName = dbContext.Tbl_Class
                        .Where(c => c.Class_ID == selectedClassID)
                        .Select(c => c.Class_Name)
                        .FirstOrDefault();

            string childName = Registration.NameOfApplicant;
            string subject = string.Format("Congratulations, {0}! Registration Successful", fatherName);
            string body = GenerateEmailBody(childName, fatherName, selectedClassName);
            string fatherEmail = Registration.Father_Email;

            SendEmail(fatherEmail, subject, body, Registration.NameOfApplicant, selectedClassName, formattedRegistrationNo, newRecord);

            TempData["SuccessMessage"] = "Data saved successfully.";

            return RedirectToAction("Index", "Frontend");
        }
        else
        {
            return RedirectToAction("Index");
        }
    }
    catch (DbUpdateException ex)
    {
        TempData["SaveErrorMessage"] = "Failed to save - please try again later." + ex;
        return View("Index");
    }
    catch (System.Data.Entity.Validation.DbEntityValidationException ex)
    {
        string propertyErrorDetails = "";

        foreach (var validationErrors in ex.EntityValidationErrors)
        {
            foreach (var validationError in validationErrors.ValidationErrors)
            {
                System.Diagnostics.Debug.WriteLine($"Property: {validationError.PropertyName} Error: {validationError.ErrorMessage}");
                propertyErrorDetails += $"Property: {validationError.PropertyName} Error: {validationError.ErrorMessage}<br/>";
            }
        }

        SendErrorEmail(Registration.NameOfApplicant, propertyErrorDetails);

        TempData["SaveErrorMessage"] = "Failed to save - please try again later.";

        return View("Index");
    }
}

I have tables with 50-70 million rows. Each row represents an object with a unique ID number. A table is for a specific year, say 2015. I need to return the IDs contained in the 2015 table that are contained in subsequent year tables.

First I query the initial year, 2015, and then I use R to iterate over the years and retrieve rows with IDs in the previous table.

query <- glue::glue(sprintf(
            "SELECT id, col_1, col_2
             FROM schema.data_{year}
             WHERE schema.data_{year}.id in ('%s')",
         toString(prev_ids)), year=year)
res <- dbGetQuery(con, query)

For a small sample this is fine, but gets increasingly slow as n increases.

I am fairly new to SQL so I am wondering, how should I approach this?

I am accessing SQL Server using a Docker container, and have done using this step:

1. docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Password@123" -e "MSSQL_PID=Express" -p 1433:1433 -v mssqlserver_volume:/var/opt/mssql --name mssql -d mcr.microsoft.com/mssql/server:2019-CU15-ubuntu-20.04 (I use this code in cmd to create a container)

2. Continue command docker ps

3. The container able to created but the status only showed running for 1 seconds, then keep exited. The logs keeps shows this when I try to run the container:

   sqlservr: This program requires a machine with at least 2000 megabytes of memory. /opt/mssql/bin/sqlservr: This program requires a machine with at least 2000 megabytes of memory.

Now I not able to proceed with command this code

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Password@123

since the container keeps showing "exited".

The methods have I tried is to restart and also swapping memory from wsl1 to wsl2 but it didn't solve my problem

Kindly need assistance to solve this problem.

Logs for SQL Server container

The command

I have a table that has multiple rows of similar data as per below:

I want to be able to run a query that returns only the rows with the maximum Version # and grouped by Name. Regardless of what the Colour is.

So the result of the query will look like this:

Thanks

I tried this

SELECT Name, MAX(Version), Colour

FROM DB

GROUP BY Name

But its says Colour needs to be in either an aggregate function or the GROUP BY clause. When I put it in the GROUP BY clause it returns rows with all the different colours and not just the MAX version #.

Application - c# .net8 Trying to use MSAL with distributed caching as sql

Application works fine locally but when deployed to Kubernetes it throw below error -

at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
     at Microsoft.Data.SqlClient.TdsParser.EnableSsl(UInt32 info, SqlConnectionEncryptOption encrypt, Boolean integratedSecurity, String serverCertificateFilename)
     at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert)
     at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
     at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
     at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
     at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
     at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
     at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
     at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
  --- End of stack trace from previous location ---
     at Microsoft.Extensions.Caching.SqlServer.DatabaseOperations.GetCacheItemAsync(String key, Boolean includeValue, CancellationToken token)
     at Microsoft.Extensions.Caching.SqlServer.DatabaseOperations.RefreshCacheItemAsync(String key, CancellationToken token)
     at Microsoft.Extensions.Caching.SqlServer.SqlServerCache.RefreshAsync(String key, CancellationToken token)
     at Microsoft.AspNetCore.Session.DistributedSession.CommitAsync(CancellationToken cancellationToken)
     at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
  ClientConnectionId:f5888c74-0cab-48c8-a5b1-3d9f7ecc5ce1
  Error Number:-2146893019,State:0,Class:20

DB connection works fine if I try to fetch some data but with session and distributed cache it fails with error. Connection string looks like, DB is inside a VM -

xx.xxx.x.x,xxxx;Database=DBName;User Id=abc;Password=xyz

Code which is used for adding distributed cache is as follow -

builder.Services
    .AddLogging()
    .AddDistributedMemoryCache()
    .AddDistributedSqlServerCache(options =>
    {
        options.ConnectionString = config;
        options.SchemaName = "dbo";
        options.TableName = "TokenCache";
        options.DefaultSlidingExpiration = TimeSpan.FromHours(24);
    });

builder.Services.Configure<MsalDistributedTokenCacheAdapterOptions>(options =>
{
    // Optional: Disable the L1 cache in apps that don't use session affinity
    //                 by setting DisableL1Cache to 'true'.
    options.DisableL1Cache = false;

    // Or limit the memory (by default, this is 500 MB)
    options.L1CacheOptions.SizeLimit = 500; // 1 GB

    // You can choose if you encrypt or not encrypt the cache
    options.Encrypt = false;

    // And you can set eviction policies for the distributed
    // cache.
    options.SlidingExpiration = TimeSpan.FromHours(2);
});

Found another error might be related -

fail: Microsoft.Identity.Web.TokenCacheProviders.Distributed.MsalDistributedTokenCacheAdapter[103]
  [MsIdWeb] DistributedCache: Write Connection issue. InRetry? False Error message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught) 
  Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
   ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
     at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions)
     at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](Boolean receiveFirst, Byte[] reAuthenticationData, CancellationToken cancellationToken)
     at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
     at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
     at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
     at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
     at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
     at Microsoft.Data.SqlClient.SqlConnection.InternalOpenAsync(CancellationToken cancellationToken)
  --- End of stack trace from previous location ---
     at Microsoft.Extensions.Caching.SqlServer.DatabaseOperations.SetCacheItemAsync(String key, Byte[] value, DistributedCacheEntryOptions options, CancellationToken token)
     at Microsoft.Extensions.Caching.SqlServer.SqlServerCache.SetAsync(String key, Byte[] value, DistributedCacheEntryOptions options, CancellationToken token)
     at Microsoft.Identity.Web.TokenCacheProviders.Utility.Measure(Task task)
     at Microsoft.Identity.Web.TokenCacheProviders.Distributed.MsalDistributedTokenCacheAdapter.L2OperationWithRetryOnFailureAsync(String operation, Func`2 cacheOperation, String cacheKey, Byte[] bytes, Boolean inRetry)
  ClientConnectionId:cf6f7680-cd59-47fd-9c80-a765527b0b30
  Error Number:-2146893019,State:0,Class:20

I have 2 table which storing Students data

Table A

Table B

Now I plan to display all student data as below

SELECT
    CASE WHEN a.name = b.name THEN a.name ELSE a.name AS Student_Name,
    CASE WHEN a.id = b.id THEN a.id ELSE a.id AS Student_Id,
    CASE WHEN a.course = b.course THEN a.course + ',' + b.course ELSE a.course AS Student_Course
FROM student a
FULL JOIN student b ON a.name=b.name

But with above query I only able to get the result as

Which Chris become NULL in final result...How can I make it to select all? Have suffer on this whole week.

I am trying to Upgrading Cyberpanel with The following command and i got this error and can not access to cyberpanel admin Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock

sh <(curl https://raw.githubusercontent.com/usmannasir/cyberpanel/stable/preUpgrade.sh || wget -O - https://raw.githubusercontent.com/usmannasir/cyberpanel/stable/preUpgrade.sh)```