texts: postgresql: mapping json array to string array

texts:
I have following PostgreSql function:
CREATE OR REPLACE FUNCTION "fn_get_list_invoice_number_by_product_ids"(_product_ids INT[])

RETURNS "pg_catalog"."json" AS $BODY$

BEGIN
    RETURN (SELECT json_agg((record)) FROM
                        (
                                SELECT DISTINCT invoice_number FROM sale.invoice_item
                                WHERE product_id = ANY(_product_ids)
                        ) record
                    );
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

After using this function I get following result:
[{"invoice_number":"POS20-000043A"},
 {"invoice_number":"POS20-000002E"},
 {"invoice_number":"POS20-000009E"},
 {"invoice_number":"POS20-000003E"},
 {"invoice_number":"POS20-000046A"}]

But if I want result as below, what should I do?
[
      "POS20-000043A",
      "POS20-000002E",
      "POS20-000009E",
      "POS20-000003E",
      "POS20-000046A"
]


texts:
My listener exists and is started, but when I try to connect to the database it tells me no listener exists.  I've tried restarting the listener to no effect.

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = CLRExtProc)
      (SID_NAME = CLRExtProc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle

tsnames.ora

EPABFM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = epabfm)
    )
  )

LISTENER_EPABFM =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

Result of lsnrctl status:

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 19-SEP-2015 14:59:14

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.164)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                19-SEP-2015 14:58:11
Uptime                    0 days 0 hr. 1 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\firexion-main\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.164)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "epabfm" has 1 instance(s).
  Instance "epabfm", status READY, has 1 handler(s) for this service...
Service "epabfmXDB" has 1 instance(s).
  Instance "epabfm", status READY, has 1 handler(s) for this service...
The command completed successfully


texts: Change SQL Server service account without restarting SQL Server

texts:
For security reasons, we want to change the password that starts the SQL Server service everyday.  Is it possible to do this without restarting the service?

If it is possible will the changes be immediately applied, or will I eventually need to restart the service to get the new password?

We were thinking of doing this change in the services option of the windows OS and not in SQL Server configuration manager.


texts: With the clock change ahead in Europe - how does SQL Server deal with the clock change? Are there known issues related to a clock change?


texts: Can comments in stored procedures cause problems

texts:
We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state for a long duration. I've ran this sp to track down what query is being ran by the processes and to my surprise, the following is what we discovered:

  --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725',
  ... Several more executes that are commented out

Here's what our SP looks like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725',
ALTER              PROCEDURE [SomeDB].[SP_POS_SU012_XML] @CRDATE        VARCHAR(08) ,
                    @CUS_CD     VARCHAR(10) ,
                    @PRODUCT_CD     VARCHAR(13) ,
                    @ORDER_GB   VARCHAR(03) ,
                    @CANCEL_YN      VARCHAR(01)
                            ...

We weren't sure if the commented execute codes we defined for testing purposes in SP were actually being ran with the SP calls. From our internal testing, we couldn't reproduce the same behavior, but removing those commented lines of execute seemed to do the trick as sp_who2 no longed showed hundreds of processes stuck on SELECT.

But since I have absolutely no way to tell if this was the real cause or not, I have to ask: could such comments actually produce side-effects described here? Or worse, could this have happened from a deliberate attempt to attack our server/database?


texts: I just inherited a set of new servers and whoever set them up left the "Log On As" account as the default "NT Service\MSSQLSERVER". I'd like to change that over to an Active Directory service name. I have never attempted changing it after we have already went live in production. Is there anything I should be aware of? Will it require a restart of the service? This server is only using SQL, not SSIS, SSAS, SSRS. I will make sure the AD service name has access to all the folders necessary to reach files. Anything else I'm missing?


texts:
I would like to transform the following sql (e.g. select Name,X,Y from mytable) result:

Name  | X   | Y
bla1  | 0.1 | 0.2
bla2  | 0.3 | 0.4

Into the following form that is displayed correctly in an Excel scatter chart (i.e. with each row as a series and each name as a label):

Name  | 0.1 | 0.3
bla1  | 0.2 |
bla2  |     | 0.4

This is the only way a scatter chart will display x-y data correctly without fiddling with chart settings:


texts: Can't figure out how to start postgres on port 5432 in windows

texts:
Setting up Postgres on Windows for frontend web dev

This is not my area of expertise (frontend guy), but I need postgres simply running on my side to connect with the test DB for the project I'm working on:

I have to run this before I start my project: dropdb [db_name] && createdb [db_name] && npm run migration:run:dev

When I do so, I get the typical:

dropdb: could not connect to database template1: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

This tells me that postgres isn't running on port 5432, which I verified in Resource Monitor and inside pgAdmin... in pgAdmin, all the PIDs are NOT 5432 and when I try to change the port to 5432 (although, already set to that), I get:

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

I have postgres installed via the packaged installer, and I've tried various solutions, such as:
https://tableplus.io/blog/2018/10/how-to-start-stop-restart-postgresql-server.html
https://stackoverflow.com/questions/38466190/cant-connect-to-postgresql-on-port-5432 (I don't need remote connections)

Note(s)

I use homebrew to do this on my mac in like 2 commands... but services doesn't work with linuxbrew. I don't think my network is an issue if my mac has no problem with it on the same network.

I'm thinking something could be running on port 5432, but nothing appears to be in Resource Monitor.

Output from netstat -tlna | grep 5432

Nothing...

Edits

It seems the PID in Network Resource and pgAdmin don't refer to the port it's running on???


texts: Pre-filtering on right table in a LEFT JOIN

texts:
I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s):

create table parent (id integer primary key, name text not null);
create table child (id integer primary key, pid integer not null references parent(id));

select parent.id, parent.name, q.cnt from parent
left join (
  select pid, count(*) cnt from child
-- where pid in (select id from parent where name like '%xyz%')
  group by pid
) q on parent.id = q.pid
where name like '%xyz%'

(Actual schema/query is more convoluted but the gist is the same.)

The issue is, unless I uncomment the WHERE clause on the right table the query takes significantly more time to execute. Having to specify the same filter in two places doesn't feel right. Am I doing something wrong? Should I transform the query somehow? Why is the inner filter even necessary; shouldn't it automatically discard records from the right table that cannot be joined? The database is PostgreSQL by the way.


texts: SQL Server 2016 - write output file to mapped Samba share (CentOS 7)

texts:
I have a stored proc that's going through the tables, getting data, and write the result into a csv file (mainly to provide up to date data for FTP customers).

When writing it to the local D: drive, everything runs fine. File writes as it should and everyone is happy.

As I'm setting up new FTP server, I decided to try and ditch 12672 batch scripts transferring files from the DB server (Win Server 2012 r2 running SQL Server 2016)
to the FPT server (CentOS 7 + Samba) and have the query outputs written straight there.

SQL Server reports that query was executed successfully, but the output file disappears into a network black hole somewhere along the way.

Slowly losing my mind with this one.

Below list of things that I'm 99% sure are not causing the issue:

Samba - even though security is still mildly paranoid it's
temporarily not user restricted.
Firewall - turning it off / on makes no difference whatsoever.
SELinux - same as firewalld, enforcing, permissive, disabled makes no
difference.
File system permissions on Linux - 777'ed a test share, and su'ed as
MSSQLUSER and successfully written a file in the same path I'm trying
to get SQL server to write into, to check that.

Samba logs absolutely nothing, which leads me to believe SQL isn't even trying to touch that share. There's nothing at all on the Linux side that would suggest SQL tried to make a connection.

My current theory is that it's refusing to do it because it's not SQL Server's service account that mapped the share, so here goes:

Is there a way to map that network drive for all users? I'm reluctant adding extra code to the stored proc to map the drive / delete it afterwards. End game is to have all the internal transfers working this way, meaning quite a number of SP's to alter, and different schedules would at some point inevitably clash. Not to mention the insane number of mappings per day. Countless Google searches point towards a simple answer "No.", but as they say, hope dies last.
What potential consequences am I faced with if I were to change the service accounts (NT SERVICE\MSSQLSERVER) credentials? Unfortunately I haven't got a server I can sacrifice in the name of science and find out myself.
Is there a simple solution I'm missing that'd allow me to do this? Am
I trying to re-invent the wheel?

Any suggestions appreciated. I can do / change whatever I want on the Linux box, Windows side of things is a bit more complicated as my knowledge / experience with Microsoft systems is "mediocre" at best.


texts: Db design for historical option data (~200GB)

texts:
I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like:

String symbol
Date date
Date exp;
double strike;
double ask;
double bid;
<20 other columns>

95% of the time I will be fetching the current date's data. But the other 5% of the time I want to analyze historical data.

Most of the queries would involve some combination of symbol/date/exp.

I'm not sure how to best organize the table/s in terms of efficiency. Would it be best to put the data into two tables: historical and current? Or an index on the date? Or maybe an extra boolean column (isCurrent) which indicates which data is current or not?

If it matters, I'm using java/hibernate/mysql under the covers.

Thanks


texts: Optimise select distinct from large table

texts:
I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records comes down to 145,878,852. The number of distinct records are 107,311,357.

select count(*)
from [BroadcastOpens] with (nolock)
where [OpenTimeUtc] >= @StartDate
and  [OpenTimeUtc] <@EndDate

The most time consuming process is SELECT DISTINCT..., which took about 40 minutes to run.

SELECT DISTINCT [SubscriberId],[OpenTimeUtc]
FROM [BroadcastOpens] WITH (nolock)
WHERE [OpenTimeUtc] >= @StartDate
AND [OpenTimeUtc] <@EndDate

I have created two indexes on this table for both columns BroadcastId and OpenTimeUtc. They helped to speed up the process, but seem to be less significant.

CREATE NONCLUSTERED INDEX [IX_BroadcastOpens_BroadcastId_Temp]
ON [dbo].[BroadcastOpens]([BroadcastId])

CREATE NONCLUSTERED INDEX [IX_BroadcastOpens_OpenTimeUtc_Temp]
ON [dbo].[BroadcastOpens]([OpenTimeUtc])

As suggested by Martin Smith, I also have created an indexed view on Table BroadcastOpens grouped by [SubscriberId],[OpenTimeUtc],[BroadcastId].

