Tag: sql

java – Erro ao executar teste de um data source sql server no was 8.5

Erro ao testar a conexão do data source SQL Server no servidor WebSphere 8.5 com java 8 dá o seguinte erro:

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “No appropriate protocol, may be no appropriate cipher suite specified or protocols are deactivated”. DSRA0010E: Estado de SQL = 08S01, Código de Erro = 0.

(!(Erro ao testar o datasource pelo websphere)(1))(1)

Tentei alterar as configurações de confiabilidade do was, porém nada aconteceu. OBS.: Esse meu WebSphere foi copiado de outra máquina, que também está apresentando o mesmo problema. E só conseguimos testar os datasources do DB2, mas pra sql server dá erro.

sql server – Sql Update on Row_Number

I have a table with few columns say, FirstName, Lastname, Phonenumber, Accesslevel

AA , BB , +447000000000, AL1

CC , DD , +447800000000 , AL2

AA , BB , +447000000000 , AL3

If the phoneNumbers and Lastname are same, I want the below results (I mean I need to Update the table instead of just select)

FirstName, Lastname, Phonenumber, Accesslevel

AA , BB , +447000000000, AL1, AL3

CC , DD , +447800000000 , AL2

AA , BB , +447000000000 , AL3

The script I have doesn’t do what i Want,

SET (AccessLevel) = stuff ( ( SELECT
                          ', ' + (ExtAccessLevelIDList)
                  FROM
                          (Table) t1
                  where t1.PhoneNumber = t2.PhoneNumber
        FOR XML PATH ( '' ) ) , 1 , 1, '' )
