All Questions

Filter by
Sorted by
Tagged with
0
votes
2answers
22 views

Prevent overwriting when using COPY

As the title said. I found it inconvenient when accidentally rerun a copy (time-consuming query) to '\tmp\foo.csv'. I tried setting set -o noclobber in .bashrc but it did not help. How can I prevent ...
0
votes
1answer
21 views

Retrieve LOGINPROPERTY information via linked server

When I run the below query on a linked server it returns nulls in the "DaysUntilExpiration" column as per resultset. SELECT '<link server>' as Instance, SL.name AS LoginName ,...
0
votes
1answer
16 views

How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
0
votes
1answer
79 views

Sql, get category tree for values from one table, names and ids

What I need I need select all ids or names from table "category" (all chidrens) until category_id is 0 (main category). Example On web there is product with category tree. In table "category" I have ...
0
votes
1answer
21 views

Not lowering avg fragmentation

I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after ...
0
votes
1answer
10 views

How to model changing OrderLine rows in fact table

In our business, we have Orders that are made up of OrderItems. Over time, these Orders can change status (e.g. Received, Challenged, Planned, Completed). During this process, the OrderItems for an ...
1
vote
1answer
63 views

sp_Blitz script output

I'm a very green DBA at my company, and ran the sp_Blitz script (on one of our test databases that is an exact copy of our production database), and I only get the message "Command(s) completed ...
0
votes
0answers
10 views

Zabbix on Postgresql - very slow deletion of event

Were using Zabbix for monitoring and I'm having problems understanding why deleting events is so slow. Zabbix: 4.2 (never mind the name of the db - it is 4.2) Postgresql: 10.8 nvps: ~400 hosts: ~600 ...
0
votes
0answers
7 views

Rolling up/aggregating a duration measure in OrderLine fact table

I am starting to model a datawarehouse, and would like some suggestions. We have Orders and OrderLines. I have read that it is often recommended to model this as one OrderLine fact table at the ...
0
votes
0answers
7 views

Django - Database design improvement

In a simple scenario, where I have many measurements table that describe the measurements that comes from a specific protocol and every measurement is linked to the respective parent device, I was ...
0
votes
1answer
30 views

SQL Server : Is it necessary to disable SA user for security issues?

We have a website that will published in national grade with millions of users with Microsoft Sql Server database. I need to know is it necessary to disable SA login for security issues like DOS ...
0
votes
1answer
35 views

PostgreSQL update column B with values generated from function that uses values from column A

Pretty new to SQL. I have a table (client_info) in PostgreSQL (v11.1) that includes the following columns: +----------+------------------+ | clientip | clientip_country | +----------+----------------...
0
votes
0answers
22 views

Read performance of SQL Server data files data from Azure BLOB

I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here. The interesting ...
0
votes
2answers
29 views

Oracle Active Data Guard - OPEN_MODE STATUS

I would to ask some question regarding Oracle Active Data Guard. Currently, we are looking for an efficient way to get a real-time backup of Oracle Database. We tried to apply Oracle Active Data ...
0
votes
2answers
26 views

CHARINDEX equivalent in DB2

I am trying to select the domain from the column where I have email addresses. For example, I have a table email_table and a column email with email addresses as below. johndoe@gmail.com jhefid@...
1
vote
1answer
23 views

SQL Server 2016 - Disparity Between View Definition and View Design

I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all. The Issue After adding a column to a table, views selecting * from that table are now ...
0
votes
1answer
21 views

Defaulting a column to the 1st of next month

I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this: ALTER TABLE Foo ADD Bar datetimeoffset(0) NOT NULL ...
0
votes
0answers
8 views

Azure ARM template SQL backup deployment intermediate failure