CREATE VIEW dbo.vwBroadcastOpensRecords
WITH SCHEMABINDING
AS

SELECT [SubscriberId],[OpenTimeUtc],[BroadcastId], COUNT_BIG(*) as tmp
from [dbo].[BroadcastOpens] group by [SubscriberId],[OpenTimeUtc],   [BroadcastId]

CREATE UNIQUE CLUSTERED INDEX CIX_vwBroadcastOpensRecords_Temp ON
vwBroadcastOpensRecords(SubscriberId, OpenTimeUtc,BroadcastId);

This is the step in the query that causes problem:

I am now testing both approaches to see which one yields better performances.

No changes to the existing tsql query, no non clustered indexed created. Running on production server.

No changes to the existing tsql query, created two non clustered indexes on the table. Running on staging server.

Create an indexed view, and modified existing tsql query to use this new indexed view to replace table ... testing... (I stopped the execution of the query as it was running for 28 minutes already... I suspect there is something wrong with my indexed view...)

Just wondering: The table BroadcastOpens gets constantly updated, does this have an impact on the indexed view that I created?

Any suggestions on how to improve the performance of this query are appreciated.

Thank you HABO and Martin Smith for your invaluable help!


texts: Finding how many times a value is shown on a table - Postgresql

texts:
I am trying to find employees that sold the most different type of subscriptions. The tables are:

Employee(PK employee_id, name, email, phone_number)
Person(PK email, ...)
Order (PK invoice_id, customer_name, customer_email, FK product_id)
Product (PK product_id, FK subscription_type, service_provider)
Subscription(PK subscription_type, price)

I can get some results but instead of finding how many types of subscription an employee sold, I get the number of subscriptions sold by the employee by subscription type.

SELECT COUNT (employee_id),employee_id,
                            s.subscription_type AS type
FROM employee
INNER JOIN person p USING(email)
INNER JOIN order o USING(email)
INNER JOIN product pr ON o.product_id = pr.product_id
INNER JOIN subscription s ON pr.subscription_type = s.subscription_type
GROUP BY employee_id,
         s.s
ORDER BY employee_id

an example:

    +-------+-------------------------------+----------+
    | count | employee_id                   | type     |
    +-------+-------------------------------+----------+
    |     3 |                          1111 | monthly  |
    |     2 |                          1111 | yearly   |
    |     2 |                          2222 | monthly  |
    |     1 |                          2222 | lifetime |
    |     5 |                          2222 | yearly   |
    +-------+-------------------------------+----------+

but I don't  want to see how many monthly he sold, I want to see how many different types he sold. In this case that would be (111,2) and (222,3). I am missing something obvious here, please help me find it.


texts: Should I replace my varchar primary key with an integer primary key?

texts:
I have a table that has a varchar primary key that's associated with "Base Product Codes".  They rarely change, and if they do, it's usually just being deleted or added.  I figured it would be better to index part of the product code so I could perform searches on the data based on a partial product code.

As an example,  "Base Product Codes" are in the following format:

ABC-AB12

The table stores the relationship between a product and accessory, and there can be multiple relationships for each product or accessory:

Id          |     Prod     |    Acc    | Val
---------------------------------------------
ABC-AB12_1  |    ABC-AB12  |   ABC1    |  1
ABC-AB12_2  |    ABC-AB12  |   DEF1    |  2
ABC-AB12_3  |    ABC-AB12  |   GHI1    |  A

I'm storing the id for each relationship as the product base code with an integer tacked onto the end of it.

I figured if I needed to to a search for all of the relationships for a particular product that it would be faster to use the primary key and search than it would be to search on the Prod column.

Is that an incorrect assumption?  Should I change the Id to be an auto incrementing integer instead?


texts:
I created a report that records entries
from a table which has 4 columns

1 Shipper ID
2 SHipper Reference
3 loading
4 Completion

My primary keys are columns 1 and 2 there are no foriegn keys, columns 3 and 4 are mandatory fileds
with a yes or no value constraint

what I want is to create a constraint that doesnt allow the user to pick these two fields (completeion and loading) as N in the same time but they can be both Y at the same time

what is the code for that constraint?

this code was my best shot

ALTER TABLE Table_Name
ADD CONSTRAINT Double_constraint check (Completion!='N' and Loading!='N')

but this one wont allow anything but completion to be Y and Loading to be N


texts: SQL Server - One of the cores always at 100%

texts:
I'm running SQL Server Web on a VM with 20 cores. (I know, Web edition can only make use of 16 cores but I'll get that resolved soon when I upgrade to Standard).

But what's funny to me is that for some reason, one of the cores is always at 100%. I've never seen this before as usually the load is spread out between all the cores.

Anyone experience this?

The long running queries/transactions are all types as this is handling an ecommerce site. The transactions themselves aren't the issue as I can paste them into new query window and they run fast so it sounds like it's just the sheer amount of them. Also, when traffic does down during off peak hours, I no longer have the issue.

There are no other applications running on the server that is using more than 1-2% of the CPU.


texts: AUTO_CREATE_STATISTICS set to OFF in tempdb

texts:
It has come to my attention that a number of my databases (2008 & 2012) do not have the setting AUTO_CREATE_STATISTICS set to ON for the tempDB.
In the Microsoft documentation it is set to ON as default.
Does turning the setting off have any performance enhancements/reductions?
What are the implications of turning this OFF?
Will any temporary tables or internal objects be suffering?
Thanks


texts: Does large VARCHAR take up more room in the transaction log

texts: On SQL Server, would VARCHAR(500) take up more space in the transaction log than VARCHAR(100) when all the rows have no more than 100 characters?


texts: Get data from different tables indexed by same ID

texts:
Database: postgresql 12. I have a table people:

person_id | name
----------+-----
1         |John
2         |Paul
3         |Jim
...

Each person can have zero or more car insurance contracts, so I have another table car_contracts as follows:

contract_id | person_id | contract_number | date_from | insured_value | car_tag | .... (more fields)
------------+-----------+-----------------+-----------+---------------+---------+
1           | 1         | C99             |2020-01-01 | 5000          | ABC     | ....
2           | 1         | C02             |2020-03-01 | 4000          | XYZ     | ....
3           | 3         | C96             |2020-02-02 | 12000         | KKK     | ....
4           | 1         | C01             |2019-01-01 | 5500          | XXX     | ....
5           | 2         | C02             |2019-06-01 | 1000          | 666     | ....
6           | 3         | C03             |2020-02-20 | 11000         | KPX     | ....
...

Each person can also have zero or more power plants (or whatever; the point is, another table with different fields but still tied to people by means of person_id), so the table plants is like

plant_id | person_id | plant_name | power | address     | .... (more fields)
---------+-----------+------------+-------+-------------+
1        | 2         | plant01    |100    | 230 rue st. | ....
2        | 3         | jimsplant1 |50     | ...         | ....
3        | 3         | jimsplant2 |65.5   |             | ....
4        | 2         | plant02    |12.5   |             | ....
5        | 1         | house01    |20     |             | ....
6        | 1         | house02    |20     |             | ....
...

I can either get a list of people with their contracts or people with their plants by joining the people table to the car_contracts and plants table respectively.
Is it possible to somehow get all the information at once (people with their contracts and their plants) with a single query?

The main issue seems to me that the number of contracts and the number of plants have no relation and can be very different, so I think joining people with contracts with plants makes little sense.


texts: Attaching a database in SSMS gives error

texts:
I am accessing SQL Server using SSMS, and a user with adequate permissions.

When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), they get an error message:

  Cannot show requested dialog.
  Parameter name: nColIndex

  Actual value was -1. (Microsoft.SqlServer.GridControl)

    at Microsoft.SqlServer.Management.UI.Grid.GridControl.SetColumnWidthInternal(Int32 nColIndex, GridColumnWidthType widthType, Int32 nWidth)
  ...
    at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SetGridColumnWidth(SqlManagerUIDlgGrid grid, Double[] columnWidthRatio, Int32 columnIndex)
  ...

If the same user uses a T-SQL command such as below, then it works fine:

CREATE DATABASE [hgnx] ON
( FILENAME = N'E:\db\hgnx.mdf' ),
( FILENAME = N'E:\db\hgnx_log.ldf' )
FOR ATTACH


texts: My database got in to suspect mode, I repaired it using dbcc, but I want to know cause

texts:
Following is the out put of dbcc checkdb.

  Msg 5282, Level 16, State 2, Line 1
  Table error: Object ID 683941979, index ID 1, partition ID 72057608853127168, alloc unit ID 72057610800988160 (type In-row data), page (30426:11).  The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.

  Msg 5282, Level 16, State 1, Line 1
  Table error: Object ID 683941979, index ID 1, partition ID 72057608853127168, alloc unit ID 72057610800988160 (type In-row data), page (61501:11).  The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
  CHECKDB found 0 allocation errors and 2 consistency errors in table 'rptpb' (object ID 683941979).
  CHECKDB found 0 allocation errors and 2 consistency errors in database 'abc'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (abc).

Generally I have seen when corruption in any index occurs, it does not put the database in suspect mode.

I want to know why this time it has put it in suspect mode.

Secondly, can I find the source of this error?


texts: How to add duplicate records by basing on the count from another table (SQL)

texts:
I wanted to add a duplicate record by basing on the count from another table
For example:
In table #1, I have the following records:
+------------------------+------------+
| first_name | last_name | Role       |
+------------------------+------------+
| John       | Smith     | Dev        |
| Jane       | Doe       | Tester     |
| Rock       | Lee       | Dev        |
+------------------------+------------+

And in table #2, I have the following records.
+------------------------+
| first_name | last_name |
+------------------------+
| John       | Smith     |
| John       | Smith     |
| Jane       | Doe       |
| Jane       | Doe       |
| Rock       | Lee       |
+------------------------+

And since there 2 records for John Smith and Jane Doe in table #2, my expected output for table #1 is like this:
+------------------------+------------+
| first_name | last_name | Role       |
+------------------------+------------+
| John       | Smith     | Dev        |
| John       | Smith     | Dev        |
| Jane       | Doe       | Tester     |
| Jane       | Doe       | Tester     |
| Rock       | Lee       | Dev        |
+------------------------+------------+


