sql – In the classifications in MySQL, what would be the column Pad_attribute?

When I display the rules for characters installed in MySQL with the command:

SHOW COLLATION;

One of the returned columns is the Pad_attributeI would like to know your usefulness and what are the default definitions, PAD SPACE and NO PAD.

Passover variable in json_value in SQL Server 2016

As answered for JSON_QUERY in this question the same is true for JSON_VALUE as well as.

Docs on JSON_VALUE

In SQL Server 2017 (14.x) and in the SQL Azure database, you can provide a
variable as the path value.

Before SQL Server 2017 you will have to build the query dynamically.


Examples

Printing the query on the SQL Server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL VARCHAR(MAX);

SET @SQL ='
select 
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"'+cast(@id as varchar(4))+'"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';
PRINT (@SQL);

Execution of the query on the SQL Server 2016

declare @id int;
set @id = 1754;

DECLARE @SQL NVARCHAR(MAX);
DECLARE @Parameterdefinition nvarchar(20) ='@id int';
SET @SQL ='
select 
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap.***"+cast(@id as varchar(4))"***.value'')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),''$.dataMap."1754".value'')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 32 and displayrequestid < 100;';

EXEC SP_EXECUTESQL  
@SQL,@Parameterdefinition,@id;

DB <> violin

Get baskets + value (Api or SQL)

I wanted to analyze how many trolleys (+ their value) were abandoned. I've found a lot of plugins showing that … but I'd rather get the data via API / SQL … do you have an approach for me?

Thank you!
E.

t sql – TSQL script returns erroneous result

I'm trying to find out if a particular index name test exists in all databases with the help of the code below. The result obtained is still NOT EXIST for all databases in an instance. However, the index does exist in only a few databases. Can any one please indicate where the code is wrong?

DECLARE @Name AS VARCHAR(100)
DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases where name not in ('master','msdb','model','tempdb')
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
--Check if index exist    
   EXECUTE('USE (' + @Name + ')')
   If 1 = ((SELECT COUNT(*) as index_count
        FROM sys.indexes 
        WHERE object_id = OBJECT_ID('dbo.TestTable')
        AND name = 'Test')) 
    Print 'INDEX EXIST ' + @Name;
    ELSE
    Print 'INDEX DOES NOT EXIST ' + @Name;
   FETCH NEXT FROM dbCur
INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur

Result:

THE INDEX DOES NOT EXIST DB1
INDEX DOES NOT EXIST DB2
THE INDEX DOES NOT EXIST DB3
THE INDEX DOES NOT EXIST DB4

database – Job SQL Server 2016 that copies files from a Windows server to a Linux server

I have a trigger that generates XML files and leaves them in a folder. These files will be used by another application. The company's policies do not allow to extract them from the folder where I create them, because this folder is on the server. where the database is installed, I was thus asked to create a job moving these files on a SFTP linux server, otherwise the trigger could be sent directly to the other server.
I therefore need a script that can do this job.

