texts: SSRS 2016 / VS 2015 Editing text in tablix's textbox very slow in designer

texts:
Same issue as described here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f6bf89cd-75de-49f9-8a3a-743419ecd631/ssrs-2016-editing-text-in-a-textbox-very-slow-in-the-designer?forum=sqlreportingservices

But since those forums are dead as a.. well, a dead thing.. I wanted to re-ask here.

As stated, when designing a report in VS 2015 against SSRS 2016, when I have a tablix and go to edit the text in a textbox in that tablix (say, a column header one, where I'm typing something in manually instead of pulling the value from the dataset), it goes V..E..R..Y..S..L..O..W..L..Y. Every character/keystroke takes many fractions of a second, if not OVER one second. It literally took almost 5 seconds to finish typing & previewing the text 'Units' in one box.

Any help, suggestions, things I can disable or check? (And if the answer is "upgrade VS to 2017 or 2019", that is acceptable IFF you have legit real-world evidence that it helps! I'm not avoiding the upgrade entirely, I'm just not convinced that it's THE solution to this particular problem!)

I will also say, anecdotally, this doesn't seem to have always been true; it seems to have started at some point within the past year-ish, but when specifically I couldn't tell you.

Software:

  VS Pro 2015: 14.0.25431.01 Update 3
  BI Developer Extensions for VS 2015: 2.1.1 (formerly BIDS Helper)
  SQL Server Data Tools 14.0.61707.300
  SQL Server Reporting Services: 14.0.806.134
  Redgate SQL Prompt: 9.2.4.6028

System:

  Core i5-5200U @ 2.2GHz, 12 GB RAM, Samsung 850EVO SSD
  Windows 10 Enterprise v1809 / build 17753.379


texts: MySQL 5.1 - Display the error-message returned inside a stored precedure

texts:
I'm trying to find an alternative to display the error-message
returned from MySQL-5.1/5.5 inside a stored procedure.

The example below is what I'm looking for, however it's seems to be only allowed in version 5.6+ :

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
      GET DIAGNOSTICS CONDITION 1
      code = RETURNED_SQLSTATE,
      error_string = MESSAGE_TEXT;
END;

Is there any alternative to skip this obstacle and get the original error message returned by MySQL during the execution of the stored procedure?
Thanks.


texts:
I am creating a bookmark system where people can save their bookmarks. Each user has its own personal page with bookmarks. A bookmark should have atleast one tag and max five. Each user can create unlimited bookmarks / tags.

I created the following tables:

User
----
Id
Email

Bookmark
--------
Id
UserId
Title
Url

Tag
---
Id
UserId
Title
Description

TagBookmark
-----------
TagId
BookmarkId

Is this normalized? Or should I do this differently? Will this perform well with a million bookmarks?

I am also not sure if I should reuse already existing bookmarks / tags.

Example 1:

User1 and User2 both create the bookmark "http://google.com". Should I save this as one bookmark? Or save them for each user individually?

Example 2:

User1 and User2 both create the tag "search-engine". Should I save this as one tag? Or save them for each user individually?

If I do not reuse bookmarks and tags between users these tables may grow quite large.


texts: Does SQL Server 2017 Express install on Dual CPU server?

texts:
A customer wants to rent a dedicated Server for their site. They do not need more than SQL Express 2017, for the time being, but they have been offered a dual Xeon system.

Does SQL Express install and work on a dual CPU system, albeit using just one CPU, or do they have to ask for a single CPU server?

Sorry but I haven't found an answer to this specific question.


texts: Insert multiple times XML data (SQL SERVER)

texts:
I have some difficulties to solve my problem.
I have a stored procedure which take 2 input parameters.
The first one is a varchar, but in reality it's going to be a list of SomeID separated by comma like that '1,2,3,4'.
The second parameter is XML, something like :

<objectList>
    <object>
        <objectTypeId>1</objectTypeId>
        <objectId>13456</objectId>
        <mode>ADD</mode>
    </object>
    <object>
        <objectTypeId>1</objectTypeId>
        <objectId>654321</objectId>
        <mode>ADD</mode>
    </object>
</objectList>

There is no limit to the number of objects in the XML.

I created a temp table with 4 columns (SomeID,objectTypeId,objectTypeId,mode)

If I have multiple ids in my first parameter (I use a function to split my varchar) and multiple objects in my second parameter, I want the temp table to look like this:

SomeID | objectTypeId | objectTypeId | mode
-------------------------------------------
  1    |        1     |    123456    | ADD
  1    |        1     |    654321    | ADD
  2    |        1     |    123456    | ADD
  2    |        1     |    654321    | ADD
  3    |        1     |    123456    | ADD
  3    |        1     |    654321    | ADD
  4    |        1     |    123456    | ADD
  4    |        1     |    654321    | ADD

I need that all of the someIDs match with each object of my XML parameter.
I hope that I'm clear.

My actual code looks like that but it works only with multiple someIDs but only one XML object:

IF OBJECT_ID ('tempdb..#tmpObject') IS NOT NULL DROP TABLE #tmpObject

CREATE TABLE #tmpObject (tmpTagId INT, tmpObjectTypeId INT NULL, tmpObjectId INT NULL, tmpMode VARCHAR(16) NULL)

INSERT INTO #tmpObject (tmpTagId)
SELECT value FROM dbo.f_Split(@TagIdList,',')

UPDATE #tmpObject
SET tmpObjectTypeId = ParamValues.item.value('objectTypeId[1]','INT'),
    tmpObjectId = ParamValues.item.value('objectId[1]','INT'),
    tmpMode = ParamValues.item.value('mode[1]','VARCHAR(16)')
FROM @ObjectList.nodes('objectList/object') AS ParamValues(item)


texts: Not able to access Oracle manager online. Port 5500 is closed

texts:
I am using Oracle 18c XE on CentOS 7. The database installed successfully but not able to bring it online to my hostname. what I did is:

Added port 5500 in firewall:

firewall-cmd --permanent --add-port=550/tcp
firewall-cmd --reload

edited sqlplus as:

SQL> exec dbms_xdb.sethttpport(5500)
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
PL/SQL procedure successfully completed.

Then trying to access as: https://linux.domain.com:5500/em

[root@linux ~]# lsof -i :5500
COMMAND   PID   USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
tnslsnr 18295 oracle   16u  IPv6 864455      0t0  TCP *:fcp-addr-srvr1 (LISTEN)

When I check in ping.eu the port 5500 is closed

Best Regards


texts: Best practice design when expecting large data in a table

texts:
I have come across a way that kind of works for me, and was just wondering if it would have any side effects, or, if I'm just thinking about this the wrong way.

I first had this idea when I was building a relatively simple location database. It would hold a table of 249 countries, and a table of 143000 cities with their longitude and latitude.

--------------------------
-         Country        -
--------------------------
-   id  -    name        -
--------------------------
-    1  -   England      -
-    2  -    Wales       -
--------------------------

-----------------------------------------------------------
-                City                                     -
-----------------------------------------------------------
- id -     name   -      lng    -    lat     - COUNTRY_id -
-----------------------------------------------------------
-  1 -  London    -  -0.127758  - 51.507351  -    1       -
-  2 - Canterbury -   1.078909  - 51.280233  -    1       -
-----------------------------------------------------------

Now, when I was using the Haversine equation to find the closest city given a longitude and latitude, it would only do about 10 results per second. My thought behind this was because it had to do this calculation on 143000 cities ...

So, instead of go out and buy a supercomputer to do these calculations, I thought I could narrow down which cities it had to do the calculations on.

I done this by basically dividing the world into 2448 grid squares, and putting those cities in a table of their own, effectively now having 2448 tables. I then use PHP to find which grid square the given longitude and latitude resides in, and then query that table, and it's surrounding 'grid squares', or tables.

This resulted in a 10 fold speed increase, returning over 100 results per second.

I was wondering if the same concept could be used in say, a user database, where the tables may be split depending on the first 2 characters of the persons username. So, if you had 1,000,000 users, (And they were only allowed a-Z for their usernames), you could effectively have these spanned over 676 tables, averging about 1500 users per table, and then increasing the speed at which a user could log on?

Ha ... Notice the question mark at the end ...

So, I'm expecting a lot of 'Nope ... Thats just wrong' ... But I kinda want to know if my brains just having a stupid week, or if someone has seen something along these lines.


texts: How to structure table in MySQL for group chat application?

texts: I'm making a messaging application, and I need a way to organize and store messages. I was thinking something like a table that had all the group chat IDs and who was in which group chat, but MySQL doesn't have a list functionality. For the actual messages, I was thinking one large table with the group chat IDs and the message ID. To be honest, I really don't have a good way to approach this. I was even considering making a table for every group chat. How should I structure a database for a group messaging application?


texts: How output a json file in postgresql functions?

texts:
I want to define a trigger in postgresql when updated a record in a db table, It output the record as a json file:

CREATE TABLE mytable(
   id serial primary key,
   first_name varchar(40) NOT NULL,
   last_name varchar(40) NOT NULL
);

CREATE OR REPLACE FUNCTION myFunction() RETURNS trigger AS
$BODY$
BEGIN

     // My Code

     RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

CREATE TRIGGER myTrigger AFTER UPDATE ON mytable
    FOR EACH ROW EXECUTE PROCEDURE myFunction();

How can I do it?


texts: Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep?

texts:
I just created a UDF and testing it I found only works when I use this syntax

SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132)

I was expecting it to work like this:

select * from fnIsReportingTo (50,1132)

or

select fnIsReportingTo (50,1132)

here is my create SQL:

create function fnIsReportingTo
(
@BossID int,
@EmployeeID int)
RETURNs bit
AS
begin
declare @ret bit
    if exists (select * from dbo.fnGetEmployeeHierarchy(@BossID) where employeeID=@employeeID)
        select @ret=1
    else
        select @ret=0
return @ret
end


texts: Restore a database with a different name on the same server

texts:
I need a copy of a live database called Carers on the same server for testing purposes. The test database name will be Carers_Test.

I took a backup of Carers using SSMS. And tried to restore it using the GUI. When I followed the steps, the only thing I changed was the name of the database.

But the restore fails with the error:

  Exclusive access could not be obtained because the database is in use.

I do not want to do anything with the current live database, why is that matter that the database is in use?
I have tried to restore the same backup on my local host and it restored successfully. When I try to restore it on the same server with the live database then I get this error.

Please someone help me...I'm going crazy!


texts: mysqld: Can't create/write to file '/home/mysqltmp/ibXXX' Permission denied

texts:
My main volume is a 20GB SSD disk mounted on /. I have a 4TB HDD mounted in /home. So to save space I want to set the mysql temporary directory to /home/mysqltmp.

However when I set to this directory mysql won't start. I get error:

mysqld: Can't create/write to file '/home/mysqltmp/ibm7cVN0' (Errcode: 13 "Permission denied")

I checked the permission and it is correct:

# ls -ld /home/mysqltmp/
drwxrwxrwx 2 mysql mysql 4096 Apr  4 03:19 /home/mysqltmp/

Running mysqld as root it starts without problem.

Server:

# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.6 LTS
Release:    16.04
Codename:   xenial

Note: Problem arose after updating to 16.04, was working on 14.04

MySQL details:

# mysql -V
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2


texts: Where to find the installer for SQL Server Native Client 11.0.7462?

texts: I would like the exact version mentioned since my application does not support the 11.0.5058 version of the SQL Server Native Client (sqlncli).


texts: Scheduling backup jobs for an Availability Group

texts:
In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node.

The approach I have seen so far was to use the SQL Server scheduler, set up identical jobs on all running instances and introduce conditional logic into the scheduler steps in order to determine if the role is primary or secondary. This won't work for my use case for several reasons:

I want to run the scheduled action on a secondary just once, but I have multiple secondaries
I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary

The backup job consists of running BACKUP LOG [...] WITH COMPRESSION, NOINIT, NOFORMAT every 15 minutes.

Right now, I am thinking about creating a clustered scheduled task bound to the respective AG's failover cluster role but wondered if there is a more easy and streamlined way to implement this.


texts:
I have two MySQL servers on different server computers. Is it possible to execute single query like this:

INSERT INTO database1.xxxxx SELECT table1.field1, table2.field1 FROM database2.xxxxx INNER JOIN ....

EDIT: I will make cron job witch will run this once a day. I don't need synchronization.


texts: Combine duplicate rows on column1 sum column2


texts: MySQL Generated columns on non-deterministic function

texts:
ON MySQL 5.7, generated columns are not allowed with non-deterministic function.

The query that I need to optimize is executing on a bulky table.

Where clause is causing a full scan on the table, need to optimize that.

The column is DateTime type, we have to use the unix_timestamp function as per application

WHERE UNIX_TIMESTAMP(Last_Used_Time) >  1540874005

I am trying to convert UNIX_TIMESTAMP(Last_Used_Time) into a generated column but throws an error disallowed function used in the expression.

Any workaround to allow MySQL to use generated/virtual column with unix_timestamp function.


texts: Trigger with multipe IF conditions doesn't trigger

texts:
I am trying to update the table latest_activities with the latest activity a employee had.

An activity has multiple conditions to be considered as the latest activity.

The reason why I don't do a JOIN to get the latest activity from the table activity is because of performance and a sub-optimal database design I cannot change.

This is the trigger I have made. It doesn't add any records to latest_activities. I suspect the problem is that there are a difference on IF() and IF. Also I am not sure that my REPLACE INTO is correct

DELIMITER $$
CREATE TRIGGER update_latest_activity
    AFTER INSERT ON activity
    FOR EACH ROW
BEGIN
    IF NEW.log = 0
        AND (NEW.type = 'hired'
        OR (NEW.type = 'note'
        AND JSON_VALID(NEW.data) = 1
        AND NEW.data->>'$.status' = 1))
    THEN
        REPLACE INTO
            latest_activities (employee_id, activity_id)
        SELECT
            pivot.employee_id AS employee_id,
            pivot.activity_id AS activity_id
        FROM
            pivot
        WHERE
            pivot.activity_id = NEW.id
            AND pivot.type LIKE '%Employee';
    END IF;
END$$
DELIMITER ;

UPDATE

After debugging (thanks to @akina) I found that NEW.id for some reason isn't working when used in the REPLACE INTO statement pivot.activity_id = NEW.id. Hard-coding the value will work, so the query is correct.

PROBLEM SOLVED

The problem was obvious when I took a step back. Trigger should be on the pivot table because the relationship is first created there AFTER the data has been inserted into the activity table facepalm.


texts: What does the WQL Select * from SQLService return 0 rows in WBEMTest mean?

texts:
So, I've been trying to solve the blue icon question mark on my sql server  in SQL Server Management Studio by following the answer in the following link:

Blue icon with question mark - what does it mean?

after executing the above recommended answer I was still unable to resolve this question mark icon issue and to prove that it was related to the WMI I followed these steps:

In windows Start > Run > WBEMTest
Type the following under namespace \\NameOfMyServerOrMachine\root\Microsoft\SQLServer\ComputerManagement13
Click on "Query..."
Enter Query Select * from SQLService and Apply
I get 0 rows

What does this mean? what else am I missing?

This is what I have tried so far:

Added my windows user to the Distributed COM Users and Performance Monitor Users
in my sql server machine, I opened the WMI Control and gave permissions to group Performance Monitor Users so that they could have Enable Account and Remote Enable access to the namespace and subnamespaces on : \\NameOfMyServerOrMachine\root\Microsoft\SQLServer\ComputerManagement13
Verified that the WMI service is running in the sql server machine
TCP Port 135 is enabled and open in the guest machines
Windows Management Instrumentation (DCOM-In) and Windows Management Instrumentation (WMI-In)  Inbound rule is enabled in the guest machines firewall.

Any thoughts?


texts: There is a user table which have many fields actually around 120-150. All these have only user_id as the primary key. What is the best approach for the DB design. To have a single table with all these fields or spiting up to many tables. One of my main concern is if I split it into many tables will it affect the performance since it require many JOIN queries to display the data.


texts: Creating a Linked Server that Points to Itself

texts:
I am attempting to create a linked server on SQL Server 2014 instance servername\instancename using the following call:

EXEC master.dbo.sp_addlinkedserver
    @server = N'servername\instancename',
    @srvproduct=N'SQL Server'

I am getting the error:

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server 'servername\instancename' already exists.

This works fine in SQL Server 2005, and according to MSDN,

  The linked server does not have to be another instance of SQL Server,

So I'm not sure what's changed in the recent version(s) that disallows this. Using the UI generates a similar message:

  You cannot create a local SQL Server as a linked server.

I understand that it's an odd thing to request, but it's to support some legacy code that worked in 2005 (and used to be on separate instances). The documentation states that it should work, but it doesn't. Is there a way to get this to work in 2014, or am I going to have to modify the underlying code?


texts: MySQL : Conditional ORDER BY to only one column

texts:
Hello and thanks for taking time to read this question.

I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific criteria to this column. For example, to the following table, I want to ORDER BY 'group', showing first the 9,7,6 'group' items and, in the end 10,8,5 'group' items:

names     group
--------- ------
susanita  10
miguelito 5
mafalda   7
manolito  8
libertad  6
felipe    9
guille    8

thanks in advance.


texts: Can a table have multiple editioning views in Oracle RDBMS 11

texts:
Can a table ABC have multiple editioning views in Oracle Database?

I have an editioning view ABC (created from table ABC) that shows the old edition to be OLD_EDITION_1 as its edition_name in the USER_OBJECTS table. I am trying to create a new editioning view named ABC with an edition name of NEW_EDITION_2.


texts: How many IP numbers are there for a SQL server active active cluster setup also a active passive setup?

texts:
How many IP numbers are required for a SQL server active/ active cluster setup also what in the case of an active/ passive setup?
Correct me if I am wrong
2 for private network
2 for public network ......
1 for virtual server IP
1 for SAN
1 for MSdtc


texts: How to script XML Schema collection in SQL Server 2012?

texts:
I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string.

I need to do this to compare current schema collections with the new schema collections after it is regenerated.

I have no idea where to start!


texts: How can I reuse a table name after deletion (in DynamoDB)?

texts:
I am working through a tutorial which requires creating a DynamoDB table with a specific name. On my first pass at creating the table I did something out sync with the tutorial, so I deleted the table with the intention of starting over again. However now when I try to create a table with the same name I get the error "Table with that name already exists."

I could use a different name, but the tutorial is particularly long and complex, and I would prefer to stay exactly in sync.

Is it possible to reuse a table name? Do I just need to wait for the original name to be recyclable?


texts: SELECT query slow even with nonclustered Index and a full-text index

texts:
I am attempting to query between 0 and 65,000 rows from a table.
The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server.
Schema
[Id] (PK)        INT
[varchar1]  VARCHAR(4)     Normal Cardinality
[varchar2]  VARCHAR(250)   Normal Cardinality
[varchar3]  VARCHAR(250)   Normal Cardinality
[varchar4]  VARCHAR(100)   Normal Cardinality
[date1]     DATETIME       High Cardinality
[varchar5]  VARCHAR(100)   Low Cardinality
[varchar6]  VARCHAR(1000)  Normal Cardinality
[varchar7]  VARCHAR(100)   Normal Cardinality
[varchar8]  VARCHAR(20)    Normal Cardinality
[varchar9]  VARCHAR(100)   High Cardinality
[xml1]      XML            Low Cardinality

Query
The following query is part of a stored procedure (the rest of which is irrelevant since it has trivial impact on the stored procedure's performance).  The column names have been replaced with the column type and a number:
SELECT   [varchar1]
       , [varchar4]
       , [date1]
       , [varchar5]
       , [varchar6]
       , [varchar7]
       , [varchar8]
       , [varchar9]
       , [xml1]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [varchar1] = '0'
AND   ([date1] >='2014-1-1' AND [date1] <= '2017-1-1')
AND   [varchar8] = 'someText'
AND   [varchar9] LIKE '%a%'
ORDER BY [varchar1] ASC, [date1] DESC
OFFSET 0 ROWS
FETCH NEXT 65000 ROWS ONLY

Execution Plan
Execution Plan XML: https://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9
But the information it gave so far is:
Query1: Query cost (relative to the batch): 100%
                               Index Seek [NonClustered]
 SELECT            Top          [Table].[i_table_index]
Cost: 0%         Cost: 4%              Cost: 96%

I am not using a PK in this search. This query has the following durations depending on the columns removed:
All columns in select statement =   18s-27s
Without [xml1] =                     8s-11s
Without [xml1] and [varchar4] =      4s-6s
The total execution from Web to DB and back needs to be within 10s. Preferably this query needs to be within 4s.
Return Size (MB)
I am looking to get anywhere between 290-310 MB worth of data back, with a margin of error being 20 MB, for 65,000 records total.
Index
There is a clustered index on the Primary Key.
There is a non-clustered index defined as:
CREATE NONCLUSTERED INDEX [i_table_index]
ON dbo.[table] ([varchar1], [date1] DESC, [varchar8], [varchar9])
INCLUDE ([varchar4], [varchar7], [xml1]);

Unfortunately I can't really remove [xml1] or [varchar4] or I would. The index didn't seem to help much, to be honest, even though the execution plan showed it was using it.
My SQL Experience is limited to what I do with C#. I appreciate any help or guidance offered (even links), and if you need more information please let me know, I'll do my best to get it.


texts:
SQL Server 2008

I have to do database replication which is a new for me.

Goal is have mirror database on servers 2 and 3.

When we set up database replication in the Server 2, 3 from Server # 1, do I need to take DB backup for Server # 2 and Server # 3 as well?

How do I test whether my databases are running fine even on the failure of Server # 1 ?


texts: Can parameter sniffing happen in other RDBMSs, not just SQL Server?

texts:
I just found out that when googling for the parameter sniffing, SQL server is also on the recommended search list but not other RDBMS such as Oracle, MySQL, PostgreSQL. Even in this community, parameter-sniffing tag almost comes with sql-server, but not for the others.
Is parameter sniffing just a SQL Server thing? If so, why is that? If not, what is the reason that parameter sniffing is almost only linked with SQL Server?


texts: Reducing the number of tables used - mySQL

texts:
I have a table for tax implementation in my application, but I am not sure the design I have done for the table. Can someone review it?

val_taxes is the table which has all types of tax listed in the table

CREATE TABLE `val_taxes` (
  `id` int(255) NOT NULL,
  `tax_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tax_value` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `val_taxes` (`id`, `tax_name`, `tax_value`) VALUES
(1, 'SGST', '9.00'),
(2, 'CGST', '9.00'),
(3, 'Service', '1.00'),
(4, 'State', '0.25');

val_tax_group is the table where multiple tax can be grouped

CREATE TABLE `val_tax_groups` (
  `id` int(255) NOT NULL,
  `tax_group_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `val_tax_groups` (`id`, `tax_group_name`) VALUES
(2, 'GST'),
(3, 'Chennai Tax');

tax_mapping is the table for mapping the val_taxes to val_tax_groups

CREATE TABLE `tax_mapping` (
  `id` int(255) NOT NULL,
  `valtaxgroups_id` int(255) NOT NULL,
  `valtaxes_id` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tax_mapping` (`id`, `valtaxgroups_id`, `valtaxes_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4);

Given all the tables and their descriptions, is the architecture correct? Or can we reduce it down to two tables without any duplications?


texts: Start Sql Server agent when sql server starts

texts:
Is it possible to automatically start the agent when sql server starts?

When you boot the server than both windows services get started.

But when you stop the main sql server service agent also gets stopped. The problem is that the reverse is not true.  When you start the main sql server windows service again than agent wont be started automatically.
This tends to be forgotten a lot here.

Is there a way to start agent as a consequence of starting sql server?


texts:
I have migrated an old, ugly query from a 2008 SQL Server to a new 2016 instance. The query runs fine on the old database even when the query plan has to be recreated. On the new database the query just runs forever and it does not seem like it is even making it through the execution plan. I can tell from my research that CXPACKET, CXCONSUMER, AND IO_COMPLETION waits are very high and it seems like this query is spilling out into tempdb heavily.

At this point I'm pretty much resigned to rewriting the thing but I am really curious as to why it is just fine on the 2008 box. The old database has less memory and slower CPUs. Both are VMs with the same MDOP and CTP values.

Any advice or insight is appreciated.


texts: Benchmarking performance in MS Jet vs SQL Server 2014 Express

texts:
I have a client agent application that stores a replica of user metadata for the entire organization in a Jet 4.0 or earlier database. It gets accessed fairly often based on user activity so low latency is appreciated by everyone. Normally this is all well and fine, but some organizations have to store multiple orders of magnitude more metadata, which would total around 10-300 million rows, instead of 1 million or less that a typical agent uses. The Jet DB runs on each client workstation, and must operate locally on aging laptops without consistent network access. Full user data syncs get pushed to it every 24 hours or the next time it can reach the servers upstream at corp, and metadata stored while offline gets replicated back to corp whenever convenient.

I have a copy of the schema that works for the server side agent which should have not changed since it was first implemented back in the early 2000's, should have very similar data access patterns, and runs on SQL Server 2008 R2 and up. The server agent does a substantial amount of reads and writes in a RBAR fashion, so I am going to assume the same access patterns are used in both.

If I profile the activity for a few hours on the server agent to generate some queries to replay activity to benchmark, how can I run it against a Jet database? Are there testing or development utilities that can create a new Jet database and populate it with a schema and data?

I am wondering if there would be any benefit in pushing for the development team to upgrade the client database engine to one of those new SQL Server engines that get loaded up only when the application code needs it. Not sure what the best options are out there, or if there is either a useful benefit either in performance or memory consumption. The client agent code runs in an old version of Java, .Net, and C++, but I have not been able to determine which language the data access layer is written in.


texts: Selecting latest row for user grouped by day help

texts:
Hoping someone can help me here as I can't quite wrap my head around the best way to do this.

Im trying to SUM() some numbers from a JSON block, which I have working just fine, how ever there could be multiple records per day per user, and I only want to SUM the numbers of the latest record per day per user.

So, essentially using the record that matches MAX(created_at) per user, per day.

Some sample data

id  | user_id  | scan_id  | data           | created_at
1   | 1        | 100      | {"score": 40}  | 2015-11-06 22:15:27
2   | 1        | 101      | {"score": 50}  | 2015-11-06 22:18:27
3   | 3        | 102      | {"score": 20}  | 2015-11-06 22:15:27
4   | 3        | 103      | {"score": 70}  | 2015-11-06 22:12:27
5   | 5        | 104      | {"score": 40}  | 2015-11-06 22:15:27
6   | 6        | 105      | {"score": 10}  | 2015-12-06 22:15:27

In the above data,  I want to SUM the values from data->'score', but you can see the first 4 rows are from two users.  I only want to use the LATEST record in the SUM, so that would be record id's 2 and 3, but not 1 and 4 (as they were older than the other records)

Record 6, would fall under its own day as its on a different date.

So, this query works without getting the latest record,  I would like to know how to alter it to only use the latest record per user per day.

SELECT
 SUM((DATA ->> 'score')::integer) AS score,
 count(*) as count,
 created_at::date
FROM
 scores
GROUP BY created_at::date


texts: How to do aggregate functions in GROUP_CONCAT

texts:
I am generating a report to show outstanding Fee Like which Fee Type, How much amount is Fixed for that Fee Type, How Much Amount Paid for that Fee type and finally Balance Amount to be paid for that fee Type.

Here I am pulling data from 5 tables like Class Names from Classes table, Roll no & Student Name from admissions table, Fee Types from Feevars table, amount fixed for fee type from studentfees table, and finally amounts paid for fee types from fee collection table.

I am able to generate the partial results by mentioning the fee type names in select statment by summing and subtracting operations.

Here is the full database and my query producing the result. Please review  **@ my query in this demo, in the select statement I have mentioned the fee types manually.
But I want to generate the result without mentioning the Fee type as Column names.

For this I did one thing, I had taken all the fee types into a sql variable
like this

set @sqlList = null;SELECT GROUP_CONCAT(concat('tsf.', Replace(FeeName,' ',''))) INTO @sqlList FROM tbl_feevars;

this will result all the Fee types into single line as column names.
And Finally I have written code to produce output what I am expecting, but I am getting error like Error Code 1064: You have error in your sql syntax.

This is My final code

Expected Output Code

Please anyone tell me, what is error in my sql query. And suggest me if any?? other way to do this report.

My Tables:

CREATE TABLE `tbl_admission` (
  `AdmNo` varchar(50) NOT NULL,
  `Grp` varchar(45) NOT NULL,
  `Cls` varchar(45) NOT NULL,
  `rollno` int(11) NOT NULL,
  `StdNm` varchar(50)
);

CREATE TABLE `tbl_classes` (
  `C_Id` int(11),
  `C_Name` varchar(255) NOT NULL,
  `G_Id` int(11) NOT NULL,
  `status` tinyint(1)
);

CREATE TABLE `tbl_feevars` (
  `id` int(11) NOT NULL,
  `FeeName` varchar(70) NOT NULL
);

CREATE TABLE `tbl_stdfees` (
  `id` int(11),
  `Admno` varchar(50) default NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00'
  );

CREATE TABLE `tbl_feecollection` (
  `auto_id` int(11) NOT NULL auto_increment,
  `GrupNm` varchar(40) NOT NULL,
  `class` varchar(30) NOT NULL,
  `rollno` int(2) NOT NULL,
  `sadmno` int(11) NOT NULL,
  `PaM` varchar(30) NOT NULL,
  `DDCN` int(25) NOT NULL,
  `bank` varchar(30) NOT NULL,
  `cheqd` date NOT NULL,
  `remarks` varchar(150) NOT NULL,
  `pdate` date NOT NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00',
  PRIMARY KEY  (`auto_id`)
);

My current query is:

set @sqlList = null;
SET @sqlList1 = null;
set @query = null;
SELECT
  GROUP_CONCAT(concat('tsf.', Replace(FeeName,' ','')))
INTO @sqlList
FROM tbl_feevars;
SELECT
  GROUP_CONCAT(concat('tfc.', Replace(FeeName,' ','')))
INTO @sqlList1
FROM tbl_feevars;
SET @query
  = CONCAT('SELECT tsf.Admno,',@sqlList,',ta.StdNm,
                    sum(',@sqlList1,') as ''feepaid'',
                    (',@sqlList,' - sum(',@sqlList1,')) as ''outstanding fee''
                FROM
                    tbl_stdfees tsf,
                    tbl_Admission ta,
                    tbl_feecollection tfc
                where
                    tsf.admno = ta.admno
                    and tfc.sadmno = tsf.admno group by ta.stdnm);');

select @query;

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


texts: How can I reference column aliases from a projection inside the projection?

texts:
Calculations on the projection (select) values are  useful and common. Yet column aliases declared in the projection can't be used there.

select sellprice as x, cost as y, x/y as markup
from mytable

gives an error.

I can accomplish this with

select sellprice as x, cost as y, sellprice/cost as markup
from mytable

But that is a simple example: We usually declare column aliases names with as for complex cases. For example, instead of cost, I might have some  function built from average etc,  that I declare as a column alias.

How can I use these aliases inside the projection clause?


texts: Get top 1 row value from third table while joining 3 tables mssql

texts:
I am new to mssql .here I need to get some data using joins between three tables .

Header join Lines join Images --> Result

Header Table :

Line Table :

For each header record we can have multiple line items .

Images Table :

Each Image will have unique Image or multiple images .Need to get 1 image url from the list of items for the header record.

Result Set :

Query :

SELECT HT.O_ID,
       HT.Type,
       HT.Total,
       IM.Image

FROM HEADER_TABLE HT

JOIN LINE_ITEM_TABLE LIT
ON LIT.O_ID = HT.O_ID

JOIN IMAGE_TABLE IT
ON IT.IMAGE = LIT.ITEM_ID

WHERE IT.SECTION = 'Retail'

This query returns multiple rows .But I need one unique row for each Header record.

Can anyone help me to fix .


texts: Unable to search between IPv6 range when converting string IP Address to VARBINARY(16)

texts:
I am attempting to search for IPGeolocation data, which is presented by the data vendor as a start and end range for both IPv4 and IPv6. However, when I convert the IP that is provided, I am getting incorrect results.

To search by range, I am converting the VARCHAR(39) representation of the IPv6 into a VARBINARY (i.e., Hex) and then using the BETWEEN operator to see if the searched Hex value falls within a specific range.

Query logic:

SELECT CONVERT(VARBINARY(16), '2600:8800:6a06:2000:1d29:3b7:8c7c:271b')

Search:

IP: 2600:8800:6a06:2000:1d29:3b7:8c7c:271b

IpHex: 0x323630303A383830303A366130363A32

Result 1

IpStart: 2600:8800:490:0:0:0:0:0

IpStartHex: 0x323630303A383830303A3439303A303A

IpEnd: 2600:8800:77f:ffff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A3737663A6666

Result 2

IpStart: 2600:8800:6a06:1d01:0:0:0:0

IpStartHex: 0x323630303A383830303A366130363A31

IpEnd: 2600:8800:6a06:24ff:ffff:ffff:ffff:ffff

IpEndHex: 0x323630303A383830303A366130363A32


texts: MySQL Still purging binary logs and ignoring expire_logs_days

texts:
We are running mysql v5.6.17 and I cannot find if there is something else that is causing the binary logs to be purged other than the following:

Adding expire_logs_days to the my.ini (and confirming it is set on the server via show global variables like 'expire%';)
Manually running PURGE BINARY LOGS

There is a nightly backup that backs up the main database, compresses it using 7zip and then FTPs it to another server. I found that the binary logs are purged every night to the binary log which was always created at 00:00 midnight. This is an issue as there is a replication machine running which uses GTIDs and it cannot find the binary logs on the master which are specified by the gtid_purged.

I created 2 events to troubleshoot this. The first event to turn on general logging into a table during this period where it was being purged, and a second to turn general logging off a few hours later. The backup starts at 00:05 and the purge is issued at around 00:07

It seems the PURGE MASTER LOGS TO command was being run by root[root] @ localhost [::1].

There is no one connecting to the server that would run this.
As far as I understand the --delete-master-logs in mysqldump command is similar to RESET MASTER, but is not the same as PURGE BINARY LOGS. We do not use --delete-master-logs however.

What could be causing the purge?


texts: why does datetime not change in this dbfiddle?

texts:
dbfiddle

Code:

CREATE TABLE mytable
(id_log int, dt datetime);

INSERT INTO mytable VALUES
    (1,getdate());
INSERT INTO mytable select id_log+1,dateadd(HH,2,getdate()) from mytable;

INSERT INTO mytable select id_log+(select max(id_log) from mytable),dateadd(HH,id_log+10,dt) from mytable;

INSERT INTO mytable select id_log+(select max(id_log) from mytable),dateadd(HH,id_log+10,dt) from mytable;

select
  *
from mytable;

output:

id_log  dt
1       2020-01-06 18:56:29
2       2020-01-06 18:56:29
3       2020-01-06 18:56:29
4       2020-01-06 18:56:29
5       2020-01-06 18:56:29
6       2020-01-06 18:56:29
7       2020-01-06 18:56:29
8       2020-01-06 18:56:29

Expectation was that the datetime increased by several hours...


texts: Create a function to generate a column value in a specific format

texts:
I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, each of the registered users should be assigned a userId like K16060600, the format being Kyymmddxx, where

K is constant for every userId,
yy stands for year (e.g. 16 for 2016),
mm stands for month,
dd stands for day,
xx is a number for 00 to 99 and it should increment from 00 whenever a new row/user is added.
It is guaranteed that I will add fewer than 100 each day.

How can I do that?

I'm using Visual Studio 2012 and SQL Server 2012.


texts: Disable auto-rehash when connecting to mysql via jdbc?

texts:
Is there a way to specify equivalent of no-auto-rehash when connecting to mysql via jdbc?
I am trying to connect to a large db and the initialization is slow.  If I connect without jdbc and specify -A, it's instant.  I don't see an equivalent option described in the JDBC docs.


texts: Postgres not using index when expression in WHERE condition

texts:
The table my_table has an index on the column timestamp.

The following query makes use of this index:

explain select
  item_id,
  timestamp
  position
from my_table t
where t.timestamp > '2020-05-05'::timestamp

QUERY PLAN
(...)
->  Index Scan using (..) on "my_table" t  (cost=0.57..641762.36 rows=926839 width=52)

But this other query doesn't — almost identical but the WHERE condition depends on an expression:

explain select
  item_id,
  timestamp
  position
from my_table t
where t.timestamp > (select max(timestamp) from other_table)

QUERY PLAN
(...)
->  Seq Scan on "my_table" t  (cost=0.00..33523870.16 rows=343386904 width=52)

What  I'm getting trouble understanding is, once the select max() subquery has been executed, the max(timestamp) value is known and fixed, so how comes Postgres is unable to make an index scan based on this (now known) value? I don't see how it makes sense.

More importantly, how to circumvent this so that Postgres does use the index on my_table.timestamp? Is there a way around this?


texts:
I’ve accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.

I know that SQL Server can’t rollback already committed transaction but I was wondering if there are any alternative ways to get the rest of the data back. Any help such as some uncommon sql scrpts or anything like that would be greatly appreciated.


texts: SELECT COALESCE with subselects is throwing error 'Operand should contain 1 column'

texts:
I made this query:

    SELECT
        COALESCE(
                 (SELECT *
                  FROM JournalEntryTitleAndContent jetc
                  WHERE jetc.language_id = 2
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT *
                  FROM JournalEntryTitleAndContent jetc
                  WHERE jetc.language_id = 1
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT *
                  FROM JournalEntryTitleAndContent jetc
                  WHERE jetc.journalentry_id = 1 LIMIT 1)
                 )

Its purpose is to check if there is a row present with language_id 2 (thats the preferred language). If that is not present then the coalesce should try to find the row with the fallbacklanguage with id 1, if that returns null as well then it should return the first row it can find where journalentry_id = 1.

When I execute this statement in workbench i get Error Code 1241: Operand should contain 1 column(s)

Can i not return a row as a single value from coalesce? If not what should i use instead then? Should i use CASE or IFNULL() instead?

Thank you


texts: why is my Database Engine Tuning Advisor trying to create statistics, even when I'm using "auto create/update statistics"

texts:
I made everything I can in some procedures and queries to tune some processes. So I'm using tuning advisor to see if I missed something, and to my surprise, there's a lot of statistics to be created ( Create statistics...).

But my database is set to auto create and update statistics. why is tunning advisor creating the? why sql server is not creating them during the execution of these queries/procedures?


texts: SQL Server shrink log after full and log backup

texts:
I have an SQL Server database in Full recovery model. The data file is just 1 GB in size, but the log file is 20 GB.

I have a space problem, so I took a full backup and then a log backup with truncate option. Is correct now to shrink the log file? Does anything speak against doing that?

This is a production DB that is supposed to be in full recovery model. Til now no one implemented a backup strategy. The log became very big and they are running out of space. I took a full backup and then a log backup with truncate option using SSMS, in order to avoid the log file increase further. Now I thought that I can schedule daily a full backup and every hour a log backup to avoid that the log file increase over 2Gb. I'm not sure if after I took full + log backup is it possible to shrink the log?

The idea is to reduce the log file from 20Gb to 1 or 2 Gb because the server is running out of space and then take a daily full backup and every hour a log backup to avoid the log file increase too much.


texts:
I’ve searched and read wide about the good practices of configuring swap space for Oracle Database installation. I am aware that for 1-2GB RAM, swap has to be 1.5x. For between 2GB and 8GB, swap needs to be equal to SGA. Above 8GB, swap will be twice SGA size (I stand to be corrected).

However, just yesterday, someone introduced something that got me confused. He said, “For Oracle Database or Oracle RAC on systems, available swap space must be at least equal to the sum of the SGA sizes of all instances running on the servers.”

I am a bit confused because I am aware that even though it is a RAC environment, each server has its OS and maintains its internal memory separately hence, the swap for each serverdoesn’t have to be equal to the sum of SGAs as indicated by my colleague.

Can anyone please help me understand what the correct value should be?


texts:
As far as I know, SQL Server holds X locks for the duration of a transaction no matter which isolation level is in use. What about the U lock? If a transaction reads 100 records in an UPDATE statement (U locks) but updates 10 records only, will we end up with 90-U and 10-X after the update is done and if the tran is still open?

In response to David's answer below: No those situations are not related (thanks for that answer!). I cannot post the code as I have no my PC at hand, but I will try to describe this case in a nutshell. I have a heap. I update it, there are 10 records updated. Q: will we have any U locks at the ... point or are they released once X locks are obtained? Thanks!

Begin tran;

Update t
Set b = 10
Where a = 1;
...


texts: Preventing users who are dbo from dropping their own databases

texts:
We allow our users to be dbo in their own databases, however this does give them the ability to drop their own databases. This doesn't happen very often, but I want to prevent them from doing this.

I've devised the following trigger that prevents this happening and it restricts the dropping of databases to sysadmin role members:

CREATE TRIGGER [deny_customer_db_drop]
ON ALL SERVER
FOR DROP_database
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
    PRINT 'You are not permitted to drop this database'
    ROLLBACK
END
GO

This works, but I'm wondering if there's a more elegant way to do this using permissions without tweaking each login's own specific permissions?


texts: How do I create databases / logins / users with minimal privileges when installing an application?

texts:
I have an application installation package that needs to

Create 1 or more databases (current process is to restore the database from a .BAK file made from a template that is adjusted periodically)
Create SQL Server logins for the server (two SQL logins a MyAdmin and MyUser account)
Create SQL Server users for each database (one for each SQL login)

At the moment I do this within the installation package by asking for SA credentials. I am trying to reduce the privileges required to install the application, since I am (understandably) getting pushback from DBAs who don't want to provide SA credentials to my installation package.

I have tried, prior to installation, creating a SQL Login with:

exec sp_addsrvrolemember 'MyAdmin', 'diskadmin'
-- FYI the MyAdmin account will run backup/restore/duplicate commands
grant create any database to MyAdmin
grant alter any database to MyAdmin

If I do this I can still successfully create the database by restoring from .BAK as MyAdmin, but when I try to use the database (so I can create users in [New Database]) I get an error.

use [New Database]

generates

The server principal "MyAdmin" is not able to access the database "New Database"
under the current security context

Something tells me that I am taking the wrong approach. Should I be creating the database with a script instead of restoring from a .BAK? Can I use an account with less privileges than SA to execute all of these things, and if so what privileges would this account require? Should I remove these things from the installer package and have a separate script that can be run under the SA or another account that has the required privileges?

What I am looking for is a process that will create what I need with minimal privileges. If possible, I would like to minimise the changes required to the process I am currently using.


texts: Performance of database with transactional data

texts:
There are lots of row modifications (several thousands of rows per minute) in our PostgreSQL database that stores transactional data. We have a problem because PostgreSQL runs vacuuming process and this very slow down our database performance. I am looking for the most efficient
solution how to solve this problem. Could be a solution some of these points?:

Separate these transactional data into another PostgreSQL database?
Change PostgreSQL for MySQL for better performance?


texts: Reaching SQL Server AlwaysOn HA listener from the Internet

texts:
I've successfully created a SQL Server AlwaysOn High Availability Group between two servers and everything seems to be working great internally. Each replica is on a separate physical location, behind different firewalls and public IPs, as well as private IPs/subnets. Right now they are able to communicate directly with each other through a site-to-site VPN.

The application that I use to access the database is able to connect to the listener's DNS. Both Failover Cluster nodes and SQL replicas have private IPs so as long as I run the application within my network, I'm OK.

The issue that I have now is that I need the application to be able from anywhere in the world. Basically, I need to be able to connect to the listener from the Internet without having to VPN in and still be able to take advantage of the HA//Failover capabilities.

How can this be accomplished? The only documentation that seems to be posted online relates to Azure which doesn't apply to my setup.

Thank you!


texts: SQL User Entitlements - Combining Multiple IDs

texts:
New to SQL, first time poster.  I searched a bit for what I was looking for.  Couldn't locate.  I appreciate your help...I'm still learning terms and commands.

Consider this:

User XYZ has access to BLAH table with GRANT permission to EXECUTE
User ABC has access to OKAY table with GRANT permission to SELECT

Question(s):

Is there an SQL query I can write that will take all the Securables of user ABC and add them into user XYZ?  If yes, is there any reason why the same query would not work on Roles?

The end result with this scenario would look like this...

User XYZ has access to BLAH table with GRANT permission to EXECUTE
User XYZ has access to OKAY table with GRANT permission to SELECT
User ABC has access to OKAY table with GRANT permission to SELECT

Things of Note:

This is a very small example.  I realize I could just manually add what I am talking about in this scenario.

I was toying around with the ALTER USER (or ALTER ROLE) statements.  I couldn't find the appropriate syntax.  When I would do an internet search I could never seem to locate what I was looking for.  I blame it on my newbie status. :)


texts: Server 2012 Linked Server From An Instance

texts:
I have 3 SQL Server 2012 Instances, 2 on SiteA on the same server SiteAServer.  I have 1 on SiteB on server SiteBServer.

SiteADefaultInstance, SiteASecondInstance

SiteBDefaultInstance

SiteBDefaultInstance can create a linked server to
SiteADefaultInstance and SiteASecondInstance.
SiteADefaultInstance can create a linked server to
SiteBDefaultInstance.
SiteASecondInstance CANNOT create a linked server to
SiteBDefaultInstance.

I get the error

  "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON"

I am setting the security to be made using the current context.

I have ran setSPN for SiteAServer and SiteBServer.  You don't have to change the commands if you have more than one instance do you?
I am in the role sysadmin.

I have never experienced where it only worked one way before.  Any suggestions where to look?


texts: SQL Server 2016: create credential and add SQL Server Agent proxy for it

texts:
I'm trying to script a creation of SQL Server identity to be then used to execute SQL Server agent jobs via a proxy.

It looks as though I can only use a Windows account and thence I would have to provide its password in plain text. Seriously? There must be a better way to do this. I need this script to work on my team-mates' machines as well as mine:

USE [msdb]

CREATE LOGIN [proxy_login] WITH PASSWORD=N'passw0rd',
    DEFAULT_DATABASE=[SSISConfig], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE CREDENTIAL [my_cred] WITH IDENTITY='proxy_login', SECRET='passw0rd'

EXEC msdb.dbo.sp_add_proxy
    @proxy_name=N'My_Proxy',
    @credential_name=N'my_cred',
    @enabled=1

Error:

  Msg 14720, Level 16, State 1, Procedure sp_verify_credential_identifiers, Line 69 [Batch Start Line 0]
  The operation failed because credential '@credential_name' identity is not a valid Windows account

In ideal world I would like to use the SYSTEM_USER login for the credential without having to supply their password.


texts: Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme

texts:
I'd like to know if it is better to use a RANGE RIGHT partition scheme or a RANGE LEFT partition scheme?

Is one of these options by default a better choice, or is it heavily dependent on the actual data?

Once you've already used a RANGE LEFT, how difficult is it to switch to a RANGE RIGHT? Or is it easier to just add in splits in the current scheme?


texts: Possible to have nested inserts in Postgres 8.4?

texts:
I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity):

create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other');

create table host (
  id serial primary key,
  hostname varchar(40),
  role hostrole
);

create table interface (
  id serial primary key,
  name varchar(20),
  host int references host(id) on delete cascade,
  mac macaddr
);

I know I can nest a select into an insert statement, my question is whether I can nest an insert into an insert (and if so, how to do so). Here's the statement I'm trying to run:

insert into interface
  (name,
   mac,
   host)
     values
  ('eth0',
   '00:50:56:9d:34:d4',
   (insert into host (hostname, hostrole) values ('foobar', 'Virtual') returning id)
);

The error comes up at the "into" of the nested insert. Can I do this in one shot, or do I have to make separate queries to do this insert?


texts: Why got pending status when use createuser command with PostgreSQL?

texts:
Using PostgreSQL 10 on RHEL 7.7.
Start service and login to postgres user:
$ sudo su - postgres
-bash-4.2$

Create a user
-bash-4.2$ createuser -s repmgr

It will pending and wait for a long time can't stop.
Without error from terminal. What's the reason?


texts: how to overcome these type of issues where null value inserted into primary key not null column in table


texts: I have a database which takes up 500gb, I have noticed that my ple is low even though I have 100gb of RAM. I have searched in google to find reasons of that and I found some interesting queries. I have a query which runs on very big table even though it uses clustered index it causes ple to drop. I checked  what is in buffer pool before and after query and moment after query is done and it looks like DB has to free some space for index used in my query and after query old indexes are loaded to the buffer pool. My question is how much RAM memory should DB have, how could I calculate it, should it be amount which will let me keep all used indexes in memory at once? Or maybe I should reduce data kept in tables so indexes will be smaller?


texts: Can Oracle SQL developer export data as a set of Update commands?

texts:
Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements.

I have two databases with a mirrored table. One of them has some column data missing from it. I tried truncating the data from that table but it has foreign keys associated and SQL doesn't let me drop it.

Is there a way to export the data from the larger/fuller table in the form of "Update Table..." commands? I tried googling first, failed. I bet there's a solution for this out there.

If nothing else, I am thinking of making a small python script that modifies the Insert command set into an Update command set after export.


texts: pgAdmin goes "out of memory" with large queries

texts:
I am trying to run query with results stored in file from PostgreSQL. Now I am trying to use pgAdmin for this task: running query to file option.
Problem starts with large queries. When I try running query which result set is around 3GB pgAdmin returns "out of memory" error.

Any idea how to extend pgAdmin memory pool? (I have still few gigs of unused memory on machine.)
Or maybe there is another way I can store large result set in a csv file?

I am using pgAdmin 1.16 with PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 32-bit.


texts:
I have a server on Azure with a MySQL database, we have two apps there and sometimes we get this error on the code:

Warning: mysqli::query(): (HY000/1194):
Table 'noticia' is marked as crashed and should be repaired
in D:\home\site\wwwroot\classes\ConnectionFactory.php on line 122

Some questions:

What kind of things could cause this error?
Where can I find the logs in order to get the cause of the error?
Could this be could a PHP problem?

This is too strange because I have been using the same ConnectionFactory class to connect to the database for several year and this is the first time that we get this error.


texts:
I'm quite new to SQL Server and some employee asked me if is possible to make monthly database backups.

We do daily full backups to NAS server via Cobian and we use SQL Server Management Studio to make maintenance plan for daily full backup as well.

My problem is that the database that I want to make the monthly backup is used by a software that is not developed by us so I have no clue how to make queries to extract data from date types, etc. The database has a lot of tables.

All I want is to extract data from January only and make a backup with January data only so i can send to external company. Is that even possible without T-SQL queries?


texts:
I have a User table having following fields:

id, name, phone, email

and an Order table:

id, order_name, order_desc, user_id (fk to User)

and a Lead table:

id, lead_desc, user_id (fk to User), lead_status

Now, I want to know for each lead (once the lead_status become closed), which order is created from this lead or no order is created from this lead. I am planning to have an order_id (with null=True) foreign key in Lead table to keep this mapping. But since Order table already have a user_id, it doesn't seem to be a good idea.

Is there a better way to do it? I am using Mysql.


texts:
Using mysql 8 on windows 10
MySql replication master-slave using GTID based(binlog-format = mixed;)        . mysqlreplicate ,mysqlrplcheck,mysqlrplshow,mysqlrpladmin commands are ok but mysqlfailover command got
ERROR: Query failed. 1694 (HY000): Cannot modify @@session.sql_log_bin inside a transaction.

How do you fix this problem?


texts: Language setting for 'TZD' format in TO_CHAR (timestamp with time zone)

texts:
Which setting controls the language of 'TZD' or 'TZR' in the TO_CHAR(timestamp with time zone) function?
e.g.
select to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD') from dual;
returns the English value for TZD "MDT" (Mountain Daylight Time), which is expected as the default value.
However, I can't find which parameter or setting to use for it to return the French value of TZD, i.e. "HNR"

I've tried adding ", 'nls_date_language=FRENCH'" to the TO_CHAR example above, as well as altering the session parameters NLS_LANG and NLS_DATE_LANGUAGE, but to no avail.


texts: How to find mongo document by ObjectID age

texts:
I have a collection of documents I'd like to pull a subset created after a certain point in time.  I understand the timestamp of creation is encoded in each documents ObjectID (assuming they are auto generated).  I see the ObjectId has a getTimestamp method that returns that portion of the ObjectID as an ISOdate.

I'm not very fluent in mongo and am having trouble constructing this seemingly simple query.

For bonus points,  once I figure out the "where clause", if you will, I'm wanting to select a single field from the documents using mongodump or what ever else might be available to export the results to a text file via a mongo shell.


texts:
I've imported a database into SSDT containing a reference to a system view (specifically, sys.columns). The problem is, that I then get warnings about unresolved references when I build the project

From what I've seen on MSDN forums, it looks like it may be a known issue: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/5a7026bd-0602-42e6-a639-d73bed903c26

Now, I know I can switch the warning off or ignore it, but does anyone know of an actual solution?

Thanks


texts: Identify MySQL create version from data files

texts: Someone has given me a copy of a database full of FRM, MYD, and MYI files.  Trying to open it in my newer MySQL version gives an error that I need to run mysql_upgrade.  I don't want to change the files at all, so I want to install the same version of MySQL that was used to create the database.  Is there some way to identify the creation version from looking at these files?


texts: Minimizing Indexed Reads with Complex Criteria

texts:
I'm optimizing a Firebird 2.5 database of work tickets.  They're stored in a table declared as such:

CREATE TABLE TICKETS (
  TICKET_ID id PRIMARY KEY,
  JOB_ID id,
  ACTION_ID id,
  STATUS str256 DEFAULT 'Pending'
);

I generally want to find the first ticket that hasn't been processed and is in Pending status.

My processing loop would be:

Retrieve 1st Ticket where Pending
Do work with Ticket.
Update Ticket Status => Complete
Repeat.

Nothing too fancy.  If I'm watching the database while this loop runs I see the number of indexed reads climbs for each iteration.  The performance doesn't seem to degrade terribly that I can tell, but the machine I'm testing on is pretty quick.  However, I've received reports of performance degradation over time from some of my users.

I've got an index on Status, but it still seems like it scans down the Ticket_Id column each iteration.  It seems like I'm overlooking something, but I'm not sure what.  Is the climbing number of indexed reads for something like this expected, or is the index misbehaving in some way?

-- Edits for comments --

In Firebird you limit row retrieval like:

Select First 1
  Job_ID, Ticket_Id
From
  Tickets
Where
  Status = 'Pending'

So when I say "first", I'm just asking it for a limited record set where Status = 'Pending'.


texts:
I have enabled slow query log on Amazon RDS with following configuration.

long_query_time = 0.5
general_log     = 0
log_output      = TABLE
min_examined_row_limit = 1000
slow_query_log = 1
log_queries_not_using_indexes = 1

to read slow query log , I am using following command from mysql workbench.

select * from mysql.slow_log where db = 'my_database' order by start_time desc;

but when I have seen log table, it shows me lots of queries used by mysql itself. like it shows me "performance_schema" related queries too. I want to filter it with my query only. does It has any option for this?


texts:
In Postgres, does this query lock films for reads and/or writes?

CREATE TABLE films2 AS
  TABLE films;


texts:
There are several alternatives to using polymorphic associations and one of them is to use the so-called "concrete supertable". From my point of view, this is the neatest solution and is pretty easy to implement so I use it almost always. But now consider the following schema as an example:

We have statuses, photos, videos, links, and albums. All of them share pretty much the same attributes, let's say: author_id, owner_id, and description/body. They also share most of its associations: can be commented, voted, shared...and so on.

So it makes sense to me to have a supertable "posts" containing the shared attributes between tables. Furthermore, its associated tables can use the post_id as its foreign key, tidy!

The problem I see here is that almost every query to the children tables implies querying the parent supertable, e.g: create a status implies create a post, update a photo implies update its post, select a user statuses implies joining on the post table, an so on.

Considering a system with a lot of concurrency, isn't this going to produce a bottleneck?

EDIT: I think I have a better question that resumes the background of my first question: There's a performance difference between concurrently querying two different tables and querying the same table (but different rows of this table)?


texts:
I'm running MongoDB (version 2.4) behind a Django application served by Apache.

In the past few days, I'm seeing the following error come up many times an hour in the Apache logs, on all sorts of different requests:

AutoReconnect: not master and slaveOk=false

I did not explicitly create a replica, and to the best of my knowledge am not running one.  rs.status() says that we are not running with --replset.

Mongo is run with:

'mongod --dbpath /srv/db/mongodb/ --fork --logpath /var/log/mongodb.log --logappend --auth'

There is one mongod process running on the server.

What's going on here?


texts: Grabbing data from a query with specific criteria, then the data around it

texts:
The title may be slightly vague... however, this is the basis of what I want to do:
I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to lowest, which I can already do, but this is where I run into an issue.

I would like to then have said query find a specific bit of data, a row with a specific 'Name' as defined in the query, then get 5 rows that would be above it, and 5 rows that would be below it. It would also be helpful if it does this in a timely fashion. If you need more detail, just ask!

For anyone wondering what research I've done, I've found queries that will do this, however those queries use full table scans, which would not be optimal in this case as there is 123k+ rows of data. Also, this is in mysql.

My current query that does a full table scan and doesn't work extremely well, nor effeciently, in general:

SELECT    allusers.rank, allusers.Name, allusers.Kills
FROM (
 SELECT   AgStats.Name,
        @t:=IF(score<=>@s,@t,@r) AS rank,
        @s:=AgStats.Kills AS Kills,
        @r:=@r+1
 FROM     AgStats, (SELECT @r:=0, @s:=NULL, @t:=NULL) init
 ORDER BY AgStats.Kills DESC
) allusers, (
 SELECT COUNT(*) AS rank
FROM   AgStats
WHERE  Kills > (SELECT Kills FROM AgStats WHERE Name = 'VoidWhisperer')
) user
WHERE    ABS(allusers.rank-user.rank) <= 5
ORDER BY allusers.rank

To give an example of what I'm trying to do: You have 10 rows of data, with the row kills on the end. I want to select a specific row out of those 10 based on name, then get a number of rows above it, in this case, 5 rows above/below, done via the query?


texts: MySQL: How to ignore /etc/mysql/my.cnf for mysqld --initialize

texts:
I compile a 5.7 MySQL database from source into a custom directory and use mysqld --initialize to initialize the database.

This works fine as long as there is no MySQL installed on the main system, or when I use mv /etc/mysql/my.cnf /etc/mysql/my.cnf_backup to get rid of the system-wide file, but of course that is a terrible workaround that breaks the system-wide mysql installation.

If a /etc/mysql/my.cnf is present, the initialization fails obviously as the paths are all wrong.

mysqld --initialize always seems to prefer /etc/mysql/my.cnf and ignores any file I specify on the command line with --defaults-file. It does however use single parameters that are specified on the command line (so it may work if I translate my complete my.cnf file into command line arguments, but that again is not a good solution and creates other problems)

Of course the whole point why I build from source into a custom directory is because I want it independent from any things that are installed on the main system.

Is there a way to let mysqld --initialize ignore /etc/mysql/my.cnf?


texts: Can I manually reset the "internal status flag for a full database backup"?

texts:
We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.

This worked last weekend, however this weekend the differential backup failed saying that

  Message

  Executed as user: username. ... 9.00.3042.00 for 32-bit
  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
  Started:  12:30:00 AM
  Progress: 2013-01-21 00:30:01.69
  Source:
  {450389BA-54C2-4892-9CD0-0126CA9B0ED8}      Executing query "DECLARE
  @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete
  End
  Progress
  Error: 2013-01-21 00:30:02.11     Code: 0xC002F210
  Source: Back Up Database (Differential) Execute SQL Task
  Description: Executing the query "BACKUP DATABASE [MyDatabase] TO
  DISK = N'E:\Database Backups\MyDatabase_backup_201301210030.bak' WITH
  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME =
  N'MyDatabase_backup_20130121003002', SKIP, REWIND, NOUNLOAD,  STATS =
  10  " failed with the following error: "Cannot perform a differential
  backup for database "MyDatabase", because a current database backup
  does not exist. Perform a full database backup by reissuing BACKUP
  DATABASE, omitting the WITH DIFFERENTIAL option.   BAC...  The package
  execution fa...  The step failed.

The job that runs the full database backup ran successfully, and I can view the backup using the query found here, so I am not sure why it is telling me "a current database backup does not exist".

I found this MSDN page which suggests that the cause of the problem is

  This problem may occur when the internal status flag for a full
  database backup does not reset after you create the full database
  backup.

I'm not able to install the hotfix right away, and doing another full database backup is not an option right now, so I was looking to try manually resetting the internal status flag for a full database backup.

Is there a way to manually reset this flag so that differentials can successfully be ran for the rest of the week? Or to set the base backup to an existing full backup that exists in msdb.dbo.BackupSet/msdb.dbo.BackupMediaFamily?

I'm using SQL Server 2005


texts: error 1067 process terminated unexpectedly

texts:
I am facing the following error while creating the Mysql instance from command line in Windows server 2012.

Steps i follwed for creating a new Mysql Instance as a window service:

I have stopped the existing Mysql service (Mysqld) using mysqladmin.exe -u root -p shutdown.

I  havecopied the contents of Mysql data folder  located at C:\Programdata\Mysql\Mysql Server 5.6\data to the data directory location on new instacnce which is locate at G:\MYSQL.

And i have created a my.ini file with following lines

client
[Mysqld]

Server
[Mysqld]
port=3306
basedir="C:\Program Files\Mysql\Mysql Server 5.6\"
datadir="G:\Mysql\"
server_id=1

Now from CMD i ran the following command to create the Mysql instance as window service

mysqld.exe --install mysqld  --defaults-file="G:\Mysql\my.ini"

the service has been created Sucessfully

But i am getting an error while i am trying to start the Service as

Net start Mysqld from CMD.

System Error Occured

Error 1067 Process Terminated Unexpectdely

Any help can be Appreciated.

Thanks & Regards

Venkat


texts: We are planning to setup non-sharded MongoDB Instance on Amazon AWS. As per my understanding we need MongoD setup on Primary and Secondary to forms as a replica set. Please advice if we need MongoS,Config Server & Arbiters


texts: TimescaleDB restore is not working properly

texts:
I did the docker volume restore from a tar file, that worked fine. The data size is around 50GB.  When we run docker I was getting below error:
2021-03-19 07:07:17.117 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-03-19 07:07:17.117 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-03-19 07:07:17.119 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-03-19 07:07:17.159 UTC [20] LOG:  database system was interrupted; last known up at 2021-03-18 19:03:38 UTC
2021-03-19 07:07:17.410 UTC [20] LOG:  invalid primary checkpoint record
2021-03-19 07:07:17.410 UTC [20] PANIC:  could not locate a valid checkpoint record
2021-03-19 07:07:17.618 UTC [1] LOG:  startup process (PID 20) was terminated by signal 6
2021-03-19 07:07:17.619 UTC [1] LOG:  aborting startup due to startup process failure
2021-03-19 07:07:17.629 UTC [1] LOG:  database system is shut down

I found below solution on a blog, and that did the trick and above issue was resolved.
bash-5.0# su - postgres
e7e644cb6382:~$ pg_resetwal -D /var/lib/postgres/data -f
Write-ahead log reset

But when I try to run the docker again, I get below errors.
2021-03-19 07:14:59.721 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-03-19 07:14:59.721 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-03-19 07:14:59.722 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-03-19 07:14:59.764 UTC [21] LOG:  database system was shut down at 2021-03-19 07:09:16 UTC
2021-03-19 07:14:59.765 UTC [21] FATAL:  could not access status of transaction 340306081
2021-03-19 07:14:59.765 UTC [21] DETAIL:  Could not read from file "pg_xact/0144" at offset 139264: No error information.
2021-03-19 07:14:59.765 UTC [1] LOG:  startup process (PID 21) exited with exit code 1
2021-03-19 07:14:59.765 UTC [1] LOG:  aborting startup due to startup process failure
2021-03-19 07:14:59.780 UTC [1] LOG:  database system is shut down

I could not find any way to resolve it. Not sure what's the problem?


texts: Change Data Capture missing changes for a specific record

texts:
I have enabled CDC in SQL Server and it generally works well but sometimes the changes are not recorded for some reason. And only for a couple of records. But then if I issue an UPDATE from SSMS, the change is recorded.

I'm wondering if it might have something to do with the SQL Server Agent jobs; maybe those jobs fail and because of that the change is not saved?

I have checked the job history in SQL Agent (for cdc.myDatabase_capture and cdc.myDatabase_cleanup) but couldn't find any failures.

What should I investigate to find the cause of the issue?

SQL Server Details:

 Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
    Jun 17 2011 00:54:03
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
 (Hypervisor)


texts: My stored procedure randomly fails due to invalid object in a swapping database instance

texts:
I have a stored procedure in my database (say it is CustomerDB) which joins with a table function in a different database (say it is dbPerformance), the sp is something like:

select something from CustomerDB.dbo.CustoemrData left join
                dbPerformance.dbo.FnPerformanceDayEnd(CustoemrData.Code, 1,1)

The dbPerformance database contains quite a lot of data and to update the data, DBA actually first populate the new data into a db called dbPerformanceNew, when the new database is ready, it will restore the dbPerformance database from dbPerformanceNew.

Randomly I see error from such as

  Invalid object name 'dbPerformance.dbo.FnPerformanceDayEnd' from my sp. The timing does related to the time that the old database dbPerformance was being restored from dbPerformanceNew.

My confusion is that when the restore happens, it already has all the data and all the functions. I thought at least I should see error like dbPerformance database is unavailable (as the dbPerformanceNew database is being restored), other than some objects are not available.


texts: High CPU Usage on certain query when changing DB Compatibility Level

texts:
We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point.
We are looking to make use of memory optimization introduced in SQL 2016 and that requires setting the compatibility level to 130.
We have had a few instances where a certain query will max out the CPU (that normally only takes a few seconds to run on Compatibility Lvl 100, but when we change it to Level 130 the CPU maxes out and does takes 2 minutes and 37 seconds to run  vs about 2 seconds on the old compatibility level)

I did pull the execution plan for each of these
CompatibilityLvl100 Plan
CompatibilityLvl130 Plan
I am not a DBA so I can see there are differences between the execution plans but I could not see any reason for it to max out the CPU and take almost 6000% longer to run the same query with the same indexes etc.
I also executed this query to get some more details about the running query, I have uploaded the results as a comparison to a Google Sheet here.
One thing i noticed is that the slow running query was using 192 worker threads, whereas the fast running query only used 32.  But I still couldn't figure out what was causing the high CPU usage.
Be glad of anyone's help or direction of something I may have missed!


texts:
Does PostgreSQL have a built in option for converting multi-line text to single line?

I need to store multiline text as Name=Value pairs, basically line .ini files, but need to convert Value back to its original form. Value might contain some escape sequences etc.

The multiline text is not that short and could be upto 65K in length.

Postgres does is capable of exporting multi-line text as single lines when it backups a table in plain format without using SQL inserts. It is the type of text passed to the COPY tablename (column1, column2, columnxx)from stdin when the plain text backup is views.

Is the function that does the conversion standalone?

I tried quote_literal but it didn't come out the way I want.


texts: How can we get the table and the column name of known data value?

texts: I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.


texts: Does SQL Server 2016 Standard Edition support table partitioning?

texts:
I want to upgrade my SQL Server 2008 Enterprise Edition to SQL Server 2016 Standard Edition; however, one database makes use of table partitions over multiple file groups (used on a large logging table, each day is a partition)

I see in Editions and Supported Features for SQL Server 2016 under the section "RDBMS Scalability and Performance", that it says that Standard Edition supports Table and index partitioning, but it does not support Partitioned Table Parallelism.

I am not sure that I fully understand the consequences of this.

What exactly does it mean in my case, and how will it affect the database's performance?


texts: store multiple date ranges in solr field

texts:
Every day I check which events happened. Each event is represented as a document in Solr index.
An event can happen 4 days in a row, skips a day and then happen the next 4 days and so on... In a typical search query, I need to find all events that happened between start date and end date and also return the date ranges of event occurrence.

In the image above, a search between 1. and 17. would return R1(1-4), R2(6-9) and R3(11-14)
How do you store multiple date ranges in indexed and stored solr field?
One way would be to declare
<fieldType name="pdates" class="solr.DatePointField" multiValued="true"/>

, create a field with pdates type and append each day that event occurs. If an event occurs 300 days in a row, then specified field contains 300 date entries. Probably not optimal...


texts:
I have this table:
id number
name text

row for example:
id name
1 {"vals":[{"id":123,"name":""}],"additional":"text"}
2 {"vals":[{"id":123}],"additional":"text text"}
3 {"vals":[{"id":222},{"id":123,"name":"this is the one"}],"additional":"text text"}
4 {"vals":[{"id":222,"name":"this is the one"},{"id":123}],"additional":"text text"}

I want to return the ids where vals[*].id=123 and their other name element is not empty(not other name in the json).
So only this one will turn:
    3 {"vals":[{"id":222},{"id":123,"name":"this is the one"}],"additional":"text text"}

I'm not strong with json type queries so I only got:
select id
from tab
where name ->>"$.vals[0].id"=123

I should have done "$.vals[*].id", but that returns to me [123] and that is not = 123
Appreciate your help!


texts: Call function where argument is a (sub)select statement

texts:
My function takes an int4 as argument and returns a table:

SELECT * FROM test_function(545421); -- works fine

SELECT * FROM test_function(SELECT customerid
                            FROM tableX where id = 1); -- syntax error

How can I make this work?
Server PostgreSQL 9.3.1


texts: SQL Server query with date range in WHERE - execution plan uses clustered PK instance not date column index

texts:
I have the following query in SQL server:

declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT
    my_date = a.itemTimestamp
FROM
    my_table a with(nolock)
WHERE
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)

It's extremely slow (90s for < 2000 records), because the execution plan uses the clustered index of the PK column for 'my_table':

Why is this happening and how can I optimize the query performance?


texts:
I have a situation where storage was ripped out from under SQL Server.  After running BBCC CheckDB I have received an "Extent (1:269886) in database ID 34 is allocated by more than one allocation object" message.

The research I have done basically tells me recover from backup.  However, this table is a subscriber in transactional replication.  Replication is set up such that I have added individual articles in the past in order to initialize in stages.  Am I correct that I can do the following:

Remove the article from replication
Add the article back
Let the agent job take a new snapshot
Let that snapshot apply

Do I need to truncate or drop the damaged table first?

SQL  Server 2008R2 on Windows Server 2008


texts: How to copy database schema in the same database for testing purposes?

texts:
I need to copy... by any means, a schema of my database in that same database, let's say, I have SC1 and I have to duplicate it as SC2 without the data contained in SC1 (in the same database).

How could that process be carried out?

I am using Oracle Database 11gR2.


texts: Apply SQLServer Service Pack to a Single Node Cluster

texts:
I have to apply a SQL Server 2008 R2 Service Pack to a Single Node Cluster.

The other node died and new hardware has been ordered.

Is there anything weird to worry about?

Normally we'd apply the service pack to the passive node first, then failover, and apply the service pack to the newly passive node, however this clust only has 1 node. We're loading Service Pack 2 so we can upgrade to SQL Server 2014.


texts: MySQL 8.0 table with JSON column that uses JSON "merge" operations has 500k rows but analyze table shows 7MM

texts:
I have a database table that according to TABLE STATUS has 7MM rows, but when I SELECT COUNT(*) it only has 500k rows.

This is a problem because table growth is increasing and we're running low on storage now.

here is schema: MySQL 8.0.15

CREATE TABLE `tasks` (
  `task_id` binary(24) NOT NULL,
  `task` json NOT NULL,
  `task_kryo` mediumblob,
  `task_type` varchar(180) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$.t'))) STORED,
  `created` datetime GENERATED ALWAYS AS (cast(left(json_unquote(json_extract(`task`,_utf8mb4'$._task.timestamp')),19) as datetime)) STORED,
  `last_updated` datetime GENERATED ALWAYS AS (cast(left(json_unquote(json_extract(`task`,_utf8mb4'$._task.latestStatus.timestamp')),19) as datetime)) STORED,
  `latest_status` varchar(180) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$._task.latestStatus.t'))) STORED,
  `marker` binary(24) GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$.marker'))) STORED,
  PRIMARY KEY (`task_id`),
  KEY `task_type_index` (`task_type`),
  KEY `created_index` (`created`),
  KEY `last_updated_index` (`last_updated`),
  KEY `latest_status_index` (`latest_status`),
  KEY `marker_index` (`marker`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

This is MySQL RDS on AWS. We have system backups disabled because this table is entirely disposable data. As a result, there is no binary log for this table. AWS disables that when you disable system backups apparently

My suspicion is that we use JSON "Merge" operations in Update statements, and because binary logging is disabled by AWS (we don't have any backups for this table as it is fully disposable / scratch data) somehow it is implementing the updates as inserts on the table (old records remain but are not deleted).

See
https://mysqlhighavailability.com/efficient-json-replication-in-mysql-8-0/

binlog-row-value-options=PARTIAL_JSON

Also, the MySQL log contains this warning on restart:

2020-05-21T20:02:08.506526Z 0 [Warning] [MY-013103] [Server] When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.


texts: Does RMAN in Oracle only copy the archive logs? What happens to the active redo log in case of failure?

texts: If RMAN copy copies the archived redo logs when they are full, what happens to the current redo log in the event of a failure (hardware failure, etc.)?  Isn't there always an active redo log that is not an archive_log?


texts: Streaming replication archive folder taking all space- Postgresql- 9.4

texts:
We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine.

We created few databases in master and it is getting replicated to slave servers.
Our Postgresql version is 9.4 and data directory is "/var/lib/pgsql/9.4/data" in both master and slave.

While setting up streaming replication, we edit postgresql.conf in master

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.4/archive/%f'

Our recovery.conf in slave is

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.10 port=5432 user=replication password=password'
trigger_file = '/var/lib/pgsql/9.4/trigger'
restore_command = 'cp /var/lib/pgsql/9.4/archive/%f "%p"'

I also added below command to clear the old archive in recovery.conf in slave

archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/9.4/archive %r'

but I started seeing below error in pg_log of slave.

< 2019-01-16 09:02:48.937 EST >WARNING:  archive_cleanup_command "pg_archivecleanup /var/lib/pgsql/9.4/archive %r": child process exited with exit code 2
pg_archivecleanup: archive location "/var/lib/pgsql/9.4/archive" does not exist
< 2019-01-16 09:07:45.927 EST >WARNING:  archive_cleanup_command "pg_archivecleanup /var/lib/pgsql/9.4/archive %r": child process exited with exit code 2

After doing some research I realized that problem is genuine as the slave is not having this directory "/var/lib/pgsql/9.4/archive" and its present in master server only.

Now, we removed "archive_cleanup_command" from recovery.conf (slave) but in our master server "/var/lib/pgsql/9.4/archive" is taking so much of space. We need to clear these old files inside "archive".

I had few doubts and wanted to take some suggestions to resolve the problem :-

Can you suggest what can be done so that we don't run into every day "No space left in /var of the master" issue ?
Can we run the pg_archivecleanup from master server and remove the old files like below

pg_archivecleanup -d archive 000000010000003700000010.00000020.backup
pg_archivecleanup:  keep WAL file "archive/000000010000003700000010" and later
pg_archivecleanup:  removing file "archive/00000001000000370000000F"
pg_archivecleanup:  removing file "archive/00000001000000370000000E"

Should I setup streaming replication again with some mount point attached to both master and slave and save the archive of master to "mount point" and then add "archive_cleanup_command" in recovery.conf?


texts: Hide column if any value is null or zero via proc sql or view

texts:
Given either of the two following datasets

Kağıt Cinsi        Sarı      Yeşil      Mavi

Kalın                4         0          5
İnce               null        0          3

or

Kağıt Cinsi        Sarı      Yeşil      Mavi

Kalın                4         0          5
İnce                 0         0          3

I want to filter via either a stored procedure or a custom view to hide any column which contains a row with either a 'null' or 'zero' value.

These are the results: I would expect from either of the above datasets

Kağıt Cinsi       Mavi

Kalın               5
İnce                3

I'm not sure what a query for this looks like, but I want to return it from the database like this.


texts:
I am advising someone on how to disable TCP Chimney Offload, which is enabled by default by Windows Server 2003 SP2. We are working in an environment with multiple server machines, namely the application server machine which communicates with the database server machine.

This can cause communication problems between application and database. See KB942861 and Windows Scalable Networking Pack - Possible Performance and Concurrency Impacts to SQL Server Workloads.

On which machine should TCP Chimney Offload be disabled? Application, database or both?


texts: Generate lower case tag names with Oracle DBMS_XMLGEN?

texts:
I am generating XML in Oracle PL/SQL with the DBMS_XMLGEN package:

OPEN base_cursor FOR SELECT * FROM foo;
base_context := dbms_xmlgen.newcontext(base_cursor);
base_xml := dbms_xmlgen.getxmltype(base_context, dbms_xmlgen.none);

My problem is that whatever I do I always get the tag names in upper case. To maintain compatibility with things further downstream, I need them to be in lower case. Is there any way I can fix this?

I know there is an option for this in DBMS_XMLQUERY, but the docs says it is not recommended to use that package.


texts: SQL Server replication with subscriber doesn't know about the publisher

texts:
I have a case where many SQL Server instances need to be merged and centralized in one master database.

This needs to be near real-time solution. After looking around in Google, I've found that transactional replication can do the trick.

The problem is that I have (n) publisher databases, and they don't even have static IPs. I had the impression that push replication will be enough to send the data to the centralized database without knowing about the publisher connection, but it seems like I was wrong.

My question is: How can I set up an solution to merge/replicate multiple publisher databases which don't have static IPs, and might suffer frequent outage from the network, all that in near real-time?


texts:
Issue:
When running sqlpackage.exe to apply a sql dacpac, it occasionally fails on broker/SignalR related objects, which breaks the build:
*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot drop the service 'Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver', because it does not exist or you do not have permission.
Error SQL72045: Script execution error.  The executed script:
DROP SERVICE [Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver];

This is our sqlpackage.exe cmd line parameters:
/p:TreatVerificationErrorsAsWarnings=True /p:DoNotAlterChangeDataCaptureObjects=True /p:DropIndexesNotInSource=True /p:DropObjectsNotInSource=True /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;LinkedServerLogins;LinkedServers /p:BlockOnPossibleDataLoss=False

Again, the failure rate is like 1 out of every 10 runs, and no one is manually removing these objects, thus I have no idea why it would not be able to drop the object if it knows about it.  Any ideas on a good solution to get it to apply consistently ?


texts: How to calculate column (integer) changes in hourly buckets?

texts:
I have following table:

id serial pk
created_at timestamp with time zone not null
user_id integer not null
points integer not null

For a given time period (min_date and max_date, both timestamp with time zone) it should return list of total (without partitioning by user_id) points changes within this period, partitioned by hour. Point change is a difference of points for a user since previous record for the same user.

For example:

created_at  | user_id | points
10:00       | 1       | 100
10:30       | 2       | 50
11:00       | 2       | 75
12:00       | 1       | 130
12:30       | 2       | 80
13:00       | 3       | 20

Without min/max_date it should return:

hour | points |
10   | 100 + 50 = 150
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35
13   | 20

With min/max_date it should return:

min_date=11:00
max_date=12:00

hour | points |
11   | 75 - 50 (from previous points for user 2) = 25
12   | 130 - 100 (from previous points for user 1) + (80 - 75 (previous for user 2)) = 30 + 5 = 35

So basically it should return sum of user point changes for each hour bucket since previous (outside of current bucket) row entry.

Note if there are more than one rows for same user in same hour bucket, then the latest should be used in calculation. Also if there will be more than one rows for same user in same bucket hour with same created_at then maximum value of "points" for given user in given bucket should be used in calculation.

Actually "points" for given user will ALWAYS grow (or will be the same as previous) so it is possible to just use max(points) for user in given bucket for calculation.

In examples above I simplified "created_at" field to just hour, but in reality it's a timestamp with time zone and it should be grouped by extract(year from created_at), extract(day from created_at), ....

So far I have:

with stats_buckets as (
  select
    extract(year from stats.created_at) as year,
    extract(month from stats.created_at) as month,
    extract(day from stats.created_at) as day,
    extract(hour from stats.created_at) as hour,
    max(stats.points), -- use max points value inside time-user bucket since points always grow (dont have to use latest entry for given user)
    stats.user_id
  from points as stats
  group by year, month, day, stats.user_id
)
select * from stats_buckets

It just groups records by year, month, day, hour, user_id and selects max(points) from each bucket. Now for each bucket I have to calculate difference between selected max(points) and previous points for given user outside of the bucket. And then group results by year, month, day, hour.

I tried to do this with windowing function but without any luck so far.

Also one more important thing: there might be a LOT of rows in points table so calculating the differences for ALL of the buckets might be too slow. However min_date, max_date will ALWAYS be used to prevent calculating ALL buckets. The problem is that it's not possible to filter out point records initially by min/max_date, since for each row, the previous one (from which we calculate the difference) might be outside of min/max_date range.


texts: Making sense of sys.objects, sys.system_objects, and sys.sysobjects?

texts:
In this question I was writing a query using sys.sysobjects. However, one of the answers mentioned sys.system_objects. I'm just wondering what is the difference between these tables?

sys.objects
sys.system_objects
sys.sysobjects

sysobjects has more things.

> SELECT count(*) FROM sysobjects;
2312

> SELECT count(*) FROM sys.system_objects;
2201

> SELECT count(*) FROM sys.objects;
> 111

SELECT count(*)
FROM sys.sysobjects
WHERE NOT EXISTS (
  SELECT 1
  FROM sys.system_objects
  WHERE system_objects.object_id = sysobjects.id
);
> 111


texts: Applying single quotes across multiple lines using ssms code snippet

texts:
further to Applying Quotes Across Multiple Lines

Is it possible to create a "surround with" ssms code snippet to turn a list of values across multiple lines into an IN clause, so I could:
select the list of values > right click > surround with > select the name of my code snippet in order to turn this:

SELECT * FROM tbl WHERE someValue
ABC1
ABC2
ABC3
ABC4

into this:

SELECT * FROM tbl WHERE someValue IN (
'ABC1',
'ABC2',
'ABC3',
'ABC4'
)