texts:
We are running kea DHCP server with MySQL DB and today we encounter following error and after googling everyone saying it could be MySQL bug so wanted to see if it's really a bug or implementation issue.

MariaDB-server-10.2.15-1.el7.centos.x86_64 (3 node galera cluster)

2020-03-07 01:59:16.484 INFO  [kea-dhcp4.leases/29796] DHCP4_LEASE_ALLOC [hwtype=1 00:0c:29:ae:77:cb], cid=[no info], tid=0x80b9c710: lease 10.10.10.22 has been allocated
2020-03-07 02:04:29.625 ERROR [kea-dhcp4.alloc-engine/29796] ALLOC_ENGINE_V4_ALLOC_ERROR [hwtype=1 00:19:85:f0:1c:89], cid=[no info], tid=0xd7790768: error during attempt to allocat
e an IPv4 address: unable to execute for <UPDATE lease4 SET address = ?, hwaddr = ?, client_id = ?, valid_lifetime = ?, expire = ?, subnet_id = ?, fqdn_fwd = ?, fqdn_rev = ?, hostna
me = ?, state = ? WHERE address = ?>, reason: Incorrect datetime value: '2020-03-08 02:04:29' for column 'expire' at row 1 (error code 1292)


texts: Cost of Increasing max pool size in SQL Server

texts:
We have a public front website that run fine most of the time. But we sometimes experience peaks of connections (like after a mailing campaign) and we were getting error like :

Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached

We are pretty sure that all our connections are closed properly (no connection leak).
So we decided to increase the number of connection allowed in the pool (which is 100 by default).
We increased it to 1000 (max pool size=1000; in our connection string), and now our site can handle most peaks of connections. (I precise the site run on a dedicated server)
My question is : What may be the negative effects of the increase of the max connections pool ?
EDIT : Here's whats I have when I use sp_who2 during those peaks :
If max pool is set to 100, I have more than 100 lines of those 'awaiting command' lines :

SPID
Status
Login
HostName
BlkBy
DBName
Command
CPUTime
DiskIO
LastBatch
ProgramName
SPID
REQUESTID

number
sleeping
db_user
IIS pool name of my website
.
MyDatabaseName
AWAITING COMMAND
0
0
05/10 14:48:59
.Net SqlClient Data Provider
137
0

And I can see that all 'LastBatch' were executed just a few second before.


texts: What can cause an "SCD is not compatible with this version of the DataFlow" error?

texts:
I created an SSIS package which includes several SCD data flows.  This works fine in Visual Studio SSDT, but when scheduled (using SQL Agent), it fails, with this error repeated for each SCD:

Code: 0xC0048020
Source: XYZ (SSIS.Pipeline)
Description: The version of SCD is not compatible with this version of the DataFlow.  End Error

The first thing that comes to mind is that, when scheduled, the package runs under a different user account.  However, I'd expect that to cause permissions problems.

I'm using Visual Studio 2013; the database is SQL Server 2014.  Neither has changed during the time I've been developing the package, there's been no upgrading, which seems to be what the error message implies.  I am developing the package on a server with SQL 2012 running, so that's used when testing within VS; from a similar question, that can have an impact for ADO.NET connections, but I'm using OLE DB.

Apparently SCD may not work with databases other than MS SQL, but I'm not using any such.  I've tried 32-bit and 64-bit mode, with no difference.

For this project, I'm violating two of my cardinal rules for SSIS: develop on the machine where the package will run, and don't use the built-in SCD tool.  I suspect that changing either one would fix this problem.


texts: Created a Materialized View in ORACLE which won't refresh

texts:
I created a materialized view log along with a materialized view  called update_nboe based on table NBOE_ EMPLOYEES_TEST  using the following code

CREATE MATERIALIZED VIEW LOG ON NBOE_EMPLOYEES_TEST WITH primary key;

CREATE MATERIALIZED VIEW update_nboe
REFRESH FAST ON DEMAND
AS
SELECT E.EMP_ID, E.USERNAME ,E.NAME, E.LOCATION , E.TITLE, E.LOCATION_CODE, E.RS_GROUP
FROM NBOE_EMPLOYEES_TEST E;

Then I updated NBOE_EMPLOYEES_TEST by inserting additional records hoping that the materialized view would update and refresh on-demand after using the following piece of code

exec dbms_mview.refresh('update_nboe',atomic_refresh_test=>TRUE);

However, I see a red cross on my connections panel for the materialized view and it won't refresh either.

Would appreciate some input.


texts: PostgreSQL not using varchar index when ordering by primary key

texts:
My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it grew, queries to it became slower and slower. This table is used for calculating global statistics periodically and for showing last 20 stats of a selected device.
At the moment, there is >150m rows in this table, and in the place where we pull the last 20 rows for a device, the query takes more than a minute to execute and causes a huge memory usage spike on the server.

The structure of the table is like this:

       Column        |            Type             |

---------------------+-----------------------------+
 id                  | bigint                      |
 datetime            | timestamp without time zone |
 device              | character varying(225)      |
 file                | character varying(225)      |
 duration            | character varying(225)      |

Indexes:
    "statistics_pkey" PRIMARY KEY, btree (id)
    "statistics_device_idx" btree (device)

There are a few more columns with informations, but they aren't included in any queries and hold no interesting data, mostly text.

When searching for the statistics, we select the device and the date, the query fetches 20 rows of that device that are before the selected date:

SELECT * FROM statistics WHERE device = '7329_218550' and datetime < NOW() ORDER BY id DESC LIMIT 20;

This query causes the trouble. Explain of it:

Limit  (cost=0.57..1029.06 rows=20 width=553)
  ->  Index Scan Backward using statistics_pkey on statistics  (cost=0.57..11129443.84 rows=216424 width=553)
        Filter: (((device)::text = '7329_218550'::text) AND (datetime < now()))

As you can see, the device index is not being used at all, I was surprised as it's the main column we use for filtering and it had an index.
I found out, that by just removing the ORDER BY from the query, the index is being used properly, and queries take ~0.004s which is a tremendous difference:

Limit  (cost=0.57..78.00 rows=20 width=553)
  ->  Index Scan using statistics_device_idx on statistics  (cost=0.57..837920.42 rows=216424 width=553)
        Index Cond: ((device)::text = '7329_218550'::text)
        Filter: (datetime < now())

I  found out, that using the datetime field for ordering (since we want the last 20 rows anyway) and although the explain is more complex, it actually USES the index again! Which surprised me even more, because ordering by a primary key should be better than by some time field:

Limit  (cost=669994.92..669994.97 rows=20 width=553)
  ->  Sort  (cost=669994.92..670535.98 rows=216424 width=553)
        Sort Key: datetime
        ->  Bitmap Heap Scan on statistics  (cost=4138.01..664235.95 rows=216424 width=553)
              Recheck Cond: ((device)::text = '7329_218550'::text)
              Filter: (datetime < now())
              ->  Bitmap Index Scan on statistics_device_idx  (cost=0.00..4083.91 rows=216445 width=0)
                    Index Cond: ((device)::text = '7329_218550'::text)

Queries ordering by datetime are executing in ~3.5s, which is a lot more than without ordering, but it's still much better than ordering by the primary key.

Why is it like that, and is there any way to use the index properly and keep the ordering?


texts:
I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

I am working with an existing design.

What I want is to be able to select all id's from table a, which are not in table b.

I am using this query:

SELECT a.* FROM `orders` a LEFT JOIN `orders_corrected` b ON
a.`order_id`=b.`order_id` WHERE b.`order_id` IS NULL;

Table a contains over 900K records and Table b contains over 200K and growing.

The above query takes about 7-8 minutes. I tried doing a LIMIT 5 as well. but it doesn't help much.

I also tried using NOT IN, which is slow as well.

order_id column is NOT NULL and UNIQUE indexed.

Ouput of show create table...

