texts: SQL Server 2019 Core - Always on Availability Group - Availability replica is disconnected

texts:
all.
(I have read through all the other topics related to this one, and I cannot find anything to is similar to this issue, unless there is one hidden that I have missed.)

OK.

  SQL Server Always On High Availability

When going through the Wizard, it completes successfully, all the databases in the joined nodes are all set as Name (Restoring...)

Here is a picture of what the SSMS tree looks like for the Primary and Secondary Servers Group listings.
[

When connecting to the Windows Core through [b]Computer Mansagement[/b]
To access the SQL Server Configuration Manager (SQL 2019).
SQL Server Services is BLANK.

As you can see, this is like this when connecting to ALL Windows Core with SQL Server instances installed.
So, I cannot access this part of SQL Server Services, to administer it on any on the VM's

Wanted to also show the DASHBOARD for SQLCore-01
Which, as you can see, shows all the other SQLCore-02 - SQLCore-05 as bad.

I have checked all the ports that are set for SQL Server and the group, and they are there are reachable.
I am at a complete loss. I have tried and searched and tried some more, but cannot find anything to resolve this issue. I need this working by the end of the weekend, or I am going to have to go back to using the SQL Cluster again, which is what I want to stay away from. I like this method better, as it had multiple SQL Servers with databases on each server—more failover than the SQL Cluster.

Ports (5022) are open on all machines
Error in SQL Log is:

  Database Mirroring login attempt by user "Domain\sqlengine" failed with error:
  Connection handshake failed. The login domain\sqlengine does not have
  CONNECT permission on the endpoint. State 84.'.

The user domain\sqlengine
I ran this command on each instance

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [domain\sqlengine]

The output is: Commands completed successfully.
But it still shows that same error in the logs.

I read this blog post SQL Authority .com
However, I cannot access through Computer Management, the
SQL Server Services for SQL 2019, for whatever reason.
See Image.

Another note.
Going by this article here. Always On Replica Disconnected After Changing SQL Server Service Account

The user is domain.local\sqlengine
This user I added to the SQL Server Logins for each SQL Server.
This is the account that is used for logging all actions of which AG will be doing but still have the same error message.

I found this page.   Availability replica is in disconnected
I used the script provided on the article and ran
I then restarted SQL Server on each Instance.

Restart-Service -Force 'MSSQL$SQL2019'

But to no avail. Still disconnected.

I also found this as well.
How to change SQL Server Service Account using PS
I tried running the script.
But I get an error.

  You cannot call a method on a null-valued expression.
  At D:\Files\SQL.ps1:23 char:1
  $ChangeService.SetServiceAccount($Uname, $PWord)
  Which is point at this section.

$UName="domain\sqlengine"
$PWord="**********"
$ChangeService.SetServiceAccount($UName, $PWord)

I was going to try and change the password, as maybe the password might have gotten corrupted or something. But, with this error, I am at a standstill.

Wayne


texts:
I have a table with a JSON column in Postgres, I am grouping the rows and I am looking for some aggregate function that could "sum up" values from jsons.

sum aggregator sums the integers, I am looking for equivalent aggregator for JSONs, for example these 2 JSONs:

 {a: 1, b: 4}
 {b: 7, c: 3}

should be aggregated to

{a: 1, b: 11, c: 3}

Note that the values in conflicting keys are summed. JSONs are always 'str->int'

Is it possible to that in Postgres? Alternatively is it possible to write a custom aggregator to do that in C?


texts: How to get host server CPU info from SSMS

texts:
Help please!
I need to get number of CPU's and cores per cpu for each host server running SQL Server. Querying master.sys.dm_os_sys_info is not producing the same result as what is seen when checked on the host server.

kindly point me in the direction of a code I can use please.

What is the difference between physical_cpu and logical_cpu and which one do I need? I'm trying to calculate total cores on each machine for licensing purposes.

Can i take the number of cores returned by the query pasted below as what I need to calculate my license requirement?

In performance monitor on the server, I have sockets =2, Cores = 16, logical processors = 32. Is 16 cores what I need for my SQL 2012 and 2016 licence calculations?

Here the query that  I'm using right now:
USE master;

SET NOCOUNT ON

SELECT 'physical_cpu' = cpu_count / hyperthread_ratio
    ,'cores' = CASE
        WHEN hyperthread_ratio = cpu_count
            THEN cpu_count
        ELSE (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
        END
    ,'logical_cpu' = CASE
        WHEN hyperthread_ratio = cpu_count
            THEN cpu_count
        ELSE ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
        END
FROM master.sys.dm_os_sys_info

Anything better than that?


texts: Anonymous subscribers does not get expired in merge replication

texts:
I have a Merge replication with anonymous subscribers, in my sysmergesubscriptions there are old subscriptions that still has the status 1 (active) and the last_sync_date is way below the retentionperiod, in fact all of the subscriptions in the table has the status of active!?

The job "Expired subscription clean up" runs every night without errors but does not seem to set them to inactive. I have tried to follow the stored procedures that it uses to understand but I do not really follow.

Do anybody know why this is happening or have some input to what i should investigate?


texts: Is there a way to test whether DELETE will fail due to constraints?

texts:
I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.

What are my options for doing this?  Is there a simple way to do a "dry run" of a DELETE?


texts: Is there any way to prevent a PostgreSQL user to change his/her own password?

texts:
I'm administering a PotsgreSQL server and recently I realized that my "nonsuperuser" users have the possibility to change the secure passwords that I assign them for insecure ones.

I searched in the REVOKE docs, but I couldn't find a way to prevent that. It is possible?

Thanks in advance!


texts: Postgres COPY, is it possible to load multiple files into the same table?

texts:
Is it possible to execute several \copy commands in parallel against the same table? I know something similar exists with pg_restore, but that splits the work up amongst tables.

I'm working with files that range from 40-100M rows. Despite dropping indices and creating them later, it still takes a considerable amount of time.


texts: Does the SQL Server backup process use a buffer cache?

texts:
When a full database backup command is executed:

Does the backup process use only the memory that has been assigned
to the SQL Server (instance)?
Does the backup process "flush out" the page from the memory (buffer pool), the dirty buffer?

I want to know if it will impact the performance (regarding all the resources that it takes and its bad impact) by flushing out data.


texts: Scenarios in which you might need to restore large number of databases at once?

texts:
I've seen several online sources mentioning that you should always have a restore script automated for the possible need to restore a large number of databases at once and quickly get back up and running to deliver on your SLA.

However, considering I'm running synchronous mirroring in two availability zones, I'm struggling to find a scenario in which I would need to do mass restores on a large number of databases where scripting a restore automation solution would be necessary.

Can anyone point to me to realistic scenario in which you'd need the capability to do a mass number of restores in a scripted manner?


texts: Converting a 50+ column table into 1 much smaller table

texts:
I'm currently dealing with a legacy PLC device which is spitting out data in an annoying MYSQL table. The table structure is:

PK | FK | FK | FK | Result1 | Result2 | ... | ResultN

As you can imagine, performing any sort of query or join on this is quite cumbersome to write and takes a long time to resolve.

I'd like to setup an automatic select -> insert statement to pull the data and put it into a more appropriate table

PK | FK | FK | FK | Result Number | Result Value

Unfortunately, I'm stuck on how to pull the column names so I know what to populate the Result Number column with, as well as then how to cross-multiply across the table to make sure each new row has all the required keys for each value.


texts:
SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received:

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2017-04-14T21:34:10). Exception Message: Cannot send mails to mail server. (The operation has timed out.).  )

I have tried several ways of setting up Database Mail, a mixture of precreated scripts and following the interface in SSMS.

I have also tried two different mail server credentials (Google and a Web Hosting Company), and both fail with the same error.

The Web Hosting Credentials are used on a PHP Script on another Server which works perfectly fine.

What should I check to get this working?


texts:
I want to change password to a more complex one. I have access as SA to the MS SQL database and it's no problem to change the password.
The problem is: I have a odbc.ini for a Windows programm to use.
There the password is stored J@JCFIJADEGD$-"YKySiAQb (it's missing one string; here is the screenshot)
The clear password stored in the MS SQL is: niwaz.

How can I create a new password with a new string to connect?


texts: SSISDB AlwaysOn Support in 2016 - SSIS Failover Monitor Job fails on non-readable secondaries

texts:
Setting up SSISDB with the new AlwaysOn support in SQL 2016 Enterprise. This creates two jobs, one of which is the SSIS Failover Monitor Job. The secondary is not readable. The code for this job is:

DECLARE @role int
DECLARE @status tinyint
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')
IF @role = 1
BEGIN
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N'SQL2', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]
END

Every time this job runs, I get the below error due to SSISDB not being online on this secondary instance. This is because SQL Server must parse all parts of the query, regardless if conditional code paths are currently valid or not. Since SSISDB is offline, it cannot be parsed.

  Executed as user: ##MS_SSISServerCleanupJobLogin##. The target database, 'SSISDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. [SQLSTATE 42000] (Error 976).  NOTE: The step was retried the requested number of times (3) without succeeding.  The step failed.

This code is generated by SQL Server, and since it is such a simple setup, I have a hard time believing it is a bug. But I don't know 1) what else it could be and 2) how one would work around it and provide the designed functionality without the secondary being readable.


texts:
I am having many database schema in mysql 5.6 server, now the problem over here is that I want to catch the queries for one schema only.

I can not enable query log for entire server as one of my schema is highly loaded and it will impact the server.

Is their any way, any tool through which I could only log the queries by single schema.

I found benchmarking graph which shows the impact on transactions/second when query log is enabled.


texts: Primary key with randomly varying increments (so it cannot be guessed easily)

