texts:
I wrote a query whereby I  joined multiple tables to get  one row per employee id and the relevant information for each employee job code, job code etc

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID
  FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))

I wanted to get another field, JPM_DESCR90 into my query by joining the table PS_NPS_LIC_VW_RPT by EMPLID with one of my other tables in the original. I tried a left outer  join between PS_PERSON_NAME B and PS_NPS_LIC_VW_RPT where by PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR')

However I got multiple rows for each  EMPLID since JPM_DESCR90 has multiple values for each EMPLID in PS_NPS_LIC_VW_RPT C .

I also tried a standard join between the two whereby B.EMPLID = C.EMPLID between
PS_PERSON_NAME B, and  PS_NPS_LIC_VW_RPT C

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, C.JPM_DESCR90
  FROM PS_NPS_VIEW_ALL A, PS_PERSON_NAME B, PSOPRDEFN E, PS_NPS_LIC_VW_RPT C
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.PAYGROUP NOT IN ('SUM','CWR')
     AND B.EMPLID = C.EMPLID)

I'm tried to get JPM_DESCR90 from PS_NPS_LIC_VW_RPT into my original query so that I can get one value per EMPLID .I know that its probably a simple solution but would appreciate some help


texts: Database for building a realtime analytics system

texts:
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on

Real time counting of unique URI visit/PV by different dimensions of user demographic information, e.g. User Agent, OS, Country etc
Real time calculation of average user session length (if the different between two request from the same IP is less than 1 minute)

Are there any good database store enable this kind of query in real time?

p.s. I am currently testing InfiDB.


texts: Does Informix have a function equivalent to OVERLAPS, which finds out whether or not two time intervals have any time in common?


texts: How do I shrink the innodb file ibdata1 without dumping all databases?

texts:
InnoDB stores all tables in one big file ibdata1.

After dropping a big table, the file is keeping its size no matter how big the table was.
How can I shrink that file without having to dump and re-import the whole database (which has several Hundred GB in total)?

I think the reason is because you are still able to rollback the dropping. In my case I don't need to.


texts: Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file

texts:
Example in the EF6 migration file Up method.

I wish to add a new column B in existing table Country.