CREATE TABLE `orders` (
 `order_id` varchar(50) NOT NULL DEFAULT '',
 `order_data` text,
 `billing_address` text,
 `shipping_address` text,
 `updated_on` datetime DEFAULT NULL,
 PRIMARY KEY (`order_id`),
 KEY `ix_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Output of explain:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 595783
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY,order_id_UNIQUE,ix_order_id
key: PRIMARY
key_len: 152
ref: func
rows: 1
Extra: Using where; Not exists; Using index


texts: How to re-declare T-SQL variables if it already exist?

texts:
Background:

I'm building a SQL command to be executed on the server which involves a lot of accessing store procedure and declaring variables which are used as input to other stored procedures. This is all generated on the client side using C# and send as a big package. I have to do it this way since the ping times are very long since the client will be located very remotely with bad connections and I don't want to send many packages and starting building up a backlog due long response times since.

Currently no more than three client can work in parallel or they start to build up a backlog since the transfer speed of one transaction with about 200 store procedures takes about 35 seconds.

Question:

How can I declare a variable which I don't now if it already exists?
I could use a dictionary on the C# side but that sounds like the wrong way to do it to me. I want a declare which accepts re-declaring of that variable if it did exist.


texts: Creating and tuning a materialized view in oracle for use by Cognos BI

texts:
I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to select its contents for fulfilling the incremental load in a timely fashion. The data is currently taking +5 hours to move between Oracle and SQL Server (Cognos 'Staging' layer).

The source code for this scenario can be accessed via the following URL:

https://github.com/pwl91/source_code

I would like to create a materialized view that will use select * from ifsapp.fb_cust_ord_lines as its source, this will need to refresh fast (i.e. quickly) every day at 2:00. I have attempted this and below is the result:

create materialized view ifsapp.fb_bi_cust_ord_lines_mv
tablespace "IFSAPP_DATA" build immediate
  using index pctfree 10 initrans 2 maxtrans 255
  storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0
    freelists 1 freelist groups 1 buffer_pool default)
tablespace "IFSAPP_DATA" refresh fast
  using default local rollback segment
as
  select
    *
  from ifsapp.fb_cust_ord_lines
  ;
  create index ifsapp.fb_fb_bi_cust_ord_lines_mv_i on ifsapp.fb_bi_cust_ord_lines_mv
    (
      order_no,
      line_no,
      rel_no,
      line_item_no
    )
    pctfree 10 initrans 2 maxtrans 255
    tablespace IFSAPP_INDEX_16K
;

I know that this will not be 100% correct (maybe not even 50%) but I would really appreciate some assistance. Please ask if you require any further information


texts: Schema Change/Update script for Database deploy

texts:
I have a need to change the database schema . I'm planning to write Schema change and update scripts for tracking database changes and updating them. I followed

Versioning Databases – Change Scripts

for a start, I got a gist of what he is getting at however since I haven't worked much on SQL scripts before, a tutorial or something to start with would be good. I did some research on the web and came to know that most people use Automatic comparing tools to generate the script which I don't want to do for obvious reason that I won't learn the anything in the process.

I'm looking for some tutorials/links on How to write Change scripts and Update scripts ? Especially update scripts as I couln't find even a single script/pseudo-code on how to do update schema by comparing SchemaChangeLog table, connecting to the table using scripts...

Thanks in advance!


texts: Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

texts:
I use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the majority of my general SQL queries, mainly because this was drilled in to me when originally learning the language.

From my understanding, this isolation level acts the same way that WITH (NO LOCK) however I only ever tend to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Is there ever a time that I should be using WITH (NO LOCK) over SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
Does  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED stop other users from being locked out of the tables that I am reading?
If  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is used to stop locks, but I am only reading data, what is the point in using it? Is it only system intensive queries that would generate locks? Is it worth using it when running queries that would return in say, 5-10 seconds?
I have been told not to use  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED when reading data that would be used in updates, presumably to avoid updating dirty data. Would this be the only reason?
With the type of database that I am working on, there is a production and testing environment. We will very rarely be querying the production environment but when I need to, I will generally be using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in my query. I understand that dirty reads are possible with this. Aside from receiving data back that may not end up being committed to the database (and therefore throw my results out) what other types of 'dirty reads' could be possible?

Sorry for the mass questions.


texts: How to generate Entity Relationship (ER) Diagram of a on-premises database using Azure Data Studio (macOS)?

texts:
I found a similar question for SQL Server Management Studio. But I use latest Azure Data Studio on macOS. We use on-premises SQL Server (not Azure cloud).

How can I use Azure Data Studio to generate an ERD from an existing database?


texts: Adding windows domain group to ORA_DBA not working in Oracle 12c

texts:
I am running into an odd situation with adding windows domain group to ORA_DBA group to grant sysdba privileges.

I am getting an invalid username/password - logon denied error while connecting to an instance  in Oracle 12c.

But, if I add my individual windows account to ORA_DBA group, I am able to login just fine.

I have tried to research for an entire day but haven't had much luck. Any help would be greatly appreciated.

  Note : The windows domain group is part of the Administrator group on the server.

This is how my sqlnet.ora file looks like:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
NAMES.DIRECTORY_PATH =(TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DEFAULT_ZONE = WORLD
TRACE_LEVEL_CLIENT = OFF
AUTOMATIC_IPC = OFF


texts: MySQL: sum values with join and group concatenation

texts:
I've got the following tables:

users:

mysql [test]> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `product` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

groups:

mysql [test]> SHOW CREATE TABLE `groups`\G
*************************** 1. row ***************************
       Table: groups
Create Table: CREATE TABLE groups (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

products:

mysql [test]> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `product_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

I would like to know how many groups are active for a user:

mysql [test]> SELECT u.name, SUM(g.active) AS active FROM users u LEFT JOIN groups g ON (u.id = g.user_id) WHERE u.name IN ('user01', 'user02') GROUP BY u.name ORDER BY u.name;
+--------+--------+
| name   | active |
+--------+--------+
| user01 |      2 |
| user02 |      0 |
+--------+--------+
2 rows in set (0.00 sec)

I also need a list of products per user but then active column show incorrect values due to JOIN:

mysql [test]> SELECT u.name, SUM(g.active) AS active, GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR ',') AS products FROM users u LEFT JOIN groups g ON (u.id = g.user_id) LEFT JOIN products p ON (u.product = p.product_id) WHERE u.name IN ('user01', 'user02') GROUP BY u.name ORDER BY u.name;
+--------+--------+-------------------------------+
| name   | active | products                      |
+--------+--------+-------------------------------+
| user01 |      6 | product01,product02,product03 |
| user02 |      0 | product11,product12,product13 |
+--------+--------+-------------------------------+
2 rows in set (0.00 sec)

So the expected results would be:

+--------+--------+-------------------------------+
| name   | active | products                      |
+--------+--------+-------------------------------+
| user01 |      2 | product01,product02,product03 |
| user02 |      0 | product11,product12,product13 |
+--------+--------+-------------------------------+
2 rows in set (0.00 sec)

Based on the following records:

mysql [test]> SELECT * FROM users;
+----+--------+---------+
| id | name   | product |
+----+--------+---------+
|  1 | user01 |       1 |
|  2 | user02 |       2 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql [test]> SELECT * FROM groups;
+----+---------+---------+--------+
| id | user_id | name    | active |
+----+---------+---------+--------+
|  1 |       1 | group01 |      0 |
|  2 |       1 | group02 |      1 |
|  3 |       1 | group03 |      1 |
|  4 |       1 | group04 |      0 |
|  5 |       2 | group01 |      0 |
|  6 |       2 | group02 |      0 |
+----+---------+---------+--------+
6 rows in set (0.00 sec)
mysql [test]> SELECT * FROM products;
+----+-----------+------------+
| id | name      | product_id |
+----+-----------+------------+
|  1 | product01 |          1 |
|  2 | product02 |          1 |
|  3 | product03 |          1 |
|  4 | product11 |          2 |
|  5 | product12 |          2 |
|  6 | product13 |          2 |
+----+-----------+------------+
6 rows in set (0.00 sec)


texts: Get rows changed between two dates (latest and previous)

texts:
I have a table _sync_history with this data:

+----+----------------+---------------------+
| id | object_name    | sync_time           |
+----+----------------+---------------------+
|  1 | territory      | 2015-07-21 22:29:02 |
|  2 | user           | 2015-07-21 22:29:19 |
|  3 | userterritory  | 2015-07-21 22:29:38 |
|  4 | account        | 2015-07-21 22:30:05 |
|  5 | accountshare   | 2015-07-21 22:39:04 |
|  6 | address_vod__c | 2015-07-22 00:47:38 |
|  7 | group          | 2015-07-22 01:23:30 |
|  8 | recordtype     | 2015-07-22 01:23:51 |
|  9 | user           | 2015-07-23 13:04:20 |
| 10 | account        | 2015-07-23 13:04:59 |
+----+----------------+---------------------+

I want to get the rows on user between 2015-07-21 22:29:19 and 2015-07-23 13:04:20 meaning the rows between last sync_time and previous sync_time for a given object_name. I was doing this:

SELECT * FROM `user` WHERE lastmodifieddate > (SELECT sync_time FROM `_sync_history` WHERE object_name='user' ORDER BY sync_time ASC LIMIT 1)

But that will give me just the ones from last sync_time and above which is not right, how do I get the ones I need?


texts: Calculating values from three related tables, without using join or union

texts:
I have a simple basic three tables on SQL Server database:

What is needed is to get the quantity available, without using joins or unions which is calculated:

  Quantity Available  = Items Opening Balance - Items Delivered +
  Items Received

Any Ideas are welcome.


texts: Script to monitor database growth on daily basis

texts:
I am fairly new to databases, I have got a specific requirement to script a Job/Stored Procedure to monitor the database growth.

We are currently using MS SQL Server 2016 Standard Edition with Always-ON HA.

I have setup mail server on the database.
It would be even more efficient to link the output of the job to the mail.


texts: SQL Server - allocation unit size in Windows 2019

texts: I'm about to install a SQL Server 2019 instance on a Windows 2019 machine and found out that there are new ntfs allocation unit sizes available - up to 2M. I used to format partitions using 64k unit size (obviously), but I'm tempted to use the larger ones this time. Any one has any thoughts or experience in this matter?


texts: Optimizing range join (x between a and b) in MariaDB

texts:
My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly.

Query

EXPLAIN
 SELECT g.id, g.name, c.id AS child_id, c.date
   FROM g
   JOIN c
     ON c.parent_id = g.id
    AND c.date BETWEEN g.start_date AND g.end_date;

Keys

Primary key of g: (id, start_date)
Primary key of c: (id)
Foreign key on c.parent_id references g.id
Index on c: (parent_id)

EXPLAIN output

+----+-------------+-------+------+-------------------+-------------------+---------+-------------+-------+----------+-------------+
| id | select_type | table | type |   possible_keys   |        key        | key_len |     ref     | rows  | filtered |    Extra    |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+-------+----------+-------------+
|  1 | SIMPLE      | g     | ALL  | PRIMARY           |                   |         |             | 18342 |      100 | ""          |
|  1 | SIMPLE      | c     | ref  | idx_parent_id     | idx_parent_id     |       4 | square.g.id |     3 |      100 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+-------+----------+-------------+

In the g (parent) table, there are no rows that overlap each other in start/end date for a given ID but the database would not know that since there is no way to indicate it as far as I know.

All of the date columns are of type date.

How can I avoid a full table scan, or if I can't, what can I do to improve the performance? As of now there are approximately 250k rows in the c table and 25.7k rows in the g table.

This query is used to create a view, and in the application there are many queries that select from this view and joins it with other tables, so it is very important to optimize this view.

What has been tried as per comments:

Adding indices g (id, start_date, end_date) and c (parent_id, date)
Replacing c.date BETWEEN g.start_date AND g.end_date with g.start_date <= c.date AND c.date <= g.end_date


texts: Updating status on delete from another table (Oracle SQL)

texts:
How can I merge these two queries?
delete from login where email='test@test.com' and date_created > created_date + interval '1' day;

update staff set status_id=1 where staff.email=login.email;


texts: Why is SQL Server 2012 memory usage limited to 4 GB

