2
votes
1answer
2k views

Can I mysqldump an entire database while using where='condition' on individual tables?

I have used mysqldump to dump an entire database, However, when I want to use --where=condition", on individual tables, I have dumped individual tables mysqldump --where='condition' mydb table ...
4
votes
1answer
153 views

Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0?

-- Example 1: With Clustered Index CREATE TABLE tbl5 ( i VARCHAR(900) NOT NULL ) ; GO CREATE CLUSTERED INDEX CIX_tbl5 ON tbl5 (i ASC) ; GO INSERT INTO tbl5 ( i ) VALUES ...
1
vote
2answers
2k views

Transactional Replication - table does not have the identity property

I have a one way transactional replication set up between two 2005 MS Sql servers. Recently I have started to receive the following message in the replication monitor from the subscriber: Table ...
1
vote
1answer
254 views

MySQL row length is almost the triple of what I expect

I shamelessly took title and content from this question and adapted it to my similar yet different problem: This is the table I have: CREATE TABLE `test` ( `field1` MEDIUMINT(8) UNSIGNED NOT ...
5
votes
2answers
229 views

Product of n Rows

From this data (assuming the number of rows is not known in advance): with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4) select * from q1; /* C1 -- 2 1 4 1 */ I ...
2
votes
1answer
2k views

Installing Oracle XE 11gR2 on Fedora 15 - linux group permission deadlock

During installation of Oracle XE 11gR2 on Fedora 15, I am presented with: The install cannot proceed because ORACLE_BASE directory (/u01/app/oracle) is not owned by "dba" group. You must change ...
3
votes
3answers
5k views

Can Oracle SQL developer export data as a set of Update commands?

Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements. I have two databases with a mirrored table. One of them has some ...
14
votes
1answer
28k views

Change length of varchar on live prod table

I have a MS SQL Server 2008 R2 DB server currently in use with a production app. A new enhancement to the app now requires a varchar(100) column in a table to be increased in length. Can the ...
3
votes
3answers
17k views

How to initiate MySQL on Mac OS X 10.6.8?

I followed this tutorial to install MySQL on Mac OS X 10.6.8. Now I get he following errors: $ mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: ...
11
votes
3answers
47k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
3
votes
1answer
1k views

How to set datadir to different values based on the hostname in my.cnf?

How to set datadir to different values based on the hostname in my.cnf? I want to use the same my.cnf file on a Linux machine and a Mac OS X machine where the datadir should have different values.
6
votes
3answers
340 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
6
votes
1answer
985 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
3
votes
3answers
7k views

What's the difference between functions and stored procedures? [closed]

I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are. Is that true? If not, what are the ...
1
vote
2answers
2k views

Cursor doesn't print text when I tell it to

I have used WAITFOR combined with a cursor in the following script: create table orders(orderId int primary key,productId int,productName varchar(20)); insert into orders values(1,11,'book'); insert ...
5
votes
2answers
3k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
1
vote
2answers
717 views

pg_upgrade fails with an unspecified error

I’m running Postgres 9.1’s pg_ugrade to migrate from 9.0 to 9.1 on Windows x64. After editing the permissions to allow full trust, running the tool as the postgres user, and working around a bug ...
1
vote
0answers
819 views

Update Statistics Maintenance Plan - Ambiguous column name error

Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86) Windows 2003 R2 SP2 I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user ...
0
votes
1answer
337 views

Milliseconds to datetime

I have this statement EXEC [spProject_Update] @ProjectID = 496 ,@Name = 'Hangman Bollywood iPhone' ,@Description = '' ,@EstimateTime = 2674800000 I will get milliseconds in @EstimateTime. How can I ...
0
votes
1answer
664 views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
1
vote
1answer
1k views

How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook?

I'm new to databases and web development so a laymen explanation is best for the time being. I'm only in the data modeling phase so no need for specific code. I'm just trying to wrap my head around ...
1
vote
1answer
339 views

Why are these queries slow in some environments and not others? Can we fix their performance?

The following stored procedures in a MySQL 5.x environment: DELIMITER // CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100)) BEGIN SELECT TABLE_NAME as ...
5
votes
1answer
2k views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
6
votes
2answers
1k views

Can't get SQL Server 2012 RTM LocalDB working on Server 2008/x86/ SQL Server 2008 machine

I have installed SQL Server 2012 and LocalDB on my development machine, and am able to connect to LocalDB using both the SQL Server Management tool 2012 and from within my winform application using a ...
5
votes
1answer
287 views

