1
vote
1answer
950 views

Elegant way of sending e-mails with Service Broker

Is there an elegant way for sending e-mails using a Service Broker service/queue? I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on ...
1
vote
1answer
513 views

Setting Alerts for Autogrow

Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)? I've searched around and haven't found the 'error number' for autogrowth, if there is one. In case this isn't possible, how ...
1
vote
2answers
5k views

Why is this MySQL proc using cursors failing to retrieve results?

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, ...
2
votes
1answer
1k views

Restoring a DB issues

I am trying to copy over the live database from one server and restore it on the dev server using SQL Server 2005. I have made a backup of the live db and copied the .bak file over to the dev server. ...
2
votes
0answers
1k views

Copy database wizard between servers results in Access Denied error. [closed]

I'm trying to copy a database from SQL Server 2008 R2 to another server, using the Copy Database Wizard in SQL Server Management Studio. I get an error telling me to check the error log on the ...
3
votes
1answer
4k views

When are InnoDB table index statistics updated?

In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated? What events trigger such updates? I've seen comments suggesting that the following might trigger it: table is ...
2
votes
2answers
409 views

SQL Server 2000 DTS package, filter rows using stored procedure returned date

I know very little about DTS packages in the first place (I'm a programmer not a dba) and have been stuck with the task of filtering rows based on the results of a stored procedure call that returns a ...
9
votes
2answers
5k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
1
vote
1answer
5k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) ...
4
votes
3answers
510 views

Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

So, I have some deadlocking issues. I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot. After doing some research into ...
2
votes
1answer
279 views

Check against multiple duplicate records