CREATE PROCEDURE dbo.pa_gea_crear_xml_bmcbridge
(
@p_ntra INTEGER – # transaction number
)
AS
To start

    DECLARE @l_Id INT                   --# ERROR O EXITO DE LA OPERACION
    DECLARE @l_Msje VARCHAR(1000)       --# Mensaje de error o exito de la operacion
    DECLARE @l_Ruta VARCHAR(250)        --#Ruta del directorio
    DECLARE @l_Trx_typ VARCHAR(32)      --#Valor del campo TRX_TYP
    DECLARE @l_Sql_Cmd VARCHAR(1000)    --#sentencia sql
    DECLARE @l_Sql_Shell VARCHAR(1000)  --#setencia para la consola shell
    DECLARE @l_Nom_Arch VARCHAR(16)     --#nombre del archivo

    BEGIN TRY

        SET @l_Ruta  ='T:RemesasLiq_RemeBackup';
        SET @l_Sql_Cmd = '';
        SET @l_Sql_Shell = '';
        SET @l_Trx_typ = '';
        SET @l_Nom_Arch = 'TLOG';

        SELECT @l_Trx_typ = LTRIM(RTRIM(TRX_TYP)) FROM EXP_POSLOG WHERE ID = @p_ntra;

        IF (@l_Trx_typ = 'TenderOutflow')
        BEGIN
            SET @l_Nom_Arch = 'TLOG'+'_'+Cast(@p_ntra as varchar(10))+'.XML'
            SET @l_Sql_Cmd = 'SELECT POSLOG FROM EXP_POSLOG WHERE ID = '+ Cast(@p_ntra as varchar(10));
            Set @l_Sql_Shell = 'EXEC xp_cmdshell ' +char(39) +'bcp' +' "'+@l_Sql_Cmd+'"'+  ' QUERYOUT '+ '"'+@l_Ruta+@l_Nom_Arch+'"' +' -T -c -t"t"'+char(39);                                                             
            Execute  (@l_Sql_Shell) 

        END


    END TRY
    BEGIN CATCH

        SET @l_Id =   ERROR_NUMBER()
        SET @l_Msje = ERROR_MESSAGE()

        SELECT @l_Id as 'id', @l_Msje as 'msje'

    END CATCH

END

GO

CREATE TRIGGER tr_gea_exp_poslog
ABOUT EXP_POSLOG
AFTER INSERT
AS
DECLARE @l_IdInsertado INTEGER
To start
SET NOCOUNT ON;
select @l_IdInsertado = the inserted ID;
EXEC pa_gea_crear_xml_bmcbridge @l_IdInsertado;
END
GO;

sql – Ranking of zombie killers

One-to-one, we have very different formatting styles for our code. Do not hesitate to ignore this difference and not to see a comment on your style (unless you prefer mine, in which case thank you for doing so).


Alias

I really do not like the practice of using short pseudonyms for tables. I have never seen anything particular and it makes me more difficult to understand. You can get easy aliases (for example, q -> questions or a -> answers) for very few characters.

I also love it a lot when each column of my output has a real name, so it's easier to understand. In this case, I would like alias COUNT(*) as (Zombie Kill Count) or something.

In addition, you can use the alias in your ORDER BY, which I find conceptually easier to understand.


Performance

One thing that jumps out at me is that you use a function in your WHERE clause. This can cause the cardinality estimator to get really confused (I do not know if SEDE still uses the inherited cardinality estimator, if they do, the problem is magnified) and you may end up with less than ideal query plans. One way to avoid this is to not include your column in the function. For example, instead of this:

SELECT *
  FROM Posts
  WHERE DATEDIFF( MONTH, CreationDate, @startDate ) > 1;

You could do that

SELECT *
  FROM Posts
  WHERE CreationDate < DATEADD( MONTH, -1, @startDate );

This allows the cardinality estimator to use the available statistics on the CreationDate column without confusing it with the function.

If you group by a value that does not actually add a new level of granularity (Users.DisplayName does not really change the grouping), it may be more efficient to use an aggregate at this location; you get the same result, but less expensive.

SELECT MAX( u.DisplayName ) -- I'm cheaper than grouping on me

Play with the APPLY operator can be fun too; I have often seen him perform better than a NOT EXISTS, as if (with a GoodAnswers.Id IS NULL in the WHERE clause to obtain an exclusive external application). Note - this one has not been tested, so YMMV. APPLY can be quite situational, but I always have fun writing them.

OUTER APPLY ( SELECT TOP( 1 ) Id
                FROM Posts OldAnswers
                WHERE OldAnswers.ParentId = Questions.Id
                  AND OldAnswers.CreationDate < @startDate
                  AND OldAnswers.Score > 0 
                ORDER BY ( SELECT NULL ) ) GoodAnswers

Overall, I arrived with something like this.

DECLARE @startDate date;
SET @startDate = CONVERT( datetime, '2019-07-21' );

