3
votes
1answer
2k views

SQL Server backup devices

I'm trying to find out how to detect if a backup device exists using T-SQL. This is how I create my backup device: EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = ...
2
votes
2answers
7k views

Run Unix command using PL/SQL

Is it possible to run a Unix command using a query in Oracle? I want to run simple commands (like df -h) using a query. Is this at all possible or am I wasting my time? I don't want to use a ...
2
votes
2answers
1k views

Why does my query run faster after I force full table scan?

I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this. SELECT ...
0
votes
1answer
328 views

Patients profiles database

I am making software for patients in hospitals. I want to make an option that if a doctor has prescribed a drug that interacts with another drug that the patient already uses, a warning will appear ...
5
votes
1answer
1k views

Calling a .NET method in a SQL Server script

In the data migration process, I need to convert some columns values form the source system before writing to the target table. I've been provided with a Csharp code which reads a string and do the ...
3
votes
1answer
344 views

SQL query returns different results when index is dropped

In SQL Server 2000 SP4, I have a query SELECT <fields no aggregates> FROM PS_VED_VCH_TAO5 A ,PS_BUS_UNIT_TBL_AP B ,PS_RT_DFLT_VW C WHERE A.PROCESS_INSTANCE = 594170 ...
2
votes
1answer
124 views

Execute job every time database changes - execute job on each change-event

I have a transactional-replication running in my database, and I need to execute a service on my website that refreshes the cache every time the database changes. So i guess i have two options to ...
1
vote
2answers
6k views

SQL Server : Is it possible to have a linked server link to another linked server?

Hello to all and thanks ahead of time for taking the time to look at my question! I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2. ...
2
votes
2answers
342 views

Sharded MongoDB backup solution

I am looking for a way that I can backup my 12 MongoDB shards into a single server. Disk space is not an issue but getting the backups to be on a single node would be the best. I am not sure if there ...
14
votes
1answer
9k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
-1
votes
3answers
5k views

Conversion failure varchar to int

When running this code: declare @SQL NVARCHAR(max); declare @intFlag INT; set @intFlag = 2; SET @SQL = ' SELECT * into [##tmp1] FROM servicesstats_0511_0412 s WHERE department=''ACC'' and ((SELECT ...
2
votes
2answers
14k views

Table Lock on INSERT - How to optimize

we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
5
votes
2answers
24k views

How to set an Oracle user's default schema?

I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?
5
votes
1answer
589 views

Installing and enabling both master and slave semisynchronous plugins on same server

I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience ...
3
votes
2answers
5k views

Mongo replicated shard member not able to recover, stuck in STARTUP2 mode

I have following setup for a sharded replica set in Amazon VPC: mongo1: 8G RAM Duo core (Primary) mongo2: 8G RAM Duo core (Secondary) mongo3: 4G RAM (Arbiter) Mongo1 is the primary ...
1
vote
1answer
319 views

postgresql pgadmin3 queryconnect attribute does not work

I'm using Fedora 17 and pgadmin3 to auto-connect to a postgresql database with one button click. Every time I want to run an SQL statement, I have to do 4 steps, (open pgadmin3), (connect to the ...
0
votes
1answer
72 views

How to design this kind of relations in a database

I have a conceptual problem and I would like to get your ideas on how I'll be able to do what I am aiming. My goal is to create a database with information of persons who work at a place depending on ...
4
votes
1answer
8k views

query to find closest lesser date

I have a table with rates. each rate has an effective starting date. (the ending date of any rate is implied by the existence of a record with a newer effective starting date.) I also have a table ...
1
vote
2answers
2k views

Why Oracle 11g installs in folder C:\app\<name_of_user>

Just curious, now Oracle 11g installs by default in folder C:\app\ Why? Before, Oracle was installed by default in folder C:\oracle... Why now is created "app" folder?
1
vote
1answer
227 views

Pitfalls of creating a foreign key constraint on a system table?

I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data. One of ...
3
votes
1answer
605 views

Applying user-defined fields to arbitrary entities

Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
0
votes
2answers
232 views

MySQL: SELECT the n-th item from an ordered result

SELECTing the top n elements from a table is easy: SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000; Finding the n-th element with a subquery is quite ...
1
vote
3answers
336 views

Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B ON A.Field2 = B.Field2 AND A.Field3 = B.Field3 AND A.Field4 = B.Field4 AND B.Field1 = "string1" AND B.Field2 = "string2" AND A.Field5 = (SELECT ...
2
votes
1answer
2k views

Tabular mode disabled during SSAS installation

I am trying to install SSAS in Tabular Mode. However, i find that the radio button is disabled. I am installing it on a separate instance as well. The regular Multidimensional SSAS had been installed ...
6
votes
1answer
2k views

MySQL table creation insanely slow

A simple table creation on one of my MySQL databases takes forever: mysql> CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY); Query OK, 0 rows affected (16.58 sec) The machine is quite ...
1
vote
1answer
765 views

drop postgres table with minimal locking

I'm trying to run the following Postgres migration script DROP TABLE some_table; DROP TABLE another_table; The problem is that this script causes excessive locking when run on the production ...
0
votes
1answer
51 views

