2
votes
2answers
71 views

Postgresql constrains on FK

I am trying to design a (part of a) database which has to accomplish the following: There is a students table, containing a bunch of students. There are educations in the database. Each student can ...
4
votes
1answer
11k views

How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this: ALTER TABLE tablename AUTO_INCREMENT = 1 I am curious if there is a way to do this from within ...
1
vote
2answers
99 views

interpreting how much data has been lost by analyzing dbcc output

We had a SAN outage and this corrupted several databases with no backups. We ran this to do a repair: EXEC sp_resetstatus 'test' ALTER DATABASE test SET EMERGENCY DBCC CheckDB ('test') ALTER ...
2
votes
1answer
701 views

Convert Oracle database to Derby

I need to migrate an existing Oracle Database into a Derby one. I want to know if there's a tool, a script or another way to do that work. It is using any of the interesting features of Oracle, as I ...
-1
votes
1answer
4k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
4
votes
4answers
6k views

How to block user access to perform database application update?

Where I work we have a SQL Server database (Microsoft SQL Server 2008 R2) that serves as the back-end with two different user interfaces, a .NET web interface and a FoxPro interface. Every month we ...
0
votes
1answer
108 views

What structure should be used to store data with multiple category identifiers?

I have a table within a MySQL database with various data in it (for the sake of example, let's give it the following: ID | Place | Phone | Email | Category ) Now, I want to redesign ...
2
votes
1answer
15k views

How to grant all privileges on all tables in a schema to a user in IBM DB2?

At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move ...
2
votes
1answer
463 views

Create a sub query for sum data as a new column in SQL Server

Suppose that I have a table named tblTemp which has this data: | ID | AMOUNT | ---------------- | 1 | 10 | | 1-1 | 20 | | 1-2 | 30 | | 1-3 | 40 | | 2 | 50 | | 3 | 60 | ...
0
votes
1answer
1k views

Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine

I have oracle server (standard edition) installed on a guest VM (Windows 8 Hyper V). I have mapped a host entry to the VM as "brettvm". I am able to log into the VM and connect to the default ...
0
votes
4answers
154 views

SQL Server transaction log growing without control

We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb. ...
5
votes
2answers
160 views

Indexing strategy for dynamic predicate

Assume SQL Server 2012 Standard edition. My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints). The ...
2
votes
1answer
55 views

Strategies for summary data in parent record based on detail data?

I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
3
votes
1answer
600 views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
18
votes
6answers
2k views

Why don't databases create their own indexes automatically?

I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly ...
7
votes
3answers
1k views

How should I best handle a rapidly growing database?

I have a database that I need to maintain. Sadly, the setup and use of that database I can't change, much (thanks to some internal politics). It's running on SQL Server 2008r2. Its only been live ...
1
vote
1answer
488 views

Increasing time-out period for schema compare (windows server 2008, Sql Server 2008 R2 and VS2012)

I need to increase the time-out period for schema comparisson using visual studio 2012 to compare two sql server 2008 r2 database on a windows server 2008. I believe this is somenthing I need to set ...
1
vote
1answer
90 views

Stored Procedure Not working as Expected

When I create the stored procedure without parameter it works as expected, but when I create it with parameter it doesn't return any row. DELIMITER // CREATE PROCEDURE search() BEGIN SELECT * FROM ...
0
votes
1answer
70 views

Need an idea for a complex SQL question

I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-) Here is what I am trying to accomplish (for PostgreSQL): Got a ...
3
votes
2answers
6k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
1
vote
1answer
74 views

Sql Server Server Group query

Scenario I have 4 front-end sql server. All of them have the same db instances. Data are replicated from back-end databases. Issue In SQL Management Studio I have registered the servers and I have ...
2
votes
4answers
138 views

What happens when I try to store more 8019 bytes in a SQL Server table row?

I read - total fixed and variable length data are still limited to 8019 bytes total But my data can possibly be more than this. What happens if it's more. Does it still store the data correctly? ...
3
votes
1answer
136 views

Increment of integer PK values by 1 doesn't throw duplicate key error. Why?

As stated in the title I am wondering why the following piece of code will note fail with duplicate key error. As a result of the following test, obviously, the update does not check for duplicity ...
0
votes
4answers
1k views

How do I backup a database from MySQL 32-bit and restore it to MySQL 64-bit?

I'm currently using MySQL version 5.0.16, on my local computer (which runs a 32-bit OS), for easier access while developing my program. Now that I'm ready to deploy my program, I'd like to test the ...
1
vote
2answers
998 views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
0
votes
1answer
44 views

Load infile query issue in mysql

We are using Load data infile query to load txt files in the database (mysql) . we have a continues flow of text files to process using load query and is dumped in the table but sometimes the load ...
0
votes
2answers
45 views

Why is the padding on months in Oracle 9 characters?