Sql("ALTER TABLE [dbo].[Country]
     ADD [B] INT DEFAULT((0)) NOT NULL;")

Then I will need to add this new column B to it's VCountry view and I did the following:

DropView("VCountry")

Sql("CREATE VIEW [dbo].[VCountry]
         AS
         SELECT A, B FROM Country")

Everything looks good until I run the Add-Migration XXX command and the Invalid column name 'B'. prompt out.

What I trying to figure out is how to add the new column to the table and it's relevant view at the same migration file?

Any help would be much appreciated!


texts: generate a series of dates in progress-db

texts:
I am looking for a way in progress-db to generate a series of dates in the same way I would in PostgreSQL.
    select date::date
    from generate_series(
                          '2021-01-07'::date
                         ,'2021-01-12'::date
                         ,'1 day'::interval
                         ) date;

Returns the following dataset in rows:

1/7/2021 , 1/8/2021, 1/9/2021, 1/10/2021, 1/11/2021, 1/12/2021

I looked in the documentation, but I am unable to find any generate_series keywords or anything like it.
Is this something that can be done in this RDBMS? If this is not possible, please let me know.


texts: Local users access SQL Server correctly, but domain users don't return all the data

texts:
We have just changed from a workgroup to a domain running SBS 2012 and SQL Server 2008R2. The problem I have is when logged in as a domain user my application connects to the server but only returns some of the data.

If I use the same computer and log in as the old local user (same user name and password as the domain account) it works fine.

I have added the domain user name as sysadmin in SQL Server and it still only returns some of the data. I know the obvious answer is just to use the local accounts but then we wont be able to use the exchange email and other functions in the domain.

I have tried everything I can think of and spent 3 days searching through SQL forums and found no answer yet.


texts: Stress test MySQL with queries captured with general log in MySQL

texts:
Is there any tool available which can do stress testing using the log file created by MySQL general log?
After a lot of search on google I found few stress testing tools which only use some benchmarks for stress test. One solution is using Apache JMeter, but it does not create test plans from MySQL log file and creating custom test plan for all the queries I have is too time consuming.
Or is there a tool which can at least create .sql file from MySQL log?


texts: How to improve Procedure Cache Hit Ratio?

texts:
From what I can tell Procedure Cache Hit Ratio below 95% is a problem.  On my box, the values hover from 85 to 95%.

How do I fix this issue?  The server seems to have plenty of RAM so that shouldn't be an issue.  What else can it be?


texts: Mysql Order , Select highest value item and update to another table

texts:
i have list in data_1 table. i want to select highest value item in one group and update it to another table. can i do that with 1 query?

example : Select "Group B Leader Jane" and update it to other table with coulmns

Table 1 : data_1

id(ai) - group - name - serial - point
52        A      Jhon    A54V     14
53        A      Arthur  B896     11
54        B      Jane    RT3A     14
55        B      Kim     RF31     13

After UPDATE -> Table 2 : data_2

id(ai) - name - text1 - text2
8        Jane   RT3A      14


texts: How to check the syntax of pg_hba.conf and other postgresql conf files on ubuntu/debian/linux?

texts:
Many times, the postgresql service can't restart after some configuration changes.

Is there any command line tool allowing to check the syntax of pg_hba.conf and other pg *.conf files before reloading/restarting the service, or even better, after any config change ?


texts: Finding films where 2 specific actors appeared together. (sakila db)

texts:
I'm following a book which states that if we wanted to show a table for the films where Cate MacQueen and Cuba Birch both appeared, I should run the following command:
select f.release_year, f.title, concat(a1.first_name," ", a1.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join film_actor fa2
    on f.film_id=fa2.film_id

    inner join actor a2
    on fa2.actor_id=a2.actor_id

where ((a1.first_name="CATE" and a1.last_name="mcqueen")
    and (a2.first_name="cuba" and a2.last_name="BiRcH"));

My question is why the need for the second inner join  film_actor fa2 ? Why can't we just do
select f.release_year, f.title, concat(a1.first_name," ", a1.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join actor a2
    on fa1.actor_id=a2.actor_id

where ((a1.first_name="CATE" and a1.last_name="mcqueen")
    and (a2.first_name="cuba" and a2.last_name="BiRcH"));

?
I tried my way, but it just returned an empty set, instead a 2-row table like in the book.


texts: What can I do to speed up this query which aggregates over an interval?

texts:
I have a star schema database in Redshift. I'm running an aggregation on a fact table order_facts with the following relevant columns:

total - FLOAT - total order cost

payment_date - INTEGER - payment date in the format YYYYMMDD

shop_id - INTEGER foreign key to the shops dimension

I'm using the following dimension tables:

DATES

id - INTEGER - date in format YYYYMMDD

date - DATE - SQL date formatted field

SHOPS

id - INTEGER - primary key for the table

created - DATETIME - timestamp for when the shop was created

My goal is to obtain a table of the total amount of orders for each shop in its first 30 days.

My query is this, and should give an accurate answer. However, it's taking over a half-hour to run:

SELECT of.shop_id, SUM(of.total) FROM order_facts of
INNER JOIN shops s
ON of.shop_id = s.id
INNER JOIN dates d
ON of.payment_date = d.id
WHERE d.date <= (s.created + INTERVAL '30 days')
GROUP BY of.shop_id

I tried rewriting it like this, but the query still does not finish after over 20 minutes of runtime

SELECT SUM(r.total), r.shop_id
FROM (
    SELECT s.created, of.shop_id, of.total, of.payment_date
    FROM order_facts of
    INNER JOIN shops s
    ON of.shop_id = s.id
) r
INNER JOIN dates d
ON r.payment_date = d.id
WHERE d.date <= (r.created + INTERVAL '30 days')
GROUP BY r.shop_id

I'm not currently understanding why it's taking so long to run. Understanding that would help me know how to correct the query. Likewise, seeing a better version of the above query would help me understand where the inefficiency exists. Either way is extremely helpful for me.

Just getting the total order amount per shop for the past X days, or all-time, is a very fast query. Thus it seems I'm doing something suboptimal with the join to the dates table, but it's not clear what.

EDIT: output of EXPLAIN

1
XN HashAggregate (cost=868301988.49..868301991.01 rows=1009 width=12)
2
-> XN Merge Join DS_DIST_NONE (cost=0.00..868286973.07 rows=3003084 width=12)
3
Merge Cond: ("outer".id = "inner".payment_date)
4
Join Filter: ("inner".shop_id = "outer".id)
5
-> XN Nested Loop DS_BCAST_INNER (cost=0.00..1773635510.00 rows=32472000 width=8)
6
Join Filter: (("outer".date)::timestamp without time zone > ("inner".created + '30 days'::interval))
7
-> XN Seq Scan on dates d (cost=0.00..110.00 rows=11000 width=8)
8
-> XN Seq Scan on shops s (cost=0.00..88.56 rows=8856 width=12)
9
-> XN Seq Scan on order_facts "of" (cost=0.00..90092.52 rows=9009252 width=16)
10
----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----


texts: Convert row into concatenated string with column names included

texts:
I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in the query?

I'm doing this because the columns represents fields in an event report. I'm putting them back together so a person can read the report in a logical manner.

I've done some of this with a query, but it is laborious to do for each column and seems error-prone.

Here is a brief snippet showing three columns concatenated in the format I need, done in the column-by-column approach:

SELECT
 Concat(
   IIf(Id IS NULL, Null, Concat('Id: ' , [Id] , '\n') )  ,
   IIf(StandardClientId IS NULL, Null,
     Concat('StandardClientId: ' , [StandardClientId] , '\n') )  ,
   IIf(ClientName IS NULL, Null,
     Concat('ClientName: ' , [ClientName] , '\n') )
 )  AS ReportLine
FROM dbo.DataDecoded;

Thanks


texts: how to find number of empty tables in any schema?

texts:
I want to query how to find all the empty tables in my schema HR
i already tried with
select *
from dba_tables
where owner='HR' and num-rows is null;
but i dont know if this is the correct logic or not


texts: change journalCompressor to none - not default snappy

texts:
Quote from ObjectRocket Support

  Hi, Fyodor! The journalCompressor setting is specifically related to
  the journal files. Journals are typically not compressed because
  compressing them causes additional latency when flushing the data from
  the WiredTiger memory cache to the disk. This latency trickles down to
  the write concern.
  journalCompressor none is a default value in our configurations.

ObjectRocket seems to understand something about DBaaS

  ObjectRocket is a cloud database (DBaaS) company based in Austin,
  Texas, specializing in NoSQL datastores including MongoDB and
  Redis. In 2013, they were acquired by Rackspace

What do you think about journalCompressor not snappy? Is this a valid config for DBaaS provider and best for most use cases?

storage:
   dbPath: "/data/mongodb"
   journal:
       enabled: true
   engine: "wiredTiger"
   wiredTiger:
       engineConfig:
           journalCompressor: none
           directoryForIndexes: "/indexes/mongodb/"
       collectionConfig:
           blockCompressor: snappy
       indexConfig:
           prefixCompression: true


texts: mysql error 1265 data truncated for column

texts:
I created a table in mysql like this :

create table if not exists  data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
execute_time DATETIME NOT NULL default current_timestamp,
db VARCHAR(25) NOT NULL,
book varchar(6),
`open` float(20,3) not null,
`close` float(20,3) not null)

and when I want to insert value into this table, it fails and show the exception as DataError: (1265, "Data truncated for column 'open' at row 1")

and the values I want to insert into open is  3535929.559. I code the insert data in python  like this:

insertData = """INSERT INTO {table_name} (execute_time,db,book,`open`,`close`) values
                        (%(execute_time)s,%(db)s,%(book)s,%(open)s,%(close)s)""" .format(table_name = settings["dumpTable"]["data"])

    conn.cursor().executemany(insertData,data_in_dict)
    conn.commit()

The most strange thing is when I insert the same value in MySQL workbench ,it went smoothly, the query is like this :

insert into data (db,book,`open`,`close`,execute_time) values ('au','book','3535929.559','1079339.851','2016-7-22');

But the data shown in the table is:

 '5', '2016-07-05 00:00:00', 'au', 'book', '3535929.500', '1079339.875'

I think the problem maybe insertion from python is more restricted? Then how to set it to be less restricted?  Any help would be appreciated!


texts: mysql query only works when value is compared with primary key

texts:
I'm working with this table:

mysql> describe dispositivos;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sn        | varchar(25) | NO   | PRI | NULL    |       |
| device_id | varchar(25) | YES  |     | NULL    |       |
| ip_adress | varchar(25) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I've noticed that when I type a query like this:

mysql> select * from dispositivos WHERE sn = "SAL1027TVGH";
+-------------+---------------+------------------+
| sn          | device_id     | ip_adress        |
+-------------+---------------+------------------+
| SAL1027TVGH |  CU_DGIE_6509 |  148.228.110.254 |
+-------------+---------------+------------------+
1 row in set (0.00 sec)

everything goes fine, but if I search by any other field, the query is not working.

mysql> select * from dispositivos WHERE ip_adress ="148.228.110.254";
Empty set (0.00 sec)

mysql> select * from dispositivos WHERE device_id ="CU_DGIE_6509";
Empty set (0.00 sec)

Do you know why this is happening?

there are other tables, but the problem just affect this one, it is being filled automatically with LOAD DATA LOCAL INFILE.

thanks.


texts: ListServ Database: Statistics Table Design Question

texts:
I've got a little bit of a design question going on here.  I have my own ListServ implementation written in Python, that works with a Postfix+Dovecot system for handling mail, and a PostgreSQL database backend for most of the functionality for determining the following:

What lists exist on the listserv (the lists table in the diagram below)
Who has ever been seen on the listserv as a member of any list. (the members table in the diagram below)
Who is a current member of which lists, and: (the listserv_membership table in the diagram below, linking individual 'members' to the individual 'list' they are a member of by listserv ID and member ID)

Can they send to the list? (hidden field in the table)
Can they receive messages sent to the list? (hidden field in the table)

ListServ statistics - Message counts for each day, based off the current date's timestamp, for each individual list that has had activity. (the listserv_stats table below)

Currently, the structure of the DB looks like this (Note that I have blanked out all the non-relevant fields in tables I'm not going to be focusing on, and leaving the primary keys in place; the relevant table is marked with three white asterisks next to it, and this diagram is created with DataGrip):

Please don't call me out about diagonal arrows in the diagram - this one was done as a quick and dirty diagram, while I have a good ERD done by hand in Visio, which is unfortunately not available on the computer I'm on now

The specific table I'm going to ask about is the listserv_stats table here, not the rest of the design of the database.

Now, initially, I was only worried about tracking statistics over a day-to-day basis and 'all time' very rough daily average based on how many days the listserv has had activity.  I am now a lot more concerned about other statistics, such as monthly averages, month-to-date average, year-to-date average, and yearly averages, so I'm considering a redesign here to better accommodate such searches.

The current table CREATE statement is this:

CREATE TABLE listserv_stats
(
    lsid INTEGER NOT NULL,
    datestamp DATE DEFAULT now() NOT NULL,
    msg_count INTEGER NOT NULL,
    CONSTRAINT listserv_stats_lsid_date_pk PRIMARY KEY (lsid, datestamp),
    CONSTRAINT listserv_stats_lists_lsid_fk FOREIGN KEY (lsid) REFERENCES lists (lsid)
);
COMMENT ON COLUMN listserv_stats.lsid IS 'ListServ ID';
COMMENT ON COLUMN listserv_stats.datestamp IS 'DateStamp';
COMMENT ON COLUMN listserv_stats.msg_count IS 'Message Count';

What I'm thinking of doing is expanding the table to replace the datestamp column as follows.  This would allow me to better handle month-to-date, monthly historic, year-to-date, and yearly historic values, in my opinion, in my Python code (which can reconstruct the individual values into datestamps):

CREATE TABLE listserv_stats
(
    lsid INTEGER NOT NULL,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    day INTEGER NOT NULL,
    msg_count INTEGER NOT NULL,
    CONSTRAINT listserv_stats_lsid_date_pk PRIMARY KEY (lsid, year, month, day),
    CONSTRAINT listserv_stats_lists_lsid_fk FOREIGN KEY (lsid) REFERENCES lists (lsid)
);
COMMENT ON COLUMN listserv_stats.lsid IS 'ListServ ID';
COMMENT ON COLUMN listserv_stats.year IS 'Date: Year';
COMMENT ON COLUMN listserv_stats.month IS 'Date: Month';
COMMENT ON COLUMN listserv_stats.day IS 'Date: Day';
COMMENT ON COLUMN listserv_stats.msg_count IS 'Message Count';

From this, I can restructure the existing predefined routines in the DB to allow for my Python code to call "New Date Record" and "Update Date Record" functions by passing the entire date as values.

My question is, is this design change a good idea, or can I achieve everything with the original "datestamp" table I already have in place?  And should the original design be kept, I will have to figure out how to structure the routines to provide the data for those specific date ranges from the system (I'll post on SO for that, most likely).


texts:
I'm using MySQL 5.7.17 and I have this table :

CREATE TABLE `posicaorastreadores` (
`Id` CHAR(36) COLLATE utf8_bin NOT NULL DEFAULT '',
`ComandoInicialString` VARCHAR(1024) COLLATE utf8_general_ci NOT NULL,
`Serial` BIGINT(20) NOT NULL,
`DataEnvio` DATETIME NOT NULL,
`DataGps` DATETIME NOT NULL,
`Latitude` DOUBLE NOT NULL,
`Longitude` DOUBLE NOT NULL,
`Velocidade` DOUBLE NOT NULL,
`TensaoBateriaPrincipal` DOUBLE NOT NULL,
`TensaoBateriaBackup` DOUBLE NOT NULL,
`DataInclusao` DATETIME NOT NULL,
`DataAlteracao` DATETIME NOT NULL,
`Ativado` TINYINT(1) NOT NULL,
`EnderecoArmazenado` TINYINT(4) NOT NULL DEFAULT 0,
`ModeloRastreador` INTEGER(11) NOT NULL,
PRIMARY KEY USING BTREE (`Id`),
KEY `enderecoArmazenado_idx` USING BTREE (`EnderecoArmazenado`),
KEY `serial_idx` USING BTREE (`Serial`),
KEY `datagps_idx` USING BTREE (`DataGps`),
KEY `ativado_idx` USING BTREE (`Ativado`),
KEY `ModeloRastreador_idx` USING BTREE (`ModeloRastreador`),
KEY `posicaorastreadores_idx1` USING BTREE (`ComandoInicialString`),
KEY `posicaorastreadores_idx2` USING BTREE (`DataEnvio`),
KEY `posicaorastreadores_idx3` USING BTREE (`Ativado`, `Serial`, `DataGps`)
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

I need to use partitioning.

I'm try to use with this command:

ALTER TABLE posicaorastreadores PARTITION BY HASH( DAYOFMONTH(DataGps) )
PARTITIONS 31;

I'm receiving this error:

  A PRIMARY KEY must include all columns in the table's partitioning function


texts: SQL Server - Round a number when it's one decimal before .50

texts:
I would like to know if there is a way to round a number if it's one decimal before 50.
i.e,
I am using the below code and it works, it gives 8.50:
select Round(8.49,1) = 8.50

The problem comes when I want to round the below one, I would expect this one to be only 8.48 but results in 8.50 as well.
select Round(8.48,1) = 8.50

The idea is to keep the 8.48 as it is, but when it's 8.49, it needs to be 8.50.


texts: TSQL Solution to backup ALL databases on ALL instances of a server?

texts:
I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But I've thought a lot of things were impossible with computers so hopefully I can solve my problem in a different way.

I've already created a PowerShell solution that uses SMO to backup all databases on all instances, but due to security reasons I'm unable to have that solution work without making the account for the proxy that runs the Agent job a local administrator on the server.

As an alternative I wrote a T-SQL script that will be ran as an Agent job and take either full, differential, or transaction backups on all databases in a SQL Server instance every 15 minutes.

    DECLARE @sql nvarchar(MAX) = N'';

--Check time for full backup
IF (DATENAME(weekday,getdate()) = 'Sunday' AND convert(time,getdate()) > '00:00' and convert(time,getdate()) < '00:15')
    BEGIN
        SELECT @sql += 'BACKUP DATABASE ' + QUOTENAME(name)
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__'
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.bak'''
                    + ' WITH COMPRESSION
'
        FROM sys.databases
        WHERE name <> 'TempDB';
    END
--Check time for differential backup
ELSE IF (convert(time,getdate()) > '00:00' and convert(time,getdate()) < '00:15')
    BEGIN
        SELECT @sql += 'BACKUP DATABASE ' + QUOTENAME(name)
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__'
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.diff'''
                    + ' WITH COMPRESSION, Differential
'
        FROM sys.databases
        WHERE name <> 'TempDB' and name <> 'Master';
    END
--Take transaction log backup
ELSE
    BEGIN
        SELECT @sql += 'BACKUP LOG ' + QUOTENAME(name)
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__'
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.trn'''
                    + ' WITH COMPRESSION
'
        FROM sys.databases
        WHERE name <> 'TempDB' and name <> 'Master';
    END
print @sql;
exec sp_Executesql @sql;

This script works fine, however it only backs up ALL databases on ONE instance.
The goal is to backup ALL databases on ALL instances. An even better solution is to backup all databases, on all instances, and on all servers. From my current knowledge it seems like there are three solutions, (hopefully more based on answers)

Use this script in an Agent job on every instance. This will work
but just seems like such an ugly solution.
Figure out how to give the Agent proxy the minimum amount of
permissions to successfully run the PowerShell script which is instance aware
Look into creating a master server. I'm not familiar with this option but I know the master server is aware of other servers, and i may be able to get a list of all databases on all servers.


texts:
One of my database mirrors is in the suspended state today and the error log on the mirror shows the following.

  Could not redo log record (338934:102862:477), for transaction ID
  (0:848150408), on page (1:3486737), database 'MyDB' (database ID 5).
  Page: LSN = (338520:235006:14), type = 2. Log: OpCode = 11, context 3,
  PrevPageLSN: (338520:235021:28). Restore from a backup of the
  database, or repair the database.

Scary stuff.  However this isn't the first time it has happened and I have checked and double-checked and this isn't because of a normal physical change on the principal that couldn't be applied to the mirror such as a file getting added.  DBCC CHECKDB comes back clean.

I tried scanning the transaction log for the LSN given and found the following.

SELECT * FROM fn_dblog('338934:102862:477', '338934:102862:477')

Current LSN             Operation           Context         Transaction ID
00052bf6:000191ce:01dd  LOP_DELETE_SPLIT    LCX_INDEX_LEAF  0000:328dbf88

Description
Moved 176 row(s) at slot 0 from page 0001:00353411 to page 0000:00000000

I removed a lot of the columns in the output as they were null or didn't seem to have relevant information.

Why would a regular page split not be able to be restored?  I have also tried manually restoring the transaction logs and gotten the same error "could not redo log record.." error.

Any ideas what is going on?


texts: GRANT permission ON database TO user not working on AWS

texts:
I'm having trouble granting a user permission on AWS.

mysql> GRANT CREATE ON *.* TO 'hmrex_db'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'hmrex_db'@'%';
+------------------------------------------------------------------------------+
| Grants for hmrex_db@%                                                        |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hmrex_db'@'%' IDENTIFIED BY PASSWORD <secret>         |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON `hmrex_db`.* TO 'hmrex_db'@'%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The user I'm logged in as has GRANT ..., CREATE, ... ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION
read_only is 0.

I can't work out why this isn't working.

I discovered I had made a typo and was setting the permission on a user that doesn't exist, however, this worked. When I fixed the typo, it still didn't work.


texts: Can multiple process pull off the same record from an Oracle FIFO queue?

texts: My understanding of Oracle AQ (Advanced Queuing) is that no two (or more) processes can pull off (drain/consume) the same record of an Oracle FIFO queue.  Is this correct or is some code needed to prevent this from occurring?


texts: Accidently Named all tables in my database starting with a number

texts:
I've accidently named all my tables (100+) in my sql database with the prefix 2019_

Wasn't aware this was not a good idea until just now (just returned to SQL after 2 years)

Is there any way to bulk swap it to a suffix or even delete all the tables? I can't even seem to open them. Really don't want to go through them all manually.

Appologies if this is a silly question, potentially googling the wrong terms

Thanks!


texts: SQL query output to a file is failing with error code 9 when the data contains French and English characters with size > 32k

texts:
Database: Oracle 12 C

I have a MESSAGE table with BODY VARCHAR2(32000), METADATA VARCHAR2(32000).

Initially I had a problem when both columns have 32k+32k chars of data. I had fixed it by applying the TO_CLOB function and I am able to write the output to the file without truncating the result. (This is working only when

All are English characters(Eg: SQL query result record contains around 64 K characters)
Combination of French & English characters when the SQL query result record < 32K characters)

But when the BODY part is having French & English characters with expected SQL query result record > 32K characters, In this case, my query execution is failing with error code : 9.

Please help me on how to fix this issue without truncating the data.
Below is my Unix script code

export PATH

export LD_LIBRARY_PATH

export ORACLE_ACCESS

#export NLS_LANG=.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

output=$(sqlplus -S "$ORACLE_ACCESS" << EOF
    WHENEVER OSERROR EXIT 9;

    whenever sqlerror exit sql.sqlcode;

    set NEWPAGE none

    set pagesize 0

    set trimspool on

    set trimout on

    SET LINESIZE 32767

    SET LONG 500000000

    SET LONGCHUNKSIZE 100000000

    set echo off

    set heading off

    set feedback off

    set term off

    set recsep off

    set serveroutput on size unlimited

    SPOOL $FILE_NM APPEND

    select '02~^'||TO_CLOB(EVENT_ID)||'~^'||BODY||'~^'||META_DATA||'~^'||'|^#' from MESSAGE where ((CREATE_TS >= TO_DATE('01-Jan-2025','DD-MON-YYYY') and CREATE_TS < TO_DATE('01-Jan-2025','DD-MON-YYYY')+1) ORDER BY ROWID;

    SPOOL OFF

    exit;

EOF

)


texts: improving join performance on sql server table

texts:
I have a table on sql server lets say TableA.
and a another table as TableB.Both of these tables are heavily loaded.

Table A is loaded with records 35883788. It is holding a lot of duplicates on column program_id.

Table B is loaded with records 27343331. It is also holding a duplicate on column on program_id.
For some business reason we need to keep duplicate records on program_id on both the tables.

Now I am performing a left join on given table as:

select
a.*,b.date_of_birth,
datediff(year,b.date_of_birth,a.from_date)-
(case when dateadd(year,
           datediff(year,b.date_of_birth,a.from_date),b.date_of_birth)
           > a.from_date then 1 else 0
                   end) as age_final,
case when datediff(yyyy,b.date_of_birth,a.from_date) is null then ''
     when datediff(yyyy,b.date_of_birth,a.from_date) <=1 then 'less than 1 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=17 then 'then 1 - 17 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=29 then 'then 19 - 29 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=39 then 'then 30 - 39 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=49 then 'then 40 - 49 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=59 then 'then 50 - 59 year'
     when datediff(yyyy,b.date_of_birth,a.from_date) <=64 then 'then 60 - 64 year'
else 'More than 64 years' end as Age_band
from TableA a
left join ( select program_id,date_of_birth,max(process_date)
            from TableB
            group by program_id,date_of_birth) b
    on a.program_id=b.program_id;

my inner query is giving me a unique set of program_id as using group by statement.

None of these tables are indexed. If I have to create an index on both the tables, what kind of index do I need to have on program_id for both the tables.

what all other things I can take care of to improve the joining performance.

need suggestion to optimize the join and indexes.


texts: Sequence of dates - months from January to December with SQL

texts:
How can I create in SQL a function to search how many courses does a company each month from January to December in a specific year (2015)?

In the table fechahora form cursos, I have 1 in June, 1 in July, 3 in August, 1 in November and 0 in the other months. Furthermore I have the dates in this format '2015-01-01' and I need the name of the month.

I'm trying with this SQL statement.

SELECT
  MONTHNAME(FECHAHORA) AS 'MONTH',
  COUNT(Monthname(FECHAHORA)) AS 'TOTAL'
FROM COURSES
WHERE DATEOFCOURSE BETWEEN '2015-01-01' AND '2015-12-31'
GROUP BY MES
ORDER BY FECHAHORA;

From this statement, I have the results from the months that have a course but I need from January to December.


texts: Postgres handle case to continue Insert other row if some row doesn't exists

texts:
I am trying to insert data to a table using below query:
INSERT INTO projects_users (user_id, project_id, is_admin) VALUES (
            (Select id from users where username='example'),
            (Select id from projects where name='example'),
            True
        ),(
            (Select id from users where username='example'),
            (Select id from projects where name='example'),
            True
        )

This query fails when some user is not there in users table (Select id from users where username='does_not_exists').
I want to handle such cases, and skip such rows (where username doesn't exists) and continue adding other rows.
In case of duplicate rows, I know we can do "On Conflict Do Nothing". Is there something similar available for cases like this? And is it a good practice to do?


texts: Issues installing SQL Server 2008 R2 or any other version on Windows 10

texts:
I have a serious problem by installing any SQL Server version (tried 2008 R2 and SQL Server 2014) on Windows 10.

First question: are the above mentioned versions compatible with Windows 10 or Windows 8.1?

Because that's how it was, when I bought a computer it was with Windows 8.1 first, then I had an icon showing me that I could update it to Windows 10. And that's what I did it. I updated it and now, I try to install Any SQL Server of the above and I get loads of the error, obviously you will get the Error that " This version is not compatible of this version on windows " but, on the videos in youtube, I saw that you can install the SQL server 2008 R2 or SQL server 2014 without that notice and it installs without errors.

When I try to follow their steps and downloading the same thing, I get lot's of errors and I don't know what to do. I will show few pictures what I'm getting by installing the 2 versions of SQL servers. I hope you can help me. Thanks.

And I'm geting these errors. So I don't know how to fix this.

In this It always stops here, I was waiting for hours but it never proceeded. So I had to close in task manager "msiexec.exe" to complete but I get the error you can see the other picture below.

I added everything with pictures. This should show you what kind of problem I have.


texts:
Problem:
is it possible to use sp_executesql or EXEC inside of a user defined function?

Thanks!


texts: A large percentage of unused space in the table

texts:
SQL Server 2005.

I have a table size 16GB.  Table has 14 GB of free space.

sp_spaceused 'FREECLIENTDOC'

name            rows   reserved      data          index_size  unused
--------------- ------ ------------- ------------- ----------- ------------
FREECLIENTDOC   72407  16168232 KB   1742544 KB    30696 KB    14394992 KB

Indexes do not take up much space:

DBCC SHOWCONTIG scanning 'FREECLIENTDOC' table...
Table: 'FREECLIENTDOC' (659533433); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 3508
- Extents Scanned..............................: 457
- Extent Switches..............................: 2555
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 17.18% [439:2556]
- Logical Scan Fragmentation ..................: 71.18%
- Extent Scan Fragmentation ...................: 69.80%
- Avg. Bytes Free per Page.....................: 1981.6
- Avg. Page Density (full).....................: 75.52%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Perhaps the problem in the fields of image types.
How to determine what is in an unused space?
I'm interested in the general case definition table space.
This table - a example.


texts: SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

texts:
We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database (assume the database password = dbpassword, and if it was only this level of protection I think we would already have linked to the Access database with minimal problems.

However, the Access database is on a network drive, and within a directory on that network drive that is protected. We know the username and password (let's assume the username = cat and the password = pancakes).

I'm looking into this sp_addlinkedsrvlogin, and I believe it's what we need to do. Isn't it?

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
     [ , [ @locallogin = ] 'locallogin' ]
     [ , [ @rmtuser = ] 'rmtuser' ]
     [ , [ @rmtpassword = ] 'rmtpassword' ]

The problem is, I don't know what to put in these parameters.

I'm assuming rmtuser would be 'cat' and rmtpassword would be 'pancakes'...

Here is the full path to the Access database:
\volume9\v9\projects\cats\cooking\pancakedb\back-end

volume9 appears to correspond with the K: drive...

So would rmtsrvname = '\volume9\v9\projects\cats\cooking\pancakedb\back-end'?

Also,  useself = 'FALSE' right? And I don't know about locallogin.

Am I going about this the right way? This is the first time I've ever set up a sp_linkedserver. Any help is greatly appreciated!

EDIT:

Forgot! Our linked server is called PANCAKECONFIGDB. I don't know if that's useful

EDIT 2:

These are the settings for SQL Server on my local machine. I'll put the information about the server's one's in a moment, as well as the full sp_linkedserver text. Is this where I should be changing things?

EDIT 3:

Here is the server's Log On As thing:

Also the script for the linkedserver...

****** Object:  LinkedServer [PANCAKECONFIGDB]    Script Date: 06/20/2012 14:14:28 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PANCAKECONFIGDB', @srvproduct=N'Access 2007', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\volume9\v9\projects\cats\cooking\PANCAKECONFIGDB\back-end\PANCAKECONFIGDB.accdb', @provstr=N';PWD= dbpassword'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PANCAKECONFIGDB',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'PANCAKECONFIGDB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


texts: SQL Server Database size - doubled running DBCC

texts: I was running a dbcc command using ola hallengren checkdb on a large database (775Gb) when half way through the process the tempdb ran out of space. I have run this in other environment on different databases in the past and no problems. This time though the database went from 775 GB to 1.6 TB, doubling in size. I have never had this issue in the past and anything I do increase the size by 100 GB. What can be causing this issue like that? SQL server 2016.


texts: Is it possible to have 2 or more databases active, and synchronized between them?

texts:
edit
I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I discovered Merge Replication, and in paper looks great (although I still need to know what happens if a server goes down and them comes alive again, will it synchronize? can you answer me to this one too?)

edit

My objective is to have various applications working simultaneous and communicating with one database only. But having the databases synchronized between them and that in case of one database goes down, all its data is not lost.

Is it possible to do this with Merge Replication?? where all sql instances are both publishers and subscribers, and have an distributor inside them.

Any considerations I should take, or other possible solutions for my problem?

regards


texts:
I'm looking to replicate a table that has a particular column for deciding if a row should be replicated. If the column is set to 'Y', then replicate. From my searching, Static Row filters are my friend here, but I have a question on them.

All of the rows will be initially inserted with the column set to 'N'. Then, a script is run that identifies which rows are supposed to be replicated and updates those rows to 'Y' (the logic behind which rows is complex and not easy to identify at insertion time). Additionally, a subsequent run of the the script may find the a row is no longer valid for replication and will update the 'Y' back to 'N'.

My question is this: If my action on the source table is an update action, won't that break on the subscriber side when the update transaction tries to run against a row that doesn't exist on the published table (since the row was not valid for replication until the update occurred?) Similarly, if I update the row to 'N', will that update trigger a delete on the published table (since the row would no longer meet the filter criteria)? Or is the SQL server replication agent smart enough to turn that update into a insert/delete respectively?


texts: SQL agent job, why is the job history showing that a step is still running even though the job has completed

texts:
I have a sql agent job that runs Powershell as its first step (there are 3 steps in total).

I have set this step to have 2 retries, with a 3 minute retry interval.
When I look into the job history, the step_1 states that it is still running, and also that it has completed. It has done this for every time that it has run (at least the last year).
Am I missing something from my powershell? Or is this something to do with sql agent itself?

Details of querying the sysjobhistory table (Pipe seperated):

Step_name|step_id|run_date|run_time|run_duration|run_status

(Job outcome)|0|2014/02/12|01:20:00|5|Succeded

Record volume space to file|1|2014/02/12|01:20:00|2|In Progress

Record volume space to file|1|2014/02/12|01:20:00|2|Succeded

Load Volume Space|2|2014/02/12|01:20:02|1|Succeded

Record database data file space|3|2014/02/12|01:20:03|2|Succeded

Step 1 runs the following Powershell:

gwmi win32_volume | select name, capacity, freespace | Export-Csv c:\temp.tmp


texts: Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?

texts:
Expressions like NOT IN (1,2,3) are valid in a where clause:

SELECT foo FROM bar WHERE
  ((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22))

However if I want to move an elaborate expression composed of checks like the above from the WHERE clause to a SET @Var= statement, is there a way to do that without rewriting it like this:

declare @record_type int
set  @record_type  = 1

declare @ignoretype bit
IF    (@record_type <> 2)and(@record_type <> 3)and(@record_type <> 7)and
      (@record_type <> 12)and(@record_type <> 13)and
      ( (@record_type < 18) or (@record_type > 22))
     set @ignoretype=1
else
     set @ignoretype=0

I feel like it's really gross that what used to fit in one readable line is now a
gigantic gross mess.  Am I missing some syntax in SQL that is "NOT IN (1,2,3)" equivalent that works in set @var = or IF expressions? I would have written the above as:

select @ignoretype = (@record_type <> 2) ....

But you can't even use "<>" inequalities in a select statement in Microsoft SQL Server, which might be normal situation for DBAs and SQL experts, but as a relatively new SQL Server guy, I'm still finding the various syntax limits in various contexts confusing.


texts: SQL Server 2014 Availability Group failed

texts:
I have 3 servers on VMware workstation:

Win 2012 R2 as ADDC -Cluster service.
Win 2012 R2 as SQL server primary replica .
Win 2012 R2 as SQL server secondary replica (auto fail-over).

My Availability group fail in this scenario:

When I disconnect primary replica from the network the cluster fail to announce a fail-over to secondary replica when I try to manual fail-over from secondary replica instance I get this error:

however this not the case when I stop primary replica from cluster manager because it will do auto fail-over.


texts: Why my application is blocked infinitely and SQL Server never timeout and kill the deadlock

texts:
I have a multi-thread application that can opened more than one database connection to perform database operation. These connections are using the same credential.

Here is a problem, the application has a thread A opened an transaction, and start deleting items from a table, but the same thread A trigger another thread which open a new connection to read same table and to look for more thing to delete.  As you can imagine, the deleting table is locked, and you can't do select on it until its transaction is completed.  As a result, the application is blocking itself.

This application logic need to be fix. However, here are my questions:

Is there a way to have SQLServer detect this application deadlock and kill one of the open selection, similar to a deadlock victim exception, or some kind of timeout setting?
Is is possible to write a SQL Agent, that detect this situation and kill one of the session?
Why would SQL Server throw a deadlock victim exception if it is caused by different application, and not for the same application?

Thanks


texts: Full text indexing not working + Mysql 5.6.4

texts:
CREATE TABLE `table_copy` (
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
  `fulltxt` mediumtext,
  `indexdate` date DEFAULT NULL,
  `size` float DEFAULT NULL,
  `visible` int(11) DEFAULT '0',
  `level` int(11) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`link_id`),
  KEY `indexdate` (`indexdate`),
  FULLTEXT KEY `fulltext2` (`fulltxt`)
) ENGINE=INNODB AUTO_INCREMENT=274212 DEFAULT CHARSET=latin1

The above table contains about 2,74,211 records, when i tried to get data from table it takes 2 minutes to execute.

Query:   SELECT c.link_id from table_copy c where MATCH(fulltxt) AGAINST('Council')

How can i optimize  the above query?


texts: Executed a Stored Procedure in the Production Server and the following warning message is shown

texts: I executed a stored procedure on the production server; and though it shows that query has been successfully completed it doesn't allow me to close SQL Server Management Studio and the below warning message is shown. Please advice I'm more concerned because this is the production server.


texts: How To get value of column when it exist in content value of column by sql server 2016

texts:
I want To get value of column when it exist  in content value of column by sql server 2016

For Example:

Table1:

Title1
----
Jack
Benz
Volvoo

Table2:

    Description              Names
    ----                    ---------
  The Jack isnt A122           NULL
  Some  B1234 Benz             NULL
  This Volvoo Are C            NULL
  This Maria Are D             NULL
  Michael Jackson              NULL

example:

Search Row By Row From table 1 in the row by row column Description in Table 2 and get value and insert to cloumn Names

IF Jack or Benz Or Volvoo exist in The Jack isnt A122
get Jack and Update column Names in  Table 2

    Description              Names
    ----                    ---------
  The Jack isnt A122           Jack

For 'Jack owns a Benz' that matches 2 titles (Jack and Benz), update with either one of them.

For Other Rows in Table 2 same:

    Description              Names
    ----                    ---------
  The Jack isnt A122           Jack
  Some  B1234 Benz             Benz
  This Volvoo Are C            Volvoo
  This Maria Are D             Others
  Michael Jackson              Others

The tables do not have primary keys.
Table1 (Title1) is UNIQUE.


texts: Is is possible to convert SQL Server from single instance to failover clustered node?

texts:
If SQL Server 2014 Enterprise has already been installed, can it still be converted/added to a failover cluster? Specific scenario - Amazon EC2 instance of Windows 2012 + SQL Server 2014 (license included).
Or is the only option an installation of a failover cluster node from scratch?


texts: How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

texts:
I am running following command to dump my database

./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY

and getting following error

Export: Release 12.2.0.1.0 - Production on Fri Sep 14 06:14:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
UDE-00018: Data Pump client is incompatible with database version 12.1.0.2.0

I tried searching and there were suggestion to use VERSION parameter which I did still no success.

Does anyone have idea what should i do to overcome the issue?

I cant use instantclient-tools-macos.x32-12.1.0.2.0.zip as it doesnt have expdp command.


texts:
I'm trying to free up some space in a database, firstly by swapping out and archiving partitions. The DB is massive though, and i want to start freeing up some space on the drive, but I don't want to completely shrink the DB as it will take weeks.

Is it possible to shrink a SQL Server DB by say 10gb?

Thanks all!

CM


texts:
I have a 1 GB .LDF for a test database which I want to shrink right down. I am happy to delete its contents if needed.

How can I do this for SQL Server 2000?

I have tried the following to no huge avail:

BACKUP LOG Database_TEST with truncate_only
DBCC SHRINKFILE (Database_TEST_Log, 1000)


texts: Single address table for company, company_branch, users

texts:
I'm a database noob and i'm asking this simple question because i found no answer to my question. I'm using MySQL.
I have 3 tables Company, Company Branches, Users and these 3 tables have similar fields like address. So i want to store address information of company, company branch and user in a separate table "address_information".
Can i achieve this?


texts: Unique index corrupted SQL. Select query returns single row but create unique index fails

texts:
I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here.

When I run a select query for these key values, it returns only one row, but when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) these errors are thrown:

  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'uc1SycContents'. The duplicate key value is (7696031,
  08703987-557d-e111-9888-e61f13c44f03).
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'nc2MSmerge_contents'. The duplicate key value is (424222,
  7696031, 08703987-557d-e111-9888-e61f13c44f03).
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'nc4MSmerge_contents'. The duplicate key value is
  (08703987-557d-e111-9888-e61f13c44f03, 7696031).
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index
  name 'nc3MSmerge_contents'. The duplicate key value is (-425819,
  7696031, bf8a5a9c-6f7d-e111-9888-e61f13c44f03).
  Msg 2601, Level 14, State 1, Line 2
  Cannot insert duplicate key row in object 'dbo.MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.
  Msg 1505, Level 16, State 1, Line 2
  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_genhistory' and the
  index name 'unc1MSmerge_genhistory'. The duplicate key value is
  (236bfa48-965e-4a65-b4cd-ac527de04d1b, ).

How can I repair my database?


texts: adding an order by to this query returns faster than without, why?

texts:
Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen?

SELECT TOP (500) r.cID,r.aField,a.Description
       FROM dbo.tblR r
            inner join dbo.tblA a on r.aID = a.ID
            left join dbo.tblX x on x.cID = r.cID
       WHERE (ISNULL(x.anotherField,'') <> r.anotherField or x.field3 is null)
            and (r.ID=(select max(ID) from tblR where cID = r.cID and
            ISNULL(aField,'') <> ''))
            and r.cID in (select ID from tblC)
       ORDER BY r.cID ASC -- when I comment this line out it runs much slower

The execution plans are not helping out much.


texts:
I am doing test on a small percona machine on centos 7. Yesterday I tried to restart mysql server but it took too long nearly an hour. So I notice in the log this

InnoDB: Waiting for page_cleaner to finish flushing of buffer pool.

After that I ran this query

select
'innodb',
concat(round(raw/1024/1024,1),'MB') as totalLen,
@@innodb_buffer_pool_size as innodb_buffer_pool_size,
round(raw-(1.1*@@innodb_buffer_pool_size),0) as 'Suggested change'
from (
select sum(index_length) + sum(data_length) as raw
from information_schema.tables
where engine ='innodb'
) sum

innodb
totalLen
innodb_buffer_pool_size
Suggested change
innodb
167.8MB
134217728
28259123

I notice the suggested change is less then the current settings? Next to over come this I read http://www.speedemy.com/how-to-speed-up-mysql-restart/.
Its suggested set global innodb_max_dirty_pages_pct = 0. What worries be will it have impact on the db such as corrupting the db? How much of innodb_io_capacity is to be set? What else should I do to overcome is the ib log should also be increase to help?


texts: Is the Change Data Capture __$update_mask value still valid after removing and adding columns to table?

texts:
Let's say I have table I enabled cdc for.
Now, if I want to add some columns to the table and also remove some columns and preserve the CDC data for the old columns, is it going to mess up the update mask values of the historical data?
Based on my tests, dropping columns can leave gaps in the column_id values and new columns receive new column_id values. The column_id field seems to work like an identity field for columns.
So, even if I re-enable cdc for a table after removing & adding columns, the column ids of new columns should be assigned new numbers therefore the masks for historic data should still work, of course as long as I have all the columns that were ever added to the table stored somewhere (cdc.captured_columns contains only the current captured columns).
Thanks


texts: Why is my index on the join keys not used in a very simple hash join case?

texts:
I have two tables:

create table animal
(
    aid integer,
    cid integer,
    aname varchar(255) default NULL::character varying,
    species text
);

create table daily_feeds
(
    aid integer,
    zid integer,
    shift integer,
    menu integer
);

and a query:

SELECT
aname,
shift
FROM animal
NATURAL JOIN daily_feeds
WHERE menu = 1;

The table animal contains around 40000 row, the table daily_feeds contains around 80000 row, 2 for each animal.

I thought that adding an index on the columns used for the join animal.aid and daily_feeds.aid could result in a different execution plan and improved performances.

create index aaid on animal (aid);

create index daid on daily_feeds using btree (aid);

This doesn't happen. Why is that?

Obviously this is a very simple and academic example that I'm trying to use to learn more about database query optimization.

EDIT:

Added execution plan when no indexes are present:

Hash Join  (cost=1439.24..2488.23 rows=499 width=10) (actual time=11.701..55.260 rows=487 loops=1)
    Hash Cond: (animal.aid = daily_feeds.aid)
    ->  Seq Scan on animal  (cost=0.00..694.00 rows=40000 width=10) (actual  time=0.007..20.775 rows=40000 loops=1)
    ->  Hash  (cost=1433.00..1433.00 rows=499 width=8) (actual time=11.590..11.590 rows=487 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 28kB
        ->  Seq Scan on daily_feeds  (cost=0.00..1433.00 rows=499 width=8) (actual time=0.011..11.218 rows=487 loops=1)
            Filter: (menu = 1)
            Rows Removed by Filter: 79513
Planning time: 0.124 ms
Execution time: 55.521 ms

EDIT2: another question here:

Since I use two indexes I can also make them clustered indexes, physically ordering the table by the aid attribute.

cluster animal using aaid;
cluster daily_feeds using daid;

If I do that the Execution Plan doesn't change again. Isn't a Merge Join a more effective join strategy if both tables are sorted by the key column?

I'm not really sure about the behavior of Postgres here.


texts:
I have this table:

Name    Address                  Code
James   CT                       Homeaddress
Mark    mark@goole.com           Email
John    John@hotmail.com         homeemail
...

This is what I want:

Name        Homeaddress      Email           homeemail
james       CT
mark                         mark@google.com
john                                         John@hotmail.com

I am creating a view and dont have privilages to alter the base tables. I was thinking maybe an IF statement might help


texts: Stored Procedures Best Practices: Fenced or Unfenced?

texts:
I believe I understand the reasons behind fenced and unfenced stored procedures.

Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine should there be issues with things like pointers.

Unfenced runs "inside" of the database, which means that performance is better.

From what I have also researched, SQL PL is always basically unfenced, because it is SQL and therefore cannot access memory like programming languages can.

C/C++ and Java procedures can run fenced or unfenced. But since they can possibly access memory, there should be a consideration for running them fenced, unless there is a certainty on the quality of the code to not crash and it needs performance.

First of all, am I correct in my understand of the above?

Next, is it generally a best practice to start out with all stored procedures (even those defined as SQL PL) as fenced first?

Any other best practices for stored procedures, especially as related to fencing and/or security?

  EDIT: Further research has shown that SQL PL procedures cannot run fenced. Since they do not contain any code that could harm the
  database engine such as pointers or file I/O, DB2 knows they are safe
  and runs them inside the engine (ie, unfenced). That being said, I am
  still looking for best practices regarding all other stored
  procedures.


texts:
I been trying to install SQL Server 2012 on Windows 7 SP1 32 bit.

Logged in as Administrator and launched setup.

Everything installs fine but SQL Server DB Services fails with following error while

One or more command-line switches were invalid

and current file which it was reading was : sql_config_repair_loc_wmi_provider_cpu32_1033

and in log its says:

  The SQL server feature 'SQL_Engine_Core_Inst' is not in a supported state for repair, as it was never successfully configured.  Only from successful installations can be repaired.  To continue, remove the specified SQL Server feature.


texts: SQL Server: how to select rows when changes happen?

texts:
I have a question how to select row when changes happen.
Here is the table:

CreationDate | Value
2019-06-04   | apple
2019-06-01   | apple
2019-05-30   | apple
2019-04-01   | orange
2019-03-01   | apple
2019-02-01   | apple
2019-01-01   | orange

If value doesn't change, then remove from the table.
I sort by date from new to old.
I want select value changes rows.

I expect a table like this:

CreationDate | Value
2019-05-30   | apple
2019-04-01   | orange
2019-02-01   | apple
2019-01-01   | orange

I want a table of which changes happened.


texts:
My SQL Server book says you can't run a function inside EXEC and shows an example:

DECLARE @NumberOfLetters int = 15;
EXEC(‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar)
 + ‘) AS ShortName FROM Production.Product’);

This doesn't work as the CAST() function needs to be fully resolved prior to the EXEC line, so we can modify the code as:

DECLARE @NumberOfLetters AS int = 15;
DECLARE @str AS varchar(255);
SET @str = ‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar) + ‘) AS ShortName FROM Production.Product’;
EXEC(@str);

This time it works because, as mentioned above, the CAST() function needs to be fully resolved prior to the EXEC line.

But I have one more question, what about the LEFT() function, why it can be run against EXEC?


texts: Is it possible to change the data-type of a primary key column in SQL Azure?

texts:
Possible Duplicate:
  How can I alter an existing Primary Key on SQL Azure?

OK, so say I have a table: 'Contact' and a primary key: 'ContactID'.

The primary key is an INT but I want to change it to a BIGINT.

In order to do this, I have to drop the primary key constraint.

In order to drop the constraint, I have to remove the clustered index.

But SQL Azure requires a clustered index on all tables at all times, so in order to remove the clustered index, I have to add another one on another field.

The problem is, I can't have more than one clustered index in a table.

So it seems like a catch-22: I can't remove the clustered index because there must be at least one, but I can't create another clustered index because there cannot be more than one.

So is it possible at all to change the data-type of a column in SQL Azure?


texts: How can I tell which data sources are being used in SSRS?

texts:
We have a bunch of SSRS (2008) reports deployed to our web portal.  We have edited some of the reports to use a shared data source different than the one with which it was originally deployed.

I am looking for a way to query the ReportServer database to show me which reports use which of these shared data sources. I found that you can use the XML data stored in Catalog.Content to show what data source is being used, but this appears to the data source with which the report was originally deployed.


texts:
We are moving from SQL Server to AuroraDB (mySQL).  Both our source database (SQL Server) and our target (AuroraDB) are on AWS RDS so we are naturally using DMS for the migration.

We make heavy use of synonyms in SQL Server (as I come from an Oracle background) but synonyms are not supported in mySQL.

It is of course not practical to modify tens of thousands of queries (which refer to synonyms) at switchover of the database.

It would be heavenly to make use of "Table Mapping" in AWS DMS.  At least from the name "Table Mapping", it's my assumption that this describes a method of mapping data import from one table name in the source to a different table name in the target.  Unfortunately, I don't see any way to do this in the GUI.  Further, I can't find any mention online of anyone ever trying this, so no examples of the JSON format for writing a mapping rule.

Has anyone out there accomplished this using AWS DMS?

Bonus: Since we have 480+ tables in our source database,  it would be nice to create the mappings in JSON.  It would be nice to know the JSON format for this type of mapping (if possible).


texts:
Why does full qualification appear to be getting ignored?

Here, I USE the database I'm querying, and get the expected results:

use [_DBMgt];
GO
select
    s.[name] as [Schema],
    o.[name] as [Table]
    from [_DBMgt].[sys].[objects] o
    inner join [_DBMgt].[sys].[schemas] s
        on s.[schema_id] = o.[schema_id]
    where OBJECTPROPERTY(o.[object_id], N'IsUserTable') = 1
    order by s.[name], o.[name]
;
GO

Schema Table
------ ---------------
dbo    TableAbbrLookup

(1 row(s) affected)

Here, I did not USE the database I intend to query, but fully qualify the database reference; however, I am not getting the expected results.

use [master];
GO
select
    s.[name] as [Schema],
    o.[name] as [Table]
    from [_DBMgt].[sys].[objects] o
    inner join [_DBMgt].[sys].[schemas] s
        on s.[schema_id] = o.[schema_id]
    where OBJECTPROPERTY(o.[object_id], N'IsUserTable') = 1
    order by s.[name], o.[name]
;
GO

Schema Table
------ ----------------------
sys    sqlagent_job_history
sys    sqlagent_jobsteps
sys    sqlagent_jobsteps_logs

(3 row(s) affected)

Either I'm expecting the wrong thing (likely) or I've found a bug (unlikely).

What am I missing?


texts: Managing and updating data from different tables with similar columns, centrally through a single form

texts:
I have year wise tables of mails in MS Access 2010.
Tables are having fields :
Mail ID, YYMM(Year and Month), Reference No, Subject
, Reference Date, Needs Action(Y/N), Assigned to, Status(Open/Close), Remarks.

"Mail ID" and "YYMM" are concatenated in the format YYMM-XXXX, for example 2101-7000, by query.
Each table is having around 7000 records and around 50% records are of nature "Needs Action".
I need to regularly monitor and update the status of "Needs Action" mails. How can I merge all the mails from all tables into one table or query and update them through a form in there respective year table.
I merged the tables with sql union query but it is not allowing to update the status. Please advise how can I do this.


texts: Repeatable read: the transaction may not be serializable with respect to other transactions

texts:
The text below is from 'Database System Concepts, Silberschatz. I can't understand the bolded part. How the transaction may not be serializable with respect to other transactions? And also how a transaction may find some of the data inserted by a committed transaction, but may not find other data inserted by the same transaction?

Repeatable read allows only committed data to be read and further
requires that, between two reads of a data item by a transaction, no
other transaction is allowed to update it. However, the transaction
may not be serializable with respect to other transactions. For
instance, when it is searching for data satisfying some conditions, a
transaction may find some of the data inserted by a committed
transaction, but may not find other data inserted by the same
transaction.


texts: Error message is causing Error Log to grow out of control

texts:
The error log is growing like crazy - in one day it has reached 32GB.

There is one error that keeps being logged.

I have searched online for this error, however I couldn't find any helpful solutions.

  Error: 8354, Severity: 16, State: 1.

  Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 997, Event class ID: 12, Cause: 997(Overlapped I/O operation is in progress.).

I want to add that this happened after updating the security patches by the System admin.

Sql Server 2012 running on windows 2008/R2


texts: Difference between isolation levels in locktable implementation

texts:
I'm trying to implement lock tables in database with c++ and mysql. i'm using tpc-a benchmarks where i simulate a mini bank database and i run a bunch of threads on it and each thread do 10 transactions concurrently and goal is to maintain consistency after all transaction finished their work.
here is a piece of code that is a part of for loop that each thread run 10 times :

g_lockTableAccount->lockRecord(hashInt(randomAccountNumSource, g_numAccount), randomAccountNumSource, GetCurrentThread());
    startTransaction(conn);
    //get first account balance and branch.
    {

        std::string query = "SELECT account_balance,branch_ID from `account` WHERE account_ID = " + std::to_string(randomAccountNumSource) + ";";
        executeQuery(conn, query);
        MYSQL_RES * res = mysql_use_result(conn);
        MYSQL_ROW row = mysql_fetch_row(res);

        sourceAccountBalance = toInt(row[0]);
        sourceAccountBranch = toInt(row[1]);
        mysql_free_result(res);

    }
    //--------------------------------------------------------------------
    sourceAccountBalance -= amount;
    {
        std::string query = "UPDATE `Account` SET account_balance = '" +
            std::to_string(sourceAccountBalance) + "' where account_ID = " + std::to_string(randomAccountNumSource) + ";";
        executeQuery(conn, query);

    }
    //--------------------------------------------------------------------
    commitTransaction(conn);
    g_lockTableAccount->unlockRecord(hashInt(randomAccountNumSource, g_numAccount), randomAccountNumSource);

i lock and unlock other records with the same pattern. but i feel there is something wrong with it because i think the pattern of running a sql command and committing it is not correct, but the problem is when i start transaction at the beginning of for loop and commit at the end this pattern ensue deadlocks because at any isolation level the database won't release the locks it acquired. how can i do this correctly and without getting deadlocks?
is there anyway at all?
how different isolation levels affect this problem?


texts:
We have a 32 node cluster with an average of approximately 150GB of data on each node. When running a full repair (not doing primary range) it is taking approximately 26 hours to complete. This seems very slow to complete.. any ideas on why this type of repair is going so slow? Are there any settings I may tweak to make it go faster?

Marshall


texts: SQL Server - Export large table without primary key

texts:
I need to sync a large table ~500 millions rows without a primary key between SQL Server and MySQL. The table has only a clustered composite non-unique index.

I do have a ODBC connection between the servers, but an import of ~8 million rows took around 45 minutes, so I believe a larger single import would be unreasonable as interruptions may occur at any point. I can't change the existing table structure, I can add other tables. After further reading, offset / fetch is not an option for large tables. "Select ... where x between ... and ... " is not an option as I don't have an unique key.

How can I export the table in batches that are guaranteed to contain all rows?
My problem is that since the clustered key is not unique, ordering after it would not guarantee the physical rows have the same order between consecutive queries and ordering after all columns would take too long. And how would you recommend to migrate the batches, through ODBC or CSV files?


texts: How do I select the next row in SELECT FOR UPDATE?

texts:
I am modifying my web crawler program to use SELECT FOR UPDATE to avoid multiple crawlers (with multiple children) pulling the same row to process.

How do I do this?

select * from visited where machine='1' order by revisit asc limit 1 for update

Lets say this query pulls website id # 12345

If I run this query on two crawler children, the second one waits for the first one to finish before selecting a row - is there a way for the second query to pull the next row? 12346 for example?

Each instance will select, update revisit to a future time, do work, and then commit.


texts: Ola Hallengren Index Optimise Script not Rebuilding Indexes

texts:
I have downloaded Ola Hallengren's script and deployed to master database.
I run it using the below...

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30

I get this output in SSMS but the Indexes have not been rebuilt. Fragmentation is still very high. Am I missing something?

Date and time: 2015-03-01 14:07:24
Server: TestSvr
Version: 10.50.2500.0
Edition: Standard Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000, @SortInTempdb = 'N',
@MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL,
@LOBCompaction = 'Y', @UpdateStatistics = NULL,
@OnlyModifiedStatistics = 'N', @StatisticsSample = NULL,
@StatisticsResample = 'N', @PartitionLevel = 'Y',
@MSShippedObjects = 'N',
@Indexes = NULL, @TimeLimit = NULL, @Delay = NULL,
@WaitAtLowPriorityMaxDuration = NULL,
@WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL,
@LogToTable = 'N', @Execute = 'Y'
Source: https://ola.hallengren.com

Date and time: 2015-03-01 14:07:24
Database: [TestData]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE


texts: How do I use the IF...ELSE condition in a WHERE clause?

texts:
I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.

Should this work?

CREATE OR REPLACE package body If_Else_Pack IS

    PROCEDURE Moving
    (
        obj_A       IN varchar2,
        obj_B       IN varchar2,
        obj_C       IN varchar2,
        obj_D       IN varchar2,
        cur_Result  OUT T_CURSOR
    ) IS
    BEGIN
        OPEN cur_Result FOR
        SELECT
            w.assetid,
            w.vehiclenumber,
            w.LatLong,
            w.CurrentSpeed,
            w.timeOfMovement,
            w.CurrentPlace,
            w.curTime,
            w.motion,
            w.fuelR,
            w.VehicleStart
        FROM waypoints1 w
        WHERE
            IF ((obj_D= '0' OR obj_D IS NULL) AND (obj_C= '0' OR  obj_C IS NULL)) THEN
                WHERE w.customer_id =obj_A
                AND w.delegate_user_id = obj_B;
            ELSE IF ((obj_D= '0 'OR obj_D IS NULL) AND (obj_C<> '0' OR obj_C IS NOT NULL)) THEN
                WHERE w.customer_id = obj_A
                AND w.category_id = obj_C
                AND w.delegate_user_id = obj_B;
            ELSE IF ((obj_D<> '0' OR obj_Dis NOT NULL) AND(obj_C= '0' OR obj_C IS NULL)) THEN
                WHERE w.customer_id = obj_A
                AND w.fleet_id = obj_D
                AND w.delegate_user_id = obj_B;
    END MOVING;
END IF_ELSE_PACK;


texts: Set up new publication on SQL Server 2014 database which is part of an availability group

texts:
I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica.

On the instance I have already another running publication. The distributor is running outside of the availability group, even on a different domain.

Problem

Initially the set up of the distributor was done on the other replica (which is currently the secondary). Also the other existing publication was initially created on the other hosts. That's the first publication I have to create after I did a failover of the availability group to the secondary.

Error

Running the following command on the database to publish

EXEC sp_replicationdboption @dbname = 'mydatabase',
                            @optname = N'publish',
                            @value = N'true';

I received the following error:

  Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 56 [Batch Start Line 0]
  The Distributor has not been installed correctly. Could not enable database for publishing.

I wonder why that occur as I have already on publication up and running on another database without any error and also working properly.

Ideas

Checked the management logs doesn't help as nothing was logged. After cross checking the distributor I saw also that the publisher is registered properly.

Any ideas what could be issues here?


texts: Multiple one to many relationships between tables

texts:
I'm creating a simple dabase design with three entities and have a question about the relationships between my Employee and Task tables.

The thing is:

A task has an owner, which is represented by the "createdBy" attribute, which is a FK of empID;
A task also has an assignee, which is represented by the "assignedTo" attribute, which is also a FK of empID;

An employee can have multiple tasks, even if there was only one field using empID as a FK. But, you could say a task can have multiple employees, eventhough it can only have two. This would make it a many to many relationship, thus require an additional table.

However, because the number of employees that a task can have is fixed, because it has two designated fields, I feel like this is not the proper solution. Still, it feels strange to have multiple one to many relationships.

Could someone tell me whether or not it is possible to have the relationships the way they are now? Or would I need to treat it as a many to many relationship anyway?


texts:
We have a client who has been having performance issues. One of the developers is saying that a select * from a table is taking ~50 seconds to perform the query (no joins just straight select with no fields). The table has 166 columns (I know that is probably over the top, but this is from an ERP software that the developer integrates with) and around 400,000 rows.

Now considering the crazy number of columns and 400k records, 50 seconds seems about right for me.

The SQL Server is running on a Hyper-V VM and has decent hard drive read/write speeds, the hard-drive specs are: 16 x 600gb 15RPM 6.0Gbps SAS drives in Raid 10.

The VM performs well and has plenty of RAM, just interested to see if any experts can chime in whether they think that query is performing within reason?


texts: Replace all rows in a table based on date

texts:
I need to copy the values from a staging table into a production table in SQL Azure V12, considering the following rule:

  I need to replace all rows for a given day (see datefield column) in the production table with all rows for the same given day in the staging table provided that the extraction date/time (see extract_datetime column) of the rows in the staging table are newer than what is in the production table.

  EDIT: I also must include all new rows for days that don't already exist in the production table. See the purple rows in the table examples below.

The reason for this rule is that records tend to be amended and deleted from the system which we are extracting data from, so even though at one time a given day could have 200 entries for it, it might be that subsequently we only end up with 3 rows for that same given day.

If a day is included in a data extract, it will always have the most up-to-date records for that entire day (from 00:00:00 to 23:59:59 hrs).

Here is an example. Consider the following tables:

In this example, the production table has 9 rows in total, as follows:

01/07/2016: 4 rows
02/07/2016: 2 rows
03/07/2016: 3 rows

Then we do a load what is in the staging table into the production table, and the following happens:

01/07/2016: The 4 rows in production for this day are replaced with the 1 row in the staging for this day, because what is in staging has a newer extraction date/time.
02/07/2016: The 2 rows in production for this day are replaced with the 4 rows in the staging for this day, because what is in staging has a newer extraction date/time.
03/07/2016: The 3 rows in production for this day are kept and those in the staging table are discarded, because what is in staging has an older extraction date/time.
07/07/2016: The 1 row in staging for this day is inserted into the production table. This is a brand new row and there is no match for it in the production table.

So in the end we end up with the following production table:

These data loads are never bigger than a few megabytes, so at other tables with less complex rules what I tend to do is:

First delete all rows in staging for when there are newer records in production;
Delete all rows in production for when there are newer rows in staging; then
Insert the remaining rows from staging into production, then truncate staging for the next load.

In this case I am quick lost as on how to do this matching because it is not really in a row by row basis (so I think), so any advice on how to get this code working is greatly appreciated.

Regards,
P.


texts: Oracle 12c create manual script for automatic startup on RHEL7

texts:
I need to create a script to start and shut down a 12c database in rhel7 automatically when the operating system starts and shuts down, it turns out that I have tried the common options with dbstart and dbshut, but it only works for the listener, it does nothing with the database instance ... recently I did an migration of asm to the operating system's filesystem for that reason I disabled the start of asm since I didn't need it, I don't know if this would be influencing the dbstart and dbshut scripts Don't start, I have the oratab file like this:
myoradb:/u01/app/oracle/db/12.1:Y
The truth is that the content of these dbstart and dbshut seems a bit dense and I do not see where the problem could be so that they do not run correctly with the database instance.
Is there an example of how to create these scripts manually without using dbstart and dbshut ???
Thanks


texts: postgresql recovery.conf file location in 11.5

texts:
I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing method for incremental + PITR for postgres 11.5. However, I couldn't find much and found https://www.scalingpostgres.com/tutorials/postgresql-backup-point-in-time-recovery/

meaningful. I started working around the steps mentioned there. However at #add recovery.conf

    # add recovery.conf
    nano /var/lib/postgresql/10/main/recovery.conf

The article says to create the recovery.conf in main directory. However, as per 11.5 installation, there is no main directory. The directory structure is like:

    /var/lib/pgsql/11/data
    [root@local-pos-52 data]# ls -ltr
      total 96
      -rw-------. 1 postgres postgres 23971 Aug 30 16:05 postgresql.conf
      drwx------. 6 postgres postgres    50 Aug 30 16:07 base
      -rw-------. 1 postgres postgres  4370 Aug 30 16:12 pg_hba.conf
       drwx------. 2 postgres postgres     6 Aug 30 16:12 pg_replslot
       drwx------. 2 postgres postgres    17 Aug 30 19:04 pg_notify
       -rw-------. 1 postgres postgres    58 Aug 30 19:04 postmaster.opts
       -rw-------. 1 postgres postgres    30 Aug 30 19:04 current_logfiles
        drwx------. 2 postgres postgres     6 Aug 30 19:04 pg_stat
        drwx------. 4 postgres postgres    65 Aug 30 19:04 pg_logical
        drwx------. 3 postgres postgres  4096 Aug 30 19:04 pg_wal
       -rw-------. 1 postgres postgres    96 Aug 30 19:04 postmaster.pid
        drwx------. 2 postgres postgres  4096 Aug 30 19:04 global
        drwx------. 2 postgres postgres    60 Aug 30 19:19 pg_stat_tmp
        -rw-------. 1 postgres postgres     3 Aug 30  2019 PG_VERSION
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_twophase
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_tblspc
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_snapshots
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_serial
        drwx------. 4 postgres postgres    34 Aug 30  2019 pg_multixact
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_dynshmem
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_commit_ts
        -rw-------. 1 postgres postgres 23891 Aug 30  2019 postgresql.conf-
       bak
       -rw-------. 1 postgres postgres    88 Aug 30  2019
       postgresql.auto.conf
       -rw-------. 1 postgres postgres  1636 Aug 30  2019 pg_ident.conf
      -rw-------. 1 postgres postgres  4269 Aug 30  2019 pg_hba.conf-bak
       drwx------. 2 postgres postgres    17 Aug 30  2019 pg_xact
       drwx------. 2 postgres postgres    17 Aug 30  2019 pg_subtrans
       drwx------. 2 postgres postgres    31 Aug 30  2019 log

Also, there is no folder named "postgresql" got created in this installation at /var/lib location.

I tried to go with creating recovery.conf at this path "/var/lib/pgsql/11/data" only. However, after that when I restarted postgres services,it didn't work.

It will be appreciated ,if someone specifies where to create  recovery.conf, so that it will actually work or share the doc/link to implement incremental backups as well as PITR for postgres 11.5

Thanks for any help in advance!


texts:
I'm creating unit tests on a python application that uses digital certs on the server. Since I am not testing the connection between the server and the database, I need to setup the temporary test database to authenticate w/o a password for the CN listed in the certificate. The production server uses peer authentication, but I want to avoid having to force the unit test to run under a different username.

Long story short, is there a way to create a superuser in postgresql with no password in order to create a database, run a schema, etc.?


texts: How to repair corrupted PostgreSQL pg_toast error

texts:
The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I try to access certain rows on my single biggest table, I get errors like:

ERROR:  could not read block 28126 in file "base/106800/107273": read only 0 of 8192 bytes

I've mostly fixed this by going through the rows and deleting the ones that were not retrievable. However, I still get errors like:

ERROR: missing chunk number 0 for toast value 151065 in pg_toast_106852

Is there any way to fix this? Googling doesn't find any suggestions. I found this similar question, but the sole answer is essentially "I think your database is corrupt", which is diagnosis but not a solution.

Other proposed solutions were to do a vacuuming or reindexing, both on the table and the entire database, but those had no effect.

How do I repair this damage? I know truncating the table would fix it, but that would destroy all remaining data, and most of the records appear to be intact.


texts: Navicat Premium doesn't show/list tables in postgres database schema

texts:
I have a local instance of postgres in my Windows machine, when I explore the databases and schemas in PgAdmin4 I can see every table and make queries and etc.

But when I connect to this same postgres instance with same host, user and password with Navicat Premium it only show me the databases but I cannot see the tables in them, but if I do a query I get the result.


texts: Calling stored Procedure from one db to give me a result in another db

texts:
CREATE DEFINER=root@localhost
PROCEDURE AccountReqs( IN IsNewAccountStart VARCHAR (255)
                 , In AccountName varchar (255)
                 , IN AccountNumber varchar(255)
                 , In PaymentTerm varchar(255)
                 , IN Batch varchar (255)
                 , In AdditionalEmailAddresses varchar (500)
                 , In WorkEmail varchar(500)
                 , In PersonalEmail varchar (500) )`
BEGIN
    SELECT * FROM ACCOUNTS
    WHERE IsNewAccountStart <>'TRUE' AND IsNewAccountStart <>' '
    Union all
    SELECT * FROM ACCOUNTS WHERE AccountName=' '
    Union all
    SELECT * FROM ACCOUNTS WHERE AccountNumber=' '
    Union all
    SELECT * FROM ACCOUNTS
    WHERE PaymentTerm <> 'Net 30'
    AND PaymentTerm <> 'Net 45'
    AND PaymentTerm <> 'Net 60'
    AND PaymentTerm<> 'Due Upon Receipt'
    Union All
    SELECT * FROM ACCOUNTS WHERE Batch <> 'Batch1';

    SELECT * FROM ACCOUNTS
    WHERE AdditionalEmailAddresses <> ' '
    OR WorkEmail <> ' '
    OR PersonalEmail <> ' ';

END


texts: User cannot query cross-database, despite having permissions for both databases

texts:
SQL Server 2017 Enterprise (14.0.3356.20), running on Windows Server 2016.
I have a situation where an application's user needs to join on a table in a separate database in the same instance, but I'm getting permissions errors when I try to run anything cross-database.  Basically, what it boils down to is that this works fine:
use DB2
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM dbo.Table
REVERT

But this doesn't:
use DB1
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM DB2.dbo.Table
REVERT

I get the following error on the second query:
Msg 916, Level 14, State 1, Line 3
The server principal "domain\appUser" is not able to access the database "DB2" under the current security context.

The user is mapped to both databases.  There are plenty of places elsewhere in our other applications that we have used this same kind of cross-database join, and they work fine.  Logged in as my admin user, this also works fine of course. I admittedly don't know too much about permissions in SQL Server, and this one has me stumped.


texts: TempDB uses a lot of space in buffer pool

texts:
I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory.
That's the query I used:

SELECT COUNT(*) AS cached_pages_count ,
(count(*) * 8.0)/1024 as MB,
        CASE database_id
          WHEN 32767 THEN 'ResourceDb'
          ELSE DB_NAME(database_id)
        END AS Database_name
    FROM sys.dm_os_buffer_descriptors
    GROUP BY DB_NAME(database_id) ,
        database_id
    ORDER BY cached_pages_count DESC;

But when I check what tables are in TempDB, there are only 40 tables and they use 728KB. This is the query:

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY  t.Name, s.Name, p.Rows
ORDER BY  t.Name

I also checked sys.dm_db_session_space_usage to see if there was a session that allocated all that space in TempDB

My buffer cache is 2.2GB and almost 50% is taken by TempDB

Can someone try to explain why TempDB is so large?

UPDATE
I did a DBCC PAGE of some pages found in the dm_os_buffer_descriptors and it looks like they are not allocated (GAM/SGAM Not Allocated, PFS 0_PCT_FULL)

  PAGE HEADER:

  Page @0x0000000DB026C000

  m_pageId = (1:159735)               m_headerVersion = 1
  m_type = 1 m_typeFlagBits = 0x0                m_level = 0
  m_flagBits = 0x20 m_objId (AllocUnitId.idObj) = -1948083318
  m_indexId (AllocUnitId.idInd) = 1 Metadata: AllocUnitId =
  435280365092864                                  Metadata: PartitionId
  = 0 Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (1:160115) m_nextPage = (1:160144)             pminlen =
  13                        m_slotCnt = 217 m_freeCnt = 3406
  m_freeData = 4352                   m_reservedCnt = 0 m_lsn =
  (13668:97392:89)            m_xactReserved = 0
  m_xdesId = (0:0) m_ghostRecCnt = 0                   m_tornBits = 0
  DB Frag ID = 1

  Allocation Status

  GAM (1:2) = NOT ALLOCATED           SGAM (1:3) = NOT ALLOCATED
  PFS (1:153672) = 0x0   0_PCT_FULL DIFF (1:6) = NOT CHANGED
  ML (1:7) = NOT MIN_LOGGED


texts: "INNER JOIN" vs "INTERSECT" performance comparison when dealing with huge amounts of data in Oracle

texts:
I have two tables with the structure below:

create table Table_One
(Ef_date           Date,
Customer_Num      Number,
<Other_Columns>   <Combination of Number/varchar2(50)>);

create table Table_TWO
(Ef_date           Date,
Customer_Num      Number,
<Other_Columns>   <Combination of Number/varchar2(50)>);

There is a Normal index on the customer_num column of both tables and there is a partition on the ef_date column. I want to know which query is better in terms of performance when there is a huge amount of data, for instance there are nearly 54,962,020 records added each month  .

QUERY-1:

select Customer_Num
from Table_one
<Conditions on Other_columns>

    intersect

select Customer_Num
from Table_TwO
<Conditions on Other_columns>

QUERY-2:

select t1.customer_Num
  from Table_one t1
inner join Table_Two t2
on t1.customer_num = t2.customer_num


texts: How to monitor a file share witness for accessibility

texts:
In the context of a SQL Server Availability Group, what's the best way to monitor a file share witness for accessibility?

I want to make sure the witness can be accessed by the Availability Group replicas, which is something different than just being available.  For instance, the account used to run SQL Server needs access rights to the file share.

Edit: I recently noticed some events logged by the cluster worthy of being monitored.

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Date:          6/1/2019 1:23:09 PM
Event ID:      1564
Task Category: File Share Witness Resource
Level:         Critical
Keywords:
User:          SYSTEM
Computer:      node1.contoso.com
Description:
File share witness resource 'File Share Witness' failed to arbitrate for
the file share '\\servername\sharename'. Please ensure that file share
'\\servername\sharename' exists and is accessible by the cluster.

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Date:          6/1/2019 1:23:08 PM
Event ID:      1562
Task Category: File Share Witness Resource
Level:         Warning
Keywords:
User:          SYSTEM
Computer:      node1.contoso.com
Description:
File share witness resource 'File Share Witness' failed a periodic health
check on file share '\\servername\sharename'. Please ensure that file
share '\\servername\sharename' exists and is accessible by the cluster.


texts:
Hope you can help me out.

My current SQL setup is as following
Two SQL Instances (2012 and 2016) stacked on a single virtual machine. With the SQL components SSRS and SSIS 2016 installed on same.

The only reason I did this was two lower licencing costs. My 2012 instance is primarily for older apps that aren't certified on SQL Server 2016. With the goal of moving all my databases over to SQL 2016.

My problem, I've had a request from a supplier for a  standalone virtual machine with SSRS 2016 installed for there reports only. I don't believe we are covered to install SQL components such as SSRS and SSIS on a separate virtual machine.

Any thoughts ?
Thanks