How to summarize Bridge results?

I am trying to summarize Bridge results for each pair (north/south pair and east/west pair). Match points for pair north/south is MPNS, for pair east/west is MPEW. Is there a way to summarize all ...
0
votes
2answers
917 views

Row level restore with Oracle Recovery Manager (RMAN)

We're approaching the end of 2012 and I still find myself relying on database exports to maintain the ability to do row level restore operations. While this is a proven method, it sure would be nice ...
1
vote
2answers
319 views

Mix of Cyclic and Chained MySQL replication - What do you do with “log-slave-updates”..?

I have the following MySQL replication topology set up: Master1 (M1) replicates to Master2 (M2) Master2 (M2) replicates to Master1 (M1) Master1 (M1) replicates to Slave1 (S1) Pictorial View ...
1
vote
1answer
268 views

Can't start MySQL after creating new slave from innobackupex [closed]

I am setting up a new MySQL Slave and I can't start mysql after replacing the datadir (/db/mysql) with the innobackupex backup from the master. I have given the directory the permissions with: sudo ...
1
vote
1answer
13k views

Oracle 11gR2 archive log destinations

I want to add a second archive log destination to an Oracle 11gR2 RAC database with ASM, the idea is that I will then have some redundancy if the primary storage should fail. Archive log is already ...
2
votes
0answers
931 views

Finding Microsoft.AnalysisServices.Xmla dll in SQL Server 2008 R2

I'm not able to find Microsoft.AnalysisServices.XMLA.dll that I need to import using Microsoft.AnalysisServices.Xmla; I have found some articles and answered questions for SQL Server 2005 and 2012. ...
1
vote
1answer
429 views

Getting max values from MySQL tables

I have three tables: competitions (id, name, date) athletes (id,name) results (place, id_athlete, id_competition, ranking_points) where: results.id_athlet=athlet.id ...
3
votes
2answers
34k views

How to store single result in variable and reuse it in a query (Oracle)?

I have a query like SELECT id FROM xyz WHERE ...; which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a ...
-6
votes
3answers
2k views

SQL Server Express with 20000 databases won't start [closed]

I have a SQL Server Express instance with 20,000 databases (twenty thousand databases).. and the service won't start. I get error message 1053: The service did not respond to the start or ...
5
votes
2answers
724 views

How should I best design the tables and relationships, given the following rules?

In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
2
votes
1answer
154 views

Rank in more tournaments and boards

Source data is of sample tournamet board player score 1 1 2 990 1 1 4 980 1 1 6 480 1 1 7 450 1 1 9 980 ...
1
vote
1answer
1k views

Store table in separate filegroup in SQL Server

I have big SQL Server database, with a lot of blob data in some tables. For performance optimization, I want to move one table to separate hard drive. I created new filegroup, and "row data" file. ...
1
vote
2answers
7k views

Loop through Self JOIN on table until the operand column is NULL completely

For the problem I have a single table which store a Member's ID and His Referee's ID. This is something like a Multi-Level-Marketing plan where you join a company for some tasks and then someone join ...
3
votes
3answers
1k views

mongodb service not running after changing path

I have changed the dbpath of mongodb.conf. And after that trying to start mongodb service,when I run sudo service mongodb start I get back mongodb start/running, process 7320 After that I run: ...
1
vote
2answers
175 views

How to remove tables and indexes?

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed. How to remove table and indexes ...
4
votes
1answer
123 views

Set the primary key for many to many relationship

For the two relations, Customer and Equipment: There is a limited number of equipments (stock) and upon hiring I need to store the date of hire and the expected return date. I sketched this: ...
3
votes
2answers
252 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
4
votes
2answers
680 views

Execution plan results for sp_executesql suggests adding missing index for already existing index

I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
2
votes
1answer
463 views

How to test mirroring with a single Microsoft SQL Server license?

I want to test mirroring in Microsoft SQL Server Enterprise in order to decide if I would implement one in production. Buying another copy of SQL Server for another machine is not an alternative, ...
4
votes
1answer
2k views

Binlog has bad magic number

I keep getting this error whenever I start MySQL. 121028 1:38:55 [Note] Plugin 'FEDERATED' is disabled. 121028 1:38:55 InnoDB: The InnoDB memory heap is disabled 121028 1:38:55 InnoDB: Mutexes and ...
1
vote
1answer
43 views

I have deleted a table, by exporting over the same table, and selecting the 'drop destination table before insertion'

I understand this might not be the best forum, but I could sure use some help right about now. If not, please suggest a forum where I can. I was trying to backup a table, by using the export wizard. ...
2
votes
1answer
402 views

How to solve the error “Error 'Got error 0 'No error' from NDBCLUSTER' on query”?

I'm currently working with a large network of MySQL 5.1 DBs (using MyISAM tables) and I'm evaluating a move to MySQL Cluster. I would like to have the MySQL Cluster replicate off of one of the ...
4
votes
2answers
2k views

How to compare two SQL Servers instance level objects for a migration?

I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server). The databases themselves will be transferred using a database backup and ...
5
votes
3answers
2k views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...

15 30 50 per page