I have data that i should insert into table. But before inserting i need to check against duplicate records and report list of those records. Table: CREATE TABLE `test` ( `A` varchar(19) NOT NULL, ...
10
votes
2answers
6k views

In Sql Server's RANK function, how to limit the results to just the first 3 ranks

In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
2
votes
1answer
606 views

Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions

Currently I have an internal CRM application that houses all my data and runs my entire company. The front end is ASP, and the back end is SQL. The external site will run SQL What I'd like to do ...
2
votes
1answer
582 views

3 Databases, Best Replication Setup?

We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of ...
7
votes
3answers
4k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
1
vote
1answer
221 views

How to create a Job for Merge Replication?

I want to create a step in a Job for Merge Replication. I basically want to know which command should I write so that a particular Subscription starts synchronizing?
1
vote
1answer
125 views

SQL database with temporal data

Is it possible to do this in a SQL database SELECT * FROM Employee WHERE BirthDate BETWEEN DATE 1970-01-01 AND DATE 1970-01-01 The result that was said to from this query is all the ...
3
votes
1answer
212 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
2
votes
1answer
2k views

What is the optimal way to select the parent and child rows on a table with a self-relationship?

Sample data from the genres table: id | genre_name | genre | parent_id --------------------------------------------- 2 | Blues | MAIN | NULL 6 | Folk | MAIN ...
1
vote
1answer
528 views

How to get a unique highscore per user

I have the following tables: users, highscores I would like to get the top ten best results in highscore. The scores should be unique, meaning one score per user. I tried using INNER JOIN and GROUP ...
1
vote
1answer
589 views

Error with Oracle trigger, invalid identifier

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working. DML statement UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT ...
2
votes
1answer
427 views

How to increase the amount of memory sql server uses on the server?

On the db server, we have 4 GB of memory. Currently, the memory utilization of Sql Server 2005 on that server is 1,725,000 K and the total memory usage is stable at 2.04 GB. The Cpu usage on the ...
-1
votes
1answer
1k views

Difference in between HAS MANY and BELONGS TO in Yii framework [closed]

I am new to the Yii Framework. I discovered HAS MANY and BELONGS TO in the documentation, however I am confused about these features. Can someone give some real life examples with a diagram that ...
0
votes
1answer
734 views

Look for software that can convert sql (mysql) to erd

I would like to have an ER Diagarm in phpmyadmin, however, its graph is not up to my expectation, the graph has no relationship indicator (1-1) (1-m) etc at all. So i am wondering whether there is a ...
0
votes
1answer
1k views

export SQL statements from Oracle 11g database via command line

I'd like to export an Oracle 11g database as a series of SQL statements via the command line. I'd like the functionality of SQL Developer (the process described here ...
4
votes
1answer
387 views

What factors prevent and delay TRUNCATE command?

I had a quite large table, perhaps a 50M rows and I had to truncate it. Usually truncate is really fast but in this case it took hours to complete. What are the factors that can prevent truncate ...
2
votes
1answer
691 views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
6
votes
3answers
26k views

How to display database structure in phpMyAdmin?

Is there any way to generate a report of my database structure using phpMyAdmin? What I would like is a table format like the following, and an Entity Relationship table. Table name: List ...
3
votes
1answer
948 views

Creating UUID extension as the non super privileged user

I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions. Is there a way to do this? ...
26
votes
3answers
34k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
10
votes
1answer
4k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
6
votes
1answer
464 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...
1
vote
1answer
251 views

Update statement errors, parenthesis, failed validation ORACLE

i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value. Original statement The statement below works flawlessly, ...
5
votes
2answers
237 views

How should an object that can reference an object of multiple types be modeled in a relational schema?

Apologies for the obscure question, it may make more sense with a concrete example: In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
6
votes
1answer
4k views

Understanding block sizes

My question targets Postgres, but answers might just be good enough coming from any database background. Are my assumptions correct: Disks have a fixed block size? RAID controller can have a ...
1
vote
1answer
225 views

apache hang on deadlock

i use code like this to prevent deadlock. while($retry<3 and $notsone) { try { $transaction->commit(); $notdone=false; } catch { $transaction->rollback(); $retry++; } } if ($retry==3) { ...
3
votes
2answers
1k views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
2
votes
1answer
260 views

Postgres synchronized backup - what's with the archive?

I am setting up a Postgres 9.1 master and a hot standby-server. I read the documentation but I'm not quite sure yet what I should do with my WAL files. Every commit is done on both servers before ...
4
votes
3answers
963 views

Get notified when SQL Server 2008 Audit Server logs something?

I'm setting up SQL Server 2008 Audit Server to log any activities made by certain group of users of certain database. All I need to do is get notified when the Audit server logs something, so that I ...
5
votes
3answers
1k views

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
3
votes
2answers
202 views

What is the signifigance of a fillfactor of 20?

I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
4
votes
2answers
394 views

Running different instances of SQL Server from different networks on one box

I want to be able to run SQLSMS and select an instance to connect to but the instances are on different networks. I remotely log on to our server and from there, I want to be able to access our ...
11
votes
1answer
728 views

Behavior of data in indexes based on fill factor

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%? From my understanding, when the data is inserted there ...
2
votes
1answer
1k views

Performance Inserting and Updating Millions of rows into a table

We have a CSV the user imports into the Application. After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a ...
1
vote
1answer
1k views

Does the order of keys in compound Primary key make any difference?

Package Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion ...
5
votes
2answers
830 views

Transaction independent writes inside a trigger

I am using sql express 2008 r2 express edition. Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire? I have triggers ...
3
votes
2answers
453 views

do partitions make sense when dates are not involved?

We are having some discussions about partitioning some application tables in SQL Server 2008 based on their quickly growing size. One is an event table that is typically used for INSERTS (80% of the ...
3
votes
2answers
331 views

Create table statement in MYSQL

I am trying to create the following table in MYSQL and is giving errors, CREATE TABLE CUSTOMER ( CUS_ID INT NOT NULL AUTO_INCREMENT, CUS_NAME VARCHAR, CUS_DOB DATE, CUS_ADDR VARCHAR, CUS_EMAIL ...
5
votes
2answers
2k views

MySQL Locks while CREATE TABLE AS SELECT

I am running the following (dummy) query CREATE TABLE large_temp_table AS SELECT a.*, b.*, c.* FROM a LEFT JOIN b ON a.foo = b.foo LEFT JOIN c ON a.bar = c.bar Suppose the query ...
0
votes
1answer
415 views

Data base structure of a subscriber list

I am building a application that allow different user to store the subscriber information To store the subscriber information , the user first create a list For each list, there is a ListID. ...

15 30 50 per page