I ran two simple sql statements select length(to_char(sysdate, 'fmMonth')) from dual; select length(to_char(sysdate, 'Month')) from dual; The length of the first statement was 4 (It's June) and the ...
2
votes
3answers
380 views

How to merge data sets without including redundant rows?

I think this must be a fairly common thing to do, but I haven't been able to find an example. It involves merging data that involves dates/times (or more generally any sequential values) without ...
2
votes
0answers
584 views

TempDB MDF file huge after update script ran

On the weekend we had a huge deployment run that affected a large chunk of our system. It was all run in one massive transaction (probably the wrong thing to do). As a result our TempDB DatabaseFile ...
0
votes
1answer
47 views

MYSQL - Using simple inventory number convention as Primary Key

I have an inventory with three numbered groups like so: Group 1: 0-999 Group 2: e0-e999 Group 3: v0-v999 Can I simply make a one-column table to store this information and make that column the ...
0
votes
1answer
592 views

Oracle 11g http listener configuration for PL/SQL server pages

Could please someone point me how to configure oracle db to be able to display PL/SQL Server pages. I have successfully created and mapped dad with the DBMS_EPG. Here is the listener: # listener.ora ...
1
vote
0answers
45 views

What is a good way to Merge 2 range sets into a single view

The real purpose of this has to do with effectivity dates and contains additional keys and fields, but I simplified it for the sake of, well, simplicity. Sample: test_Widget Table theFirst ...
2
votes
1answer
873 views

Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition. ...
1
vote
0answers
259 views

Problem with sorting matrix groups in SSRS

I've hit the following issue in SQL Server 2005 Reporting Services This is a slightly odd and complicated situation: I have a table containing various groups and within each group is also embedded a ...
6
votes
2answers
5k views

Create database role to allow read access to all tables; write access to some

We have a database with over 1000 tables. I need to create a role that allows read only on all tables, as well as write access to two specific tables. I was messing around with database roles, but ...
3
votes
1answer
488 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
12
votes
1answer
6k views

“Access is denied” when connecting SSMS to Integration Services

I receive the following error when attempting to connect SSMS to Integration Services using a particular SQL Server cluster's network name: Connecting to Integration Services service on the ...
1
vote
1answer
308 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
2
votes
1answer
112 views

Execution Plan not matching Stored Proc

Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists. The execution plan shows an INNER JOIN of the M and A tables, where in the ...
0
votes
0answers
137 views

Passing parameters through Excel to an Informix DB works through MS Query but fails when returned to Excel due to General Error

I require to query an informix database using parameters and Excel. When the query is executed in Microsoft Query the dataset is fetched and returned succesfully, when I attempt to return to Excel the ...
2
votes
2answers
147 views

Design consideration regarding state handling: how to store multiple, variable states for one entity [closed]

First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ...
0
votes
1answer
276 views

Stored procedure not working

I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error: ERROR 1303 (2F003): Can't create a PROCEDURE from within another ...
0
votes
1answer
253 views

MySql one time event never runs?

Please have a look at below events 1) create EVENT Test1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second ON COMPLETION PRESERVE ENABLE DO ... 2) create EVENT Test2 ON SCHEDULE EVERY 20 ...
8
votes
1answer
200 views

-E startup option and SSDs

Has anybody seen evidence of the effect of -E when using SSDs? The effect on 'spinning rust' drives is not disputed - but SSDs aren't really flustered by random I/O. I wonder if the -E option might ...
1
vote
1answer
2k views

Optimize command issuing “'View is not base table” and “Corrupt” error messages

I have no experience with MySQL. My boss ran an optimize against a MySQL database. Against a bunch of views we got the message "tablexyz" is not BASE TABLE and the next has message is ...
4
votes
2answers
2k views

How do you find home information for Oracle RAC on Unix?

Need help finding Oracle home path corresponding to a database instance in RAC environment. I am aware of few of the ways to achieve the same. Listing them below to avoid the same answers. ...
0
votes
2answers
613 views

Storing dynamic data in NoSQL

I have scenario where I need to store unstructured data but the rest of my data is structured and relational. An example of the type of unstructured data is as explained below: User Type 1: How do ...
0
votes
1answer
103 views

Embedded select with rank

I am having the hardest time following the tutorials on this site regarding ranking with MySQL. I have a larger problem than others seem to show. My table is constantly updating with single answers ...
1
vote
2answers
490 views

Does a database restore remove existing stored procedures

I have a PROD and NON-PROD system NON-PROD already has a copy of an application PROD database I have also created new stored procedures on the NON-PROD application database and made changes to a ...
0
votes
4answers
140 views

How can I create a fresh copy of a large database on SQL Server?

I have a very large database (400GB) that is now corrupted. I tried repairing it with the repair_allow_data_loss option, but that did not fix the problem so my next option is to restore to a previous ...

15 30 50 per page