I am having an intermittent error when trying to deploy a backup solution during provisioning. The error I get sometimes is: Deployment failed. Correlation ID: bb100978-3c7b-46f4-bcca-8b9abb490a98. {...
1
vote
1answer
27 views

Prevent updating table using procedure in different database

This is my scenario: Windows User Gabe has db_owner access of database DB1. Windows User Gabe has db_reader on database DB2. When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The ...
0
votes
1answer
12 views

RDS MySql replica db no longer updating. Bonus: AWS Newbie tips? [on hold]

Hello all and thank you for your help, I'm in quite a pickle here. I've been put on a project to fill in for someone who is swamped for time, so I'm not super familiar with the setup of everything ...
10
votes
1answer
67 views

Hash aggregate bailout

A question that arose in a chat discussion: I know hash join bailout switches internally to a sort of nested loops thing. What does SQL Server do for a hash aggregate bailout (if it can happen at ...
0
votes
0answers
14 views

Best database for indexing [closed]

I am using an elastic cluster to store a few hundred GBs of data. Sometimes, I have to make searches on the data in the form of 3rd level indexes. Let's say that I have a table of gender, age, past ...
-2
votes
0answers
17 views

Does anyone have experience with RedGate SQL Provision? [closed]

My company has about 8 TB worth of "Production" databases (7 differrent databases total) that we would like to be able to restore to lower environments. At the same time we have HIPAA data that needs ...
1
vote
0answers
41 views

Login connection data and time

I wonder if there is a way to query SQL Server to find out dates and times a login has connected to a particular server?
8
votes
3answers
411 views

Using SPID in DB Tables (instead of Table Variable)

Transactional database used for booking things... Our vendor was asked to replace #temptables with @tablevariables (because of heavy compile locks) but instead they replaced with an actual table ...
0
votes
3answers
51 views

Must declare the scalar variable question

That is my first post here, need helps: in a stored procedure I had some following code CREATE PROCEDURE [dbo].[SP_getAvg] ( @projectId INT ,@carrierId INT ,@fetchType VARCHAR(20) ) ...
0
votes
0answers
16 views

Mysqldump is not exporting the full table (phpmyadmin and sqlyog neither)

When I export the table from PhpMyAdmin it downloads a .sql file with just a small part of the database (like 2 or 3% my table is 84M rows) I have tried with SQLYog and mysqldump with a similar result....
1
vote
1answer
87 views

Slow join performance SQL Server

I am struggling a bit to improve performance on one of my queries. What I had is basically all of those 4 tables for reporting without any indexes, PKs, etc. I was thinking that if I copy them (...
1
vote
1answer
30 views

SQL Server - Update with Output not working in Parallel environment

I know that the output parameter does not guarantees parallelism logic. And the logic I want to implement is failing in a multi thread environment. To test this, just open 2 windows and execute them ...
0
votes
0answers
6 views

How to mongodb stitch webhook service with any content-type [migrated]

I have to create the webhook API to get the POST request from the external server. They are sending the details with Content-Type:application/x-www-form-urlencoded. But I dont see any option to change ...
1
vote
0answers
46 views

Group by equidistant integer intervals

I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like: CREATE TABLE IF NOT EXISTS altitude ( time TIMESTAMPTZ NOT ...
1
vote
2answers
39 views

Virtual Environment for learning [closed]

I am a new DBA and my friend told me the best way to learn about becoming a DBA is to setup your own server (virtually or physically). I don't have a spare machine laying around so I will need to set ...
0
votes
1answer
36 views

Copy Data from Two different databases with FK relation

So the situation is as follow: I have production DB called DB1 which has huge data, around 250 GB. I have Test DB called DB2 on my local machine which has the same schema as the production DB but ...
0
votes
0answers
12 views

How to query Groupy by and count with lookup in Mongodb?

I am trying To use group by in a collection and COUNT in another collection. I have 2 collections. One for customer and other for order_fact. Example Sample data for collection customer. {"Id_Cust":...
0
votes
2answers
32 views

SQL Server: Join all tables from a table listing their names [duplicate]

I have a table that contains a list of metrics and their IDs in the format: L6Metrics | ID | Name | -------------------------- | 734 | L6A_Velocity | | 736 | L6B_Velocity | | 738 ...
0
votes
1answer
17 views

cron command running postgres at high cpu [closed]

I can see that a cron command is running under a postgres user and killing the CPU but I have no idea how it's being executed or how to stop it !
2
votes
2answers
32 views

Restore DIfferential backup on azure managed instance?

I am migrating my database from on-premise to Azure managed instance. In order to move the database I'm using the organization weekly full backup, and I am asking them to take a diff after they take ...
0
votes
0answers
76 views

SQL Server Query Timeout with NOT IN clause but IN works fine

So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server. Works fine on Test and Dev and if we backup and restore the production DB to either of ...
0
votes
0answers
33 views

Which permission is required for OBJECT_ID to return non-null value [on hold]

Firstly, yes, I know I've tagged this 2008, but unfortunately that is a requirement under this particular project I'm working on. This is a rather complex setup, so please bear with me... there are ...
0
votes
1answer
25 views

CREATE TABLE in Postgres locks pg_class forever

I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class ...
5
votes
1answer
91 views

pg_ctl hangs over ssh

I am designing a process to test my postgresql 10.8 backups by restoring them to a random point in time in a throwaway virtual machine. I have been unable to fully automate the process though. I am ...
0
votes
0answers
12 views

What it is better to choose for installation of postgresql - debian or ubuntu? On base big loading [closed]

I have a big Postgres database on Windows the server. 200 GB, are a lot of different requests. I plan migration this base to Ubuntu. What distribution you recommend to use? Ununtu 18.04 or Debian 9/...
0
votes
0answers
15 views

Mysql removing null value rows from column [closed]

I want to remove null values only from column and not a table because I find suggestions to remove from values from table.
1
vote
1answer
21 views

Does AE certificate include both master and encryption keys together?

After setting up Always Encrypted, I can see a certificate in my Current User when selecting select * from sys.column_master_keys under the key_path I see this location, but there is no location ...
1
vote
0answers
19 views

Aggregation vs Ternary relationship - When to use?

I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project ...
0
votes
1answer
31 views

Indexes for combined time and location data

I have a multi-TB table with geolocation data for a certain item id and timestamp. It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the ...
0
votes
2answers
55 views

Get value from one field, and paste it in to a new column

I'm relatively new to Microsoft Access, and just a beginner with SQL programming. I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or ...
2
votes
1answer
25 views

Facing permission error while trying to execute a stored procedure published through CLR

I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to ...
0
votes
2answers
21 views

Get matching part from conditional pattern

I'm having a table like this: |id|key |value | ------------------------ | 0|name |Homer | | 1|surname|Simpson | | 2|town |Springfield| I'm running this query: SELECT * FROM ...
0
votes
0answers
15 views

Search for partial and full matches in two tables

I have two tables users and products: 1) users: id | username | ... | ---------------------- 1 | name1cat | ... | 2 | cat | ... | 3 | name4 | ... | 4 | test | ... | 2) ...

15 30 50 per page