from (Table) t2```

sql server – Inserting a row in a two tables having

I have two tables each have a foreign key from the other table

CREATE TABLE Nurse (
  Employee_ID int(11) NOT NULL,
  Care_Center_Name varchar(50) NOT NULL,
  Date_Hired date ,
  Certificate varchar(50) ,
  foreign key (Employee_ID) references Person(Person_ID)
)

CREATE TABLE Care_center (
  Name varchar(50) NOT NULL,
  Nurse_ID int(11) NOT NULL,
  RN varchar(30) ,
  Location varchar(50) NOT NULL,
  primary key (Name),
  foreign key (Nurse_ID) references Nurse(Employee_ID)
)

ALTER TABLE Nurse (
ADD FOREIGN KEY (Care_Center_Name) references Care_center(Name)
)

when i try to insert in any table an error occur
this is the error

#1452 – Cannot add or update a child row: a foreign key constraint fails

How can i insert safely?

sql server – Error converting data type varchar to numeric

I keep getting this error and I dont understand why. I checked all CONVERT functions but not sure where is the issue.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This is the query and you can see Line 1 does not have any type of conversion.

SELECT 
  'Entity Code|Department Code|Job Code|Pay Code|Pay Period Code|Employee Code|Fiscal Year|Time 
  Class|Unit Type|Amount|Home Entity Code|Home Department|Pay Period End Date' AS PRL_HEADERR
UNION ALL SELECT       
  (ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DIST_COMPANY, 4))), '')    
  + '|' + ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DST_ACCT_UNIT2, 4))), '')   
  + '|' + ISNULL((SUB.JOB_CODE2), '')                      
  + '|' + ISNULL((SUB.PAY_CODE2), '')                      
  + '|' + ISNULL((SUB2.PAYPERIOD), '')                     
  + '|' + ISNULL(
      (CASE WHEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),1,1)='0' 
      THEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),2,LEN(RIGHT(REPLICATE(0, 7) + 
      SUB.EMPLOYEE, 7))) 
      ELSE RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7) END), '')    
  + '|' +       ISNULL((SUB.FISCAL_YEAR), '')                    
  + '|' +       ISNULL((SUB.TIME_CLASS), '')                     
  + '|' +       ISNULL((SUB.UNIT_TYPE), '')                      
  + '|' +       ISNULL((SUB.UNIT_VALUE), '')                     
  + '|' +       ISNULL(HOME_ENTITY_CODE, '')                     
  + '|' +       ISNULL(HOME_DEPARTMENT, '')                     
  + '|' +     ISNULL(PAY_PER_END_DATE, ''))  AS PRL_HEADER
FROM
 (
  SELECT
     TOP 100 PERCENT DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Hours' AS UNIT_TYPE,
     HOURS AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE 
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  UNION ALL
  SELECT TOP 1000
     DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Amount' AS UNIT_TYPE,
     WAGE_AMOUNT AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE 
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  ORDER BY
     EMPLOYEE,
     PER_END_DATE 
 )
 AS SUB 
 INNER JOIN
  (
     SELECT
        ROW_NUMBER() OVER (ORDER BY PP_END_DATE) AS ROWNUM,
        SUB3.PP_END_DATE,
        (
           (CONVERT(VARCHAR(4), SUB3.PP_END_DATE, 112) + '-') + ROWNUM
        )
        AS PAYPERIOD
     FROM
        (
           SELECT DISTINCT
              TOP 100 PERCENT PER_END_DATE AS PP_END_DATE 
           FROM
              TEST_TABLE
           WHERE
              (
                 PER_END_DATE >= CAST('20200101' AS DATETIME)
                 AND PER_END_DATE <= CAST('20201231' AS DATETIME) 
              )
           ORDER BY
              PER_END_DATE 
        )
        AS SUB3 
  )
  AS SUB2 
  ON SUB2.PP_END_DATE = SUB.PER_END_DATE

docker – After many successive and successful queries SQL Server starts refusing connections

I have a GraphQL server which uses typeorm to connect to a SQL Server, also it runs inside a docker container, connection parameters are:

  let connection = await createConnection({
    type: "mssql",
    host: HOST,
    port: PORT,
    database: DB,
    username: USER,
    password: PASSWORD,
    // NOTE: Use typeorm-model-generator for Entities
    logging: false,
    synchronize: true,
    requestTimeout: 300000,
    connectionTimeout: 300000,
    entities: ("./models/mssql"),
    pool: {
      max: 1000, min: 1,
      idleTimeoutMillis: 3000,
      evictionRunIntervalMillis: 1500000,
    },
    options: {
      encrypt: false,
    }
  })

Problem:
After numerous sucessful queries the SQL Server starts refusing connections with the error:

‘ConnectionError: Failed to connect to x.x.x.x:port – connect ECONNREFUSED x.x.x.x:port’

The problem happens when query a specific type which looking down the hierarchy has a lot of resolvers, also the amount of data varies.
The more data the more likely that the problem will occur.
What also interesting is that it is more likely that the problem will occur if the program is running inside a container or as PM2 service.

I’ve tested the same data where connections are refused inside a container and not refused in VS Code debugger or when run from terminal.
I tried fiddling with the pool options which gave modest results.
I’ve also checked that i do not exceed the amount of connections allowed by the DB.
I’ve checked the logs on the SQL Server, there are no issues or errors in them at the time I make requests. I’ve just now added support for tracking log attempts on the SQL Server and it now registers some 776 successful logins from this application in period 15:03:46.01 – 15:04:48.07 and after that I assume the I get the errors

java – Connection error sql remote communications link Failure g cloud

Hello at all I try to connect through a server Tomcat hosted on google cloud to my host DB (Green Geek) I have donats created two rule firewall between /out on google is one on GreenGreek for the sql Distant cepandant after multiple attempt the system answers me:
Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
I attach my java ee code

private Connection appelBD(String dataBase, String user, String motDePasse) {
    Connection connection = null;

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
    } catch (ClassNotFoundException e1) {

        modelI18n = new ModelI18n();
        modelI18n.setName(e1.getMessage());
        modelI18n.setUrl(e1.getLocalizedMessage());

        e1.printStackTrace();
    }
    try {
        connection = DriverManager.getConnection("jdbc:mysql://184.154.119.210:3306/" + dataBase, user, motDePasse);
        /*
         * MysqlDataSource ds = new MysqlDataSource(); ds.setDatabaseName(dataBase);
         * ds.setUser(user); ds.setPassword(motDePasse);
         * ds.setServerName("184.154.119.210"); ds.setServerTimezone("UTC");
         * ds.setPort(3306);
         */
        return connection;
    } catch (Exception e) {

        modelI18n2 = new ModelI18n();
        modelI18n2.setName(e.getMessage());
        modelI18n2.setUrl(e.getLocalizedMessage());

        return null;
    }
}

would have leads to help me thank you in advance

sql server 2016 – Dirty buffer pages after issuing CHECKPOINT

I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a “cold” read a well as I can. Part of that is clearing the buffer cache before performing the queries. From everything I can find dirty buffer pages are supposed to be written during a checkpoint. However, even after issuing a CHECKPOINT, there still seem to be 169 dirty pages of my database in the buffer pool (assessed via SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id=7 AND is_modified=1).

Is there anything I am misunderstanding about checkpoints or the content of sys.dm_os_buffer_descriptors? If not, why do I still have dirty pages after they were supposedly written away?

sql server – Passing database name to input parameter from a stored procedure

I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn’t figure out what is the issue my code.
Hopefuly, you guys can help me out !

I have few database and everyday, a new database is created with XXXXX-YEARMONTHDAY as name.
What i want to achieve is to pass a new database name as input parameter from my stored procedure.

When it’s hard written, it’s working like a charm :

GO
/****** Object:  StoredProcedure (dbo).(WinccToAlarms)    Script Date: 03-12-20 08:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE (dbo).(WinccToAlarms)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM (WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924).dbo.AlgRtTextsFRA
END

But i want to dynamically name the (WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924) part of the request.
Here is the code that i wrote :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms20
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END


    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ('+(@NomDBAlarms)+').dbo.AlgRtTextsFRA'

    Exec @Sql
    END
GO

Can you help me figure out what is wrong with this piece of code?

Thanks a lot !!!

sql server 2008 – Log File Size and dbcc sqlperf (logspace)

Consider the following log file setting for a database named XYZ:

Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB

I have created a SQL Job to monitor the size of XYZ database ldf using the following script and insert it into a table in a different database named ABC.

DECLARE @sql_command VARCHAR(MAX)
DECLARE @action VARCHAR(MAX)
DECLARE @size REAL
DECLARE @sizeUsed REAL

IF exists (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = OBJECT_ID(N'tempdb..#TempForLogSpace'))
    DROP TABLE #TempForLogSpace;   

CREATE TABLE #TempForLogSpace
(
    DBName varchar(MAX),
    LogSize real,
    LogSpaceUsed real,
    Status int
)

SELECT @sql_command = 'dbcc sqlperf (logspace)' 
INSERT INTO #TempForLogSpace EXEC (@sql_command)

SELECT @size = LogSize, @sizeUsed = LogSpaceUsed FROM #TempForLogSpace WHERE DBName = 'XYZ'

INSERT INTO dbo.ABC.tbl (DBName, FileSize, SpaceUsedPercentage, LoggedDate)
VALUES('XYZ', @size, @sizeUsed, GETDATE())

Initial result after running it for several hours is as follows

enter image description here

My expectations is that,

  1. as transactions being made in database XYZ, the SpaceUsedPercentage = LogSpaceUsed (DBCC)
    should be increasing,
  2. and if the ldf size reaching the initial size, SQL server
    would increase the ldf file size by another 500MB and so would the the value of
    FileSize = LogSize (DBCC).

But after seeing the result:

  1. Seems like SpaceUsedPercentage is increasing/decreasing from
    time to time – what is actually happening?
  2. If the ldf reaching initial size – will the FileSize captured is
    the initial size or the increased size?

Thank you for your kind attention.

sql – agrupar cada periodo por mes

Estou fazendo essa consulta, porem não estou conseguindo agrupar cada período por mês, do ano anterior

  • Para cada mês do ano anterior ao ano corrente, retornar a quantidade de carros estacionados por período (de 00:00 a 07:59, 08:00 a 15:59 e 16:00 a 23:59)

select
ent_datahora_entrada as mes, veiculos as periodo , count(*)
from (
select

   to_char(ent_datahora_entrada, 'dd/mm/yyyy hh24:mi') ent_datahora_entrada 
    
  , case when to_char(ent_datahora_entrada, 'hh24:mi')>='00:00'
          and to_char(ent_datahora_entrada, 'hh24:mi')<='07:59' then '0 as 8'
         when to_char(ent_datahora_entrada, 'hh24:mi')>='08:00'
          and to_char(ent_datahora_entrada, 'hh24:mi')<='15:59' then '8 as 16'
         when to_char(ent_datahora_entrada, 'hh24:mi')>='16:00'
          and to_char(ent_datahora_entrada, 'hh24:mi')<='23:59' then '16 as 0'
         
    end veiculos
  from entradas_saidas
 
 )

group by ent_datahora_entrada, veiculos

;
esta saindo assim.
12/01/0020 09:26 8 as 16 1
14/01/0020 11:26 8 as 16 1
19/01/0020 16:26 16 as 0 1
28/01/0020 01:26 0 as 8 1
17/02/0020 21:26 16 as 0 1
01/03/0020 10:26 8 as 16 1
10/03/2019 16:00 16 as 0 1
12/04/2019 16:00 16 as 0 1
19/04/2019 16:00 16 as 0 1

mas o que preciso é que agrupe todas as datas do mês, exemplo : 01/01/19 ate 01/02/19 p/ todos os meses
8 as 16 5
16 as 00 10
00 as 8 7