texts:
I have an SQL Server Installation which suffers very bad performance due to lack of memory (queries runnig very slow, RESOURCE_SEMAPHORE...). The memory usage never goes above ~4.1 GB, although the machine has 16 GB of physical memory and only 44% is used.

Resource Monitor shows a working set of 4,289,000 KB for sqlservr.exe.

I'm wondering where this limitation is coming from, although we increased "maximum server memory" to 10 GB.

Both Os and DB Server are 64-bit and the editions used have much higher limitations than 4 GB.

I've used sysinternals TestLimit to see if there is a general memory limitation for processes, but a testprocess could use more memory, so it seems to be related to SQL Server itsself.

Any help is appreciated!

OS: Windows Server  2012 R2 Standard 64-bit

SQL Server: SQL Server Standard 64-bit, Version 11.0.5058.0

Server is virtualized on VMWare ESXi 5.1.0


texts: How to specify MDX set by property range?

texts:
I'm trying to create a named set representing the next 12 fiscal months. I have [Fiscal Month] with [Fiscal Month Ordinal] as a property, with '0' as the current period and positive and negative integers as the future and past, respectively.

I would have expected something like this to work, but a query of this set returns no data:

FILTER( [Time].[Fiscal Month].[Fiscal Month],
        [Time].[Fiscal Month].CURRENTMEMBER.Properties("Fiscal Month Ordinal") = 1)

That should be for one period, at least. Leaving aside the need to include the range from 1-12 for the moment.


texts: tsql Process running forever... what is it doing

texts:
Trying to create a big test DB, I have an SP that I created that uses a predefined list of tables (35 That I'm interested in) and multiplies the data in each one of them.

In other words, A table has data for one store.  I insert into this table the same data but with a different store number (which gives me 2 stores). I then call the same SP that multiplies the 2 Stores data to 4 stores, and so on.  Over the weekend I started the process of 6 consequent calls of the SP thinking that when I come back Monday my DB would have data for 64 stores.  Unfortunately, this wasn't the case...

When I came in Monday, I found the data HD full but the process never crashed and seemed to be suspended.  my DB is in simple recovery model, I made space by moving files and by shrinking the TempDB (through the shrink file option in SSMS) and created an extra ndf file with 100GB initial size on an external drive... The process is still running (over 24 hours after making space).

The spid's task state shows suspended with wait type PAGEIOLATCH_EX but with small wait times (below 100ms) which makes me believe that the process is still running.

34 tables out of 35 are decent size and are quick enough to multiply...
The process has multiplied the 1 store of data in all tables to 8 stores and is still in the process of completing the multiplication of the data from 8 to 16 stores... the one table it seems to be working on is the biggest table of all...

The actual process of multiplying the data from 8 to 16 stores for this table, is inserting 400Million Records from a select of 400million records and the table has 200 columns all different types.

Am I wasting time waiting? I'm afraid that killing the process will take several days to roll back... Should I just wait it out? Has it become a Phantom  process?  How can I tell what it's doing?

Any opinion/ideas would be greatly appreciated.

Thank you


texts:
The Oracle documentation has a section in the 11.2 Performance Tuning Guide with the title Determining Which Segments Have Many Buffers in the Pool.  The section lists the following query:

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

If we change this to a right join we get rows from v$bh regardless of whether there is a matching object_id in dba_objects.

SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID(+) = bh.OBJD AND o.DATA_OBJECT_ID IS NULL
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*);

My question is what do these row represent?  They have file#, block#, and objd columns, but the objd values do not exist in dba_objects.  I dumped one of the blocks and it appeared to contain an index, but I couldn't tell anything else about it.  There are plenty of rows in v$bh that do match indexes in dba_objects, so the unmatched v$bh rows can't be unmatched just because they are blocks of indexes.  Something else must be going on.

There are more unmatched objects on one of my systems, but they all have unmatched objects including the 11.2.0.2.6 system.

Update:

For the system with the most unmatched objects, the majority can be matched when comparing the bh.objd with the dataobj# field from obj$.  I'm not sure why these objects aren't reflected in dba_objects, but perhaps an examination of the view (or a separate question) will answer that.

For the remaining 391 unidentified entries, here is some other information of interest.

