texts: "Upsert" trigger writing mass duplicates from SSIS

texts:
I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled)

CREATE TRIGGER [dbo].[MP_VOL_UPSERT]
   ON  [dbo].[vwMP_VOL]
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

UPDATE [dbo].[vwMP_VOL]
   SET
    [vwMP_VOL].[id_batch] = inserted.id_batch
      ,[vwMP_VOL].[id_invoice] = inserted.id_invoice
      ,[vwMP_VOL].[text_vchr] = inserted.text_vchr
      ,[vwMP_VOL].[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[vwMP_VOL].[amt_line_hcur] = inserted.amt_line_hcur
      ,[vwMP_VOL].[line_total_hcur] = inserted.line_total_hcur
      ,[vwMP_VOL].[id_tax_1] = inserted.id_tax_1
      ,[vwMP_VOL].[id_tax_2] = inserted.id_tax_2
      ,[vwMP_VOL].[id_tax_3] = inserted.id_tax_3
      ,[vwMP_VOL].[id_tax_4] = inserted.id_tax_4
      ,[vwMP_VOL].[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[vwMP_VOL].[id_po] = inserted.id_po
      ,[vwMP_VOL].[cnt_rlse] = inserted.cnt_rlse
      ,[vwMP_VOL].[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted
INNER JOIN (SELECT [Org]
      ,[id_vend]
      ,[id_org_orig]
      ,[id_vchr_nbr]
      ,[id_vchr_line]
  FROM [vwMP_VOL]) AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

INSERT INTO [dbo].[vwMP_VOL]
SELECT inserted.* FROM inserted
LEFT JOIN (SELECT [Org]
      ,[id_vend]
      ,[id_org_orig]
      ,[id_vchr_nbr]
      ,[id_vchr_line]
  FROM [vwMP_VOL]) AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line
WHERE old.Org IS NULL;

END

Inserting new rows works fine, but all existing rows get updated with one set of values.

SELECT
text_vchr, count(*) as dups
FROM MP_VOL
group by text_vchr
order by count(*) desc

Results:
text_vchr                   dups
-------------------------   -----
MEDIA MONITORING SERVICES   20897
                                1

Do I have to disable fastload in SSIS, or is there some other way to fix it?

(It's SQL Server 2005, so MERGE is not available)


texts: Data read from linked server returns different data-type on production server

texts:
I have a SQL Server grabbing data from a linked Oracle server inside a SP.

On my development server, EF6 insists the SP is returning a double datatype for one of the columns, but in prod, it insists the same column is a decimal.

The SQL Server version is slightly different:

Prod: 11.0.5582.0
Dev : 11.0.5613.0

There are no (obvious) differences in how the linked servers are configured (i.e. I've compared the connection properties in SSMS).

Being a new feature, the production database has very few (zero, even) rows in the database, while the dev server has many. I have a feeling that might be related, because I recall having to change the datatype in the middle of development (though I didn't think much of it at the time).

What might be the problem here?


texts:
I have a simple SQL Server 2014 Availability Group, one database on a two-node cluster. We need to use CDC to move data to another environment, in my case AWS.
This is a super-critical OLTP very large DB, which clearly any expert DBA would tell you DO not CDC and pull data from Primary DB because of the big performance impact.
So you think all that big $ you spent on AG cluster will let you to have the CDC running from readable secondary? Right? ... Wrong! Apparently you ONLY can run CDC on Primary node! Is it really true?
Is it possible to move data from Secondary DB ONLY using CDC? Technically, CDC reads transaction logs and the secondary database should have all log changes...
Please give me your thoughts. Without this simple feature, which completely makes sense, a big project is in danger! A similar setting worked in Oracle, PostgreSQL.. causing other DBAs to make fun of SQL Server.


texts: query to identify all data types used in postgresql database tables

texts:
I don't want to know all data types, just all data types used in my database. Can this information be queried?

PostgreSQL 8.4 and 9.x versions

I currently need to know all data types for over 200 tables in public ( and other schemas )


texts: Custom Deletion for Ola Hallengren Scripts

texts:
I am trying to find a way to delete backups being taken by Ola's native SQL backups.
I need to keep only 2 weeks of transaction logs, 5 weeks of differentials, 14 weeks of full backups, 13 monthly full backups, and 3 beginning-of-the-year full backups.
I already asked the question and can do the first three with changing the @CleanupTime parameter in each of the respective jobs, however I don't know how to stop the Ola scripts from deleting the monthly or beginning-of-the-year backups.


texts:
I am working on sql script and I am having a requirement of stop continuing the script if some conditions are not satisfied.

When I Google it, I found the RaisError with 20 severity level will terminate it.
But for some reasons I cannot use that option.

Can please provide me what are the possible alternatives to stop SQL script execution.


texts: Use SQL Server Database Mail with Amazon SNS

texts:
Is it possible to configure SQL Server 2012 Database Mail (hosted on EC2) with Amazon SNS?

I'd love a single place to manage all alert notifications and their subscribers in SNS, but Database Mail requires SMTP account information which SNS doesn't seem to provide, but I'm not sure.


texts: Doubts in a Glenn Berry Query ( CPU per Database )

texts:
Please, if this is not place to post questions like these, let me know and I will delete it.

Inside the Glenn berry's Diagnostic queries, there is a query to show how much CPU a database is using. This is the query:

-- Get CPU utilization by database (Query 24) (CPU Usage by Database)
WITH DB_CPU_Stats
AS
(SELECT DatabaseID,
        DB_Name(DatabaseID) AS [Database Name],
        SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
             WHERE attribute = N'dbid'
              ) AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

And I would like to know, if this is a query to see databases that are now using more CPU, or is this based with past information?

I'm trying to know what is causing my server to have high CPU use:

Well, in this picture the server is pretty good, but almost always we have 90%+ of CPU usage, and using sp_whoisactive I can't find nothing ( obviously I got a lot of queries, but none of them seems to be hammering the server ).And read\write is pretty low ( everytime ). And thats what i'm having problems to understand. how can a low read/write server be using so much CPU? Does I/O have nothing in common with CPU?

I'm trying to know what database is the heaviest one, to migrate it.


texts: Should I drop MS_DataCollectorInternalUser in msdb?

texts:
From SQL Server 2008, there is a user in msdb called MS_DataCollectorInternalUser. This user has no login associated with this and can be considered as a orphan user.

It's a security best practice to drop orphan users.

The MS_DataCollectorInternalUser cannot just be dropped using DROP USER TSQL command because it has granted a database permission IMPERSONATEto a user called dc_admin.

We don't use the Data Collector feature of SQL Server.

What do you suggest ?


texts: Why does MSSQL choose scan in execution plan?

texts:
Here is my query (It is a Microsoft Axapta Query):

(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1.CASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,
-- *Snipped lots of columns in T1* --
T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID
FROM LEDGERJOURNALTRANS T1
WHERE (((PARTITION=123123123) AND (DATAAREAID=N'test')) AND (REVRECID=@P1))

Current execution plan :

Current plan

Actually, there is a appropriate index on table.

Index columns : (PARTITION,DATAAREAID,REVRECID)

Fragmentation :

I tried index force. This execution plan (index seek+key lookup) is faster than after plan (index scan):

Force Index Plan

And I tried to :

UPDATE STATISTICS
Changed it the column order, for example
(REVRECID,PARTITION,DATAAREAID)

Why does MSSQL choose clustered index?


texts: Which benchmark assess string pattern matching in a database?

texts:
I have a postgresql DB in which I do a lot of string matching ala :

UPDATE table1 SET colum1 = TRUE WHERE colum2 LIKE '%teststring%';

colum2 is a whole html file

As I said I do A LOT of this and I'm looking for a CPU that can do this faster. Because my understanding is that the pattern matching is mostly CPU bound. So which CPU benchmark targets this kind of workload?


texts: how to encrypt one column of a table without using always encrypted?

texts:
I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier.

It requires a linked Server.

When run, this procedure outputs the following set of scripts that need to be applied in the current server for synchronising the logins (example):

 Create Login [MY_COMPANY_UK\DW_and_Extracts] From Windows;
 Create Login [MY_COMPANY_UK\AuPairDWETL_TS] From Windows;
 Exec  sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'NT SERVICE\MSSQLSERVER';
GRANT VIEW ANY DEFINITION  To  [MY_COMPANY_UK\RGDLMonitoring];
GRANT CONNECT SQL  To  [MY_COMPANY_UK\DW_and_Extracts];
GRANT CONNECT SQL  To  [MY_COMPANY_UK\AuPairDWETL_TS];
GRANT CONNECT On ENDPOINT::[SSBEndpoint] To [MY_COMPANY_USA\sqlservice];

I would like to log all these commands to a table before I apply them to my current server, but I understand these are sensitive information, and I would like to be able to encrypt it and be able to decrypt.

What could I achieve this?

I have already seen this:

Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

How could I achieve this, with minimal effort, and not affecting anything else on my systems?

(I currently don't use always encrypted)


texts: Skip multiple rules during SQL Server installation

texts:
I want to install SQL Server 2008, but I get 2 errors which prevent me from going forward.  I know from this post how to skip one rule. Is there a method to skip both?

I am using Wine (PayOnLinux) on Linux and simulated an environment with help of this post. I managed to skip first rule, but I also need to skip the PowerShell rule. I am just interested to know if the below syntax can be altered to skip 2 rules:

setup.exe /ACTION=install /SKIPRULES=PerfMonCounterNotCorruptedCheck


texts: Access Linked Server error "The provider did not give any information about the error."

texts:
A little information.

I'm running SQL SERVER 2008 R2 64bit.
I have Microsoft.ACE.OLEDB.12.0 provider installed.

On my production server I've setup a linked server to the access db using the provider listed above and everything works great.

Copy the Access db to our dev environment and try to setup linked server useing the same provider.
Test Connection comes back as success
Select from linked server comes back with

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYdb" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYdb".

Both servers are running same version of SQL Server.  Both are Windows NT 6.1 64bit OS.

When I set AllowInProcess to 1 the test connection fails.

Any help would be greatly appreciated as Google is failing me.

Thanks,

Chris


texts: bcp copy from snapshot failed due to snap being in transition

texts:
We have a SQL job that runs each night to perform a data extract and import function.  The job performs against a snapshot and copies from several tables in a serial loop.  Today the job reported the following error:

Starting copy...
SQLState = 37000, NativeError = 952
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Database 'SnapDW' is in transition. Try the statement later.

This did not occur near the time of the Snap recreation job.  Several of the table data extracts (that point at the same snap) prior to this one worked fine. And the job appeared to recover for the next table data extract.

I'd like to find out what caused this job to fail.  Any help on places to look would be helpful as it doesn't appear that there is any reason for the snapshot to have been offline.


texts: ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking?

texts:
I have a database (SQL Server 2012) that is used mainly for ETL of data. Every hour SSIS packages run that truncates/deletes and reloads full datasets. The database keeps growing even though the data is wiped out and replaced.
I ran a SHRINKFILE and the database went from 140 GB down to 2 GB. As the days pass, the database increases in size 2+GB a day.
I am trying to figure out how to prevent the database from growing so rapidly.
Questions

Should I run truncate command or delete command? I try to use truncate but I was having issues with getting a lock on the tables so I switched to a delete
Is it better if I completely delete the table and recreate the object each time instead? Would this help with the database growth?
I have read where "shrinking" is not a good thing, but sometimes it is necessary. Would setting up a shrinkfile command to run weekly during off hours solve my problem?


texts: Oracle 11g encrypt traffic from clients to database server

texts:
I have Oracle 11gR2 db at my work. Last week security team informs that we need to encrypt all the traffic for database users to our databases. First thing that I have checked was Oracle solution - Oracle Advanced Security but after checking the license it would cost huge money for the company. So management decided to find check other solutions.

My second idea was to create SSH tunnel from clients to DB server. But in this case i have no idea how to restrict the access from users to DB only over encrypted tunnel. Can you suggest me how to restrict the access to db server only over the tunnel? Or maybe you have other solution how to my problem.

Thank you in advance for your reply.


texts:
I have asked a question about dynamic table name in a prepared statement here , but I want to know if Mysql server support dynamic table name like this :

SET @tbl = 'Dawin';

INSERT INTO @tbl (col1,col2) VALUES (val1,val2);

I think it doesn't support that , because I tried all that possible syntax that I know .


texts: Meta-relationships (Relationships between relationships) versus tuples

texts:
I am designing a database which needs to store some fairly complex relationships among many different types of entities.

In the simplest example, let us say I have three types of entities - "Student", "Tutor", and "Course", each with their own tables (student, tutor, course).  First of all, I need to represent simple many-to-many relationships between each possible pair of entity types:

student_course - Each student can be enrolled in zero or more courses, and each course can have zero or more students.  (ex: "Alex is enrolled in Biology").
student_tutor - Each student can be working with zero or more tutors, and each tutor can be assigned to zero or more students. (ex: "Emma is assigned to Alex").
course_tutor - Each tutor can be approved to teach zero or more courses, and each course can have zero or more approved tutors. (ex: "Emma knows how to tutor Physics").

Easy enough.  However, I also need to represent some more advanced relationships, like:

"Tutor John is working with students Alex and Maria on course Chemistry"
"Tutor John is working with student Alex on course Algebra"

Notice that these are not captured by the binary many-to-many relationships I described earlier - John can be tutoring Maria, and Maria can be enrolled in History, but that doesn't mean that John is tutoring Maria in History.

The way I see it, I have two options:

Option 1 - A single table containing triplets

Table student_course_tutor:

| student_id | course_id | tutor_id |
|------------|-----------|----------|
| 1          | 1         | 1        |
| 1          | 1         | 2        |
| 1          | 2         | 5        |

This is nice because it explicitly lays out the full relationship in a single row.  However, I now need to maintain this table in addition to my simple relationship tables.

Option 2 - Meta-relationships

Table student_course:

| id | student_id | course_id |
|----|------------|-----------|
| 1  | 1          | 1         |
| 2  | 1          | 2         |

Table student_course__tutor:

| student_course_id | tutor_id  |
|-------------------|-----------|
| 1                 | 1         |
| 1                 | 2         |
| 2                 | 5         |

Since I was going to have a student_course table anyway, adding this "meta-relationship" table is less redundant than representing the full triplet.  However, it now becomes a lot more abstract and difficult to understand.

Which way is more correct?


texts: Find the closest bar, when searching for a drink

texts:
Problem: Find the closest bar that serves the drink(s) I'm looking for.

Here you can find the generated MySQL code http://pastebin.com/5Uc2ewUW

The API Request that interacts with this problem will have these parameters

query, String, ideally the drink name
lng, double, the starting longitude
lat, double, the starting latitude
range, integer, max distance in meters (with a default value)

The query parameter may select more than one drink (think about searching for "Vodka").

What would be a good strategy to write a SQL query with good performance?

I'm not very expert, but my idea is to

SELECT bars in the range
SELECT from drink__bars where bar_id is in the previous select result
JOIN the drinks table to get drinks data

How do I set the order based on the distance?

Any suggestion is appreciated!


texts: Replicated timestamp column in different timezone

texts:
I have the master running Mysql 5.5.38 on Ubuntu 14.04 LTS. The timezone in the master is UTC+5:30. The slave is in AWS RDS which runs in UTC. The slave is primarily used for running for SQL queries by staff.

To maintain their sanity, I setup the timezone hack. This also applies to the replication user.

One of our innodb tables records scheduled appointments. The relevant parts of the table are below:

`startDateTime` timestamp NOT NULL DEFAULT '2000-01-01 12:00:00',
`endDateTime` datetime DEFAULT NULL,
`createdOn` timestamp NULL DEFAULT NULL,

I am seeing the following time on the master when querying the table:

startDateTime - IST
endDateTime - IST
createdOn - IST

However on the slave:

startDateTime - UTC
endDateTime - IST
createdOn - IST

Running SELECT NOW(); on either servers shows current time in UTC+5:30. The bin_log format on both master and slave is STATEMENT

How do I ensure that the replicated column startDateTime is also in IST?

Update:

RDS is a hosted mysql with no access to my.cnf. Futher time_zone variable is restricted and hard coded to UTC


texts: Any reasons to use "SET search_path" if use schema-qualified names everywhere in Postgresql

texts:
I googled but I couldn't find answer on this question: does it makes sense to use  SET search_path in case of use schema-qualified names everywhere in Postgresql.

Any ideas will be highly appreciated


texts: Best way to run an ETL on a production MongoDB database

texts:
We have the following environment:

3 servers + 3 replica set servers
Each server has 3 shards
Our main collection has around 40,000,000 documents that average at around 6kb
Our shard key is hashed(_id) - _id being pure BsonID
We peak daily at around 25,000 I/OPS and low point at around 10,000

We want to run an ETL that loads all of the documents on the main collection, do some in memory calculation (in our application tier) and then dumps it into an external DB.

We took the very poor and naive approach and simply loaded documents without a query using limit, skip and batchSize - which was a complete failure (severly hurt our service level - Even though we set the readPreference to secondary).

I was wondering what would be the best approach to be able to load all of our documents without hurting the performance of our database.

The data can be a bit stale (a few seconds delay from the actual data on the primary is fine).

Sorry in advance if this is the wrong part of StackExchange to post my question.

Thanks!


texts: Beefing up capacity of existing AAG cluster

texts:
I am planning to add 3 new databases to an existing AAG cluster in a VM. The AAG has Veeam databases on it as of now. Veeam backups are not running on it as of now apart from a couple of batch processes of Veeam. I ran the PAL tool to figure out if memory was under pressure because SQLserver is consuming almost 93% of memory. I checked the page life expectancy and it looked OK. The Veeam team told me they had some performance issue and this is what i got from the PAL tool.

Do you think i need to add more memory to the VM? To make sure that the other 2 applications would just run fine. One of them is really light weight. But the other one is quite intensive

So I ran the following perfmon for almost a week, and this is where I am getting alerts. After changing the Max memory setting from default to 19gb

The following counters were run

•   Memory: Available Bytes
•   Memory: Pages/sec
•   Process: Working Set
•   SQL Server: Buffer Manager: Buffer Cache Hit Ratio
•   SQL Server: Buffer Manager: Total Pages
•   SQL Server: Memory Manager: Total Server Memory (KB)


texts: How to audit all DML operations on a SQL Server 2008 R2 database

texts:
In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables.
The database is new to me as well as the relevant business knowledge, so I just have no idea.

Is there a way to quickly collect a statistic within couple of days?

I am using Standard Edition.


texts:
Can anyone share proper advantages-disadvantages? If possible, I am trying to search for really great benchmarks between both, so please share it with me.

Also, which will be best between them for Transacts(I know SQL server is specially made for T-SQL, but even if you choose Postgres over SQL Server, then why?)? I am trying to experiment.


texts: ORA-00904: "GET_JAVA_PROPERTY": invalid identifier - 11gR2

texts:
In Oracle DB 11.2.0.4, linux 6.7 single instance

select get_java_property('java.version') from dual;

SQL> show user
USER is "SYS"
SQL> select get_java_property('java.version') from dual;
select get_java_property('java.version') from dual
       *
ERROR at line 1:
ORA-00904: "GET_JAVA_PROPERTY": invalid identifier

I can't find anything on this ORA-00904: "GET_JAVA_PROPERTY": invalid identifier, anyone know why this happens?

This works fine

SQL> SELECT  dbms_java.get_ojvm_property(PROPSTRING=>'java.version') FROM dual;

DBMS_JAVA.GET_OJVM_PROPERTY(PROPSTRING=>'JAVA.VERSION')
--------------------------------------------------------------------------------
1.6.0_191

SQL>

thanks


texts: SQL Server 2008R2 - Replication - Log Files / Indexes

texts:
We have 4 SQL Servers engaged in Transactional Peer to Peer Replication. I am doing some work on the Indexes and have noticed 2 things I didn't expect:

The Indexes have different fragmentation levels - I understand that only the Insert/Update/Delete commands are replicated - but wouldn't they therefore create the same level of fragmentation on each server? (They're not wildly different, but enough to catch the eye and change the order of priority (i.e. 99% at the top etc))
The Log Files are wildly different sizes (the smallest is 10Gb the largest around 85Gb)- again, I was expecting them to be similar/spot on the same, surely if the log has recorded and performed the same actions due to the replication they would be similar?

Any thoughts/wisdom?

Thanks

MH


texts:
I set up a MongoDb sharded cluster (2 shards) as described in this tutorial.

Now, I want to test that the shard 1 (RS1) and shard 2 (RS2) works well. However, all the databases, collections and documents that I create goes in RS1 but seems to never go in RS2.

Am I missing something? How to make sure that RS2 works?

I use MongoDB shell version: 2.6.9.


texts: Reverse engineer Oracle database in visio

texts:
Trying to reverse engineer tables added into a database but when i connect, I get strange Chinese symbols with a lot of apex stuff.

I dont see anywhere the tables that i added, what am i doing wrong.


texts: Is it okay to clear SQL Server Buffer every weekend or month end?

texts:
We have started using SQL Server 2014 Enterprise edition on our new server having 32 gigs of RAM, and in less than 3 weeks the server process is accumulated 13 GB and plus of RAM.

Including IIS worker processes the total amount of RAM being used is accumulated somewhere 75%-80% so we are just curious if we need to clear the buffer every weekend of at the month end. But, before that we want to make sure if it is a wise idea to do so! SO my questions:

Is it a wise Idea to limit the max server memory for SQL Server? (I guess NOT)
Is it a good idea to clear the buffer pool at a certain frequency (weekend / month-end)? Will it impact the performance?

Thanks in advance!


texts: How do I construct a LINESTRING by aggregating Points (ST_MakeLine) in MariaDB?

texts:
Let's say I have a table with 3 points,

CREATE TABLE foo ( id int PRIMARY KEY, geog point );
INSERT INTO foo (id, geog) VALUES
  ( 1, Point(0,0) ),
  ( 2, Point(1,1) ),
  ( 3, Point(2,2) );

How can I construct a LINESTRING with those three points in those three different rows. The resulting WKT should be LINESTRING( 0 0, 1 1, 2 2 )?

PostGIS does this with a Spatial Aggregate called ST_MakeLine

SELECT ST_MakeLine(geog)
FROM foo
ORDER BY id;


texts: Consolidated realtime mysql backup with master/slave setup or binlog

texts:
I have 20+ mysql servers and their have low write requirements.

So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose.

If I have only a single master, then replication is easy, however, in this case, I have 20+.

Are there any easy way if I don't want to setup 20+ mysql salve on a single server?


texts: SQL Server FCI online with incorrect file path

texts:
I just encountered this strange issue with my SQL FCI yesterday.

Windows 2008 R2 and SQL 2012 - 2 nodes FCI

SQL server/ SQL agent resources are online and working as normal, but the system databases are now on a different drive.

They are now in drive X as opposed to drive K and drive K is empty. sp_helpfile and SQL startup parameter are showing the old drive path which is drive K. System db backup jobs failed because they were still pointing to the old path, which is expected. Now drive X has all the files that K used to have and K now is empty and has a new size.

I know SQL won't start if the file path is different because the master database files are not there. However, we never restarted SQL service and somehow the system database files were moved to the new drive without interrupting the SQL resources.

Has anyone encountered this behavior? My guess would be X is formerly K and the files didn't get moved, but how could SQL could still be on pointing to the old drive path K?Disk X is also under SQL server dependency.

Thanks.


texts: is there any point of moving tempdb files into different disk drive if all LUNs are from the same RAID pool?

texts:
According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk.

I have a virtual server which had originally 4 LUNs from the same RAID 10 pool. By the help of volume manager, I converted those 4 LUNs into 4 different volumes.

Now the question is, will it make any difference by moving tempdb into separate volume, or it is just fine to keep them with other SQL Server files in terms of performance?


texts: Postgres: "show max_connections;" output a different value from postgresql.conf file

texts:
I'm trying to increase the max_connections value from the config file, but the show max_connections; command always shows 100.
I restarted postgres after changing the conf file,
I have modified the correct config file which is displayed by the command: show config_file;


texts: Accessing Shared Folder Using Oracle Database

texts:
I have two machines A & B. I shared a folder from machine B and mapped it as a drive on machine A. Now I want to configure my Oracle Database in Archive Log mode and want to set the Archive location to that mapped drive. I have also set full (Read/Write) permissions to that folder. But for testing purpose I tried to create a file on that folder through SQL as:

SQL> CREATE pfile='z:\archive\pfile.ora' FROM spfile;

It says:

ERROR at line 1:
ORA-09210: sftopn: error opening file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

How can I make this drive available for Oracle Database?

Regards,
Ali


texts: How to write Procedure to fetch data from other table?

texts:
drop procedure if exists findClosestTimeStamp;
delimiter $$
create procedure findClosestTimeStamp()
begin
   declare tt1 time;
   declare dt1 date;
    declare  view1 varchar(30);
    declare done int default false;
    declare curComm cursor for
        select * from t1; -- This is the query used by the cursor.
    declare continue handler for not found -- This handler will be executed if no row is found in the cursor (for example, if all rows have been read).
        set done = true;

    open curComm;

    loop_comm: loop

        fetch curComm into dt1,tt1, view1;

        if done then
            leave loop_comm;
        end if;

        SELECT * FROM logs
        where tt <= tt1 -interval 1 minute;
    end loop;
close curComm;
end $$
delimiter ;
call findClosestTimeStamp();

Table t1 has
dt         tt       view
2020-06-26 16:47:03 TouchDisabled
2020-06-26 07:58:46 Toucbled
2020-06-26 07:58:48 Touchsabled
2020-06-26 07:58:49 TouchUpled

Table logs has
AppName Button      dt          tt
App1    Button1     2020-06-26  07:45:37
App2    Button2     2020-06-26  07:45:42
App3    Button3     2020-06-26  07:45:44

I am trying to fetch data which occur one minute before the time attribute pointed by cursor in table t1 from the logs table. I dont understand why this is code is printing same value 3 times .and also not doing what I am trying to do!!
edit 1:

when cursor is pointing on the first row of table t1 I will use this tables time into the variable tt1 ,Now using this tt1 time I want to fetch the data in logs table.
The data to be fetched should be betwwen tt1-1 minute to tt1
example:

if I take row 1 time 16:47:03 so it should output the logs which are having tt values between 16:46:03 to 16:47:03.
I hope this clears question more!!


texts: Benefit to additional index with key column moved to includes

texts:
Say I have an Index Foo with:

Keys [A, B, C] and Included columns (D, E)

Is there any performance reason to have an additional index Bar with:

Keys [A, B] and Included columns (C, D, E)

My assumption is that a non-needed key at the end would be treated the same as an included column.


texts: How to identify which schema calls a function in another database without schema?

texts:
I have a database with 3 schemas (db1) where each schema calls the “guardarTransaccion” function of a database without schema (db2).

When calling the “guardarTransaccion” function pass the record id and the db2 has to connect to the corresponding schema using DBLINK and load the detail data (eg db1.schema1).

The problem I have is when we call the function from a schema in db1 I dont have a way to identify which schema I called the function for to load the corresponding data.

The code of the function in DB2 is:

CREATE OR REPLACE FUNCTION "public"."guardarTransaccion"("tipo" text, "idRemito" int4, "idPicking" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$
DECLARE numeroRemito TEXT;
BEGIN
SET search_path = 'public', 'public', 'pg_catalog';

    -- load db1 data using dblink view
    IF "tipo" = 'E' THEN
        UPDATE picking SET rom_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM romaneos_venta WHERE rmv_id = "idRemito";
    ELSE
        UPDATE picking SET rmv_id = "idRemito", pk_remitado = 1, pk_cerrado = 1 WHERE pk_id = "idPicking";
        SELECT rmv_numero_comprobante INTO numeroRemito FROM remitos_venta WHERE rmv_id = "idRemito";
    END IF;

    -- operaciones con los datos / Operations with data

RETURN 1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

The way we find it to solve it is to create schema in the db2 only with the changes to each db1 schema, but it does not seem correct, what would be the best way to solve this?


texts: create one private database link for two users oracle

texts:
I am trying to create one private database link to be accessed by only two users but it gives me syntax error.

My code:

CREATE DATABASE LINK "mylink"
 CONNECT TO USER1,USER2
 IDENTIFIED BY password123
 USING 'mydb';


texts: Database design for a customer and payment system

texts:
I need help with database schema design.
Business rules

We have users, each user can have listings.
Each listing has one or more license.
There are n type of licenses (products)
Each listing can have only one product per type

Current tables arrangement
I have a product table (type, price, duration, recurring, manual). Each product has price, duration, if it is recurring or one time of, if payment type will be manual or automatic and a type.
The business_product table stores all assigned products to listing, start time, end time, business_product_status enum('paid','canceled', 'processing').
The user_product table contains product type and how many licenses they have for it, start time, end time.
A billing table is generated from the user_product table:
CREATE TABLE `billing` (
  `billing_id` int(11) NOT NULL auto_increment,
  `external_id` VARCHAR(100) DEFAULT NULL,
  `invoice_date` datetime,
  `bill_date` datetime,
  `start_date` date,
  `end_date` date,
  `payment_method_id` int(11) default NULL,
  `user_id` int(11) default NULL,
  `authorized` VARCHAR(200) DEFAULT NULL,
  `fee` decimal(12,2) default NULL,
  `notes` varchar(500) default NULL,
  `billing_status` ENUM('new','paid','returned','canceled', 'pending', 'overdue')  CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'new',
  `billing_type` ENUM('auto','manual'),
  PRIMARY KEY  (`billing_id`),
  FOREIGN KEY (payment_method_id) REFERENCES payment_method(payment_method_id),
  FOREIGN KEY (user_id) REFERENCES user(user_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

Where its gets complicated is connecting back billing to user_products, ideally if a user doesn't pay on time we want to cancel licenses and remove them from listings.
Any suggestions how to simplify or improve this?


texts:
I'm trying to use ON CONFLICT on two columns where one can be null. Unfortunatelly with partial index I don't seem to be able to do it.

create table tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
values(1, 3)
ON CONFLICT(col1, col2) DO UPDATE SET col3 = true;

  ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification


texts: Can anyone help me understand the following paragraph?

texts:
I was reading Database Systems Design, Implementation and Management by Carlos Coronel. In the chapter of Normalization after he explained 3NF, he writes

  The existence of multiple candidate keys can also influence the identification of transitive dependencies. Previously, a transitive dependency was defined
  to exist when one nonprime attribute determined another nonprime attribute. In
  the presence of multiple candidate keys, the definition of a nonprime attribute as
  an attribute that is not a part of any candidate key is critical. If the determinant of a
  functional dependence is not the primary key but is a part of another candidate key,
  then it is not a nonprime attribute and does not signal the presence of a transitive
  dependency.

I am having hard time to understand what he wants to say. Can anyone explain this paragraph in simpler terms.


texts: when can AG primary replica be a different host to WFC primary?

texts:
To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster.

I've seen cases after certain failover modes where the primary IP address in the cluster (in Windows Server Manager -> Failover Cluster Manager -> Cluster Core Resources) can be the address associated with the secondary replica in the AG.

Is this a problem – does it matter at all?

More generally, is there any reference on how the WFC relates to the AG, and to the Availability Group Listener?


texts:
I have been looking all over the web now, and can not seem to find the option to disable this command. I think this is quite a risky security hole.

There is an option to disable SHOW DATABASES; , but not SHOW TABLES;

Maybe some of you had


texts: Which of sys.dm_exec_query_stats columns should I pay most attention?

texts:
I'm trying to tune my database and currently staring into sys.dm_exec_query_stats (with actual queries):

SELECT SqlText.text, * from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS SqlText
ORDER BY total_elapsed_time DESC

and the output is 170 rows with lots of numbers and ... well, different queries have different columns "very large" - some have total_worker_time of 12+ seconds, some have 3+ million total_logical_reads and I have no idea which of that is more important than the other.

Which columns do I pay most attention to?


texts: How to detect performance issues in function working on temporary tables?

texts:
I have a function which creates many temporary tables and performs inserts, updates, deletes and selects on them.
Some columns are textual and I have a lot of SIMILAR TO operations going on
The function is taking too long to execute and I don't know how to detect the problem, because I can't do an explain analyze on temporary tables to detect expensive operations.
What could possibly be done? Any suggestion would be appreciated.


texts: SQL logs filling up with "Setting database option Compatibility_Level" messages

texts:
On several of my servers, I have "Setting database option Compatibility_Level" messages filling up the SQL log and application event log to the tune of twice every 5 seconds. This makes finding anything else in the SQL log nearly impossible.

I'm aware that this is a known bug in MS Reporting Services (marked "Closed as Won't Fix"). I'm looking for other ideas to resolve this. Using profiler, I found the code being run by the reporting server is:

DECLARE @currVer nvarchar(128), @currMajorVer nvarchar(32)
     , @idx int, @currMajorVerInt tinyint ;

SELECT @currVer = CONVERT(nvarchar(128), ServerProperty('ProductVersion')) ;
SET @idx = CHARINDEX('.', @currVer, 0) ;
SET @currMajorVer = SUBSTRING(@currVer, 1, @idx-1) ;
SET @currMajorVerInt = CONVERT(tinyint, @currMajorVer) ;

DECLARE @dbname sysname

IF @currMajorVerInt =  10
BEGIN
    SELECT @dbname=DB_NAME()
    EXEC sp_dbcmptlevel @dbname, 100 ;
END

Ideas?

The listed "workaround" suggested modifying the system stored procedure sp_dbcmptlevel, which although possible, is not recommended for a variety of good reasons.

Another thread on this issue suggested changing the system account used by the reporting services service. I'm not sure if that applies to our situation. Has anyone found that this resolves the issue for you?

Only other idea I had: is there any way to intercept and either block a query from being executed, or to alter the syntax of the query before it is executed? Like a kind of INSTEAD TRIGGER or something?


texts: How to insert multiple values in a specific field in a table using mysqli?

texts:
I am working on a project and I I have a scenario like this: I have many field in my table :

table_name : transaction_tbl
-id
-name
-description
-ref_number : text(datatype)

In my inserting here is my code:

 $sql = "INSERT INTO transaction_tbl (`name`,`description`,`ref_number`) VALUES ('$name','$desccription',$ref_number)";

if ($conn->query($sql) === false){
                    trigger_error('Wrong SQL: ' . $sql . 'Error: ' . $conn->error , E_USER_ERROR);
                }else {
                    echo "Successful ! Data is inserted in database ^__^" ;

                }

As the name itself ref_number or reference number, so there will be a time that I will have a lot of reference number,how can I let it insert if it will have multiple values? Thanks :)

UPDATES :

I want something like this :

name    description            ref_number
bag     to be use               10359435846
                               05438547656
                               035848576


texts: Only one simultaneous connection per user?

texts:
I'm currently experiencing some trouble, seemingly because my application refuses to connect to the database if there is already another connection open from the same user (but in a separate process).

Based on my observations, it doesn't seem to matter how many or few other users are connected, and @@MAX_CONNECTIONS is set to 0 (unlimited).

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

Here is my connection string:

[db]
sql=MS SQL
provider=SQLOLEDB.1
data source=foo\bar
connection=Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=foobar

As you might be able to discern from the above, we are connecting to the database using Microsoft OLE ADO. The application is an old VB6 legacy program.

By "refuses to connect" I mean that the method Open on the ADODB.Connection object fails.

Sadly, there is currently no log of the error output from this method in the production environment. I'm currently waiting for a patch to the application to be applied, to see if I can get extended information here.


texts: Is this user-storage database system efficient, effective and secure?

texts:
I am using:

PHP Version 5.2.17-MIT-BACKPORT-GnuTLS
SQLite 2.8.17

I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root worked out, and am sure that there are now security flaws.

root stores this essential information:

Unique ID
Email
Salt for password
Hashed password
Time-stamp

The above rows are created with the following methods:

The unique ID is created with this code: uniqid(rand(1000,9999));, producing a sixteen digit unique ID, like this: 36756ceb8a84874d, made up of a four digit random number prefix (3675) and a uniqid (36756ceb8a84874d).
The email is a user entered string, which is stored raw in the database.
The salt for the password is a sixteen digit unique string like the one created for the unique ID.
The hashed password is created by hashing the raw password inputted by the user, with the above salt, like this: crypt($password,$salt);.
The time-stamp is a simple date/time number, like this: 1456388595.

My questions are these:

Are the rows that I have and the techniques that I am using best practice?
Are there any security flaws in them?
Does anything need to be improved?
Should anything be added?
Can it be more efficient?

I want to get the root table working before expanding on to other tables. Maybe I should use another table called cookies, which would have rows including: id of user, id of computer, salt and hashed key?

root is the root table of the database, with all the essential information. Say it was Stack Overflow, the other tables would hold things like questions, answers, comments, reputation changes, about me, setting etc. so root is the table with all the stuff that needs to be secure, like login details, hashed password, email etc. The cookie list would be separate and for auto-logins on different devices.


texts: "The SELECT permission was denied" only for some of my tables

texts:
In my database there are about 100 tables on 2 tables I get the following error when accessing the database by a SP (all data access uses SP):

  The SELECT permission was denied on the object 'XXX' database 'XXX' schema 'dbo'. sql server 2008

I can fix the error by giving grant on the select:

GRANT SELECT
  ON OBJECT::[dbo].[XXX] TO [allsp_user]
  AS [dbo];
GO

when running

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions

I see only for my 2 tables:

XXX allsp_user  dbo SELECT  GRANT

My problem is that the error comes only on 2 tables, all others are working fine with selects. None of them have a special permissions except the 2 I added the Select Grant. I do not want to Grant the user the select permissions because I do not understand why he needs them, in all other projects and on all other tables it works fine without the permissions. How to find what causes the error on those 2 tables?


texts: Import bacpac containing security policies and temporal-tables with schemabinding

texts:
I've created a bacpac backup of my database (both Azure and local with SSMS). Trying to import the bacpac to same or another machine (again both Azure and local) the import always fails with this error:

  Error SQL72014: .Net SqlClient Data Provider: Meldung 3729, Ebene 16, Status 4, Zeile 1 Cannot ALTER 'SecTable' because it is being referenced by object 'sec_SecTable'. Error SQL72045: Fehler bei der Skriptausführung. Ausgeführtes Skript: ALTER TABLE [dbo].[SecTable] SET (SYSTEM_VERSIONING = OFF); ...

SQL-Server versions: 14.0.3223.3 and 15.0.1800.32.

Already tried different versions of SSMS (17,18) and via SqlPackage.exe (Visual Studio 19 and SSMS 17/18) to execute the import/export of the bacpac.

The smallest DB creation script that will reproduce this problem is:

CREATE TABLE [dbo].[SecTable](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [SystemHistoryStart] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SystemHistoryEnd] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    [SecValue] [int] NOT NULL
 CONSTRAINT [PK_SecTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SystemHistoryStart], [SystemHistoryEnd])
) ON [PRIMARY]
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[SecTable_History]))
GO

-- Function
CREATE FUNCTION [dbo].[sf_SchemaBoundTableFunction]
(
    @secValue int
)
RETURNS TABLE
WITH SCHEMABINDING
RETURN
    SELECT 1 AS Result
GO

-- security policy
CREATE SECURITY POLICY [dbo].[sec_SecTable]
ADD BLOCK PREDICATE [dbo].[sf_SchemaBoundTableFunction]([SecValue]) ON [dbo].[SecTable] BEFORE DELETE
WITH (STATE = ON, SCHEMABINDING = ON)
GO

Any suggestions or workaround how to solve this problem or how to configure the import/export of the bacpac without removing the schemabinding? Is it maybe a bug or am I doing something wrong?


texts:
I am getting this error in SQL Server log:

  Failed to create appdomain "master.sys[runtime].2"
  Could not load file or assembly System.Data . Not enough storage is available to process this command

After this error, my web application is not working. Following are the server specs:

SQL Version : SQL 2012 (SP1) - 11.0.3128.0 Intel(x86) Standard Edition
RAM : 64 GB (Max Memory: 2147483647)
Hard Disk Memory Size Available : 600 GB
Effective memory in use by instance:
(1) physical_memory_in_use_Gb : 2.94 (2)locked_page_allocations_Gb:0 3)virtual_address_space_committed_Gb: 2.98

I have searched on this. But not got any proper solution. Please help me.


texts:
Is there some alternative in MYSQL for Postgres VALUES syntax:

select *
from (values('1'), ('2')) as f;


texts: Why is the sum() of zero numbers not zero?

texts:
For the following query

SELECT sum(c)
FROM (SELECT 1 c WHERE false) t;

I thought it takes the sum of zero numbers, and therefore should return 0. (Similarly, I expect, e.g. an array aggregate function to return an empty array on zero elements).

Yet, it is returning a NULL (using PostgreSQL 11).

=> SELECT sum(c) FROM (SELECT 1 c WHERE false) t;
 sum
-----

(1 row)

This doesn't make sense to me logically.

Why is the sum of zero numbers (for any numeric type) not zero? Is this NULL returning behavior standard?

Or maybe I am missing something. Any reason one has to make the sum NULL in some corner cases?


texts:
I get the below error running a CREATE DATABASE script...

CREATE FILE encountered operating system error 5(failed to retrieve text for this
error. Reason: 15105) while attempting to open or create the physical file
'H:\TestDatabase.mdf'

I am logged in as sa, which is a sysadmin.

What am I missing?

CREATE DATABASE TestDatabase ON PRIMARY
(
    NAME = N'TestDatabase',
    FILENAME = N'H:\TestDatabase.mdf' ,
    SIZE = 51200000KB ,
    FILEGROWTH = 1024KB
)


texts: Script for migrating server role permissions - SQL Server

texts:
I need to obtain a script for all the user-defined server roles in my SQL Server instance with their assigned permissions, such as the below:

GRANT ALTER ANY AVAILABILITY GROUP TO [ServerRole1]

What I have done so far is:

SELECT ' GRANT ' + SPerm.permission_name + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
    where sp.type='R' and sp.name!='public'

but I am getting the error Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in add operator occurring in SELECT statement column 1.

I have then tried to solved it as below I am getting the error 'Expression type int is invalid for COLLATE clause':

SELECT ' GRANT ' + SPerm.permission_name + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id collate SQL_Latin1_General_CP1_CI_AS  = SPerm.grantee_principal_id collate SQL_Latin1_General_CP1_CI_AS
    where sp.type='R' and sp.name!='public'


texts:
I'm in the middle of my little project here and i had some problem here, this is the detail of it.

I have 4 server, 3 server using for getting data from my sensor module and 1 is using for storing data. I try to using chained replication but it seems didn't work for me. I'm using multi master in 3 server and 1 slave in other server.

S1 <--> M2 <--> M3 <--> M4

Each server have different database, in M2 I have database name "PT100", M3 = "PT100_2" and M4 = "PT100_3", so I created 3 database on each server.

The problem is M2 cannot receive replication with database "PT100_3" from M4, even though M3 is receiving "PT100_3" from M4.
S1 even worst, it only can receive "PT100" from M2, even though there is "PT100_2" replication running on M2, nothing either on "PT100_3".

What I want is, storing all the database from M2, M3 and M4 in S1, that's all.
How can I achieve this? Is it really possible to doing this and I just do some mistakes in configuration?


texts:
I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables.

insert into tab(id,name,date) values
((select max(id)+1 from tab),(select name from tab1),now());

Can you tell me how to do achieve this?

Whether this can be achieved by simple query itself or stored procedure is the only option?


texts: Cassandra multi-DC:write on LOCAL and read from any DC

texts:
We use a multi-data center (DC) cassandra cluster. During write on to the cluster, I want only LOCAL DC to perform writes on its nodes as we are already routing the write requests to the desired DC only based on the source from where write is initiated. So, I want only LOCAL DC to process the write and no other DC to perform the writes on its nodes. But later on by virtue of replication among nodes across DCs, I want the written data to be replicated across DCs. Is this replication across DCs possible when I am restricting the write to only one DC in the first place.If I do not open connections to REMOTE hosts lying in different DCs during my write operation, is data replication possible amongst DCs later on. Why I definitely need replicas of data in all DCs is because during data read from cluster, we want the data to be read from any DC the read request falls on, not necessarily the LOCAL one.

Do anyone has solution to this?


texts: Necessary to include clustered index columns in non-clustered indexes?

texts:
Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index?

In other words if the Products table contains a clustered index on ProductID, when creating a non-clustered index where it would be advisable to include the ProductID column, is it necessary to nonetheless add it as a column?

If not, are there scenarios where it would be good to add the column name to the non-clustered index?


texts:
I'm granting the following rights to a user:

grant super on . to 'user'@'host';

does this include the rights on dbname given by:

grant all on dbname.* to 'user'@'host';

thank you


texts:
I have some SQL servers that are used only by developers, and the developers (i.e. me) are expected to maintain them. We really don't care much about losing data, so backups are not a priority, but the transaction logs do have a tendency to fill up, slowing everyone down until I can remember how to truncate them.

Does anyone have a sample scheduled task that deals with this situation? Second best: advice on how I should write my own?


texts: Why are execution plans wildly different when selecting 1000 rows vs 2000?

texts:
Consider the queries below.  The only difference is 1000 vs 2000 rows returned.

SELECT TOP 1000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

vs

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

However, 1000 rows are returned in under a second, while the query with 2000 rows takes significantly longer.

The query plan for the first query is reasonably simple:

but the 2nd query is using parallelization:

What would using just 1k rows more force parallelization?

P.S.  The table contains over 6 million records and TimeStamp column is indexed.


texts: Unable to connect to DB using Mysqlworkbench

texts:
I am running a Mysql db inside docker container as shown below:

Adding a new user from within the container and logging in with same user works fine within the container but the same doesnt work when i try to connect from the workbench:

However the login works fine as a root user both from within the container as well as the workbench.


texts:
The Transaction Log Usage fluctuating quite frequently.

When I run DBCC SQLPERF('LOGSPACE') it shows Log Space Used (%) as 3% which is like 650MB while at very next minute or two when I run it again it might show as 0.0123% without doing any kind of Log backup or anything and it happened few times.

Just wondering what it could be? I was trying to analyse Log usage pattern over the period of time.

Thanks in advance


texts: postgres listen only runs when I do something

texts:
Consider the following demo in postgres 9.6.

I did the following in this order:

I ran in PSQL: LISTEN foo;

I ran in DataGrip.  NOTIFY foo,'thad man';

Went back to the PSQL console and don't see my expected:

 Asynchronous notification "foo" with payload "thad man" received from server process with PID 4117.

It is only in PSQL when I run any statement do I get my notification. How might I be able to see notification are made without doing anything? Is this possible? If I have to run a statement like SELECT 1; what  benefit might I get from listening in the application layer? Would I have to be running SELECT 1; in a constant loop every second or something to get notified?


texts: How to Apply a Title-Case RegEx Condition for Object Names with Policy Based Management

texts:
I'm implementing PBM on a SQL Server 2014 instance and want to enforce a condition that all objects are created outside the dbo schema and all object names are created in title-case (i.e., OrderDetail). I have the regular expression drafted [A-Z]{1}[a-z]+[A-Z]?[a-z]* but am not able to create a satisfactory object name with it. An example output is here.

Policy condition: '@Schema != 'dbo' AND @Name LIKE '[A-Z]{1}[a-z]+[A-Z]?[a-z]*''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE TABLE Test.OrderDetail (
    Col1 int)

Is this particular expression not supported by PBM or is there alternate syntax I should use in the condition?


texts: Resolving DUPLICATE for a column with UNIQUE

texts:
I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test title-2.

Is there a way to make a unique value upon DUPLICATE?

INSERT INTO table1
  (title, abr, name)
  VALUES
  ('title', 'abr', 'name')

In this typical example, all three columns are UNIQUE. When the INSERT fails, I do not know which col caused the error to change its corresponding value.

How can I make a query to change value to value-identifier upon duplicate error?


texts:
Possible Duplicate:
  How do I get back some deleted records?

Our C#.NET application mistakenly deleted all records from all tables of its database.
The application automatically takes backups, and the last backup it took after this delete is big in size, which means there must be useful data in the backup. The recovery model of the database is SIMPLE and I turned the server off after the records were deleted.

How can I recover the deleted records?


texts: How to estimate/predict data size and index size of a table in MySQL

texts:
I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer

For an example we have a table City with InnoDB engine,lets say in future (in next 1 year) it will have 1 million of records so what will be the estimated data size and index size of that table in this period.

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

UPDATE

What will be the estimated upper bound (Max size of table) with 1 million records and how can we estimate it.


texts: SQL 2005 Replication - How do I get the DA Profile MaxBCP option to work with a Transactional Publication?

texts:
I have two publications on a database.  One is a transactional publication, the other is a snapshot publication.  We have a stand-alone Distributor (separate from the publisher) and both publications have push subscriptions (to the same database on the Subscribers).  There are two subscribers (our client is transitioning from SQL Server 2005 to SQL Server 2008 R2, so we're pushing the data to both machines currently).  The Publisher and Distributor are both running SQL Server 2005.

I'm using the same Distribution Agent Profile for both publications.  When the snapshot is applied at the subscriber, the snapshot publication will honor the MaxBCP setting and push multiple BCP files simultaneously.  Unfortunately, the Transactional publication (which is configured to use the same profile for the Distribution Agent) will only push the BCP files serially.

We have a pretty fast link between our site and the subscriber (on the other side of the country), but the single streams only utilize about 2-2.5 Mbit.  Utilizing multiple BCP would allow us to take full advantage of the bandwidth.  We tested setting MaxBCP to 16 with the snapshot publication and that actually allowed us to burst up to 35 Mbit with an average rate of about 29Mbit.

We're planning on migrating the database to SQL Server 2008 R2 on our side as well and we'd like to take full advantage of the available bandwidth when we reinitialize it on SQL 2008 R2 as the DB is very large (~250 GB).


texts: Postgres: foreign key constraint for components of a composite type?

texts:
In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one?

Suppose I have

CREATE TABLE main (
    -- a and b are separate fields
    a INTEGER,
    b INTEGER,
    PRIMARY KEY (a,b)
);
CREATE TYPE abtype AS (
    a INTEGER,
    b INTEGER
);
CREATE TABLE sub (
    -- a and b are components of a composite type
    ab abtype,
    PRIMARY KEY (ab)
);

How do I set up a foreign key constraint specifying that sub.ab references main.(a,b)?

The following attempts are all syntactically wrong.

ALTER TABLE sub ADD CONSTRAINT sub_ab_fkey FOREIGN KEY
... (ab) REFERENCES main(a,b);
... (ab) REFERENCES main((a,b));
... (ab) REFERENCES main((a,b)::abtype);
... (ab) REFERENCES main(a,b)::abtype;
... ((ab).a,(ab).b) REFERENCES main(a,b);
... (ab.a,ab.b) REFERENCES main(a,b);


texts: Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

texts:
Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table between "ProductCategory" and "Sales".

If there was a nonclustered index on pr.ID and pr.CategoryID, the DBMS would do an Index-only-check, which has a very good performance. But, if I am right, a clustered index actually IS the entire table, ordered by the index columns. So, even if the clustered index had pr.ID and pr.CategoryID as it's index columns, the database would still have to load the entire table with all the heavy nvarchar(4000/max) stuff, only for two small columns.

-- get total sales amounts for product categories
SELECT pc.ID, pc.Name, SUM(sl.Amount) AS TotalSalesAmount
FROM ProductCategory pc
INNER JOIN Product pr ON pc.ID=pr.CategoryID
INNER JOIN Sales sl ON pr.ID=sl.SoldProductID
GROUP BY pc.ID, pc.Name

with Product being a heavy table like this:

CREATE TABLE Product
(
    ID int not null PRIMARY KEY, -- clustered index 1st column
    CategoryID int FOREIGN KEY REFERENCES ProductCategory(ID), -- clustered idx 2nd.
    Name                 nvarchar(200)
    RecommendedPrice     decimal(11,2),
    Creator              nvarchar(200),
    SafetyReport         nvarchar(4000),
    FutureDevelopmentsProposal nvarchar(4000),
    ExpectedSalesSurvey  nvarchar(4000),
    CrystalBallVision    nvarchar(4000), -- nonsense to represent a bloated table
    TarotCardsResult     nvarchar(4000),
    Horoscope            nvarchar(4000),
    FortuneTellerReport  nvarchar(max)
)

One remarkable thing I found out with a similar query on SQL Server 2008 R2:
The query plan contained an index scan on a completely unrelated, nonclustered index, like one on the pr.RecommendedPrice column only.

My idea is that the unrelated, nonclustered index contains references to the clustered index rows (pr.ID, pr.CategoryID), and it's cheaper to get these from a nonclustered index scan, rather than from the actual clustered index.

Am I right in my assumptions?


texts: How to check if a string contains a sequence of binary regexp chars in postgres

texts:
I am trying to filter all non-UTF characters in a field of a table in postgres. It looks to me that I need to filter anything that is not:

 $$[\09\0A\0D\x20-\x7E]|$$||               -- ASCII
 $$[\xC2-\xDF][\x80-\xBF]|$$||             -- non-overlong 2-byte
  $$\xE0[\xA0-\xBF][\x80-\xBF]|$$||        -- excluding overlongs
 $$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$||  -- straight 3-byte
  $$\xED[\x80-\x9F][\x80-\xBF]|$$||        -- excluding surrogates
  $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$||     -- planes 1-3
 $$[\xF1-\xF3][\x80-\xBF]{3}|$$||          -- planes 4-15
  $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$||      -- plane 16

Now how can I compare if a string has satisfies regular expression [\xC2-\xDF][\x80-\xBF]?

select 'ó' ~ '[\xc2-\xdf][\x80-\xbf]'
select '\xc3\xb3' ~ '[\xc2-\xdf][\x80-\xbf]'

return false.


texts: How to find dependencies on a table in mysql 5.0?

texts:
I am trying to drop a table in MySQL but its complaining about "Cannot delete or update a parent row: a foreign key constraint fails"...

Questions

Is there a way to find out all the table dependencies and objects dependent on it?
For Sybase, DBArtisan gives a really easy way to find dependecies. Is there any such tool for MySQL?


texts: Reorder data in one column based on data in another column

texts:
I have the following table structure:

create table abc ( Name number , No number )

The data is like this:

Name | No
-----+-----
 100 |  11
 101 |  20
 102 |  12
 103 |  16
 104 |  13
 105 |  14
 106 |  15

I need to retain the exact values that are in the No column; I just need to modify the records the current values are associated with, so that the seq values are in the same order as the Name values.

Name | No
-----+-----
 100 |  11
 101 |  12
 102 |  13
 103 |  14
 104 |  15
 105 |  16
 106 |  20


texts: Count movies with specific score, per actor

texts:
I have movies and actors tables. Movies have a rating from 0 to 10. Each actor belongs to a group (table groups). Movies and actors are linked through movies_actors table.

For analytics purposes, I need the number of movies with rating between 0 and 1 (the upper bound excluded, named count0), between 1 and 2 (count1), ..., for every actor in a specific group.

Example of expected result:

actor_id | count0 | count1 | count2 | count3 | count4 | count5 | count6 | count7 | count8 | count9 |
---------|--------|--------|--------|--------|--------|--------|--------|--------|--------|--------|
       1 |      0 |      1 |      0 |      0 |      1 |      0 |      1 |      0 |      0 |      0 |
       3 |      1 |      0 |      0 |      0 |      1 |      0 |      0 |      0 |      0 |      0 |

sqlfiddle here

As you can see in the sqlfiddle, I achieved to get count0, count1, ... for a specific actor whose id is hardcoded into the query.

How do I get this information for every actor in a specific group?

Note: I use Postgres 9.4.1 with Ruby on Rails 5.


texts:
Please help me out with a problem I am facing.

I have a table T. I altered the table to alter the column C from varchar (10) to varchar (100).

Now, there is a view V that is schema bound and selects columns from T. So before altering T, I had to drop the view V.

Steps I followed: 1. Drop view V 2. Alter T alter C varchar (100) 3. (re)create V

Error I am facing: Replication-Replication Distribution Subsystem: agent XXX failed. The object 'V' is dependent on column 'C'. I performed the changes in Preprod environment. Table T is in replication.

I want to know: 1. How to fix this error 2. what did I do wrong?


texts: What is the correct result for this query?

texts:
I came across this puzzle in the comments here

CREATE TABLE r (b INT);

SELECT 1 FROM r HAVING 1=1;

SQL Server and PostgreSQL return 1 row.

MySQL and Oracle return zero rows.

Which is correct? Or are both equally valid?


texts: PostgreSQL - Execution plan - Actual time meaning

texts:
I have a question regarding the meaning of actual time in an execution plan.

I read the following things but they seem to be contradictory:

The first value is the time to retrieve the first line and the seconds value is the time to retrieve the last line.
When I want to know which operation takes a long time, I take the second value of actual time of the outer operation and the second value of actual time of the inner operation.

Example:

EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
-> Sort(cost=172682.84..175182.84 rows=1000000 width=37)(actual time=612.793..729.135 rows=1000000 loops=1)
   Sort Key: c1
   Sort Method: external sort  Disk: 45952kB
   -> Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37)(actual time=0.017..111.068 rows=1000000 loops=1)
Total runtime: 772.685 ms

According to the point 1, the sort operations takes (729 - 612) 117ms.

According to the point 2, the sort operation takes 618 ms.

Can anybody explain to me what I'm missing here?


texts: Sql Service broker BEGIN DIALOG CONVERSATION and SEND. Should they be in a transaction?

texts:
I regularly see code that looks like this:

BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @u....
SEND ...
COMMIT;

BOL for BEGIN DIALOG doesn't use the transaction handling in the examples and it doesn't mention transactions at all.

Are transactions needed?  If so why?


texts: SSRS 2014 Report - looking up single row from dataset

texts:
I have a table that I'm trying to report out on in SSRS-2014.

A cut down version of the table:-

TABLE_NAME  FEE_NAME    FEE_VALUE
PDE         1st year    100.00
PDE         2nd year    120.00
PDE         3rd year    130.00
PDE         4th year    140.00
PDE         5th Year    150.00
PGB         1st year    500.00
PGB         2nd year    520.00
PGB         3rd year    530.00
PGB         4th year    440.00
PGB         5th Year    850.00
PUS         1st year    100.00
PUS         2nd year    120.00
PUS         3rd year    130.00
PUS         4th year    140.00
PUS         5th Year    150.00
PCA         1st year    100.00
PCA         2nd year    120.00
PCA         3rd year    130.00
PCA         4th year    140.00
PCA         5th Year    150.00
PJP         8th year    600.00
PJP         10th year   820.00
PJP         11th year   40.00
PJP         12h year    140.00
PJP         14th Year   750.00

How can I display the data so that it's :-

Table Name  1st year    2nd year    3rd year    4th year    5th year    6th year    7th year    8th year    9th year    10th year   11th year   12 year 13th year   14th year
PDE         100.00      120.00      130.00      140.00      150.00
PGB         500.00      520.00      530.00      440.00      850.00
PJP                                                                                600.00                    820.00        40.00   140.00              750.00

And so on.

The year columns will range from 1 to 20 and there wont always be a value for every year.

I've tried using :-

=Lookup(ReportItems!Textbox3.Value, Fields!FEE_NAME.Value, Fields!FEE_DIS1.Value, "Fees")

to use the year in the header row of the SSRS table to lookup the FEE_NAME from the dataset, but I get a row for every entry in the dataset but each row shows the same (correct) value.

I've tried using CASE in the SQL :-

CASE WHEN FEES.FEE_NAME = '3rd year' THEN FEE_VALUE END AS [3rd year],

Again, I get the value I want and then empty rows for the rest of the TABLE_NAME rows.

I also looked at Pivot (SQL) and a Matrix (SSRS) table but I just want to return the corresponding fee value and display it in the relevant year column whereas from what I've read so far Pivot and SSRS need an aggregate.

Hopefully this makes sense.

Cheers.


texts: T-SQL Update Random String Value in Column

texts:
I have a table that contains a file path column containing data like this.

C:\ABC\Files\AA.dat
C:\ABC\Files\AA.Unbound.qp
C:\DEF\AA\GGG Build 1 Modules\Random123.qpp
C:\DEF\AA\GGG Build 1 Modules\Random456.qpp
C:\GH\DC.Random789.qpp
C:\IJK\Random987.qpp

I need to replace the file path to a generic path before the actual file name, so everything before the final \ needs to change but keep the same file names, something like this

D:\FILES\AA.dat
D:\FILES\AA.Unbound.qpp
D:\FILES\Random123.qpp
D:\FILES\Random456.qpp
D:\FILES\DC.Random789.qpp
D:\FILES\Random987.qpp

Table has around 2000 rows and if possible I want to update in a single script. Running SQL 2008R2 and this is first stage of finally upgrading so any help would be appreciated.


texts: How do I measure performance of a single sql execution in oracle

texts:
I need to measure the duration of individual sql executions in Oracle DB.

I have the following query but it has two major problems.

SELECT
   instance_name,   host_name,
sql_id, sql_fulltext, plan_hash_value,
    CASE
        WHEN executions > 0
        THEN ROUND(elapsed_time/executions,3)
        ELSE NULL
    END elap_per_exec,
    elapsed_time,
    executions,
    PHYSICAL_READ_BYTES/1024 read_kb,
    buffer_gets,
    rows_processed
FROM v$sqlarea,user_users
CROSS JOIN (SELECT instance_name,host_name FROM v$instance)
WHERE v$sqlarea.PARSING_USER_ID = user_users.user_id AND executions > 5

It gives a sense of performance but this is the total duration for all executions divided by the number of executions so spikes in duration won't be noticeable
The duration is total of all CPUs that spent time on the task including paralleled tasks. A query that took 1 second may have 48 cores so it appears as 48 seconds.

Is the any way to get the performance for a single execution?


texts: Is there a way to limit the number of foreign key references for a single table index

texts:
I'm having difficulty finding an answer because I'm having difficulty expressing the question. So I apologize if their is a duplicate (or similar) question on here.

Is there a way to limit the number of references to a specific foreign key in MySQL?

Explained via Metaphor:

Imagine it as a study group with a 'leader'. You have a 'leaders' table, and you have a 'members' table.

Is there a way to prevent each 'leader' from having more than 'x number' of 'members' assigned to them via foreign key?

Or is this something I would have to do when creating the 'members' in the first place? ie. some way to check how many references already exist and change the foreign key if the ID is > 'x number'

Thank you for any help or direction you can give!


texts: Change Linux Family for Postgres Database

texts:
I have Postgres 9.5 running on "old" Debian server and about to move it on "new" CentOS, so here is the question.
Is it OK to just install binaries of the same 9.5 version and just copy database cluster (PGDATA) to a new server?


texts: PostgreSQL 9.5: Permission to deny functions body

texts:
I create new role without any grants.

New user can't select on tables, but can see functions body (pl/PgSQL)

I want permissions for new role:

allow INSERT into one table.
deny see tables, sequences, functions and function's body.

Is there any way to configure it?


texts:
I've been through several questions and youtube tutorials, i understand that 2NF is removing the partial dependencies and 3NF is the transitive ones, but i can't get my head around how the following example should look like in 2NF.

studentID | studentName | courseCode | courseTitle | modCode | modTitle | credits | resultCode

My attempt is the following for 2NF:

Student

studentID | studentName | courseCode | modCode | resultCode

Course

courseCode | courseTitle

Module

modCode | modTitle | credits

Is this correct? if not where am i going wrong and why.

The following is the 3NF:

Student

studentID | studentName | courseCode

Course

courseCode | courseTitle

Module

modCode | modTitle | credits | courseCode

Results

studentID | modCode | resultCode

Same goes for this, is this correct - if not where and why?

Any help would be great as i've been going over this topic for days now and still not quite sure! Thank you.


texts: Does this mean the 'public' has full select, insert, update, and delete access?

texts:
I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View.  But no matter what I try when I login as this new user I can select from any table.  I ran this query:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
WHERE princ.name = 'public'
AND object_name(perm.major_id) = 'User_Table'

And get these results:

name    type_desc       permission_name     state_desc  class_desc          (No column name)
public  DATABASE_ROLE   DELETE              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   INSERT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   REFERENCES          GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   SELECT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   UPDATE              GRANT       OBJECT_OR_COLUMN    User_Table

Does this mean no matter what anyone that logs in into the database will have full access based on the 'public' permissions?


texts: Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

texts:
Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked out using this TSQL

select * from sys.dm_os_schedulers;

The schedular is not showing DAC (Dedicated administrator connections) scheduler ID.

Any idea or suggestion will be appreciate.


texts: School marks database with "mark categories"

texts:
I'm trying to build a database to manage my school marks. Here in Germany we have different "categories". For example we have one category for all our written work and one for our tests. I want to have a defined number of "categories", each of them has its own list of marks. How do I have to design my database? I have tried the following:

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL,
    [Average] decimal(18,3) NOT NULL,
    [TestsId] INT NOT NULL,
    [WrittenId] INT NOT NULL,
)

I come from C#, so I might not think like a SQL-Developer.


texts: Should we centralize databases or have a database with each application?

texts:
My company develops different web applications for different clients.
For each new client (or important project), we use a new server with a new MySQL database.

This way, an application can access its database only and the database is directly linked with the project (as it is in the same server so basically in the same directories). Until now it has not shown any particular problem.

As we are changing our methods, my colleagues wonder if we could use instead only one database with all projects in it and use some master/slave replication.

Our different databases have some tables in common, but with different data as they belong to their respective apps.

I did some research on the subject :
I find mostly posts that recommend using a database for each application for these reasons :

Distinct log files and backups for each project
Better scalability as we can inspect the needs for each database
Simple to use from application without having to change data or create views, etc.

Most of what I read about this is between 5 and 10 years old so I'm not sure if the disadvantages of the other solution are still relevant.

I struggle to find precise information and advantages about the unique-database solution and the use of Master/Slave replication for this kind of cases.

I'm new to this idea : I understand the concept of master/slave here but I don't have technical knowledge about it.

(Could using a unique database be beneficial ?)

Should we have :

The database of an app on the same server than the app,
A single server for all databases, and keep the apps in other hosts ?

Note that we are using MySQL but PostgreSQL is a possible option if we chose to use a single database with different schemas for each app.


texts: LIKE Operator not returning desired result after using LOWER

texts:
In Oracle SQL Developer, I have the following two queries :

Sample Data for Reference from Employee Table

COMPANY_ID  NAME                                EMP_ID
1           Steven                                  4
2           Steven steven is working                4
3           That work was done by steven            4
4           Bradley is a good guy                   4
5           His name is yoshio                      4

Query #1:

SELECT COMPANY_ID,NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%Steven%'

The above query returns the results like the following which is perfectly fine:

COMPANY_ID |  NAME                      | EMP_ID

1             Steven                        4
2             Steven steven is working      4

Query #2:

The LOWER(NAME) as NAME converted all the upper case letters of the NAME column to lowercase and when I used the following
query, I didn't not receive three rows which contains the word steven but only one as shown below:

SELECT COMPANY_ID,LOWER(NAME) as NAME,EMP_ID FROM employee WHERE emp_id = 4
AND NAME LIKE '%steven%'

Here is the output of the above query in Oracle SQL Developer:

COMPANY_ID |  NAME                      | EMP_ID
2             steven steven is working      4

So, basically the pattern I am noticing after running the Query #2 is that if the searchable word(in our case it is steven) is
surrounded by something on its left and right, that row is returned otherwise no other row with the searchable word is returned.
For example, from the sample data above, rows with company_id 1 and 3 containing the word steven wasn't returned.
Could anyone tell me why it's happening like this?


texts:
This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake.

Here is my question:
When dropping Constraints, are their indexes dropped as well?

Basically, I'm asking because I want to modify an existing constraint. I want to change the foreign key to another column.

Here was the original query to create them:

-- Creating foreign key on [Industry_Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseIndustry]
    FOREIGN KEY ([Industry_Id])
    REFERENCES [dbo].[Industries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseIndustry'
CREATE INDEX [IX_FK_CourseIndustry]
ON [dbo].[Courses]
    ([Industry_Id]);
GO

Here is what I'm thinking of doing:

IF OBJECT_ID(N'[dbo].[FK_CourseIndustry]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_CourseIndustry];
GO

-- Creating foreign key on [IndustryId] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseIndustry]
    FOREIGN KEY ([IndustryId])
    REFERENCES [dbo].[Industries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseIndustry'
CREATE INDEX [IX_FK_CourseIndustry]
ON [dbo].[Courses]
    ([IndustryId]);
GO

Is that enough and will it work? Honestly, I don't mind if it's not an elegant solution just so long as it works.

Any piece of advise or information would be highly appreciated. Thanks!


texts: Extracting dates from PostgreSQL database

texts:
The db is PostgreSQL 9.6.

I'm creating an app that has events, and the user chooses a date / time that he wants to attend.

So, I created the following structure:

CREATE TABLE public.intervals (
    id            int4 PRIMARY KEY,
    event_id      int4 NULL,
    date_start    date,
    date_end      date,
    configuration jsonb DEFAULT '{}',
)

I've chosen to put data into a jsonb column instead of creating a new table ( configuration ). There, I put an array of objects, like this:

[ { "id": "f53382aa-86b6-e442-8de2-f8c457e5ef11",
    "capacity": 2,
    "duration": 30,
    "end_hour": "12:00",
    "start_hour": "08:00",
    "days_of_week": [ 4, 5 ] },
  { "id": "3d68b5de-7594-fdb9-2848-1190688dd532",
    "capacity": 2,
    "duration": 10,
    "end_hour": "18:00",
    "start_hour": "14:00",
    "days_of_week": [ 1, 2, 3 ] } ]

Now, I want to extract the dates from this data, and I think I'm getting there, but it's getting too hard.

Am I making a mistake using jsonb to store the configuration?

Here's a fiddle of the table and some example data, in case someone wants to take a look: http://sqlfiddle.com/#!17/99d30/3/1

Thanks!

Edit:

I think I got it:

http://sqlfiddle.com/#!17/99d30/44/0

-- select * from intervals;

with days as(
    select
        generate_series(
            date_start,
            date_end,
            '1 day'
        ) as day,
        i.configuration
    from
        intervals i
),
lines as(
    select
        day,
        jsonb_array_elements(configuration) as config,
        ((jsonb_array_elements(configuration)->>'days_of_week')::jsonb) @> ('"' || extract(dow from day)::text || '"')::jsonb as dow_enabled
    from
        days
)
  select
    generate_series(
        (
            day::date::text || ' ' ||(
                config ->> 'start_hour'
            )::text
        )::timestamp,
        (
            day::date::text || ' ' ||(
                config ->> 'end_hour'
            )::text
        )::timestamp,
        (
            l.config ->> 'duration' || ' minutes'
        )::interval
    ) as event_date, l.config ->> 'capacity' as capacity
from
    lines l
    where dow_enabled = true

It works and it looks fast, but I'm open to suggestions.


texts: Receiving connection timeout error when call PQconnectdbParams to connect to Postgres

texts:
What are the allowed format for sending "connection string" to the PQconnectdbParams method to connect to Postgres DB?

I am trying the following, but it's not working and receiving timeout errors always. I am not sure what I was doing is wrong.

provider_dsn := 'host=xxx.xxx.us-west-2.rds.amazonaws.com port=5432 dbname=dbname password=my_password user=user_name'

Error message receiving is as follows

ERROR:  could not connect to the postgresql server: timeout expired

I can provide further details if required.

Note: I can connect the DB using psql command from the terminal but can't when I use PQconnectdbParams with same credentials.


texts: Mongodb TTL Index not expiring documents from collection

texts:
I have TTL index in collection fct_in_ussd as following
db.fct_in_ussd.createIndex(
    {"xdr_date":1},
    { "background": true, "expireAfterSeconds": 259200}
)

{
    "v" : 2,
    "key" : {
        "xdr_date" : 1
    },
    "name" : "xdr_date_1",
    "ns" : "appdb.fct_in_ussd",
    "background" : true,
    "expireAfterSeconds" : 259200
}

with expiry of 3 days. Sample document in collection is as following
{
    "_id" : ObjectId("5f4808c9b32ewa2f8escb16b"),
    "edr_seq_num" : "2043019_10405",
    "served_imsi" : "",
    "ussd_action_code" : "1",
    "event_start_time" : ISODate("2020-08-27T19:06:51Z"),
    "event_start_time_slot_key" : ISODate("2020-08-27T18:30:00Z"),
    "basic_service_key" : "TopSim",
    "rate_event_type" : "",
    "event_type_key" : "22",
    "event_dir_key" : "-99",
    "srv_type_key" : "2",
    "population_time" : ISODate("2020-08-27T19:26:00Z"),
    "xdr_date" : ISODate("2020-08-27T19:06:51Z"),
    "event_date" : "20200827"
}

Problem Statement :- Documents are not getting removed from collection. Collection still contains 15 days old documents.
MongoDB server version: 4.2.3
Block compression strategy is zstd
storage.wiredTiger.collectionConfig.blockCompressor: zstd
Column xdr_date is also part of another compound index.
Observations as on Sep 24
I have 5 collections with TTL index.
It turns out that data is getting removed from one of the collection and rest of the collections remains unaffected.
Daily insertion rate is ~500M records (including 5 collections).
This observation left me confused.
TTL expiration thread run on single. Is it too much data for TTL to expire ?


texts: I've been told that MongoDb does not, but do any NoSQL DBs support this functionality?


texts: Getting ratio of reads to writes on SQL Server

texts:
Sorry I am not a DBA so bear with me.  I wanted to get a better understanding of the ratio of reads to writes in our archiecture.  The best to get this is at the database. We are using SQL Server 2008.  Is there anyway to get this without impacting performance and to get smart summaries?  For example, the daily read / write ratio?

Many thanks.


texts:
After concatenating column how do i order by using one of the columns? Below is select statement i was trying to come up with.

SELECT * FROM (with tbl_demo AS
    (SELECT eod_date,
    branch_code branch_code
    , eoc_batch
    ,eoc_stage
    , start_time
   ,  end_time
     from aetb_eoc_programs_history where eoc_ref_no in
     ( select eoc_ref_no from aetb_eoc_runchart_history where eod_date = (select prev_working_day from sttm_dates where branch_code = '000'))
order by eod_date)
SELECT a.bank_code||'|'||param_val||'|'|| eod_date||'|'||
branch_code ||'|'||
       eoc_batch||'|'||
        eoc_stage ||'|'||
      to_char(start_time,'dd-mm-yyyy hh24:mi:ss')
     ||'|'|| to_char(end_time,'dd-mm-yyyy hh24:mi:ss')
        ||'|'|| numtodsinterval((end_time - start_time),'day')
from tbl_demo , sttm_bank  a,cstb_param  b
where end_time IS NOT NULL
and b.param_name = 'RELEASE'
order by  numtodsinterval((end_time - start_time),'day') desc)
where rownum <=10;


texts: Fetch only those record which do not have matching amounts

texts:
I have a mongo collection as shown below

{
    "_id" : ObjectId("5c5c86633b897535539c1b36"),
    "studentId" : "dsdlkjafhiuelfdldahfiu",
    "marks" : [
        "subject" : "Physics",
        "sectionMap" : {
            "section_1" : {
                "scale" : 0,
                "unscaled" : NumberLong(1)
            },
            "section_2" : {
                "scale" : 0,
                "unscaled" : NumberLong(10)
            }
        },
        "detailMap" : {
            "Mechanics" : {
                "scale" : 0,
                "unscaled" : NumberLong(3)
            },
            "Optics" : {
                "scale" : 0,
                "unscaled" : NumberLong(3)
            },
            "Fluid Mechanics" : {
                "scale" : 0,
                "unscaled" : NumberLong(5)
            }
        },
        ...
    ]
}

From the above collection, I want to fetch all the records for which the sum of sectionMap and sum of detailMap is not the same. The number of entries in sectionMap and detailMap can change.

From all the examples that I looked up online all of them had fixed attribute names. Is there a way to group sectionMap and detailMap and compare them in the same query.


texts:
Can somebody help explain the "data_length" of the table? Why is it so big in my example below of a table with just one column and one row?

mysql> create table `network_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into network_type(id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_length FROM information_schema.TABLES  WHERE table_schema = "test";

+-------------+
| data_length |
+-------------+
|       16384 |
+-------------+
1 row in set (0.01 sec)


texts: How to retrieve closest value based on look-up table?

texts:
I'm attempting to create a query that will find the closest value from one table and return its ID into the resulting table.

Below is an example that should describe the situation better.

Sample Data

These two tables will exist in the SQL database.

Main Table

+----+-------------+
| ID | Measurement |
+----+-------------+
|  1 | 0.24        |
|  2 | 0.5         |
|  3 | 0.14        |
|  4 | 0.68        |
+----+-------------+

Look-up Table

+----+---------------+
| ID | Nominal Value |
+----+---------------+
|  1 | 0.1           |
|  2 | 0.2           |
|  3 | 0.3           |
|  4 | 0.4           |
|  5 | 0.5           |
|  6 | 0.6           |
|  7 | 0.7           |
|  8 | 0.8           |
|  9 | 0.9           |
+----+---------------+

Goal

This will be the result of a query. Measurements should not be on the border (0.25 for example).

+----+-------------+-----------+
| ID | Measurement | Lookup ID |
+----+-------------+-----------+
|  1 | 0.24        |         2 |
|  2 | 0.5         |         5 |
|  3 | 0.14        |         1 |
|  4 | 0.68        |         7 |
+----+-------------+-----------+

Is there a query that would be able to return this kind of result?


texts: Why is "Lookup" colored as a function reserved word in SQL Server?

texts:
In SSMS 2208, the identifier "Lookup" is colored hot pink as if it were a function (same color as, say, "Power" or "Convert"). Why?

I cannot find it in the official list of reserved words. Searches on the web seem useless as there are an awful lot of "lookup" terms out there that have nothing to do with my question.


texts: Are indexes bloated if using nvarchar field sizes that are much bigger than needed?

texts:
I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is nowhere near that size.

If I create an index on one of those fields, does the index reserve the whole field size or just use the number of bytes used by the actual value stored in that field?


texts: Is it a good choice to choose NoSQL for a finance management app?

texts:
I am working on a personal finance management application, which will also analyse the user's financial statement and give a good overview about their expenses and savings.

What I am having trouble is choosing between SQL and NoSQL. As per the data it looks very straight forward when using relational database managing the transactions and the user data. When using non relational approach de-normalizing the data might be a problem because there are a lot of referencing to transactions, category, modes and more.

What would be a good approach to this?

*Note: We are planning to use MEAN Stack for the app


texts: MS SQL - Create trigger to prevent insert if it doesn't match specific parameters

texts:
Suppose I have two tables

payments with columns payment_id, batch_id
and batches with columns batch_id, batch_type

Can I add a trigger to prevent inserts into the payments table when the batch_id being inserted is not a specific batch_type?

In short, for the inputted batch_id I want the trigger to check in the batches table to see if that batch_id has batch_type P.

Something similar was discussed here but for MySQL (I am looking for a MS SQL trigger ) :- How to make a trigger that will compare input with a value of other table?.

Thanks for your help!


texts: Does SQL Server use multiversion concurrency control (MVCC)?

texts:
Reading an older overview of CouchDB published on the IBM site HERE, I was surprised to find the following:

  Most modern databases have started to move from locking mechanisms to
  MVCC, including Oracle (since V7), MySQL (when used with InnoDB) and
  Microsoft® SQL Server 2005 and later.

I am using SQL Server quite a bit at the moment (V2012) and never considered that there may be any form of MVCC. I have also used with (tablock, holdlock) in stored procedures before.

Does SQL Server really implement MVCC anywhere and how does that reconcile with the idea of with (tablock, holdlock) if it does?


texts:
I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure and schema intact.

I tried:

select *
into [dbo].[new_Non_partitioned_Table]
from [dbo].[partitioned_table]

I then dropped the partitioned table and renamed the new table. However, it caused me to lose the data structure and some dependency (FK) that forced me to drop constraints. This is for production and loss of data is not an option.

Is there a better way to remove a partition from an existing table?


texts:
I have a database with different data I need to make a query to select a group with summed quantities.

Two tables: Positions and Documents.

There can be many positions in one document both are connected by unique_id.

The Documents have two different statuses: In and Out

I need to see the result of these two status on one row with a column for each case, but instead my query gives me two rows. How do I do it?

My query doesn't give me this result:

Select   documents.code
        ,positions.fullname
        ,case when documents.status='In' then Sum(positions.quantity) else 0 end as DocIn
        ,case when documents.status='Out' then Sum(positions.quantity) else 0 end as DocOut
from positions
    inner join documents
        on documents.unique_id=positions.unique_id
group by documents.code, positions.fullname, documents.status


texts: How can I change the location for backup - Oracle 11g

texts:
I have my database in disk C: , and also the default folder for backup is in disk C. I need to change location for database backup from C: to D:.
How to do that?!