texts:
I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below:
- 20 (random number between 1 and 100)
- 30 (add random number 10 that's between 1 and 100)
- 113 (add random number 83 that's between 1 and 100)
- 118 (add random number 5 that's between 1 and 100)
- 217 (add random number 99 that's between 1 and 100)
- 220 (add random number 3 that's between 1 and 100)

The data domains are often exposed via HTTP REST endpoints, and I would like the end users to not able to guess the primary keys by simply incrementing numbers.

I'm trying to avoid UUID/GUIDs if possible. My REST URLs are longer, often with parent-child identifiers. (Yes, it's avoidable but I prefer it for simpler testing/troubleshooting). So I prefer numbers as identifiers.

Is there any simpler solution that I'm not aware of? I'm using PostgreSQL but any generic solution is also fine.


texts: Mysql error : Slave SQL Could not execute ...on table

texts:
Im running a percona cluster of 3 nodes.

Yesterday I needed to throw some more memory in them, so I had to power down all nodes
Now I booted up and then my 3rd Node, Gave me this error :

140701 23:40:51 [ERROR] Slave SQL: Could not execute Update_rows event on table database_prod.cron_schedule; Can't find record in 'cron_schedule', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 206, Error_code: 1032
140701 23:40:51 [Warning] WSREP: RBR event 2 Update_rows apply warning: 120, 69137050

Whats my options regarding fixing this error. And what does it actually mean.


texts: SSIS: Selecting multiple input columns and mapping them to their respective destination in one action

texts:
Can selecting multiple available input columns and mapping them to their respective destination be done in one action!?!?! Instead of selecting 1 input at a time! I've tried holding control and the Shift key to select multiple ones and drag them over, but only the one you click on copies over. This is a huge time waster for tables that have multiple columns and for whatever reason SSIS doesn't automatically map them over, even with the columns names matching!  I'm in SSIS 2008 R2 BTW.
The image below is just an example to illustrate what I'm talking about, it is not my personal example:

(source: ggpht.com)


texts:
I have a Postgresql database in LATIN9 encoding.

I recently migrated my web applications (PHP) from iso_8859_15 (latin9) to UTF8.

So, now, customers are able to specify UTF-8 characters in web forms like comment form or contact form.

I open PG connections in UTF-8 encoding from PHP (PHP function pg_set_client_encoding('UTF8') or so...)

That works well for standard characters like "éàù"... PG is able to convert them from UTF-8 to LATIN9 (the database encoding). But if someone inserts some exotic character like japanese characters "日本語" PostgreSQL throws an error like this:

ERROR:  character with byte sequence 0xe6 0x97 0xa5 in encoding "UTF8" has no equivalent in encoding "LATIN9"

I know that I should migrate my database to utf8 to solve this problem, but for some reasons, I can not do that for the moment.

In my case, I'd rather PostgreSQL saves my string removing characters it can not convert or for example replacing them with some symbol like "?" rather than throwing an error...

The only way I figured to make things work like this is to open my connections in LATIN9 and use utf8_decode() PHP function that works like this (replacing unknown characters with "?" symbol)

Is it possible to do it in a better way? Maybe a PG parameter (but I don't see such parameter in postgresql.conf)

Or anyone have another idea on how to handle this?

Thanks,


texts: Finding what the source of a specific PostgreSQL configuration setting

texts:
If I get it right, settings are hierarchical:

postgres.conf->
postgres.auto.conf (ALTER SYSTEM)->
ALTER DATABASE(where applicable)->
ALTER USER(where applicable)->
SET SESSION(where applicable)->
SET LOCAL(where applicable)

let's say I have enable_seqscan set to off at some point. SHOW or current_setting( or pg_settings will show me the current value only. But to check why I have it off, I have to check whole chain. Eg I suspect somebody set it per user or per database, or in auto.conf - to find how wide the setting is I have to check em all. Otherwise resetting value can fail, eg:

vao=# show enable_seqscan;
 enable_seqscan
----------------
 off
(1 row)

vao=# set enable_seqscan to default;
SET
vao=# show enable_seqscan;
 enable_seqscan
----------------
 off
(1 row)

Because it is so for user vao, or for database vao, or deeper...

What is a short way to find the primary source of current value?.. Or preferably matrix of all sources for the setting. Is there any interface or monkey hack?

update to reflect brilliant Abelisto answer:

source, sourcefile from pg_settings is a wonderful source of information, still I'm asking for a monkey way to check for those reasons: to see the database or user in source I have to relogin - obviously. So before they actually are applied to the session, I can't check them. local and session set shows both SESSION (which kind of reasonable too) and lastly I still have to reset to default step by step all hierarchy. Eg:

vao=# select setting, source, sourcefile from pg_settings where name = 'enable_seqscan';
 setting |  source  | sourcefile
---------+----------+------------
 off     | database |
(1 row)

vao=# select * from pg_file_settings where name = 'enable_seqscan';
                    sourcefile                     | sourceline | seqno |      name      | setting | applied | error
---------------------------------------------------+------------+-------+----------------+---------+---------+-------
 /etc/postgresql/9.6/main/a                        |          1 |     1 | enable_seqscan | off     | f       |
 /var/lib/postgresql/9.6/main/postgresql.auto.conf |          3 |    26 | enable_seqscan | on      | t       |
(2 rows)

So If I don't check every source my changes can be not respected.


texts: MySQL proxy to replication configuration

texts: Is there any solution (say a proxy) that makes MySQL replication cluster behave like one database towards developers? Developers then won't need to worry about using master to write and slaves to read etc.?


texts: SQL server - copying tables, constraints and indexes from one database server to another server

texts: An accidental DBA, We have sql server 2012 on both of our payroll production server and development server. We have to copy few tables and indexes and constraints from the production database to the development database. Please let me know the best method to do so. I appreciate your response.


texts: Why would shrinking TEMPDB log lead to slowness?

texts: I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?


texts: Aborted connection (Got an error reading communication packets) MySQL on Amazon RDS

texts:
I'm getting the following warning in MySQL:

  Aborted connection (Got an error reading communication packets)

I'm using Amazon RDS for MySQL hosting. Both MyISAM and InnoDB storage engines are used (I know it's a bit weird, but it's legacy code). The database is used as storage for PHP application. MySQL version is 5.5.27. PHP connects to MySQL using old mysql extension (http://www.php.net/manual/en/ref.mysql.php)

What I've tried:

Increase max_allowed_packet to 1G (as suggested on MySQL Error Reading Communication Packets)
Tried to change innodb_log_file_size and innodb_log_buffer_size (as suggested on MySQL: Error reading communication packets), but unfortunately Amazon RDS doesn't allow to change innodb_log_file_size, and when I tried to change innodb_log_buffer_size to the maximum possible value (4294967295), database instance didn't boot because of incompatible settings.
PHP application that uses this database inserts data on every request, and I thought it might cause an issue, so I removed INSERT statements that run on every request, still the same result.
It turned out that PHP application creates new MySQL connection for each query it executes. I've changed that so now it uses the same connection for all queries.
Moved my Amazon RDS instance to the same availability zone where EC2 instance with PHP application is located.
Upgraded my database instance from db.m1.small to db.m1.medium.
Changed max_connections setting from 128 to 256.

All these steps didn't help me to solve the problem.

Also, I noticed that this error always happens every 15 or every 30 minutes. From error log:

140528 16:30:00 [Warning] Aborted connection 8709 to db: 'multisite5' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:30:00 [Warning] Aborted connection 8706 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:30:00 [Warning] Aborted connection 8708 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:45:00 [Warning] Aborted connection 11675 to db: 'multisite5' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:45:00 [Warning] Aborted connection 11674 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

I was under impression that the error is triggered by some cron script. I disabled all cron scripts to test the theory, but the error still happens.

Perhaps it's not possible to solve this problem on the server side because of RDS limitations, but I wonder which type of SQL queries can trigger this error? For example I've read that someone might want to increase max_allowed_packet if SQL queries use large BLOB fields, or when you insert many rows in one statement. So the question is - what can I change to make MySQL server happy with current values of innodb_log_file_size and innodb_log_buffer_size? Perhaps it's slow queries, or too many concurrent connections, or something else? Unfortunately moving my database from RDS to EC2 is the last option I would choose, because as I said I'm not experienced in database tuning so I'll have hard time trying to setup backups, replication etc.

Please let me know if I need to provide some additional details, I'm not database expert so I can miss something important.

Thanks in advance!


texts: Consistency in ACID and CAP theorem, are they the same?

texts:
From my understanding, the consistency in ACID is ensuring the data integrity.

However, the consistency in CAP means the data can appear quickly in distributed system.

Does that mean: they are not the same concept?


texts: copy data from Previous non null rows and if first row is null copy next non null value

texts:
I need to copy data from rows with non-null rows and if first row is null copy the next non-null value.
it is not a big table. There is no ID column and date column.
The datatype of this column is varchar.
I am using SQL Server 2012
This is how the data is in the table:

Masked

5574-12XX-XXXX-1338

6674-13XX-XXXX-0855

7784-14XX-XXXX-5237

Null

Null

Null

9984-11XX-XXXX-6196

3384-11XX-XXXX-0615

Null

Null

This is expected result.

Masked

5574-12XX-XXXX-1338

6674-13XX-XXXX-0855

7784-14XX-XXXX-5237

7784-14XX-XXXX-5237

7784-14XX-XXXX-5237

7784-14XX-XXXX-5237

9984-11XX-XXXX-6196

3384-11XX-XXXX-0615

3384-11XX-XXXX-0615

3384-11XX-XXXX-0615

online solutions work with simple data. but I am struggling with this data. I am very desperate for the solution. Thanks in advance.


texts: Will a mysql db import be interrupted if my ssh session times out?

texts:
I have a large-ish (several GB) mysql DB that I'm planning to import via

mysql -u root -p mydb < mydb.sql

in a SSH session.

What would happen if my SSH connection drops?

Is it safer to run something like

nohup mysql -u root -p mydb < mydb.sql

Or does mysql not care?

Using the following

MySQL 5.1.45
CentOS 5.x


texts: Set a column's default value to the concatenation of two other columns' values?

texts:
I have a Postgresql 8.1 database. In one table, there are three columns: first_name, last_name, display_name.

Is it possible to set the default value of display_name to be first_name + " " + last_name?


texts: Convert rows into columns and group some colums

texts:
I saw a lot of example on how to convert rows into columns and I was able to accomplish that.Here is the table that have:

Userid     action      date            TRACKING         zip        Subject
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        C        1/2/2020           Amz           30105       $1 Sales journey
cbg10        P        1/2/2020           Amz           30105       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        p        1/2/2020           Amz           45685       $1 Sales journey
cbg10        O        4/18/2020          TG            30105       Summer arrives
cbg10        P        4/18/2020          TG            30105       Summer arrives
cbg10        C        4/18/2020          TG            30105       Summer arrives

This what I hope the table would look like: As you can see some open open the email 2 times. Instead of having 2 rows like in the first table this one add the actions taken by the person by subject

Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject
cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
kel64    1/2/2020     3         2        1            Amz     45685    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives

This is the code that I have, but it is not giving me what I need
select UserId,

max(case when Aciton = 'O' then 1 else 0 end) as [O(Open)],
max(case when Aciton = 'P' then 1 else 0 end) as [P(Paid)],
max(case when Aciton = 'C' then 1 else 0 end) as [C(Cash)],
 [Date],
 Tracking,
 Zip
 from myTable
 GROUP BY UserId, [Date], [Tracking], [Zip]
 ORDER BY UserId


texts: Clean up space used by moveChunk in file system

texts: I have a directory in my local file system where databases are stored called moveChunk which I understand is used by MongoDB for shard movement of chunks.  This is taking up around 206GB and I can't find any information on MongoDB site for cleaning this.  Some of the data is from earlier in the year.  Is there a resource to determine if this is safe to clean?  Or is there a process within MongoDB that cleans this?


texts: SQL Server Filetable document properties

texts:
I'm using SQL Server 2012's Filetable to store documents and search them with Semantic Search.

I was wondering if there is a way to list all the document properties (meta data). There is a way to let Full-Text Search index and search through document properties. I can make a list of properties SQL Server Indexes with the following statement:

 SELECT * FROM sys.registered_search_properties;

I can also extend this list with SQL or using a program.

What I didn't find is a way to list the actual information. What I'm looking for is a list like:

Author: Ruud van de Beeten
Title:  Test document
custom property: custom value

Can anyone point me in the right direction?

Edit: Bob Beauchemin created a ticket describing my problem. As the DMV is not listing the property values, I can't use it in my project.

I ended up using C# to list custom properties with the OleDocumentProperties object. This object can read properties from an Office document. I'll keep watching this question, hoping for a better solution.


texts: Why does my SQL Server database triple in size after deleting millions of records?

texts:
We have two database tables ErrorLog and Audit which have both grown fairly large over the last couple of years. So in an effort to reduce the size of the database, I have to write a script to clean out all the rows older than 6 months from these two tables.

This is what I came up with:

ALTER DATABASE StudioWebTest SET RECOVERY SIMPLE;

DELETE FROM [Audit] WHERE AuditDate < DATEADD(M, -6, GETDATE());
DELETE FROM [ErrorLog] WHERE ErrorDate < DATEADD(M, -6, GETDATE());

ALTER DATABASE StudioWebTest SET RECOVERY FULL;

On my development machine (SQL Server 2008 R2) with or without changing the recovery mode the database size grows 2-3 times in size. However if I follow this up immediately with a shrink command it cuts the size of database back to half the original size (prior to the record deletion).

However if I hold off on doing the shrink for a few days, the shrink is not nearly as effective, in that while the database size is reduced, it is still almost twice the size of what it was (before deleting the records). Not knowing too much about how SQL Server uses the space it has allocated I assume that this has something to do with it using the extra space that has been freed.

This would all be okay except for one thing.  When we run this on our production test environment which uses SQL Server 2005, the shrink command does not reduce the size of the database back to half its original size.

As an alternative I have also tried using TRUNCATE in place of DELETE but this doesn't appear to make much difference.  The database still grows massively after the statements complete and I still have to shrink it for the same result.  We haven't tried this on the production test machine yet, but as the shrink command doesn't seem to be cutting it, it seems doubtful that this will yield any improvement.

Anyway, I was just wondering if someone could explain why the database grows by so much despite changing the error recovery setting?  How can this be prevented?  Or possibly suggest an alternative means for reducing the size of the database if this is not the best way to go about this.

UPDATE:

I was just doing some more tests using TRUNCATE it doesn't seem to be increasing the size now (maybe I imagined it).  I do still need to shrink the database to see a reduction in overall size though.  I might try this on the production test server.  At the end of the day I think my managers will be happy as long as the size goes down to some degree.


texts:
I'm not sure how to couch this question. I have a table that has multiple columns including an id column, a foreign key column 'JobId', and a 'GxpId' column.

I have a requirement that a 'JobId' can have at most one 'GxpId' but the table can have multiple rows with the same 'JobId' and 'GxpId'.

There is a separate process that is importing data into the table and the application using the table has failed because the import process has created two rows with the same 'JobId' but different 'GxpId'.

I would like to add a constraint to the table to ensure that any row for a specific 'JobId' must have the same 'GxpId'. Hope that makes sense.


texts: SlaveThreadCount at 0 for non-root user, should be 2

texts:
In MySQL 5.5 running on Ubuntu 14 in a master-master-slave configuration

From one of the masters, when executing the query below as a user with (ALL PRIVILEGES) on the database but no other privileges elsewhere, this query returns 0

SELECT COUNT(1) SlaveThreadCount
FROM information_schema.processlist
WHERE user='system user'

When executing the same user as root, I.E. (ALL PRIVILEGES) on everything, I get back the real slave count.

Any particular reason I'm getting back 0 and not the real SlaveThreadCount which is 2 in this case?

Is this is privileges issue?

If I run the query without there WHERE in the non-root user, I only see my processes.

SELECT * FROM information_schema.processlist

If I run it in a root user, then I see all processes.

So definitely a permissions issue, so looks like I need PROCESS, just answered my own question.


texts: setting up multi-user, multi database server(sql)

texts: here's the thing, i'm taking an advanced java class and we are going to be accessing a database to edit tables and such,  we tried using freemysqlhosting.net but we all couldn't connect at the same time due to same external IP (possibly). I was wondering if I could host a sql server at home using any microsoft or free sql server program for everyone to access their own database from school without any problems.  Is there any possible way to do this?


texts:
I have a simple ksh script that executes db2 queries, such as

#!/bin/ksh
#some initialization
#step1,2,3
set -e
db2 -x "select * from table a "
#step4

The main issue seems to be caused by the db2 command. If the query returns records the shell script works perfectly fine. But if the query returns no results, then step4 will not be executed at all, and exits the shell immediately. This doesn't make sense. If the query didn't return anything, the shell still shouldn't crash anyway.

Can someone explain the reason ?

it turns out that there is a option "set -e" in the long script which will capture all unhandled execptional scenarios and abort the shell execution.


texts: Find the violated index on ORA-00001 (dup_val_on_index)

texts:
I have a table with several indexes. In my application code there could be instances where any of these indexes could be violated, depending on user input.

I would like to log exactly which index was violated when my PL/SQL code stumbles upon an ORA-00001.

Is there a way to know which index violation caused Oracle to raise the dup_val_on_index?


texts: For every inserted row, generate series of rows in another table with AFTER INSERT trigger in PostgreSQL

texts:
Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initialy. At the same time, I want to generate a series of  rows in the table2 with a combination of the newly generated ID in table1. Has to be executed for every new order. Like:
Table 1
columns - id ,qty
          A1   25
          A2   32

Generates:
Table 2
column - sub_id
          A1_1
          A1_2
          .
          .
          A1_25
          A2_1
          A2_2
          .
          .
          A2_32


texts: Hi folks, In one of my Agent Job is running by every minute

texts:
One of my SQL agent Job is running every minute, the job has failed . I need the job history of past five days.

Is there any way we can get the history details?

I have searched in the log but not able to find the details.

Please help me.


texts:
I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and password. I would like to create a trigger on users table so if I insert pairs of values in users table for name and password fields and after I update the value for password field where name = some of the inserts I have made in users table, trigger fires and inserts old value of name and password in formerpasswords table. How could I do that in SQL Server? I know that this can be done easily in MySQL using a before update trigger but in SQL Server that doesn't exist so I look for a way to do this.

Thank you so much in advance.


texts:
I am doing some analysis on merge replication and triggers for a project.

I have two tables, Product and Audit that I am replicating.

I have an update trigger on the Product table which is replicated. This trigger adds a row to the Audit table, which itself has an identity column.

On a client subscription I can update an item in the Product table, and it adds an item to the Audit table.

As soon as I sync the changes to the server I get an error,

  A row update at 'ReplicatedDatabase' could not be propagated to
  'ServerDatabase'.  This failure can be caused by a constraint
  violation. Explicit value must be specified for identity column in
  table 'Audit' either when IDENTITY_INSERT is set to ON or  when a
  replication user is inserting into a NOT FOR REPLICATION identity
  column.

Am I right in thinking this error is because the trigger has fired at the client, and then again at the server when merged.

Is there a way to replicate a trigger to a client subscription, and not have it fire again when syncing to the server (if indeed this is what is happening)? My example here is not great, but our product will have times where triggers will need to fire at the client subscriptions.

The same triggers will need to fire at the server if data is updated directly on the server (not via replication).


texts: MySQL Trigger Replication between Master and Slave

texts:
we are running a rather highly loaded MySQL Application replicating between Master and two Slave instances. We are using Statement-based replication.

Recently, we have upgraded our hardware and a very strange problem appeared.

On Master (and slave(s)), there is an after_insert and an after_update trigger defined on Table A. Every time the trigger is fired it inserts a "log" entry into a separate table. Below is the Trigger Code:

    DELIMITER |
CREATE TRIGGER orders_after_insert AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        RESIGNAL;
    DECLARE EXIT HANDLER FOR SQLWARNING
        RESIGNAL;
    DECLARE EXIT HANDLER FOR NOT FOUND
        RESIGNAL;

    IF NEW.company_id = 1 THEN
        INSERT INTO changelog SET
           company_id  = NEW.company_id,
           dbtable     = 'orders',
           row_id      = NEW.order_id,
           created     = NOW(),
           action      = 'INSERT';
    END IF;
END;
|

DELIMITER ;

After our hardware upgrade we now face the following problem: every now and then, the trigger will NOT fire on Master, it fires okay on both slaves. As a result we have inconsistent data between Master and Slave.

All of this is always happening inside a transaction!
The changelog table the trigger is inserting into does NOT have a primary key. Adding an auto-increment column to the table is not an option, as it makes the trigger unsafe for replication (MySQL Warning)
Both tables are InnoDB Tables

I am already out of ideas how to further debug this problem. It is reproducible on our production system only. Happens randomly and rarely. How can this be analyzed? How can I dig to the root of the problem?

I will be really grateful for any inputs!


texts:
we're using the SQL functions from Ola Hallengren since many years. Sometime between 2015 and 2018 the check for open transactions was added in IndexOptimize. So now, after upgrade to the current version, the sp fails with "The transaction count is not 0."

Question: What's the functional reason for the check? It seems to me, that the old version w/o the check was running fine too. Is it safe to remove the check?

Regards
Holger


texts: Does PostgreSQL and other databases have a feature to reset serial/identity values?

texts:
I recently started a new personal project. The first thing I do after thinking about it is to construct an install.php script which consists of a series of database calls where it does things like CREATE TABLE to "build the basic foundation".

When I felt reasonably confident about the structure, I ran that PHP script. From that point on, I instead used a GUI software to edit/add/delete columns and constraints, as I "fine-tuned" the database while developing the application.

For a while, I would edit the install.php for each change I did through the GUI, as to have them perfectly "synced", but after a while, as there were more changes, I said: "Screw it! This is only ever going to run on my computer anyway!" and deleted the install.php.

Now, I'm reaching some kind of "finished" state for the application, and realize that, after deleting the dummy records, various serials/bigserials are now starting at high numbers (instead of 0 or 1, as they do from the beginning). Of course, this means no practical problem, but it is psychologically very annoying to know that you started the "production application" with "non-vanilla" internal counters.

Is there a feature in PostgreSQL which automatically sees to it that any data remaining in the tables of the given database is "pulled back down" so they get ids like 1, 2, 3 instead of 6732, 6733, 6734, etc.? That is, not nuking the database/tables, but intelligently "resetting" them in this logical manner? I also wonder if other databases have this feature.

Note: I have foreign keys and primary keys and uniques and such all set up, so this should technically be possible without messing up the "relations" when the ids change.

I bet there isn't any such thing. Would be really nice if such is the case, though. Like a "database serial defragmenter" of sorts. It would even be sensible to run on a product database if you have deleted huge amounts of records for some reason, so that you never reach the maximum for a "serial" (integer).


texts: When running a script with several commands, how to find out where we're at?

texts:
I have some processes that require different steps to be taken before they start. For example:

Disable check constraint on foreign key:

select 'alter table '+fk.table_schema+'.'+fk.table_name
+' NOCHECK CONSTRAINT '+fk.CONSTRAINT_NAME
from SAProduct.information_schema.table_constraints fk
join SAProduct.information_schema.tables t
on t.table_schema = fk.table_schema
and t.table_name = fk.table_name
WHERE constraint_type='FOREIGN KEY'

This will produce a big list of statements, I am copying only 3:

use saproduct
go
alter table dbo.tblProdClassificationCode NOCHECK CONSTRAINT FK_tblProdClassificationCode_tblProdClassification
alter table dbo.tblProdClassificationCodeDescr NOCHECK CONSTRAINT fk_ProdClassificationCodeDescr_ProdClassificationCode
alter table dbo.tblProdClassificationCodeGenerate NOCHECK CONSTRAINT fk_ProdClassificationCodeGenerate_ProdClassification

If I was to run that on one go, how would I find out where I am at?
which table I am dealing with, and which one is the next?

The same for the delete:

select 'DELETE FROM '+table_schema+'.'+table_name
from saproduct.information_schema.tables
where table_name like 'tbl%'

the script above would produce the set of statements below:

use saproduct
go
DELETE FROM dbo.tblBLanguage
DELETE FROM dbo.tblBLgCategoryXRef
DELETE FROM dbo.tblBLgSegmentXRef
DELETE FROM dbo.tblProdClassification
DELETE FROM dbo.tblProdClassificationCode
DELETE FROM dbo.tblProdClassificationCodeDescr
DELETE FROM dbo.tblProdClassificationCodeGenerate
DELETE FROM dbo.tblProdClassificationDescr
DELETE FROM dbo.tblProdClassificationMarket
DELETE FROM dbo.tblProdClassificationTier
DELETE FROM dbo.tblProdClassificationValue
DELETE FROM dbo.tblProdData
DELETE FROM dbo.tblProdDataDescr
DELETE FROM dbo.tblProdDataMarket
DELETE FROM dbo.tblProdDataTempTier1Descr
DELETE FROM dbo.tblProdDataTempTier1Tier2Descr
DELETE FROM dbo.tblProdDataTier
DELETE FROM dbo.tblProdDataValue
DELETE FROM dbo.tblProdDataValueDate
DELETE FROM dbo.tblProdDataValueNumber
DELETE FROM dbo.tblProdDataValueString
DELETE FROM dbo.tblProdName
DELETE FROM dbo.tblProdNameSizeAlias
DELETE FROM dbo.tblProdNameSizeRuleAlias
DELETE FROM dbo.tblProdNameStructure
DELETE FROM dbo.tblProdNameStructureDescr
DELETE FROM dbo.tblProduct

Which table is the next one for me to delete?

I am trying to access tables in a certain order to minimise deadlocks, specially when having to put any type of exclusive locks. it is not here, but I try to avoid lock escalation too, so it can get complex. I like to "see" what exactly is going on. I am using SQL Server 2014:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


texts:
My Issue
After shutting down and starting my system up, my MySQL daemon gives me this in the logs (the pertinent snippet is as follows):
2021-05-03T12:48:32.458594Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: fut0lst.ic:86:addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA thread 139822345225984
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:48:32 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7fffe0c79cb0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...

This is the second time I'm running into this issue. After struggling for a while the last time, I re-installed mysql server, which worked. I'm puzzled as to why this keeps happening and want to get to the root of the issue this time. I did nothing out of the ordinary (as far as I'm aware) to cause this error.
Troubleshooting
innodb_force_recovery = 3 (not below) in my.cnf enables the daemon to run. According to InnoDB recovery guide and the official link in the error description this has something to do with "So for example: if your MySQL server starts up on mode 3, but not mode 2, it might be a safe assumption to say that the crash has something to do with the transaction rollback process." I've added innodb_fast_shutdown = 0 and innodb_purge_threads = 1 and dropped all the non-system schemas as well, but to no avail.
SHOW FULL PROCESSLIST revealed an event listener and my current process, so I don't think there was much there.
Hdd space and memory is not an issue.
And so...
I'm not certain how to proceed with debugging from here on in. A nudge in the right direction would be appreciated. Thanks.


texts: Can MySQL server send a "server started/running" signal to a remote server?

texts:
I have a MySQL 8 server on a virtual machine. I'd like to start it up when another server wants to communicate with it, and shut it down some time after there hasn't been any connections or queries.
Right now I'm polling the VM to get it somewhat working, but there's gotta be a better way.
I read the MySQL docs on handling Unix signals, but there isn't a "startup" type signal.
I also read about MySQL events, but those seem to be limited to operations within the database like SQL queries.
Does MySQL have a way to push events to a remote server on startup, shutdown, error, etc.?
It's probably possible to achieve in a script on the OS, and then send a curl request to the remote server with appropriate info, but MySQL's a big software and I'm curious if this functionality is hidden somewhere in it.


texts: Does SQL Server for Linux support Application Consistent Backups

texts:
We are looking to migrate from SQL Server 2k16 on Windows to SQL Server 2k17 on Linux.

Currently using DPM for our backup solution and looking it replace it with EC2 Snapshots.

On SQL Server for Windows, we could use VSS to get an Application-Consistent Snapshot.  I can't see any such support for the equivalent on Linux.

Does anyone know if there is a way to get Application-Consistent Snapshots on Linux?


texts: "Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio

texts:
I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

which I used the Microsoft SQL Server 2008 Management Studio.

I created a stored procedure testtable_pricesmaller as follows

use testbase
go
create procedure testtable_pricesmaller
    @pricelimit money
as
select * from testtable where price = @pricelimit;
go

and are able to view the Stored Procedures on the Object Explorer of the Microsoft SQL Server Management Studio. (It is listed in the following tree structure of the Object Explorer)

Databases
    + testbase
        + Tables
            + dbo.testtable
        + Programmability
            + Stored Procedures
                + dbo.testtable_pricesmaller

I find it very strange when I receive the following error:

Could not find the stored procedure 'dbo.testtable_pricesmaller'.

when I execute the following SQL statement:

execute dbo.testtable_pricesmaller 50

What could it be missing?


texts: How can I simulate deadlock in SQL Server nested transactions?

texts:
Actually I would like to find answers for the following situation:
Let's say I have a stored procedure (Usp1) which at some moment calls an other stored procedure (usp2). Each of the stored procedures is a transaction. So there are nested transactions.

Can a deadlock occur in nested transactions while modifying the same table from 2 different sql server sessions?
Are there any possibilities of conflicts which leads to deadllock between the inner and outer transactions of the same stored procedure in a single session?

Examples would be really appreciated.


texts: All users are having NO Privileges in phpMyAdmin

texts: By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one database. But I have nearly 25 databases created with 'root' user. If I want to access all other databases I need to GRANT Privileges for the root user again but all other users are not permitted to execute GRANT statement. So is there any way to grant permissions for the root user again. The all other Databases contained in my DB Server are very much important. So Please help in resolving this issue.


texts: Best Data Type to Store Result of HASHBYTES('MD5', ...)

texts:
What would be the best data type to store the results of the HASHBYTES('MD5', ...)?

It outputs 16 bytes of binary as follows: e.g.

0x5CFCD77F9FF836189D2F647EBCEA183E

I could store it in the following data types:

char(34)
binary(16) (I think - I read here (https://stackoverflow.com/questions/14722305/what-kind-of-datatype-should-one-use-to-store-hashes#16680423) that using the same algo should return the same number of bytes every time regardless of the input string)
other?

Every row will have a value (no nulls), and the column will be used for comparison against a similar column in another table.

Which is the best data type to store HASHBYTES output for use as described above?

I was thinking that since fixed length data types can sometimes be more efficient on joins, etc. binary(16) vs varbinary(8000) (the default output of HASHBYTES) seems best, and binary(16) vs a varchar(34) is better since it would use less storage space.


texts: How can I compare the schema of two databases?

texts:
Is there a way to find the differences in two SQL Server databases (schema only).  One is local and the second is at a customer's site.  We are experiencing problems with crystal reports running some reports and some code not executing and it would appear that the schemas don't match.

Can I run the same command on both databases and compare the results to tell where the differences are?


texts: Should I move repeating foreign keys into separate table?

texts:
Idea

Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set the chosen channelfor each uploaded item. Having **Many To One** relationship betweenitemandchannel`.

There actually exists upload_process table as well. When upload is started, a process is created to capture any metadata and store the state of the running process.

Currently all the queries that deal with Items, directly use the channel_id from on the item itself, but all the items that are uploaded in one process always have the same channel.

Example

User uploads 100 items and chooses Channel 1.
Upload process is created, storing information like process status, who uploaded, upload date...
When processing, each item channel is set to Channel 1.
I end up having 100 items with foreign keys pointing to Channel 1

Question

Should I move the channel relationship to the upload_process entity and store that there instead of each individual item?

There are over 10 million items in the database. Currently there are max 2 joins on all the queries that deal with items and queries run max 2 seconds, adding another join table shouldn't hurt the performance that much.

What is better here design wise? Thanks!


texts:
I am trying to deploy a package to a project in my SSIS catalog and am using the following command:

dtutil /FILE C:\Changes\ReceivedEDI.dtsx /DestS MARSQL12 /COPY DTS;TradingPartners\TradingPartner\ReceivedEDI

Where the TradingPartners is the folder name, the TradingPartner is the project name and the ReceivedEDI is the package name.

I am getting an error of:

Error (0x800706BA) while checking for the existence of package "TradingPartners\
TradingPartner\ReceivedEDI" in the SSIS package store.
Error (0x800706BA) saving package "TradingPartners\TradingPartner\ReceivedEDI" t
o the SSIS package store.

Can anyone explain what I am doing wrong?


texts: SSIS convert date in mmddyyyy format to date format using SSIS expression

texts:
I'm receiving dates in a mmddyyyy format but want to convert it to the date format in sql/ssis yyyy-mm-dd, how can I do so using an SSIS expresion.
example of dates I'm reciving are 03051978 but want it to appear in 1978-03-05.
Thank you


texts: is there a way to find out what route and endpoint my service broker session is using?

texts:
I have been trying in many way, from sys.conversation_endpoints to find out which route and which endpoint my service broker session is using.

SELECT conversation_handle, is_initiator, s.name as 'local service',
far_service, sc.name 'contract', state_desc
FROM sys.conversation_endpoints ce
LEFT JOIN sys.services s
ON ce.service_id = s.service_id
LEFT JOIN sys.service_contracts sc
ON ce.service_contract_id = sc.service_contract_id;

-- Error messages in the queue
SELECT *
FROM sys.transmission_queue;

Is there a way to find this out?


texts:
I am having trouble understanding the Postgresql's Statistics Collector.

Here is the official page of describing the pg_stat_database view.

I performed query on the pg_stat_database view,

 postgres=# select tup_inserted, tup_fetched from pg_stat_database;
 tup_inserted | tup_fetched
--------------+-------------
            0 |           0
            0 |           0
            0 |      425511
            0 |      407918
            0 |           0
         1011 |      413373
          324 |      360500
         1167 |      345411
         1139 |      346655
          787 |      342410
         1049 |      328089
            0 |      204959
         1646 |      231987
          892 |      205624
           41 |       66538
            0 |       41609
          838 |     2591785
(17 rows)

But these are ridiculous numbers. Even If I combine all my databases' rows, still they won't be equal to this.

What data does this actually tells us ?
And moreover I have not used these databases for weeks, means it is showing me old data.

I want to monitor the number of queries being performed, memory used, clients connected etc.

Is there any kind of tool to monitor my postgresql database ?


texts: Is there some SQL that I can use in order to find out which databases on the Server haven't been used recently?  I need to find the ones that are no longer used and detach them.


texts: MS SQL Back up not restore the MS SQL 2014?

texts:
I am new comer to MS SQL 2014. I will try restore db back up. but given following error. expect some expert help.

  TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'MSSQL_MED' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)

------------------------------

Database 'MSSQL_MED' cannot be started in this edition of SQL Server because part or all of object 'Answer_statusComponent_signings_History' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'MSSQL_MED' cannot be started in this edition of SQL Server because it contains a partition function 'archive_applog_pf'. Only Enterprise edition of SQL Server supports partitioning.
Database 'MSSQL_MED' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

-------version info-------------------

Microsoft SQL Server Management Studio                      12.0.2000.8
Microsoft Analysis Services Client Tools                        12.0.2000.8
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                     3.0 6.0
Microsoft Internet Explorer                     9.11.14393.0
Microsoft .NET Framework                        4.0.30319.42000
Operating System                        6.3.14393


texts: Microsoft Optimal method for columns into 1 word: XML Raw or Concat

texts:
What is fastest, performance method to make columns into one word? Does Microsoft have recommendation, or is there performance Big-O studies on FOR XML RAW vs Concat?

Company has multiple tables with different column types including int, varchar, string, different number of columns, some not/not null, maybe anywhere from 5-25 columns in a table.

SELECT (Column1, Column2, Column3, Column4... FOR XML RAW)))

SELECT CONCAT(Column1,'|',Column2,'|',Column3,'|',Column4...))

We are seeing different timing results on our server, however both seem to be relatively close, curious if there is any general optimal to apply, rather than reviewing 1000 tables.

Background will be utilized to apply hashing of row later.


texts: Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

texts:
If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision (YYYYMMDDHHmmss) and still realize the index performance?

For example:

 SELECT * FROM MYTABLE
 WHERE TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') > 20110622105738
   AND TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') <= 20150622105738
 ORDER BY MYCOLUMN

This is for Oracle 11


texts:
I am working on a script that needs to loop an array and loop a sub array. I want to take in rows from a table (that piece works fine) that look like:

invoice | item
inv1    | it_A
inv1    | it_C
inv2    | it_D
inv2    | it_B
inv3    | it_A

and display the JSON (also works) like pseudo code

inv1

   it_C
   it_D

inv2

   it_D
   it_B

inv3

   it_A

but I have the loop wrong, thanks for any assistance

$result = $conn->query($sql);

if ($result->num_rows > 0) {

echo "rows found";
    while($row = $result->fetch_assoc()) {

$invid = $row[invoice_number];
echo $invid;

foreach($invid as $invoicelines)
{

$sql1 = $invoicelines[item_code];

echo $sql1;


texts: How many cores are used for serial plans in SQL Server?

texts:
I understand that a parallel plan will use (at most) as many cores as set up in MAXDOP configuration (per parallel zone).

I have configured my MAXDOP to 4 and my “cost threshold for parallelism” to 55. So, my question is...will all the serial plans with a cost less than 55 use only the first core?

If that’s the case...if I have mostly low-cost queries, my 1st core would be exhausted while the rest of the cores would be idle.


texts: Is there any way to find out when particular view was compiled?

texts:
I have an Issue with a view, recently I made changes to the view and committed the code (in SVN). Code was moved to another repository and compiled, but my modifications are not reflected. I searched on Google to find when the view was last compiled, but I didn't find any clue.

Is there any way to find when particular view was compiled? Does Oracle store historic data (when was it compiled/modified/etc.)?


texts: Impact of OS timezone change on Postgres Database

texts: I want change OS time zone to UTC. This will shift back time by 2h. Can this possibly damage data in any way (for example overwrite data)? What should I do to safely change the OS time zone?


texts: mysql query - how to delete duplicates based on highest int?

texts:
I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId.

+----------------------+--------------+------+-----+-------------------+-------------------+
| Field                | Type         | Null | Key | Default           | Extra             |
+----------------------+--------------+------+-----+-------------------+-------------------+
| lastPriceBid         | varchar(125) | YES  |     | NULL              |                   |
| dateBidded           | varchar(125) | YES  |     | NULL              |                   |
| owner                | varchar(65)  | YES  |     | NULL              |                   |
| status               | int(11)      | YES  |     | 0                 |                   |
| userId               | varchar(125) | YES  |     | NULL              |                   |
| siteId               | int(11)      | YES  |     | NULL              |                   |
| lastPriceBid_rounded | int(11)      | YES  |     | NULL              |                   |
| from_nickname        | varchar(125) | YES  |     | NULL              |                   |
| id                   | int(11)      | NO   | PRI | NULL              | auto_increment    |
| time_updated         | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update         |
|                                                                        CURRENT_TIMESTAMP |
+----------------------+--------------+------+-----+-------------------+-------------------+

So far I've tried

DELETE n1
FROM offers n1, offers n2
WHERE n1.lastPriceBid_rounded < n2.lastPriceBid_rounded
AND n1.userId = n2.userId;

but it doesn't seem to work well. I still have some duplicates (I see that when I use SELECT DISTINCT).


texts: Configuration file missing after successful SSRS migration

texts:
Getting the backup of all SSRS databases including compressing and encrypting can be straight forward.

However, when planning a complete solution for SQL Server Reporting services backup, following the link below can be more demanding.

Backup and Restore Operations for Reporting Services

Among other things, including the encryption keys, master keys, Backing Up the Configuration Files

Reporting Services uses configuration files to store application settings.
You should back up the files when you first configure the server and after you deploy any custom extensions.

Files to back up include:

Rsreportserver.config
Rssrvpolicy.config
Rsmgrpolicy.config
Reportingservicesservice.exe.config
Web.config for the Report Server ASP.NET application
Machine.config for ASP.NET

QUESTION:

I have just installed SQL Server Reporting Services, migrated all databases and reports, including all folder permissions,  from a previous server (sql server 2008),and all is working fine.

However, on the backup strategy, I cannot find some of the .config files that are required to backup.
The file that is missing is Rsmgrpolicy.config

Why is this file missing?

cls
# PowerShell script to list the DLL files under the system32 folder
$Dir = get-childitem "C:\Program Files\" -recurse
# $Dir |get-member
$List = $Dir | where {$_.extension -eq ".config" -and $_.name -like "*rs*"}
$List | format-table Directory, name


texts: CPU usage among Numa nodes in sql server

texts:
I need to understand the below scenario

On Sql server 2014 with compatibility mode of 2008R2, having 80 logical processors with 20 distributed amongst 4 numa nodes. I. E numa node 1 has 0-19 , numa node 2 has 20-39 and so on.

While checking the cpu usage, we believe workload should be equally distributed amongst all of them. Sometime i see like equal distribution to some extent amongst all numa nodes to their processors.

But some time lets say during maintainence window like backup or index main jobs or runs, why is that lets say numa node 0 and 1 will not have any equal usage compares to 2 and 3 or vice versa. As far as i think it should be equally distributed.

Note we are running enterprise core edition.

Is this expected or something missing in the settings of these cores?

Update@ As said by Randolph, some process can go single threaded, i got it, but why out of the 80 they are using 80-90 % of cpu on few processors while other sitting idle. Cant the workload be equally divided when say there are 10-15 with high usage showing while remaining 50 odd processors sitting idle


texts:
If I make full/differential/log backups to disk under full recovery model it is important to specify MEDIANAME?

Is it true that MEDIANAME is only for tape backups? If not, when and why should I specify MEDIANAME in the backup statement?

Additional question:
msdb.dbo.backupmediaset.media_set_id actually means backup file id if backup media is disk, it that true?


texts: Retain column statistics on paragraph-style columns?

texts:
Specific to Microsoft SQL Server...

If a query has a search predicate against a paragraph-style text column, it will create a system-created statistic to answer the query.

Got it.

My question is if those should be removed later as they can't be of very much help since they are based purely on the first few characters of the text string and not actually useful in the search which will be a scan against the table no matter what.  Later "grooming" operations via auto-stats update, or a self-developed-stats-grooming process will encounter these and may try and update them.
To complicate it, this column is typically "wide" and can often be "deep" (say, a bajillion rows) and cause a large IO hit to accomplish even a small sample scan to update the stat.

My thinking leads me to say they should be removed.  What do others say?

I am not asking if queries against these types of data are advisable.  They happen.  I am just doubting the usefulness of these auto-generated stats and their justification to exist unless you happen to have an text column with the first few characters actually useful in finding the rows you need - enough so that you'd consider create an index on that column - and I expect that is unusual.

What say you?  Keep these albatrosses or get rid of them?


texts:
We have a database with 1,1TB .mdf file in SQL Server 2014. I want to split it into two datafiles (add new .ndf file). As I know, there are two methods:

To rebuild big clustered indexes and then shrink the .mdf file.
Add new .ndf file and run a DBCC SHRINKFILE ('filename', EMPTYFILE). After this, new values will be replaced in both datafiles.

Which method is safer, faster, better? We will do first on TEST and then in PRODUCTION environment.

And maybe there is another way to split datafile?


texts: Any potential issues with changing the owner of a symmetric key?

texts:
We're doing a security audit on a database I support, and there's one old, obsolete user who can't be removed because he owns a symmetric key in the database. The symmetric key is being used to encrypt user passwords.

Are there any potential implications to changing the owner of the symmetric key to dbo? We wouldn't be changing anything else about the key, just issuing ALTER AUTHORIZATION ON SYMMETRIC KEY::PasswordKey TO dbo.


texts: SQL Server 2012 Merge replication not replicating all changes

texts:
Having a problem with replicating a database between two servers, both running SQL Server 2012. We have a table where each site that stores the database updates a record every minute with the current time stamp. This way either location can see whether sync is up to date.

The problem we've got is after so many months of this working perfectly one of the sites (subscriber, not publisher) will stop transmitting this change to the other. The subscriber still updates this record but this change never gets sent to the other site. Other changes do, so sync is still up. The publisher's change and any other subscriber's changes to its own record gets sent across. Nothing appears in logs or the conflict logging table for any problems with this table. It just doesn't update.

If we validate the subscription it'll see the change and update, but then will again not take any further changes to the records unless validation is again done. If we re-push the database then the new subscription works fine and doesn't show any of these problems. It seems to be something tied to this one particular subscription/database. Possibly related but Management Studio's replication monitor reports the data flow as being around .01 rows per second, though checking activity it's frequently downloading/uploading or waiting. We can't repush/reinitialize due to the importance of these systems staying up.


texts: MySQL - change all tables' ENGINE from MyISAM to InnoDB with a single query

texts:
I had some foreign key issues in my database when I change the table engine "MyISAM" to "InnoDB" but then it works fine.

I found one query to change a single table's engine but I have many tables so what I want to know is there any way to do it all once?

ALTER TABLE tbl_name ENGINE=INNODB;

What else I have tried?

I also tried to update the my.ini file by adding the code below but it doesn't work!

default-storage-engine = InnoDB

I found a query way is more convenient as of my understanding so any suggestion for a change it all table engine at once?

This is the Database server detail:

Server: 13227.3232.3232.3232 via TCP/IP
Server type: MariaDB
Server version: 10.4.10-MariaDB - mariadb.org binary distribution
Protocol version: 10


texts: Help configuring MySQL install for remote connections / root accounts understanding

texts:
I installed mysql/phpmyadmin/apache on a linux box for internal network use only. I don't have the strongest understanding of MySQL.

Hostname: server.domain.lan
IP: 192.168.0.54

After I installed MySQL I ran mysqladmin -u root password STRONGPASSWORD
I edited /etc/my.conf and set the bind-address to 192.168.0.54

I am trying to set up a system to run on my LAN that I can upload websites to that need to create databases.

Screenshot from phpMyAdmin of the Privileges menu:

So my questions are:

Why are there 3 root accounts?
What do I need to do to configure MySQL to allow remote connections from the root account so I can create databases, etc?

(Sorry I'm kind of new to MySQL so I'm not certain that I'm even asking the correct questions...)


texts: MySQL Master-Slave setup: master is not indexed and slave is indexed

texts:
Is it possible to have a MySQL master-slave setup where the master has no indexes (for faster insertion) and the slave will be indexed?

Thanks in advance!


texts: How do I replicate Mysql DB server to new server?

texts:
Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration).

I want to replicate every bit of data on production server to new server keeping permissions, users and privileges intact. I just learned about the mysqldump -u root -p databasename > file.sql command but I am not sure how it is going to affect the permissions and users.

Please suggest what is the best way to replicate database in Mysql by keeping the integrity of the data. Help is greatly appreciated.


texts: Determining whether column in view can only contain unique values

texts:
I have a SQL Server 2008 database with several views. I'm looking for a way to definitively determine whether a particular view has a column that can only contain unique values (i.e. can be used as a primary key).

Some of these views have simple definitions, but some are also quite complex, so I find that manually analysing each of these views would be very time-consuming.

As well, some of these views have columns that concatenate values from other columns in the view in order to be "unique", but I can't be certain whether these values are actually unique because in some cases I have found that they are not.

One simple approach I had was to use the following query to determine if any duplicate values exist for a particular column in a view:

SELECT   column_name
FROM     view_name
GROUP BY column_name
HAVING   COUNT(column_name) > 1

The above query works fine when there is enough data in the view to catch any non-unique values, but this still isn't definitive.

Is there a way to have SQL Server perform this analysis to determine whether a column in a view can only contain unique values?


texts: Should I load the data into Temp table or keep calling it from the normal table?

texts:
I am writing a procedure and find myself using the same select statement with the same where clause a lot. Currently the table holds over 55000 rows and grows about 100 or so rows each day. The two where clauses cut the data down to a few hundred rows in total.

I run the exact same select statement over a dozen times and the data results will always be the same, within the same run. Would it be better, performance-wise, to keep running the same select statement or should I do an insert into select to fill a temp table with only the data I need and pull from there instead?

Select statement ran (with some some IDs redacted):

SELECT WorkDay1, WorkDay1Brea, StartBreak, StrtTm
FROM dbo.DriverTimes
WHERE DriverTime.DrvrID = @DriverID
AND CONVERT(VARCHAR(12), dtwrkd, 112) = CONVERT(VARCHAR(12), @StartDate, 112)
AND PryllID IN (
 ListOfIDs
);

This select is ran a total of 8 times for each Driver and there are 40 some odd drivers.


texts: Time Difference Between Two Consecutive Rows

texts:
i need simple SQL to calculate the time diff between Two Consecutive Rows
anyone can help


texts: Best Practice for SQL Server data validation

texts:
I have CSV files that I would like to import into SQL Server 2014. Each record in the files has around 20 columns, each of them should be validated for data type compliance as well as some other checks per column (min/max ranges, null/not null etc.).

I'm looking for advice regarding a model architecture on doing this in a efficient way. Using SSIS and some error tables seems to me a bit inefficient since it looks as if I only can get one column checked at a time and the error handling (especially the column lockup in SSIS is clumsy at best).

I would prefer to have each record processed and right away have an overview of all errors that are in that record, so that I can provide a feedback of all issues at once to the people providing the data for import.


texts: I'm looking for ways to browse and dump the database of a particular content from the website. To be more specific i am collecting the database of the Skills designed for the device amazon alexa. Is there any way where i can access the database of all the updated skills list without browsing one by one and copying each skill to create a list? Thanks and regards, EJ


texts: MySQL Server :: My C Code can remotely create a database, but not "INSERT INTO" it

texts:
I’ve written a C program which does the following when it starts:

Connects to a remote Linux MySQL server over the network
Creates a new, uniquely-named database
Sends a “USE _____” command to specify we want to use that database
Creates a single table within the database

Here’s examples of the actual commands my code is sending:

(no command sent to initiate connection)
"CREATE DATABASE IF NOT EXISTS myDB_2019424_205718;"
“USE myDB_2019424_205718;”
“CREATE TABLE IF NOT EXISTS myTable ( timestamp VARCHAR(15) ,
Column1 VARCHAR(8) , Column2 VARCHAR(4) , Column3 VARCHAR(4) );”

All of the above works great.  The trouble is, once the database is set up and ready, I need the C program to crunch a lot of numbers on-the-fly, and periodically send “INSERT INTO” commands to the server.  Here’s the first “INSERT INTO” command my code sends:

INSERT INTO myTable VALUES ( '1556139439', NULL, NULL, NULL );

When my code sends its first “INSERT INTO” command, the server immediately sends back an error and my code reports “****** MySQL server has gone away ******”

I Googled that error, of course, and I see from posts like this that the two possible culprits are a low timeout or small packet threshold.  So I’ve reset those to maximum on my server:

mysql> SELECT @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|            107373568 |
+----------------------+
1 row in set (0.00 sec)

mysql>

But still no luck.  Worse, when I examine /var/log/mysql/error.log after a failed session, I see no error message recorded.

One more clue…  I’ve tried manually logging into the MySQL server and pasting these commands in one-by-one.  All commands are accepted on the manual interface.  There’s something buggy when my code sends the “INSERT INTO” command, and I can’t figure out what.  Has anyone seen this before?

Some system information…  My C code is running on an Ubuntu container, version 16.04.3 LTS.  I’ve installed the latest mysql library.  The SQL server is running on an identical container; the actual version of MySQL Server is Ver 14.14 Distrib 5.7.25.

Sooo… what could be the problem here?  I’ve included an excerpt of my C code below.  (The full code is too long and confusing to include here.)  Any suggestions or advice will be appreciated.


texts:
Char convertion in Oracle have a pattern: to_char(number,'pattern') and provide option "s" to take sign to result, for ex: select to_char(10,'S999') from dual; will return +10, but when i check select to_char(0,'S999') from dual; it return +0
Anyone could explain why :-)


texts: MongoDB 3.0 - how do I check which storage engine is being used?

texts: I have MongoDB 3.0.0 running on a Digital Ocean droplet.  How can I tell which storage engine it is using?


texts: ALTER fixed-length COLUMN on compressed table

texts:
I wonder if compression of a table actually changes ALTER TABLE rules. I found no information on this topic. Let's consider a simple example. I'll create a table and alter one of the columns from INT to BIGINT.

CREATE TABLE dbo.test1
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
)

Physical row looks just like we can suggest, two fields, 4 bytes each.

SELECT pc.leaf_offset, pc.max_length
FROM sys.system_internals_partitions p
  join sys.system_internals_partition_columns pc
    on p.partition_id = pc.partition_id
WHERE p.object_id = object_id('dbo.test1')

leaf_offset max_length
4           4
8           4

Let's change c2 from INT to BIGINT

ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint not null

We increased the length of a fixed-length column so SQL Server adds one more column at the end of the row. The same query shows

leaf_offset max_length
4           4
8           4
12          8

So far so good, everything works just like books and numerous articles describe. Let's try to do the same thing with a compressed table.

CREATE TABLE dbo.test2
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
) with (data_compression = page)

The physical structure looks like this:

leaf_offset max_length
-1          4
-2          4

All 'offsets' are negative. Only variable-length columns suppose to have negative offsets. It surprised me at first, I never seen this documented. It makes sense though, the compressed page can't have fixed offsets from the beginning of the row. Now let's try to alter the column.

ALTER TABLE dbo.test2 ALTER COLUMN c2 bigint not null

The second surprise, the 'new' column has not been added.

leaf_offset max_length
-1          4
-2          8

One more surprise, it looks like on SQL Server 2017 this operation is 'metadata-only', still about to confirm it. SQL Server 2014 handles it in the old way.

My questions

Do we have any document available on how table compression affects ALTER TABLE? Who can shed some light on it?
Does SQL Server 2017 improve this?

I have read this sample chapter from Microsoft SQL Server 2012  Internals. It covers the column descriptor row format for compressed data, but covers only physical structures involved and doesn't touch schema changes at all.


texts: Find the days difference between two dates per month

texts:
I have a table 'data' in which there are two columns i.e. Startdate and Enddate.
Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to find the working days of employee per month.
If the Startdate is  '2015-06-21' and the Enddate is '2015-08-21' then the working days will be:
June 10days
July 31days
August 21days

I have the query for the total days for the work:
Select datediff(dd,startdate,enddate)
from tablename

but I need to take the days as above (per month).
Update
I actually have three columns, 'Startdate', 'Enddate' and 'Salary'. The full query needs to divide the salary over the calendar months. If salary is 620 then for the above dates sample the result is:
June-for 10 days = 100
July = 310
August = 210


texts: SQL rolling back all iterations of a while loop when each iteration has its own explicit transaction

texts:
For the past few days I've been trying to write a query for an automated process that will delete records from a log table within a specified date range (a month) to another table, so they can then be dumped to disk and the records deleted from the database. A first, naïve approach, á la
DELETE FROM <sometable>
OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

of course blew up the transaction log. I then tried doing the same, but now in batches of 1m rows, as shown below
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (1000000) FROM <sometable>
    OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
    WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}
END

but that did nothing to change the transaction log growth. Next, I tried using an explicit transaction within the loop.
WHILE @@ROWCOUNT > 0
BEGIN
    BEGIN TRAN

    DELETE TOP (1000000) FROM <sometable>
    OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
    WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

    COMMIT
END

Again. No difference. What's worse, if I cancel this query and do a ROLLBACK to release the table, it rolls back everything. Not just the last iteration, as one would expect, but the whole thing. Why aren't the individual transactions within the loop being treated as such? Surely there must be a way to move huge amounts of rows around the database without SQL throwing a hissy fit and blowing up on its user(s)?


texts: In SQL Server, what is "Partitioned Table Parallelism" that is only available in Enterprise Edition?

texts:
I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I observe when running a query across Standard and Enterprise are comparable, and queries run in parallel across partitioned tables in the execution plan.
I have verified that:

The queries produce identical query plans
The queries produce similar runtime with set statistics time on

It seems that in SQL Server 2016 this difference is not reproducible. Is there something else this feature affects - maybe I am not testing for the right thing but the queries are comparable to that in the demo.
This is the script I have used to test:
-- MAXDOP is 10

--  structure of table
--Column                type
--testData.PKcolumn1    bigint
--testData.PKcolumn2    int
--date                  datetime
--testData.PKcolumn3    bigint
--metric1               float
--metric2               float
--metric3               float
--metric4               float

--index_description                                                 index_keys
--clustered, unique, primary key located on ps_testData             categoryId, transactionId, date

--pf_testData/ps_testData is a range right datetime partition scheme,  fanout 368
GO

-- Actual partition count: 368
-- all 10 threads participate in the scan, 12MM to 13MM rows / thread
--      each partition likely read by a single thread
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
GO

-- Actual partitions with data count: 31
-- all 10 threads participate in the scan, 3MM to 4MM rows / thread
--      each partition likely read by a single thread
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-02-01'
GO

-- Actual partitions with data count: 7
-- all 10 threads participate in the scan, with a range of 0.5MM to 1MM rows per thread
-- some partitions were read by multiple threads
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-01-08'
GO

-- Actual partitions with data count: 1
-- all 10 threads participate in the scan, 60K to 100K rows / thread (all threads read from a single partition)
SELECT COUNT(*), MIN(date), MAX(date)
FROM testData
WHERE date >= '2020-01-01' AND date < '2020-01-02'
GO

SQL Standard Query Plan
SQL Enterprise Query Plan

n.b. This query has a group by clause - both show parallelism across partitions and within the same partition.


texts:
When I create a new user, I give them a "temporary" password and tell them to change it as soon as they log in. How can I effectively enforce this?

I tried using ALTER USER VALID UNTIL 'near-future-timestamp'; but as it turns out this expiration date does not get reset when the user changes their password.


texts: Actual moving of tablespaces in Postgres, not copy-delete

texts:
I found that

ALTER DATABASE databasename SET TABLESPACE tablespacename

Copies the data and (I believe, couldnt check it) deletes the source after all is done. I need actual movement, because I have a large database that want to move to its own tablespace and doing this takes all the remaining storage.

Is any workaround for this?


texts: In PLPGSQL assign a variable based on result of SQL function

texts:
I have a PLPGSQL function which receives an address in two parts:

num = '123'
location = 'main street, anytown, KS'

To utilize the various portions of location I found there is a SQL function SPLIT_PART() which would work how I want it to, in that, if I execute
SPLIT_PART(location,',',2) it would render the 'city' portion.

I've tested this SPLIT_PART() function in pgAdmin4 to verify that it works how I expect it to, but I am having trouble executing this statement within the PLPGSQL environment and assigning the resulting value to a variable.

Through my searching, it appears as though using 'SELECT ... INTO ... ' is what I want to be using, but I have tried running this directly, using EXECUTE  or EXECUTE QUERY only render errors.  the latest being:

ERROR:  syntax error at or near "SPLIT_PART"

Attempt 1

DECLARE
    cn_rec RECORD;
BEGIN
    SELECT split_part INTO cn_rec FROM SPLIT_PART('''|| i_location ||''', '','',2);
--ERROR:  function split_part(unknown, unknown, unknown, integer) does not exist
--LINE 1: SELECT split_part             FROM SPLIT_PART('''|| i_locati...
--                                           ^
END;

Attempt 2

DECLARE
    cn_rec RECORD;
BEGIN
     EXECUTE QUERY 'SELECT split_part INTO cn_rec FROM SPLIT_PART('''|| i_location ||''', '','',2)';
--ERROR:  type "query" does not exist
--LINE 1: SELECT QUERY 'SELECT split_part INTO cn_rec FROM SPLIT_PART(...
--               ^
END;

Attempt 3

DECLARE
    cn_rec RECORD;
BEGIN
    EXECUTE 'SPLIT_PART('''|| i_location ||''', '','',2)' INTO cn_rec;
--ERROR:  syntax error at or near "SPLIT_PART"
--LINE 1: SPLIT_PART('Main St,Anytown', ',',2)
--        ^
END;

Being fairly new to PLPGSQL I'm not sure if I'm up against syntax errors, or fundamental misunderstanding of how this should work.


texts: I have been trying to troubleshoot a login that is not able to view certain tables in a SQL Server 2012 database. In doing so I find I don't quite understand what membership in the db_owner role allows. I can understand the other roles such as db_datareader and db_datawriter but I remain confused as to what db_owner allows.


texts:
this my configuration:

#postgres.conf:

listen_addresses = 'localhost'

#pg_hba.conf:

local all postgres md5
local all appuser trust
local all devuser trust
local all all peer

i'm connecting in local using the C-Library libpq.
My connection string is:

host=localhost user=appuser dbname=mydbname port='5432'

However, unless I have a .pgpass file in the home of my developer user (devuser), (the one who launches the application that is linked with libpq), my executable cannot connect, I receive the error fe_sendauth : no password supplied

As I said, if the .pgpass file is here, then it connects ok.

What I don't understand: I configured local all appuser trust.
Why do I need a .pgpass file all the same?

(It's not a serious problem in itself, because later on for security measure, it will no longer be at trust, but md5, so I'll have the .pgpass file anyway. But I'd like to know)


texts: Transactions, references and how to enforce double entry bookkeeping? (PG)

texts:
Double entry bookkeeping is

  a set of rules for recording financial information in a financial
  accounting system in which every transaction or event changes at least
  two different nominal ledger accounts.

An account can be "debited" or "credited", and the sum of all credits must be equal to the sum of all debits.

How would you implement this in a Postgres database? Specifying the following DDL:

CREATE TABLE accounts(
    account_id serial NOT NULL PRIMARY KEY,
    account_name varchar(64) NOT NULL
);

CREATE TABLE transactions(
    transaction_id serial NOT NULL PRIMARY KEY,
    transaction_date date NOT NULL
);

CREATE TABLE transactions_details(
    id serial8 NOT NULL PRIMARY KEY,
    transaction_id integer NOT NULL
        REFERENCES transactions (transaction_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED,
    account_id integer NOT NULL
        REFERENCES accounts (account_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        NOT DEFERRABLE INITIALLY IMMEDIATE,
    amount decimal(19,6) NOT NULL,
    flag varchar(1) NOT NULL CHECK (flag IN ('C','D'))
);

Note: the transaction_details table does not specify an explicit debit/credit account, because the system should be able to debit/credit more than one account in a single transaction.

This DDL creates the following requirement: After a database transaction commits on the transactions_details table, it must debit and credit the same amount for each transaction_id, e.g:

INSERT INTO accounts VALUES (100, 'Accounts receivable');
INSERT INTO accounts VALUES (200, 'Revenue');

INSERT INTO transactions VALUES (1, CURRENT_DATE);

-- The following must succeed
BEGIN;
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D');
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '1000'::decimal, 'C');
COMMIT;

-- But this must raise some error
BEGIN;
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D');
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '500'::decimal, 'C');
COMMIT;

Is it possible to implement this in a PostgreSQL database? Without specifying additional tables to store trigger states.


texts: Permissions to Create DB's and be able to perform any function only on those DB's

texts:
I am looking for a way to allow users to create databases which only they and SA accounts will have access to (Perform any function; creating/deleteing tables, deleting the DB, etc).

For example, User1 creates DB1 and User2 creates DB2. User1 must have full access to DB1 but no access to DB2 and User2 will have full access to DB2 but no access to DB1.

Thanks


texts:
I have two Mysql select queries:

select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID FROM TTS
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID
where TTS.MID = 15

select max(TTS_TAG.SORTID) as maxsort from TTS_TAG
where TTS_TAG.TAGID = 1

I wish to combine both statements into one select statement, with the aggregate value added as a field in resulting output record?

The second aggregate select statement retrieves the maximum 'sort' for all records with the value 1 - if this second select yielded '3', then my desired record set would be:

# SSML, SORTID, VoiceId, TAGID, maxsort
'ssml data', '1', 'Geraint', '4', '3'


texts:
I have a production MySQL server, fairly heavily loaded with an INSERT-only load on one of its databases.  I'd like to attach a non-production (sitting on my desk, more or less) replication slave to it so I can run slow, expensive analysis queries on recently inserted data.

The slave would be outside the regular domain of our DBAs so there is a risk of it going down, running slowly, having an incompatible schema, erroring on replicated statements, etc.  What risks does this pose to the availability and performance of the master?

Given my limited ability to configure the master and complete control over the slave, is there anything I can do to mitigate these risks or shift them towards the (unimportant) slave?


texts: How to replace to_tsquery by phraseto_tsquery in PostgreSQL?

texts:
For word suggestion when do search.
I can query like
select title from test where ts_filter(tsv, '{A}') @@ to_tsquery('english_nostop', 'god<->o' || ':*')

# tsv is something like to_tsvector('english_nostop', test.title)

The <-> was something like pattern = pattern.replace(' ', '<->'). That may have some problem when pattern contain <.  Thought we can remove such char.
Combine tsquery don't work here. I can't find away to do to_tsquery(':*')
But how to use phraseto_tsquery to replace  to_tsquery ? And still can have :* work?

After @Laurenz Albe answer.  phraseto_tsquery can't do this.
May be I need give more detail. Why I want to use phraseto_tsquery.
I use the
CREATE TEXT SEARCH DICTIONARY english_stem_nostop (
    Template = snowball
    , Language = english
);

CREATE TEXT SEARCH CONFIGURATION public.english_nostop ( COPY = pg_catalog.english );
ALTER TEXT SEARCH CONFIGURATION public.english_nostop
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, hword, hword_part, word WITH english_stem_nostop;

To keep stop words can be searched.
But I need to change pattern like this in python:
pattern = info["pattern"]
replace_punctuation = str.maketrans(string.punctuation, ' ' * len(string.punctuation))
pattern = pattern.translate(replace_punctuation)
replace_space = pattern.replace(" ", '<->')

You can see I replace punctuation by ' '. Then replace ' ' with <->.
I came up this question. Because I wonder can I don't transform pattern in python and just use psql to transform pattern.
Why need both ':*' and <->. here is a example:
select title from book where ts_filter(tsv_nostop, '{A}') @@ to_tsquery('english_nostop', 'how<->t'||':*')
except
select title from book where ts_filter(tsv_nostop, '{A}') @@ to_tsquery('english_nostop', 'how<->t');

This will show  title contain how to. When user input how t. We want pop up some title.


texts: Union of results of all database query to also include database name

texts:
I'm hoping someone can help.  I have a number of servers each with multiple databases on it.  All of the databases have a table I want to query with a very simple 'select count(*) from section'.  I want to get the results plus the database name all collated together (i.e. not as separate query results that I can't copy out in one quick click). So results like:

**DB Name** **Number of Sections**

Name1  10

Name2 20

Name3 11

Name4 9

Name5 20

So far I've got two queries as below:

This query gives me a list of the results as one query altogether, however it doesn't give me the DB Name that the result relates to and I can't suss out how to add it.

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section'
FROM   sys.databases
where database_id > 4
;

EXEC (@sql);

This query gives me the exact results I want but they are all separate queries so I'd have to go through and copy each one manually to collate the results.

DECLARE @command varchar(1000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? SELECT ''?'', COUNT(*) FROM section END'

EXEC sp_MSforeachdb @command

If anyone can give me an idea on how I can get the output I want it would be much appreciated.


texts: How to handle MySQL database connections/pooling from Lambda -> RDS with Node

texts:
I'm running into some problems while testing my Lambda function. I my test, I execute in parallel N executions of my Lambda function. In my lambda function, I retrieve a database connection through Knex. I was messing around with the setting in the db init pool: { min: 0, max: [1 or 5] }, which seems to fix/break things, depending on other things in my code.

Executing my lambda function 20 times outputs correct results, however, when I up it to 500 (what I am expecting to see in production will easily exceed this), things start to break. I'm running into errors such as Error: ER_CON_COUNT_ERROR: Too many connections, Error: pool is draining and cannot accept work (if I use Knex's knex.destroy() interface at the end of my lambda function). What is the correct way to handle connections and pooling for scale in AWS Lambda and RDS? Are the problems that I'm seeing on my local machine going to replicate if I were to run the same stress tests on AWS?