SELECT TOP ( 25 )
       MAX( Answerers.DisplayName ) (Answerer Name),
       COUNT( * ) (Zombie Kill Count)
  FROM Posts Questions
    INNER JOIN Posts Answers
      ON Questions.Id = Answers.ParentId
    INNER JOIN Users Answerers
      ON Answers.OwnerUserId = Answerers.Id
    OUTER APPLY ( SELECT TOP ( 1 )
                         Id
                    FROM Posts OldAnswers
                    WHERE OldAnswers.ParentId = Questions.Id
                      AND OldAnswers.CreationDate < @startDate
                      AND OldAnswers.Score > 0
                    ORDER BY ( SELECT NULL )) GoodAnswers
  WHERE Questions.PostTypeId = 1
    -- Target old questions with new answers
    AND Questions.CreationDate < DATEADD( MONTH, -1, @startDate )
    AND Answers.Score > 0
    AND Answers.CreationDate > @startDate
    -- That didn't already have a good answer
    AND GoodAnswers.Id IS NULL
    -- Remove low quality/closed/deleted/community owned questions
    AND Questions.Score >= 0
    AND Questions.ClosedDate IS NULL
    AND Questions.CommunityOwnedDate IS NULL
    AND Questions.DeletionDate IS NULL
  GROUP BY Answerers.Id
  ORDER BY (Zombie Kill Count) DESC;

Some additional notes:

  • Unfortunately, I had trouble finding information about available resources.
    indices on SEDE. The best resource I found was this post. he
    does not list any index on the Posts table, although we have
    one on Users.Id. As such, there are no good suggestions that I have
    to join here; you're probably doing as well as you can.
  • You have a long enough WHERE clause in progress; it'll probably dilute cardinality even further, but I do not see a reasonable
    alternative.

How to make the meeting of several columns, different tables to form a table in a SQL server?

I want to know, how can I join 2 tables with totally different data and columns to form a new table with the union of its columns.

Examples;

enter the description of the image here

and put this column from another table to the new table.

enter the description of the image here

dbms – How to write a SQL database to a file?

I write a password manager in which one of the features that I would like to include is the ability to transfer the database of passwords from one computer to another or to export this database. So since my password database will be stored as an SQL database (it could be any SQL server such as MySQL, Microsoft SQL Server, PostgreSQL, Oracle or even SQLite). I need to be able to transfer this database from one computer to another. Therefore, I think that it is necessary to write this database into a file. If not, how could I make the database portable?

SQL Server 2012 – The Report Service Has Shut Down Unexpectedly

The SSRS service stopped unexpectedly and the workaround was to recycle the application pool and then restart the service, but no indication of what happened.

Looking at the error log below:

  C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesLogfilesReportServerLog.log

  SQL1

  Microsoft Windows NT 6.2.9200

  6.2.9200

  7104

  Hypervisor



rshost!rshost!1af8!08/21/2019-08:46:47:: e ERROR: CLR runtime initialization error: error type=3, hr=0x8007000e, updated=1, state=15.

rshost!rshost!1af8!08/21/2019-08:46:47:: e ERROR: Failure happened during critical phase of CLR initialization.

rshost!rshost!1af8!08/21/2019-08:46:47:: e ERROR: CLR runtime initialization failed. Generating a dump and exiting the proces

s.

But you do not know what this error means, do you have any idea?

to plan a full, differential, and cross-country backup in SQL Server 2016

I have been studying backup scheduling with the SQL Server Agent and maintenance plans (which lead to the SQL Server Agent).

My first concern is what is the best way to plan my backups?

I want a full daily backup, a differential backup every hour and a backup of the transnational log every 15 minutes. My second concern, is it a good practice?

I noticed a problem when I used SQL Server Agent. My full and differential backup overwrites. I agree that the full backup should be overwritten, but not for the differential backup, because when I have to recover it, I only have a differential backup, which is not the case. purpose of differential backups. How can I not let this overwrite on previous backups?

The last concern and question is how to implement backup planning. I will use maintenance plans and I can schedule three different backups. One for the complete, one for the differential and one for the transnational. Is this the best practice for a database used daily by users (at least 2,000 transactions per day)?