SQL 2016 uses little RAM

I have a SQL 2016 server, version:
(SP2-CU4-OD) (KB4482972) – 13.0.5239.0 (X64)

The server is a Win 2016, with 64 GB of RAM.
I have configured the maximum memory for SQL to 54 GB, but it uses less than 1 GB.
Data has been migrated from an older SQL version.

Can someone help me?

Thank you

c # – Error in dynamic reporting of sql server with .net

I am doing this post because I have a problem with a project I am working on, it should be noted that I did not do this and I am making some corrections on it, but I have encountered an error that I could not resolve, it turns out that there is a module in which a password change is made, it works well in the database and performs the change for the current user, but a method is invoked to activate the user in the SQL Server report module, the method invoked is like this:

protected void btncambiar_Click(object sender, EventArgs e)
    {
        try
        {
            string contranueva = txtconfirmar.Text;
            string contractual = txtactual.Text;

            NegocioConfiguracion clsconfiguracion = new NegocioConfiguracion();
            Boolean sw = clsconfiguracion.cambiarcontra(contranueva,contractual);
            string login = HttpContext.Current.Session("nombreUsuario").ToString();
            String rol = Session("rolUsuario").ToString();

            if (rol == "Administrador" || rol == "GERENTE" || rol == "DIRECTOR" || rol == "RESIDENTE" || rol == "INVITADO")
            {
                try
                {
                    Observatorio.SSRSUsuarios.UsrManager SSRSManager = new Observatorio.SSRSUsuarios.UsrManager();
                    SSRSManager.activarUsuario("374F6CD15A364C03982015FB958E31ED", login, contranueva, login);
                }
                catch (Exception E)
                {
                    using (TextWriter tw = new StreamWriter(Server.MapPath("/Logs/") + "log.txt", true))
                    {
                        tw.WriteLine("Error Cambiarcontraseña - btncambiar_Click " + E.Message + " " + DateTime.Now);
                    }

                    MostrarMensaje("La contraseña fue cambiada correctamente, pero fallo el modulo de reportes.");
                }
            }

            if (sw)
            {
              MostrarMensaje("Su Contraseña ha sido modificada exitosamente");
            }
            else
            { 
              MostrarMensaje("La Contraseña actual que ha proporcionado no es válida"); 
            }

        }
        catch (Exception E)
        {
            MostrarMensaje(E.Message);
        }
    }

What this method does is take the parameters of the previous key and the new key and identify the role of the user and then call a password change method from of the SSRSManager module which makes a WEB reference, the case is that it then enters the method to activate the user:

public bool activarUsuario(string token, string usuario, string clave, string nombre) {
        object() results = this.Invoke("activarUsuario", new object() {
                    token,
                    usuario,
                    clave,
                    nombre});
        return ((bool)(results(0)));
    }

Receive the token, log in the previous password and the new password and at this point the code fails and returns the following message:

Image_Error_Capture

I downloaded the project to the test server to test it directly on an ISS but it still does not know if someone has already worked with this type of projects with dynamic reports on SQL Server, I do not not know if I should check a connection on the server or a configuration.

Does anyone know why differential backups of SQL Server 2005 will not be restored correctly on SQL Server 2016? And how can I fix it?

We plan to migrate from the old SQL 2005 server to the SQL 2016 server with differential backup, but I read an article that differential backup does not support properly.

sql – Overlap intervals using mysql

    +------+------------+------------+
    | id   | start_date | end_date   |
    +------+------------+------------+
    |    1 | 2019-01-01 | 2019-01-12 |
    |    1 | 2019-01-10 | 2019-01-27 |
    |    1 | 2019-01-13 | 2019-01-15 |
    |    1 | 2019-01-18 | 2019-01-25 |
    |    1 | 2019-02-10 | 2019-02-15 |
    |    2 | 2019-01-10 | 2019-01-15 |
    +------+------------+------------+

how to merge overlapping intervals and get the result below in mysql (8.X)?

    +------+------------+------------+
    | id   | start_date | end_date   |
    +------+------------+------------+
    |    1 | 2019-01-01 | 2019-01-27 |
    |    1 | 2019-02-10 | 2019-02-15 |
    |    2 | 2019-01-10 | 2019-01-15 |
    +------+------------+------------+

Here are the commands that can be used in mysql to create the table =>


    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-01', '2019-01-12');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-10', '2019-01-27');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-13', '2019-01-15');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-01-18', '2019-01-25');
    insert into interval_dates(id, start_date, end_date) values(1, '2019-02-10', '2019-02-15');
    insert into interval_dates(id, start_date, end_date) values(2, '2019-01-10', '2019-01-15');

can you share an elegant solution without inserting it in the intermediate table in mysql (8.X)?

pl sql – Null values ​​in Oracle

So guys, I have a problem when it comes to inserting data into my Oracle tables, I am inserting the data via a PLSQL routine, and I would like to know how I & # 39; Inserts data into a respective table, but if one of the fields is empty, I want the NULL value not to be inserted, follow my routine below:

DECLARE
   ID_OCORRENCIA NUMBER;
BEGIN

