All Questions
99,461
questions
0
votes
1
answer
467
views
how to install mssql on to ubuntu
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S ...
CommunityBot
- 1
0
votes
1
answer
771
views
What causes temp tablespace to be full and generates ORA-01652 error
I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space.
Is it too much sorting occur on TEMP?
How do I identify such issue from AWR?
And ...
CommunityBot
- 1
0
votes
1
answer
18
views
Performance for query filtered by user preference
I have an app, that shows data based on user preference. I will simplify it in this theoretical example:
The app shows articles filtered based on user preference for their favorite authors or their ...
- 103
2
votes
1
answer
278
views
Postgres partition pruning with text prefix matching
I have a table where the natural identifier is a text like this: AB-C123456E-F, which is my primary key.
The table is approaching 200 million rows, and so to get better performance on the unique ...
CommunityBot
- 1
0
votes
2
answers
234
views
SQL Server effectiveness of mirroring partial database
Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only ...
CommunityBot
- 1
1
vote
1
answer
940
views
MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?
I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB).
My solution (do not work) was to add a AFTER TRIGGER ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Mysql cannot import from text file
I have a table called person setup like this
(person_id SMALLINT UNSIGNED,
fname varchar(20),
lname varchar(20),
gender enum('M','F'),
birth_date date,
race enum('B','W','A','J'),
ethnicity varchar (...
CommunityBot
- 1
3
votes
1
answer
741
views
Avoiding a full re-initialization when making changes to transactional replication
When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog.
I have had ...
CommunityBot
- 1
0
votes
1
answer
267
views
mysqldump stuck at connecting to localhost
System Info:
mySQL version: Ver 8.0.27 for Win64 on x86_64 (MySQL Community Server - GPL)
windows 10 version: 20H2 19042.1586
I am trying to create a backup of a database. When I try to run the ...
CommunityBot
- 1
1
vote
1
answer
282
views
MySQL - fine-tuning full-text search
How can I fine tune MySQL full-text search for my information domain?
For example, I might have a keyword like js that I would like the search to recognize, but I don't think allow a minimum word ...
CommunityBot
- 1
9
votes
3
answers
10k
views
the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?
Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it.
quote from MySQL 5.7 Reference Manual / GRANT Syntax
REFERENCES ...
- 495
0
votes
1
answer
318
views
optimizing or estimating postgresql table size on disk
This question is in the context of the helpcovid GPLv3+ project (work in progress in March 2020). With PostgreSQL 11 on Debian/Buster/x86-64. See the file hcv_database.cc there.
I have a table of web ...
CommunityBot
- 1
0
votes
2
answers
14
views
How can reset query buffer in psql's console?
I want to set a comment on some column ,in the sample database to add comment on table tablename's column field:
sample=# comment on column tablename.field is 'don\'t use it';
The console turn into ...
- 222
1
vote
1
answer
1k
views
MySQL Workbench Administrator - GUI "Access denied for user root " gotcha
Here is a small newbie problem I found and resolved. The behavior of the GUI is a little confusing so I figure I will share.
Running some Java examples, I have MySQL set up on Windows. After making ...
CommunityBot
- 1
4
votes
2
answers
299
views
Regretting an identity: Is there a way to force inserts to specify the identity column?
To prevent an X-Y problem here's the actual problem we're trying to solve:
The Problem:
We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, ...
- 80.5k
0
votes
1
answer
2k
views
MariaDB why do I get error "Unknown table x in field list" when it does exist (yes, another one!)
Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.
The important part of the schema shows
/*Database: mamlocal*/
----------------------
/*Table ...
CommunityBot
- 1
0
votes
1
answer
13
views
more columns or separate tables
Let's assume I've a table called post and want to add comments, they both essentially the same so there's no need to create another table, now I can just make a FK column to point to the same model, ...
- 30.5k
0
votes
1
answer
320
views
How to extract values from a JSON field with Mongodb?
When I run mongdob query I got this json results in a column.
Column name is "user"
{"email": "mail1@mail.com"}
{"email": "mail6@mail6.com", "name": "name1"}
{"email": "mail2@mail.com"}
{"email": "...
CommunityBot
- 1
7
votes
1
answer
3k
views
SQLPackage : Import or Export SQL Azure BACPAC without users
I know how to use SQLpackage.exe. I can export from SQL Azure and create a bacpac. I can also Import to SQL Server via SQLPackage.exe.
I want to export or import without the users in the database. The ...
0
votes
1
answer
755
views
Query with left join not using index
The below query is taking about 1 second to run
SELECT `taxi_driver_mapping`.*,
concat("dpk00",
taxi_driver_mapping.mapping_payment_plan_id) AS package_text,
`people`.*,
...
CommunityBot
- 1
0
votes
1
answer
17
views
SQL code that runs on Azure via GUI fails silently via pyodbc
Background: I am building a complex data analysis tool, and part of the process is to run some pretty deep and complex SQL from python. I am using pyodbc to connect to a SQL server instance hosted on ...
- 19
1
vote
1
answer
6k
views
How to generate a self-signed SSL certificate for MS SQL server 2008 R2 using OpenSSL?
Using OpenSSL, I'd like to generate a self-signed certificate for usage with Microsoft SQL server 2008 R2. This might be unusual, but I'd like to learn what's going on behind the scenes.
I have ...
1
vote
1
answer
413
views
Multiple m:m relationships LEFT JOIN giving duplicate records
DB noob here.
I have a table named invoice with a m:m relationship with a few other tables via junction tables:
invoice_customer
invoice_creator
invoice_deposit
invoice_withdrawal
...
CommunityBot
- 1
2
votes
3
answers
15k
views
SQL Server select columns which contain data
I'm working for a client to import data out of a generic off the shelf application database. If you can imagine a database that is designed by software developers that have no concept of database ...
0
votes
0
answers
10
views
Unnest is not preserving nulls or empty values while inserting the data
I have a Table Student where I have an XML as input
inputXML=
<Students><student><id>1</id><Name>SAM</Name></student>
<student><id>1</id>&...
0
votes
1
answer
442
views
How to make ST_DWithin to use meters for distance in Postgis?
I have already asked a similar question here:
Find the nearest geo points across two tables with Postgis (or without)?
but now I am stuck around the correct geo-reference to calculate the distance ...
CommunityBot
- 1
3
votes
1
answer
1k
views
Trace flag 2861 and what a 'zero-cost' plan actually means
I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?
The blurb says:
SQL Server typically won't cache plans for these trivial queries because the cost ...
- 68.5k
-1
votes
0
answers
28
views
SSMS 19 usage issues
SSMS 19 is really bad. When I've already connected to a MI's DB Engine, it prompts for credentials when I right-click and modify a stored proc. It doesn't do this for an on-prem server.
Also, when I ...
- 1
1
vote
1
answer
694
views
Get position of mysqlbinlog error. I have the error's line number
If I use mysqlbinlog to load a binlog into my database like this:
mysqlbinlog --defaults-file=/path/mysqld.cnf bin.000011| mysql -u root
and get an error like this:
ERROR 2006 at line 290: MySQL ...
CommunityBot
- 1
2
votes
1
answer
18
views
MySQL db optimization error on innodb tables
I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32.
When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
0
votes
0
answers
13
views
Create index on big table increase IO:XactSync wait event for other sessions
AWS Aurora (postgres 11.9) after downgrading instance from db.r6g.8xlarge to db.r6g.4xlarge instance and enabling logical_replication
I'm trying to rebuild indexes (cause of bloat) for table size 30GB ...
0
votes
2
answers
14
views
How to define text qualifier in BCP in
I have several csv files need to be imported in to sql database.
The data in the file looks like following.
productid,product,manufacturers[LF]
1001,TV,"Sony, LG, Panasonic, Toshiba"[CR][LF]
...
- 143
0
votes
1
answer
9k
views
“ORA-03146: invalid buffer length for TTC field” after upgrade from Oracle 12 to 19c
We are experiencing problems with updating a table record BLOB column. We get the "ORA-03146: invalid buffer length for TTC field" error. We basically are saving e.g. PDF attachments to the ...
CommunityBot
- 1
1
vote
0
answers
8
views
Single node failure in Cassandra 4.0.7 causes cluster to run into high CPU
we have 40 node Cassandra 4.0.7 cluster in GCP, after single node failure the entire cluster goes bonkers on CPU usage and I/O waits. Tried to bump the cluster size to 50 nodes the same is happening ...
- 11
0
votes
2
answers
30
views
Convert hex string representation to hex number in SQL Server T-SQL
I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-...
- 30.5k
0
votes
0
answers
21
views
Restore a .bak in azure managed Database SQL
I have a database in azure bucket (.bak) that comes daily from another system and I want to create a stored procedure to restore a database.
I have seen many guides about the command BACKUP RESTORE... ...
0
votes
2
answers
622
views
Confusion over using LEFT JOIN on multiple tables
I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research.
Let's say we have a users table and other tables that relate to the user, let's ...
CommunityBot
- 1
-1
votes
0
answers
11
views
Count daily worktime and group per day (whole month)
how can I query a table with working times, so that it is possible to count worktime in hours (rounded in 2 position after dec. point) per daily basis for one user.
The output is a calender day per ...
- 11
22
votes
2
answers
27k
views
Is it possible to change ENUM() lists?
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').
Then I executed...
...
- 164
1
vote
2
answers
556
views
pg_restore fails with drop default on identity column
Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below:
pg_restore --verbose -...
CommunityBot
- 1
-1
votes
0
answers
28
views
Create linked server encrypted from Sql Server 2019 to 2008R2 using Microsoft OLE DB Driver 19
I have some SQL Server 2008R2 that I want to connect to.
I have force encryption set to NO, and some expired certificates.
From new SQL Server 2019, I want to make a new linked server connection.
...
- 36.7k
0
votes
0
answers
20
views
Mysql get 2 latest record and display on same line
I already have the query to get the 2 most recent records from Mysql DB. Problem is, it displays the first, then the second, then the next client and so on. I am looking to get these results as shown ...
- 12.1k
11
votes
5
answers
4k
views
How can I export a subset of tabledata from a prodution database into my local testing database?
We have a relatively big production postgres based db: ~20GB.
The PostgreSQL database is hosted on heroku.
I would like to copy a small subset of the table data to my local database so I can run ...
47
votes
8
answers
91k
views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
0
votes
0
answers
21
views
Why does `Get-ChildItem` require a call to `invoke-sqlcmd` to see `SQL SERVER:\SQL\...`?
PowerShell has a cool module for interacting with SQL Server. One of the features is the ability to navigate database objects like file system objects. For example:
get-childitem -Path "SQLSERVER:...
- 483
1
vote
0
answers
9
views
Unable To Find Object Within SSMS Highlighted With Data Migration Assistant
There's a breaking change being highlighted in the the Data Migration Assistant tool 'Syntax issue on the source server' about the syntax of an object however I am unable to find this object.
I've ...
- 11
1
vote
0
answers
8
views
Always On Cluster, Network Problem, make failover or disconnections for nodes
have a nice day, I have a cluster sharing the same quorum resource, this quorum is a server with 2 share folders, one for SQL Always on Cluster, and other for SAP Always On Cluster,
I have lot´s of ...
- 11
0
votes
0
answers
9
views
create a snapshot table based on SCD2 dimension and fact table
I have an excel file coming to the landing directory every month.
Excel file consists of 40 columns of which 38 could be changing its values. One of the attributes is a business key(lets call it - id) ...
1
vote
1
answer
328
views
Remap array columns to single column with value
Here is the sample database schema.
| id | Critical | High | Low |
--------------------------------------------
| 1 | {apple, ball} | {cat} | {dog, egg} |
Now I want to remap these to ...
CommunityBot
- 1
-1
votes
1
answer
32
views
How can I limit storage space taken up by `wpr_bucket_table`?
TL;DR: What is the wpr_bucket_table object in a SQL Server database, and how can we reduce the space it takes up?
We have an Azure SQL Database that takes up significantly more storage than expected. ...
- 68.5k