Triggers and the Transaction

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic. The logic is ...
2
votes
1answer
2k views

Why is SQL Server 2008R2 using more memory than the “max server memory” setting? [duplicate]

Possible Duplicate: Microsoft SQL Server 2008 Memory I've explicitly set the Maximum Memory value in SQL server 2008R2 to 13,000 MB, however when I check task manager I see it is using ...
15
votes
1answer
12k views

CREATE DATABASE vs. CREATE ANY DATABASE permissions

In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions? I am unable to find an authoritative answer. The best I can infer is that either (a) ...
3
votes
2answers
3k views

How to identify fragmentation level of the table data itself not the table indexes, and then defrag

Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself. Also defragmentation solution is ...
0
votes
2answers
768 views

Merging Results while avoiding Key Constraint Error?

I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table) In one of the foreign tables two rows are merged and this table is ...
5
votes
1answer
159 views

Upgrade postgres without messing with ports?

The standard procedure for upgrading Postgres appears to require that I temporarily run both the old and the new version. This means that I have to change the port of the old version. This seems ...
3
votes
1answer
3k views

How to qualify variable inside trigger body (PL/SQL )?

I can explicitly qualify variable in the body of stored procedure or function : create or replace PROCEDURE ptest AS int_val INT; BEGIN ptest.int_val:=0; END; / How to do the same inside trigger ...
4
votes
1answer
913 views

How to query EndDate value from ReportServer..Subscriptions.MatchData

In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things. While querying the ReportServer db, I've been ...
4
votes
3answers
2k views

How do you structure a database that organizes post by location like craigslist?

I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to ...
10
votes
1answer
2k views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
3
votes
1answer
260 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
5
votes
3answers
705 views

Just how much is a query supposed to slow down the whole system?

This is similar to my earlier question but more generic. I have a system with 16GB of memory, 16 cores, supposedly quality hard drives, RHEL 5, MySQL 5.0.x. Yes, I know, we're working on upgrading ...
2
votes
1answer
10k views

Records in table not showing on form Access 2007

Excuse my ignorance in asking this question - I am a database novice and need help! I will explain as best I can. In the table on my DB I have 235 records. I have created a form which references ...
1
vote
2answers
290 views

Data - Changes Audit Trail

What are typical scenarios where the user might require a data-change-audit-trail?
3
votes
1answer
129 views

Synchronising databases / database technologies

I need some advice before we engage in some costly development work We have two SQL Server database servers on the same network. One runs an in house database and the other a proprietary database ...
3
votes
1answer
267 views

If i issue a rollback when recovery mode is bulk logged, will my bulk operations be rolled back?

If my recovery model is BULK LOGGED, I'm in a transaction, and I've issued some statements that are minimally logged like SELECT * INTO, will a ROLLBACK undo those minimally logged statements?
0
votes
2answers
234 views

Which SQL Server system database is more important: master or resource? [closed]

Which SQL Server system database is more important: master or resource? If the resource database is damaged, can the database engine work? Can the system work with only the master database? If not, ...
3
votes
2answers
948 views

why does my clustered index scan have ordered = false

When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, ...
2
votes
2answers
49k views

How to Get the Sum (total) of a column in a query result set

Is there way that I can get the sum of a column in my query result set by just clicking on the column (something like in excel) so that i don't have to copy and paste the column into excel to the see ...
17
votes
2answers
113k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
6
votes
1answer
3k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
2
votes
1answer
3k views

MySQL UPDATE to replace text in LONGTEXT field

I have a WordPress table wp_posts and I'd like to run an UPDATE that will replace a url from my old domain to my new domain. For example, let's say that one record in the field post_content has the ...
0
votes
0answers
237 views

Can not open oracle 10g database when migrated from oracle sql developer 1.5x to 3.1x [closed]

I have migrated from Oracle sql developer tool 1.5x to 3.1x and imported my connection settings with database 10g. When I try to open a connectionstring in the developer tool I get this message: ...
5
votes
4answers
3k views

Microsoft SQL server 2008 and linked servers

I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any ...
5
votes
2answers
644 views

Optimizing IP Range Search?

I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query. In my table I have 3 columns CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, ...
2
votes
2answers
21k views

declaring cursor to loop over some values in SQL server 2008 R2

I have some business unit such as India, International, US, UK. I have to: Create a table variable (virtual in-memory table) with columns that match SPROC SP_Report resultset. Declare a cursor that ...

15 30 50 per page