All Questions

Filter by
Sorted by
Tagged with
3
votes
1answer
2k views

Data archival strategy that handles schema changes?

I am working with a legacy application that has about ten years of customer data. A majority of this data is not used in day-to-day operations but there is a business requirement to have the data ...
10
votes
3answers
39k views

How to map another server through SQL Server Management Studio

I am trying to map another server by giving the command EXEC xp_cmdshell 'NET USE H:\\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes' I got an error with this: network path ...
3
votes
1answer
2k views

Recovering from a stuck query

I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will ...
1
vote
3answers
131 views

Storing summed values

I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other ...
4
votes
1answer
1k views

Select one name per person where spelling variations exist

I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
32
votes
3answers
31k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
2
votes
2answers
4k views

remove almost duplicates based on insert time

Have a MySql table that records magnetic stripe swipe times from identity cards: CREATE TABLE `swipes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `personalid` varchar(20) NOT NULL, `swipe_time` ...
7
votes
3answers
35k views

mysqldump: Error: 'Can't create/write to file

Can't create temporary files on /var/tmp. mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces On my OS. id mysql uid=...
2
votes
1answer
491 views

Post replication powershell script - on subscriber

I need to execute a command before a replication executes on the publisher, and after, on the subscriber. I seem to have it working on the publisher, by adding another step in the replication agent. ...
6
votes
1answer
12k views

Availability Group reporting disconnected replica

I'm trying to configure availability groups in a VM environment so I can run some tests. I think I've got the group created correctly, I can see the Always on group on both servers. However when I ...
0
votes
1answer
944 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
3
votes
1answer
177 views

query discrepancies on very large postgres table

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem. these two queries although similar are taking massively ...
4
votes
2answers
445 views

Is it possible to determine what part of a stored procedure is currently executing?

Say I have a stored procedure like this (assume that the table schemas are fine): CREATE PROCEDURE p_MyProc AS INSERT MyTable SELECT Col1 FROM Table1 INSERT MyTable SELECT Col2 FROM Table2 INSERT ...
3
votes
1answer
7k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # Hostname: ...
2
votes
1answer
2k views

oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation

I have a Merge query (open to better suggestions) that seems to cause the server to hang. (I'm not sure what it's doing, but it pegs the CPU and never completes) When I run it through the query ...
5
votes
2answers
324 views

Migrating MySQL to new server

We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B). We have a new MySQL 5.5 instance on ...
3
votes
2answers
1k views

Cleaning up transaction replication del/ins/upd stored procedures?

I'm referring to the ones that start with: dbo.sp_MSdel... dbo.sp_MSins... dbo.sp_MSupd... I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run ...
2
votes
1answer
4k views

Replicating data from Oracle to MySQL

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
9
votes
1answer
5k views

Can Availability Groups provide seamless failover (with no query failures)?

I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. ...
1
vote
1answer
2k views

How to restore a MySQL file to SQL Server 2008

I have a MySQL file (apple.sql) that I need to restore to a SQL server 2008 database or at least be able to access the database through mySQL. I have created a linked server mySQL, created an empty ...
4
votes
3answers
2k views

Cannot create login on cluster

I have a SQL Server 2008 R2 cluster on Win2008 R2. I am unable to add a login for the service account. Any other login I add to this instance works fine and I have been able to add the same ...
10
votes
3answers
39k views

Users cannot view tables in non-default schema in SSMS

I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has ...
1
vote
1answer
130 views

sqlite database: advanced SQL or redesign

I've been programming for several years, but I'm pretty new to SQL(ite). I'm designing a database for a sort of a bookkeeping program. What I've got so far: orders (o INTEGER PRIMARY KEY, ...
10
votes
2answers
62k views

sqlnet.ora or tnsnames.ora changes require reboot?

Anytime you change Oracle's sqlnet.ora or tnsnames.ora files, does the system require a reboot? In my instance, I only have the Oracle client installed on the machine I'm referring to, but out of ...
3
votes
2answers
8k views

One database with two schemas: cross-schema View permissions issue?

I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and a new one called Test. I have a view in the Test schema called View1 that selects from dbo.Table1. Both schemas are ...
5
votes
2answers
2k views

PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
0
votes
1answer
3k views

Access query to select multiple records with matching field

I have a table with a list of people ([FirstName], [Surname], [Age]). Some of these people are in the same family. I'd like to return a list of people who have another family member in the table (same ...
1
vote
1answer
3k views

Oracle Imp Dump Full

Can I create a new instance in a new server and import a dump file which was made with the command exp full=y etc? If it is possible do I need to recreate tablespaces and users before the import? If ...
1
vote
1answer
516 views

Are these database tables normalized?

I am developing a webapp for an institute and I designed these tables: Are these tables normalized?
3
votes
1answer
4k views

Ensure correct username when using pg_restore

I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this: sudo -u postgres psql -c "...
5
votes
3answers
42k views

Unable to start SQL Server Instance 2008 R2 - DB file corrupt

I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is " The log scan number passed to log scan in database ‘master’ is not valid. This ...
4
votes
1answer
1k views

High availability in postgresql

We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as ...
0
votes
1answer
957 views

May a query update a source table?

I work with a few tables with given data. I run a query built in Design View just like result:![table1].[qty]*![table2].[expenses] and I get the output in query table in field result. Is it possible ...
-1
votes
2answers
759 views

How to gauge knowledge of a SQL Server DBA? [closed]

Not being familiar with SQL Server, I need to evaluate the knowledge of an SQL Server DBA applicant. The job is mostly about increasing performance and finetuning an existing system. Do you have any ...
4
votes
1answer
4k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in on ...
3
votes
1answer
235 views

Many to many unique combination

So I originally thought I was going to have a one to many relationship in a database with the following tables ENTITY_JSON_MAP ATTRIBUTE_JSON_MAP Originally I was going to have an FK in ...
1
vote
1answer
320 views

Whitelist-based SQL Server schema setup

My group has access to a single database on a SQL Server instance. We have a number of different people in our group, each working on separate projects. In trying to determine how to segregate ...
0
votes
1answer
73 views

Several variants of a string in tables

I need: a table containing keyword phrases; a table containing URLs; a table (or maybe a view) with keyword-URL pairs (referring to IDs of the two previous tables); tables holding keywords/URLs for ...
1
vote
0answers
615 views

Oracle TOAD and Team Coding with TFS: Unable to get revisions from TFS

My company is currently exploring the option of using TOAD as a means to seamlessly allow developers to check-in/check-out code in TFS. This would be seeded by our packages, stored procedures, etc. ...
94
votes
3answers
34k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
0
votes
1answer
3k views

Error when installing Oracle 11.2.0.3 in Windows Server 2008 R2 64 bits

I downloaded Oracle 11.2.0.3 installer: p10404530_112030_MSWIN-x86-64_1of7.zip p10404530_112030_MSWIN-x86-64_2of7.zip p10404530_112030_MSWIN-x86-64_3of7.zip p10404530_112030_MSWIN-x86-64_4of7.zip ...
7
votes
2answers
1k views

PostgreSQL: Query DB for rows containing integer[] > or < than specified value

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
2
votes
2answers
7k views

The best MySQL configuration for my server

I'm little newbie in configuring mysql and I need some advices. Few days ago I made little home server on Ubuntu Server 12.04 with samba, mysql 5.5, apache 2.2, PHP 5.4.6 etc. I'm using it to ...
7
votes
1answer
24k views

Getting a “Request to run job refused because the job is already running” When no job is running?

So have an ETL job that runs through integration services that I start with a SQL job name StartStageStore. The last step of this job is to start another job and report success. The job this calls to ...
10
votes
3answers
10k views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
7
votes
2answers
4k views

Fetch the newest row grouped by a column in MySQL

My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set: log_table +--------+-----------+------------------+---------+ | log_id | ...
1
vote
1answer
530 views

Postgres replication/replication to server outside firewall

I am looking for a way to have a postgresql database (running server version 9.1) replicated. I want the mirroring to be able to have one server (the slave) standing outside our firewall. I have been ...
2
votes
1answer
2k views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
2
votes
0answers
91 views

How to display differential backup date in SSMS Object Explorer Details

SQL Server Management Studio exposes the Object Explorer Details (selectable from the View menu option) and its context menu that allows adding fields to the default display. Full and Transaction Log ...
1
vote
1answer
1k views

Oracle Enterprise Manager font problem, some text is displayed in Chinese and with squares

In Oracle Enterprise Manager 11g database control in the following section: Setup->Monitoring Templates->"Choose template name and click Edit button"->Policies tab Texts are displayed in Chinese ...

15 30 50 per page