SELECT class#, count(class#) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY class#
  4  HAVING count(class#) > 1;

    CLASS# COUNT(CLASS#)
---------- -------------
        12            59
        13            36
        18            15
        20            20
        22            25
        30            12
        32             6
        36            28
        38             3
        42            16
        44           125

    CLASS# COUNT(CLASS#)
---------- -------------
        52             4

12 rows selected.

I eliminated the class values with only one entry.  There were 33 of those.

SELECT status, count(status) FROM v$bh bh
WHERE NOT EXISTS (SELECT 1 FROM obj$ o2 WHERE o2.dataobj# = bh.objd)
GROUP BY status;

STATUS  COUNT(STATUS)
------- -------------
xcur              361

1 row selected.


texts:
Suppose I have the following schema for professions: id, type, likelihood. id is the id of the person in question, and I have info regarding the likelihood that that person will read a certain type of book.

What if I want to pad in values? So, let's say I currently have:

id     type     likelihood

1      fantasy  0.9
1      romance  0.5
2      horror   0.2

But, I have a table with all of the possible types:

type

fantasy
romance
horror
mystery
academic

And now I want to pad the table so that I have every person (id) listed with every single type. If the type wasn't in the table to begin with, place the likelihood as 0.

So, I want

id     type     likelihood

1      fantasy   0.9
1      romance   0.2
1      horror    0.0
1      mystery   0.0
1      academic  0.0

I'm having difficulties with this. I've tried using different joins but I can't seem to preserve the info I want while adding in information.

Thanks!


texts:
I have the following schema (simplified):

Locations

Id INT,
StaticLocation GEOGRAPHY

Capacities

LocationId INT
SpareCapacity INT

I'm trying to find, for each location a sum of the SpareCapacity of all locations within x miles of it.

This is complicated by the fact that there are a few CTEs which I'm joining to but the principal is the same (I hope). I'm confused because this works as a standalone query:

SELECT sum(SpareCapacity)
FROM Capacities UC
    JOIN Locations L ON L.Id = UC.LocationId
WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10
GROUP BY u.StaticLocation

When I try to join it to a previous CTE though, I get the error:

Column 'Locations.StaticLocation' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add the column to the GROUP BY clause I get another error:

The type "geography" is not comparable. It cannot be used in the GROUP BY clause.

The query inside my CTE looks like:

SELECT k.LocationId,
    (
    SELECT sum(SpareCapacity) Capacities
    FROM Capacities UC
        JOIN Location u ON u.id = UC.LocationId
    WHERE u.StaticLocation.STDistance(uk.StaticLocation) / 1609.344 < 10
    --GROUP BY u.StaticLocation
) Vacancies
FROM Cte_SomeData k -- cte which has some simple columns
JOIN Locations uk on k.LocationId = uk.Id
GROUP BY k.LocationId -- tried adding StaticLocation here without luck

If I replace uk.StaticLocation with geography::Point(53, -2, 4326) it works (but obviously I need it relative to the Location's geography not a static point. Am I missing a group by somewhere?


texts: Using Dynamic Data Masking in SQL Server set Custom String

texts:
I need to mask the data in the following table.

For example, I need to mask the first_name of the user Roberto to something like NAME<<userID>>, So the end result should look like NAME1
Is this possible in SQL Server?
My SQL Server details below.

I have tried these methods from the documentation but still no luck
from documentation


texts: Indexing a GROUP BY Select with Multiple Inequalities

texts:
I have a pair of linked issues I need help with:

Issue #1: How to Index a Table to Optimize for a "Group By" SELECT

I have a table of stock price information with the following Create statement:

CREATE TABLE `price_daily` (
  `Symbol` varchar(20) NOT NULL,
  `Date` date NOT NULL,
  `Price` float DEFAULT NULL,
  `MarketCap` double DEFAULT NULL,
  `PriceToEarnings` double DEFAULT NULL,
  `PriceToSales` double DEFAULT NULL,
  PRIMARY KEY (`Symbol`,`Date`),
  KEY `Date_MCap` (`Date`,`MarketCap`,`PriceToSales`,`Symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is fairly expansive; it spans roughly 100 million rows. Thus, imprecise SELECT queries are likely to take a long time to resolve, which I would like to avoid.

I want to be able to search the database for stocks within a specified date range that fall within selected PriceToSales and/or PriceToEarnings ranges. The query below is typical of one I would perform:

SELECT Symbol, Date, MarketCap, Price
FROM price_daily
WHERE Date BETWEEN '2002-07-31'AND '2017-07-31'
AND MarketCap >= 1000
AND PriceToSales BETWEEN 2 AND 5
GROUP BY Symbol
;

I use GROUP BY because I only need to see one result per Symbol.
The problem is, when I add the 'GROUP BY' clause at the end, the query suddenly takes many times longer to complete than it does without that clause. What am I doing wrong? I assume it has something to do with my indexing, but I have not yet 100% figured out the 'rules' for ordering columns in indexes.
(A couple non-specific specifics: without GROUP BY the query has roughly 0.5s completion time but takes >200s to fetch, as there are potentially millions of results to display.)

EDIT 4/28/20 3pm: I have been told, in multiple locations, that the syntax of the above GROUP BY query is bad because the columns Date, MarketCap and Price are not aggregated. This is presumably a trivial issue to fix; for now, I leave Issue #1 alone to focus solely on the second issue.

Issue #2: SELECTing the Smallest Value From Within a GROUPed Query

A related question to the above:
Suppose, rather than allow the 'GROUP BY Symbol' clause from the above to choose whichever date it wants, I want to guarantee that my query will always SELECT the earliest Date chronologically for each Symbol. How would I do that? I assume the correct query would look something like this:

SELECT Symbol, MIN(Date)
FROM price_daily
WHERE Date BETWEEN '2002-07-31'AND '2017-07-31'
AND MarketCap >= 1000
AND PriceToSales BETWEEN 2 AND 5
GROUP BY Symbol
;

...but, due to the issue I am having with GROUP BY above, I cannot currently test this.

Any help given on either or both of these problems will be much appreciated.
If you need me to provide more information, I'll edit this post tomorrow morning when I get back to the office.

EDIT 4/28/20 3:30pm: The EXPLAIN for the query given under Issue #2 is as follows:

+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
| id   | select_type | table       | type  | possible_keys      | key       | key_len | ref  | rows     | Extra                                                     |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
|    1 | SIMPLE      | price-daily | range | PRIMARY, date_mcap | date_mcap | 21      | NULL | 50622081 | Using where; using index; using temporary; using filesort |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

The query took 331.1 secs to resolve, generating 2735 rows. An excerpt from the table:

Symbol  Date
------  ----------
A       2003-12-19
AABA    2007-07-26
AACC    2005-10-03
AAN     2010-02-11
AAOI    2017-03-07
AAON    2013-10-01
AAPL    2004-10-05
AAT     2012-07-27
...     ...


texts:
During a spike, my connections go from 30 to over 140, as you can see below:

I'm willing to set-up a pooler like pgbouncer, but have some questions as my scenario is not that easy:

  1 Load Balancer (nginx)

  2 web servers (www01 and www02)

  5 DB servers (master01, slave01 (read-only), slave02 (hot_standby),
  slave03 (hot_standby), slave04 (hot_standby)

  Just ONE Database. Size: 2TB

  max_connections: 200

  I'm using PostgreSQL 9.2

Question:

Can pgbouncer handle with my master and slave01, as I'm using slave01 as read-only server? Also.. I've got 2 www servers... Do I have to have pgbouncer in both of them?


texts:
I forgot MySQL root password so I am trying to change it by starting it in safe mode and running following command but getting syntax error. MySQL version is 8. What is the correct syntax to update password? Or is it happening due to presence of $ and @ in password?

UPDATE mysql.user SET Password=PASSWORD('$kjD q6 MM paSSword@') WHERE User='root';


texts: SELECT statement with an explicit list of columns and then an asterisk (*)

texts:
SQL Server allows to use SELECT statements similar to:

SELECT FirstName, LastName, City, *
FROM dbo.Customers

When I try to execute such a query on Oracle database, I get the following error:

  ORA-00936: missing expression
  00936. 00000 -  "missing expression"

Doesn't Oracle support such queries?


texts: Oracle table is occupying too much space in disk

texts:
I have a simple table in Oracle. The table has 1 million records, with an average record size of 200 bytes. Adding column definition sizes, a record shall not have over 750 bytes.

According to a simple calculation the data in the table is around 200 MB, and a reserved space of 750 MB. However the table is using 102 segments of 100 MB each, for a total size of near 10 GB, according to the following query:

select segment_name, segment_type, tablespace_name, bytes/1024/1024 as size_in_mb, bytes, extents
  from user_segments
 where segment_name in (select table_name from all_tables where owner=user);

So, I have two questions:

How can I explain the oversize. The table has no indexes, or anything that should add content to the table. The table was filled from an Informatica ETL process using bulk mode.
How can I get rid of oversize.

I do not have DBM privileges on the database.


texts: I wonder if there is a way to query SQL Server to find out dates and times a login has connected to a particular server?


texts: Is it actually possible to somehow use Slony with Amazon’s RDS service? I’m aware that there’s no such thing as a host that you can log into to make Slony’s configurations, but perhaps it can be done remotely from a side server or something like that?


texts: php single table language separated autoincrements?

texts:
I have 1 table for posts.
And my columns are
Id | id_language | lang

I want to achieve, that there is a second autoincrement id for its language. For example the 80.post is the 5.post written in english language. I need separate index number for sef url. Is it possible


texts: Is there any way to 'de-cluster' an instance of SQL Server 2012 that was installed using the 'New SQL Server Cluster Installation' option? We are having issues with the failover portion of clustering services and since we did not implement the cluster (and really there is no need) we would like to turn this into a stand-alone installation. Removing the Windows Clustering portion just took SQL down with it. All help and insight is appreciated, thanks!


texts: Add UNIQUE index fails with duplicate entry error, but no duplicates found

texts:
Given table:

CREATE TABLE mytable (
  field_a CHAR(15) NOT NULL DEFAULT '',
  field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_e CHAR(1) NULL DEFAULT '',
  field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci;

Required index:

ALTER TABLE mytable ADD UNIQUE INDEX idx_key (field_a, field_b);

Data size is about 51 Mio. rows. Following problem:

Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one(!) row.

 [23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'idx_key'

 SELECT COUNT(*) FROM mytable WHERE field_a='aaaaaaaaaaaaaaa' AND field_b='11111'

returns 1 (!)

Attempt 2: If I create the index on an empty table or make a combined private key and then put the data into the table, the table contains only 27 of 51 Mio. rows (!).

Is there some kind of a limit on the unique index or a bug?

I use MariaDB 10.0.20. Please help.

Update 1

A count of unique rows

SELECT COUNT(DISTINCT field_a,field_b) from mytable;

returns 50 Mio rows. So there are about 1 Mio duplicates. This however does not explain the 27 Mio from attempt 2 or wrong exception from attempt 1.

Update 2

the table used by the attempt 2:

CREATE TABLE mytable (
  field_a CHAR(15) NOT NULL DEFAULT '',
  field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  field_e CHAR(1) NULL DEFAULT '',
  field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY idx_key (field_a, field_b)
) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci;

Update 3

Attempt 2 error solved

To populate the table we use INSERT statements with multiple value rows in each. If a single value of the insert violated the unique constraint, all other values where also not inserted and caused 27 Mio rows instead of 50 Mio.

Approach 1:
However the bug with the wrong duplicate entry message still there even after the database were dropped and recreated, but this is an evil I can/must live with.


texts: I have a development laptop with SSMS Express 2012 with a 2012 db instance and a 2008 db instance.  Have been using this configuration for over a year.  Suddenly I am unable to use the restore wizard.  The wizard will pick the backup file but when I select the "Files" option on the upper left to designate the location of the MDF and LDF the dialog hangs.  I tried a repair, no luck.


texts: How do I get the aggregate of a window function in Postgres?

texts:
I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so:

CREATE TABLE foo
(
  perm integer[] NOT NULL,
  combo integer[] NOT NULL,
  value numeric NOT NULL DEFAULT 0
);
INSERT INTO foo
VALUES
( '{3,1,2}', '{1,2,3}', '1.1400' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0.9280' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,2,1}', '{1,2,3}', '0' ),
( '{3,2,1}', '{1,2,3}', '0.8000' )

I want to find out the average and standard deviation for each permutation, as well as for each combination. I can do that with this query:

SELECT
  f1.perm,
  f2.combo,
  f1.perm_average_value,
  f2.combo_average_value,
  f1.perm_stddev,
  f2.combo_stddev,
  f1.perm_count,
  f2.combo_count
FROM
(
  SELECT
    perm,
    combo,
    avg( value ) AS perm_average_value,
    stddev_pop( value ) AS perm_stddev,
    count( * ) AS perm_count
  FROM foo
  GROUP BY perm, combo
) AS f1
JOIN
(
  SELECT
    combo,
    avg( value ) AS combo_average_value,
    stddev_pop( value ) AS combo_stddev,
    count( * ) AS combo_count
  FROM foo
  GROUP BY combo
) AS f2 ON ( f1.combo = f2.combo );

However, that query can get pretty slow when I have a lot of data, because the "foo" table (which in reality, consists of 14 partitions each with roughly 4 million rows) needs to be scanned twice.

Recently, I learned that Postgres supports "Window Functions", which is basically like a GROUP BY for a particular column. I modified my query to use these like so:

SELECT
  perm,
  combo,
  avg( value ) as perm_average_value,
  avg( avg( value ) ) over w_combo AS combo_average_value,
  stddev_pop( value ) as perm_stddev,
  stddev_pop( avg( value ) ) over w_combo as combo_stddev,
  count( * ) as perm_count,
  sum( count( * ) ) over w_combo AS combo_count
FROM foo
GROUP BY perm, combo
WINDOW w_combo AS ( PARTITION BY combo );

While this works for the "combo_count" column, the "combo_average_value" and "combo_stddev" columns are no longer accurate. It appears that the average is being taken for each permutation, and then being averaged a second time for each combination, which is incorrect.

How can I fix this? Can window functions even be used as an optimization here?


texts: Numa Node configuration for SQL server 2016

texts:
I have a question on one of my new build sql servers where we are planning to migrate our SQL databases from older SQL2012 running on windows 2012.

New build- SQL2016 on Windows 2016

My current [SQL2012]==> When querying os_nodes dmv shows what i am assuming as 4 numa nodes config with memory node id=0,1,2,3 & processor group 0,1,0,1 and scheduler count= 20 for all id's

My new [SQL2016]==> When querying os_nodes dmv shows what i am assuming as 8 numa nodes config with memory node id=0,0,0,0,1,1,1,1 & processor group 0,0,0,0,1,1,1,1  and scheduler count= 9 for all id's

Below is O/P from os_nodes dmv between old and new configured SQL servers

Questions

  When viewed via task manger/ssms it shows 2 Numa nodes. Is my above
  understanding wrong of 8 Numa nodes?

  If those are 2 Numa nodes will i have any performance impact compared
  to current setup?

Also note softnuma_configuration_desc shows ON for sql2016

Please suggest


texts: Why does my Query use a Bitmap Heap Scan

texts:
my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan.
explain analyze verbose
SELECT connections, epoch_time
FROM connections
WHERE host_name = 'xyz.z'
ORDER BY epoch_time;

Output:
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3257.93..3316.47 rows=23415 width=13) (actual time=8.607..9.624 rows=23259 loops=1)
   Output: connections, epoch_time
   Sort Key: connections.epoch_time
   Sort Method: quicksort  Memory: 1859kB
   ->  Bitmap Heap Scan on public.connections  (cost=545.88..1558.57 rows=23415 width=13) (actual time=1.341..5.840 rows=23259 loops=1)
         Output: connections, epoch_time
         Recheck Cond: ((connections.host_name)::text = 'xyz.z'::text)
         Heap Blocks: exact=651
         ->  Bitmap Index Scan on idx_host_name  (cost=0.00..540.03 rows=23415 width=0) (actual time=1.266..1.266 rows=23259 loops=1)
               Index Cond: ((connections.host_name)::text = 'xyz.z'::text)
 Planning Time: 0.171 ms
 Execution Time: 10.405 ms
(12 rows)

The table has 97806 rows and I have created an index on the field epoch_time.  The table has 4 columns (id, host_name, connections, epoch_time)


texts: How to qualify variable inside trigger body (PL/SQL )?

texts:
I can explicitly qualify variable in the body of stored procedure or function :

create or replace
PROCEDURE ptest  AS
int_val INT;
BEGIN
  ptest.int_val:=0;
END;
/

How to do the same inside trigger ?

 create table temp1(id int not null);

 create or replace trigger trg_before_insert_temp1 before insert  on temp1 for each row
declare int_val int;
 begin

   trg_before_insert_temp1.int_val := 0; -- PLS-00201, identifier must be declared

 end trg_before_insert_temp1;
 /


texts: The SQL Server Network Interface library could not register the Service Principal Name (SPN)

texts:
Windows return code: 0x21c7, state: 15. Failure to register a SPN
  might cause integrated authentication to use NTLM instead of Kerberos.
  This is an informational message. Further action is only required if
  Kerberos authentication is required by authentication policies and if
  the SPN has not been manually registered.

I am getting above error whenever I restart my SQL service. I changed it from current service account to local account and was able to connect to SSMS from local machine, but if I am using current service account then this error is coming and in SSMS the error is :

  The target principal name is incorrect. Cannot generate SSPI context.

The password is not changed for this service account.


texts: Configuring Reporting Services - ExecutionLogDaysKept

texts:
Is it possible to set Reporting Services options when Reporting Services is not installed on a server?  Is this how a SQL Server instance used by SharePoint works?

I'm a new DBA (very junior...), and I'm working in an environment where the client uses SQL Server as a back-end to CRM, SharePoint, BizTalk, SCCM and SCOM.

I recently saw a user request to increase the retention period for Reporting Services logs on two servers. The RFC said to run Update ConfigurationInfo SET Value='275' Where NAME='ExecutionLogDaysKept' on DB ReportServer_BI on server [server_name] and DB ReportServer_RS01_Content server [server_name].

I had a look at both the servers but neither one had Reporting Services installed. At least there was no instance listed in SQL Server Management Studio.

When I ran the query SELECT * FROM ConfigurationInfo the results table returned had a row for ExecutionLogDaysKept.  SQL Server Management Studio was connected to the Database Engine when I ran the query.

I apologise if I've asked the question in the wrong forum.  Is there a more appropriate forum or website I should go to?


texts: Where is Connection String Located as it relates to Availability Group Listeners?

texts:
I am trying to teach myself about Availability Group Listeners and see that I need to update a "Connection String." I know that I have a connection string in my web.config file on the Web server, but is this where it needs to be updated? Is there somewhere in SQL Server Management Studio where it needs to be updated?

I've updated the connection string there, replacing the server name with the new Availability Group Listener Name that I've created, then updated my database table to replace the server name there with the AG Listener name too. When I update the name in the database table, my records will not open in the GUI where I'm accessing them from a website.

I think this will be a problem because if there is a failover, I'll need to manually update the name of the server for each record of the table to point to the new server location. I figured that if I put in the listener name in place of the server name, this would never need to be updated in the event of a failover.

Any thoughts? I've researched until my brain is mush and really need some help with fully understanding how to get these listeners working. Thanks in advance!


texts: How does Sybase store datetime information?

texts:
We have a Sybase database that currently stores datetime values in some of the tables.  If we move that server with the Sybase database to another country which is in another time zone, will the datetime values in the database be represented differently?

In other words, consider the scenario:

The Sybase database runs in timezone +2:00.

From my Java app that lives in time zone +1:00, it writes to the database 1 Jan 1970, 1:00 which is epoch 0 in the app's time zone.

preparedStatement.setTimestamp(3, new Timestamp(0));

However, when I query the database using SQL Workbench/J client (in time zone +1:00), I see

1970-01-01 01:00:00

in my table.  In fact, it does not matter to which time zone I change my computer's clock, SQL Workbench/J client always reads 1970-01-01 01:00:00 from the database.

Another Java client that lives in time zone +3:00 reads the same value as

1970-01-01 01:00:00

which translates to epoch -7200000 or -2:00 in the client's time zone!

So to get back to my original question, does Sybase store datetime values as e.g. strings or epochs?  I do not have a free Sybase server that I can experiment on, so what can we expect to happen when we move the server to another country with a different time zone?

The type we use is "datetime", for example:

CREATE TABLE "dbo"."whatever" (eventDate datetime NOT NULL);


texts: MySQL table proper indexes for performance optimization

texts:
I have a database table (created by someone else). This table consists over billions of records and records are being inserted every second or so.
I need to optimize this table and hence the queries to fetch stuffs faster. Following is the table ProductCatalog structure.
id                  int(10)
SerialNumber        varchar(20)
BasePrice           decimal(4,1)
BatchCode           tinyint(3)
Type                varchar(5)
ItemCode            varchar(5)
ArrivalDate         datetime
InsertTimestamp     int(10)
BrandID             tinyint(3)
CompanyID           tinyint(4)
Model               varchar(10)
Description         text

Here is the script to create table
CREATE TABLE `ProductCatalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SerialNumber` varchar(20) DEFAULT NULL,
  `BasePrice` decimal(4,1) DEFAULT NULL,
  `BatchCode` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `ItemCode` varchar(5) DEFAULT NULL,
  `ArrivalDate` datetime DEFAULT NULL,
  `InsertTimestamp` int(10) unsigned NOT NULL,
  `BrandID` tinyint(3) unsigned DEFAULT NULL,
  `Model` varchar(10) NOT NULL DEFAULT 'RX209',
  `Description` text,
  PRIMARY KEY (`id`),
  KEY `SerialNumber` (`SerialNumber`,`ArrivalDate`,`ItemCode`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Here are the sample insert queries.
insert into `productcatalog` (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`, `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`) values('1','345618761','23','25','ACC','ABCD','2021-02-14 12:24:44','1613371259','37','RX209',NULL);
insert into `productcatalog` (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`, `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`) values('2','345618761','24','25','RAF','ABCD','2021-02-15 12:55:17','1613373031','45','GA317',NULL);
insert into `productcatalog` (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`, `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`) values('3','569014575','43','21','DAT','TPRS','2021-02-13 12:56:34','1613373082','34','PX452',NULL);

There could be many many entries for same SerialNumber and ItemCodein Different ArrivalDate
Initially there were three indexes
1. id => Primary
2. SerialNumber
3. ArrivalDate

Following are the queries i run against this table.
SELECT *
FROM ProductCatalog
WHERE SerialNumber='1234567890'
  AND ItemCode!="ABCD"
ORDER BY id DESC LIMIT 1; //This Query Seems slower

SELECT BasePrice
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1 //This Query Seems Slower

SELECT *
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')
  AND ItemCode='ABCD'
ORDER BY ArrivalDate ASC //This query looks ok

SELECT BatchCode
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1 //This Query Seems Slower

Then I changed the indexes such that we have only two indexes now. The primary and the composite one.
1. id => Primary
2. SerialNumber, ArrivalDate, ItemCode

Mysql Information
MySQL Version 5.7
Engine: InnoDB

Problem
The results are still not that satisfactory.

Are the indexes I changed correct to get performance gain?
Are the order of columns in index Correct?
Column SerialNumber contains 16 digit numeric value, shall i changed it to int instead of varchar to gain performance?


texts: MySQL SELECT and SORT performance - v. large table

texts:
I have a table that contains in the region of 4 million rows. This is searched using a form, and then the results are returned. However, the query seems to take an inexplicably large amount of time, and I'm hoping that there's a more efficient query to run.

SELECT *
FROM Example
WHERE STR_TO_DATE(IncorporationDate,'%d/%m/%Y') > '" . $from . "'
  AND STR_TO_DATE(IncorporationDate,'%d/%m/%Y') < '" . $to . "'
  AND PostCode REGEXP  '" . $postcode . "'
  AND `Returns.LastMadeUpDate`=''
ORDER BY PostCode ASC


texts:
I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is

SELECT  f.RetailerID
,       TypeID                                AS TypeID
,       c.CalendarMonth                         AS CalendarValue
,       SUM(ISNULL([Volume],0)                  ) AS Volume
,       SUM([VolumeBox]                         ) AS VolumeBox
,       SUM([VolumeKG]                          ) AS VolumeKG
,       SUM([VolumeUnit]                        ) AS VolumeUnit
,       SUM(ISNULL([R_Turnover] , 0.0)          ) AS R_Turnover
,       SUM(ISNULL([R_VAT],0.0)                 ) AS R_VAT
,       SUM(ISNULL([R_TurnoverExVAT],0.0)       ) AS R_TurnoverExVAT
,       SUM([SupplierRealisation_Amt]           ) AS [SupplierRealisation_Amt]
,       SUM([SupplierDiscount1_Amt]             ) AS [SupplierDiscount1_Amt]
,       SUM([Supplier_1NetSales_Amt]            ) AS [Supplier_1NetSales_Amt]
,       SUM([SupplierDiscount2_Amt]             ) AS [SupplierDiscount2_Amt]
,       SUM([Supplier_2NetSales_Amt]            ) AS [Supplier_2NetSales_Amt]
,       SUM([SupplierDiscount3_Amt]             ) AS [SupplierDiscount3_Amt]
,       SUM([Supplier_3NetSales_Amt]            ) AS [Supplier_3NetSales_Amt]
,       SUM(ISNULL(S_CostofGoodsSold, 0)        ) AS [S_CostofGoodsSold]
,       SUM(ISNULL(S_Profit, 0)                 ) AS S_Profit
,       SUM(0.0                                 ) AS AdditionalCostofGoodsSold
,       SUM(ISNULL([R_DistributionCost],0.0)    ) AS R_DistributionCost
,       SUM(ISNULL([R_Profit],0.0)              ) AS R_Profit
FROM [dbo].[EventScenarios] es
JOIN dbo.[Event] e ON es.[EventID] = e.ID
JOIN dbo.EventProduct ep on es.EventID = ep.EventID
JOIN [dbo].Product p ON p.ID=ep.ProductID
JOIN dbo.EventPL f  ON  e.CountryID = f.CountryID AND f.RetailerID = e.RetailerID AND f.EventID = e.ID
AND ep.ProductID = f.ProductID
INNER JOIN Calendar c   ON  c.DateID = f.DateID
WHERE  f.CountryID= 14  AND c.CalendarMonth BETWEEN 201301  AND 201312
GROUP BY f.RetailerID , c.CalendarMonth ,TypeID

The query plan is showing 88% time on Clustered Index seek on EventPL table but still its taking around 15 seconds to complete. Is there any way I can optimise it to around 1/2 seconds?

Query Plan XML Link


texts: Ternary Relationship in an ERD: What would the cardinalities look like in this situation?

texts:
There are four entity types in the business domain of relevance:
USER, COURSE, MODULE and TOPIC.

The business rules go something like this:

A Course contains one or more Modules, and
each Module contains one or more Topics.

So, I have drawn the following entity-relationship diagram (ERD):

Now,

a User can study many Courses, and
a Course can be taken by many Users.

I want to maintain the progress of a user in that course and so I'm storing an instance of Topic in the studies relationship.

Relational representation

My Relational Table looks like this:

The topic_id column in this table stands for the Next topic the user needs to complete. So whenever the user enrolls for a course I fetch the first topic of the first module of the course and update the row. So (user_id, course_id) becomes my composite primary key. Also, when a user completes a topic, I fetch the next topic in the list and update the instance.

The question

How do I represent relationship in the ERD with the constraints mentioned above?

Since there are three participating entity types, does this make this a ternary relationship, and, if so, what should the cardinalities be like?

Any insight would be much appreciated.


texts: Can CPU / memory influence restore performance

texts:
I want to restore a database and it takes minutes.

I am using a virtual machine with 4 CPUs and 16GB of memory and I want to know if there is any relation between the CPU/Memory to the restore DB process?

For example: if I increase CPUs to 16 and memory to 48GB, should it be faster?


texts:
Description:

We are using PostgreSQL 9.3 - Centos 6 x64 . We have a dtsc_search_data table as below:

dtsc_search_data
id ---- c1 ---- c2 ---- c3
1  ----  1 ----  1 ----  1
2  ----  1 ----  2 ----  2
3  ----  1 ----  1 ----  3

We want to search on "c1, c2, c3" coloumns on dtsc_search_data table with conditions: if seach_value is found on "c1" then return ;  if search_value is not found on "c1" then finding on "c2", if search_value is found on "c2" then return ; else return (c3).

Example:

search_value = 1 => "c1" = 1 => just search on "c1"
search value = 2 => "c1" != 2, "c2" = 2 => just search on "c2".
search_value = 3 => "c3"

CODE (update)

create table dtsc_search_data (id int, c1 int, c2 int , c3 int) ;
 insert into dtsc_search_data values(1,1,1,1);
 insert into dtsc_search_data values(2,1,2,2);
 insert into dtsc_search_data values(3,1,1,3);

 -- search_value = 2
 -- find on c1 column first
     select *
     from dtsc_search_data
     where c1 = 2
 -- if c1 is not found then c2 -- get value from here
     select *
     from dtsc_search_data
     where c2 = 2

Question:

Our solution: write 3 SQL queries search on "c1", "c2", "c3" are followed by conditions above. How can we do that with lowest performance ?


texts: Is there a limit on the number of blobs storable in SQL Server with filestream?

texts:
I use filestream to store blobs (files). I use SQL Server 2008 R2

Some customers have 60k files (50GB). They ask me if there is a limit after which, also because of NTFS, there will be problems.

Somehow the doubt is: if the number of blobs grows too much is there a limit beyond which SQL Server will not handle them properly or not?

I think SQL Server handles this well, but does anyone has a better reply?

Thanks


texts: Merge join does not use index for sorting on PostgreSQL

texts:
I am running PostgreSQL 10.9 and stuck with a strange problem. My schema is a typical EAV with two tables: contacts and contacts_values. There are two relevant indexes:

create unique index contacts_pkey
    on contacts (company_id, id);
create index tmp_ix_company_id_field_id
    on contacts_values (company_id, field_id, field_name, id);

I need to select all contacts that either have or have not any value some attribute. There are two queries what are only different in IS NULL vs. IS NOT NULL part:

SELECT contacts.id
                FROM contacts
                         LEFT JOIN contacts_values AS contacts_values_1
                                   ON contacts_values_1.company_id = contacts.company_id AND
                                      contacts_values_1.id = contacts.id AND
                                      contacts_values_1.field_id = '\x000000000000000000000000' AND
                                      contacts_values_1.field_name = 'email_work'
                WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
                  AND contacts_values_1.id IS NULL;

SELECT contacts.id
                FROM contacts
                         LEFT JOIN contacts_values AS contacts_values_1
                                   ON contacts_values_1.company_id = contacts.company_id AND
                                      contacts_values_1.id = contacts.id AND
                                      contacts_values_1.field_id = '\x000000000000000000000000' AND
                                      contacts_values_1.field_name = 'email_work'
                WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
                  AND contacts_values_1.id IS NOT NULL;

However, their plans are quite different:  https://explain.depesz.com/s/9zLQ for IS NOT NULL and https://explain.depesz.com/s/tbRW for IS NULL. The main difference is that the first plan uses tmp_ix_company_id_field_id both to select and sort rows and the second plan uses the same index to select rows but sorts them as the separate step.

I tried to rewrite these queries using EXISTS/NOT EXISTS but the plans stay the same.

Is it possible to make use of the index to sort rows in both situations? Or, looking wider, is it possible to build a more efficient query for my problem?


texts: Change left outer join on SAP Universe Design Tools

texts:
I need to create a report with SAP Universe Design Tool but when I try to construct a LEFT JOIN between tables, SAP automatically create a LEFT OUTER JOIN when it executes the query in Web Rich Client.

Is it possible to force SAP to build a query just with a LEFT JOIN so it does not create blank lines in the report?


texts: How do I find the identity of a failed connection request

texts:
I have two Microsoft SQL servers, one running 2008 R2 Enterprise, one running 2012 Enterprise. Both are logging lots of failed connections requests due to untrusted domain :

===============

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: 10.180.42.1]

Error: 18452, Severity: 14, State: 1.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.180.42.1]

===============

There's nothing in the Windows Security event logs, and only the SQL errors are in the Application event log and they are the same as the one in the SQL error logs.

The most likely culprit is an internal process trying to connect using Windows authentication, but with a local machine account, but how do I prove it?

I've tried running Profiler capturing audit events, but it doesn't have any more information than the error logs.

How can I find out what credentials the failed login attempt is using and what server the connection request is coming from?


texts:
To be honest I'm not a DBA expert. I get asked by one of my clients in terms of moving a SQL Server development instance that is stored in the production server (source) to another server (destination)

The source server has two volumes E and F:

Volum E stores Databases (E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA)
Volum F stores Logs (F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data)

I have backed one database (Backup_Test.bak) for testing purpose in the source server and restored to the destination by using the following T-SQL via SQL Server Management Studio in the destination:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE,
MOVE 'Backup_Test' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\DATA\Backup_Test.mdf',
MOVE 'Backup_Test_log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10.SPDEV\MSSQL\Data\Backup_Test_log.ldf'

After executing, I get the error

  Msg 102, Level 15, State 1, Line 4
  Incorrect syntax near 'MOVE'.

In the destination, someone installed SQL Server and locate all things stored in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER.

I'm suspecting that different location on both the source (E & F) and destination (only C) causes my issue. If so, do I have to re-install SQL Server on the destination?

The version on the source is SQL Server 2008 and on the destination is /R2. The named instance on the source is SQLSP1\SPDEV and on the destination is SQLSP2. Do I have to create an alias SQLSP2\SPDEV?

Many thanks for your help. I really appreciate.

Regards,
-T.s


texts: How to provide a access to the data base if the user is not having the privilleges

texts:
In MS SQL Server
2008 R2 is installed by a user "A" and he has a data base named "DB1" . He left the company. Now the user "B" is trying to access the data base. He is able to see the data abse and but he is not having the privillege to read the data.

We don know the password of sql authentication. Both users are tin the admin group in that machine where the sql server is installed .

now is there any way by which the user is able to read the data


texts:
Business Departments at our Finance company want IT to import various spreadsheets (companyid, productid, balance, transactions) into the databases.

Lets say, the SQL physical server harddrive contains G:\ [SQLFinance].

When business departments want to drop excel or flat files,

(1) Should we have them place excel/flat files in G:\ SQL harddrive or,

(2) Should we have them place files Not in SQL harddrives, but in another share drive Outside the SQL Server box? Then we copy files over with Powershell/Commandprompt/Robocopy (whatever) into our SQL Server harddrives? From there, we can apply SSIS to transform the excel-flat files into the sql tables.

Are there any benefits/disadvantages to each approach?


texts: Stored procedure query plan different in Always on server

texts: Stored procedure run 10 sec in primary server but keeps running(not executed) in secondary server in always on configured servers. I've verified all configurations, both are identical but execution plans are different. How to make both plans are identical in always on sql server 2012 version.


texts: Removing per column and table collation and charset

texts:
I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their respective charsets and collation properties.

Now that the transition has been completed and all of the servers and the database itself have now been set to use utf8mb4 and utf8mb4_unicode_ci, how would I go about removing those properties from the columns and tables so they inherit the default value set on the server?

For instance presently if I execute a SHOW FULL COLUMNS FROM table; Collation for most columns is set to utf8mb4_unicode_ci rather than NULL.


texts: Writing a stored procedure for deleting a table rows

texts:
I face a problem while writing a stored procedure. I want to write a stored procedure so that it gets 2 input parameters (Date and TableName) and then delete table rows which Date column of the table is smaller than the given input date.

The table has 3 columns:

Date   Name    Id

Thank you


texts: When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ALTERED should appear, or something similar.