INSERT INTO OCORRENCIA (CLASSIFICACAO_CENIPA, DATA_UTC, UF, MUNICIPIO_OCORRENCIA, LATITUDE, LONGITUDE, NUMERO_FICHA, HISTORICO, HORA_UTC)
VALUES (:P2_CLASSIFICACAO_CENIPA, :P2_DATA_UTC, :P2_UF, :P2_MUNICIPIO_OCORRENCIA, :P2_LATITUDE, :P2_LONGITUDE, :P2_NUMERO_FICHA, :P2_HISTORICO, :P2_HORA_UTC)
RETURNING ID INTO ID_OCORRENCIA;

INSERT INTO TIPO_OCORRENCIA (ID_OCORRENCIA, TIPO) VALUES (ID_OCORRENCIA, :P2_TIPO_OCORRENCIA);
INSERT INTO TIPO_OCORRENCIA (ID_OCORRENCIA, TIPO) VALUES (ID_OCORRENCIA, :P2_TIPO_OCORRENCIA_1);

END

What happens here is that when inserting an instance type into the TIPO_OCORRENCIA table, it always inserts into the table the value that I am informing and also NULL. There is a way to prevent this from happening, and at home, I wanted to insert a single datum, have only one datum in my table and not two.

SQL Server 2012 – Stuff With XML Path

DECLARE @v NVARCHAR(MAX)=''
select @v =STUFF((SELECT CASE WHEN MeterID=0 THEN '' ELSE CONVERT(VARCHAR(50),MeterID) + ',' END AS (text()) 
                        FROM (SELECT DISTINCT mr.MeterID FROM #FilteredMeterIDs mr WHERE mr.apartmentID = fmr.apartmentID)m
                        FOR XML PATH,TYPE).value('(.)(1)','NVARCHAR(MAX)'),1,0,'')
                    FROM #FilteredMeterIDs fmr
select @v

Result:
181,834,

BUT when I use the query below, it gives NULL as RESULT,

DECLARE @v NVARCHAR(MAX)=''
select @v =STUFF((SELECT CASE WHEN ISNULL(MeterID,0)=0 THEN '' ELSE CONVERT(VARCHAR(50),MeterID) + ',' END AS (text()) 
                        FROM (SELECT DISTINCT mr.MeterID FROM #FilteredMeterIDs mr WHERE mr.apartmentID = fmr.apartmentID)m
                        FOR XML PATH,TYPE).value('(./text())(1)','NVARCHAR(MAX)'),1,0,'')
                    FROM #FilteredMeterIDs fmr
select @v

Result:
NO

I used "/ text ()" to improve performance. What's wrong, can anyone tell me ?!

By executing the query below in SQL, I get "No table space error". What a mistake am i doing here

SQL

select

Account.Account key,
Account.Account NBR,
Account.vsc key,
Accoun.Date key

Department. Department NBR,
Department.Department Date key,

Sale.sale DT Key,
Sale.sale ID,
sum(sale) as Total

vendor Account .Account key,
Vendor Account. name,
Vendor Account. CD
vendor Account.vsc key

FROM
Account
inner join Department on Account.Datekey = Department.date key
inner join Sales on Sale.sale DT key = Department.deaptment date key
left join Vendor on account. account key = vendor account. account key
left join Vendor on Account. vsc key = vendor account.vsc key

SQL Server – one-to-many relationship getting recent records plus a column from the previous record

You can get the previous row value for a column using the LAG function and limit to the most recent row using ROW_NUMBER:

SELECT ProductId,
  RecentAmount,
  PreviousAmount,
  Status
FROM
(
SELECT
  t1.ProductId,
  t2.Amount AS RecentAmount,
  LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,
  t1.Status
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.ProductId = t2.ProductId
) t
WHERE Rn = 1

Your sample data, however, is a bit confusing. For PD_2, your expected results indicate that 1000 is the most recent amount and 900 the previous amount, however, the record 1000 has an Id value less than the record 900. Generally, one would assume that higher identification value records were entered differently from lower records.

In any case, you can see a working example of the LAG function, combined with ROW_NUMBER to select the single row here. This example uses a specific order in the LAG (DESC) and ROW_NUMBER (ASC) functions to return your expected result set.

If your sample data is incorrect and the record 1000 should in fact be a higher Id value, you can modify the LAG and ROW_NUMBER functions from

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS Rn,

at

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,

as shown in this example.

sql server – How to update from one table to another

What is the best way to update 150 rows in a table from a temporary table?

The temporary table includes
Col1 col2 col3 col4

Table 1 includes
Col1 col2 col3 col4

Both have values ​​in them. Table1 has 1,700 lines and the temporary table has 150 lines. I need the 150 rows from the temporary table to enter table1.

What is the request for this?

SQL server – Query sometimes takes 25 seconds to execute

After executing a set of usual data access queries, SQL Server 2019 enters a state where executing a specific query takes almost exactly 25 seconds. Under normal conditions, it takes 200 ms.

When I modify the query in an apparently unrelated way (remove ORDER BY, some columns from the SELECT list or from the AND condition), it executes normally, when I restore it to its original form, it takes 25 again seconds.

SQL Server maintains this state for approximately 10 times the query is run. Then it continues to operate normally. No trace of deadlock or deadlock in SQL Profiler.

Why could this happen and how can we track the problem?