All Questions
5
votes
1answer
340 views
How can I make the audit system create new audit files if they already exist?
I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the ...
5
votes
3answers
7k views
How to restore multiple backups
I'm using ExpressMaint and Windows Scheduled Tasks to create weekly full and daily differential backups of some MS SQL Server 2008 R2 Express databases
Weekly fulls
expressmaint -S ...
6
votes
5answers
8k views
MySQL : DROP TABLE Starting with a Prefix
EXAMPLE : I have more than 30 tables starting with a prefix "dp_" and about 12 starting with "ex_".
QUESTION : How can I drop all tables starting "dp_" in one query ?
3
votes
2answers
413 views
google database api
I would like to use my google account to store so data (time series)
Is there an easy way to do it?
I use R and if possible would like to create/use and API to send and receive data from/to R and ...
1
vote
1answer
439 views
Concurrent INSERTs in XtraDB (InnoDB)
I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows.
CREATE TABLE ...
3
votes
4answers
985 views
How different are the top DB vendor's SQL (DML-) dialects from each other?
Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from ...
2
votes
1answer
712 views
How to migrate data from Google AppEngine Datastore
How to migrate data from Google AppEngine's Datastore to other database (not Datastore)?
There is appcfg.py download_data but it produces sqlite3 file with empty bulkloader_database_signature and ...
3
votes
2answers
544 views
Using CLR User Defined Functions in SQL Server
I have used CLR user defined functions for very specific instances, such as complex string handling, but generally didn't like them from a maintenance point of view (yet another place to hide code). ...
6
votes
2answers
1k views
What's a good way to add a PRIMARY KEY to a large InnoDB table?
I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows.
I want to add a primary ...
6
votes
1answer
2k views
SQL Server 2000 : List and Disable all triggers
I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the ...
4
votes
1answer
319 views
what is the first schema checked?
I am a little confused right now. Can someone answer me with a simple answer to this question.
I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test].
...
10
votes
1answer
1k views
How to get the MAX row
In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability.
...
16
votes
3answers
12k views
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these ...
1
vote
1answer
1k views
Linked Server Problem
I have a MSSQL2008 and MSSQL2005 in my system.All SP's is up-to-date.
2008;
Microsoft SQL Server 2008 (SP2) -
10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft
...
7
votes
3answers
5k views
How to detect any changes to a database (DDL and DML)
There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
10
votes
3answers
528 views
What to do when a field in a table approaches the max signed or unsigned 32 bit integer?
In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the ...
1
vote
0answers
61 views
If anyone has experience about bugs ,problems or any related link of DB2 v.10 FOR ZOS , please reply? [closed]
Hi I want to use db2 10 for zos. If anyone has experience about bugs ,problems or any related link of DB2 v.10, please reply? thanks for your attention.
1
vote
1answer
929 views
Local package won't compile when remote package recompiles. (Oracle)
We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package. When something on A changes that requires the package on A to do an implicit ...
4
votes
1answer
531 views
Using sys.diana and sys.pidl to parse PL/SQL
It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
11
votes
5answers
8k views
Oracle Creating ER Diagrams and Data Dictionaries
I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
30
votes
9answers
2k views
How can I track database dependencies?
As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods ...
3
votes
2answers
393 views
Why can't I use newid() in a user-defined table-valued function?
I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this?
I have an alternate solution that suits my needs, so I'm not looking for ways to ...
4
votes
3answers
6k views
Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*
Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions:
Attempting to view or run ...
11
votes
2answers
2k views
What is the best way to get a random ordering?
I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How ...
2
votes
1answer
643 views
How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II?
Two weeks ago I asked "How do I configure Windows XP to start the postgresql demon after each boot/reboot?", and BenV helped me out with his answer:
Make sure the service is set to start ...
3
votes
3answers
385 views
Storing a large number of small size entries in MySQL
I have a database that stores readings from numerous gauges. Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 ...
3
votes
1answer
9k views
Expired archived logs not deleted from RMAN catalogue
If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE ...
7
votes
2answers
7k views
How do I determine if a table exists in a SQL Server database in SQL Server 2008?
How do I determine if a table exists in a SQL Server database in SQL Server 2008?
9
votes
1answer
7k views
Difference between Hash, Merge and Loop join?
In SQL Server you can specify the join hints:
HASH JOIN
MERGE JOIN
LOOP JOIN
What is the definition of those three join hints, and when should each be used?
22
votes
20answers
757 views
How to dive into an ugly database?
I'm sure many of you are/were dealing with a ugly database. You know, that database that isn't normalized at all, that database where you have to do a large painfully query to get the most trivial ...
7
votes
1answer
1k views
Unexplained InnoDB timeouts
I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example:
//# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE photos ...
1
vote
2answers
496 views
What is the best way to filter or otherwise manipulate the results returned by a stored procedure?
Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is ...
6
votes
6answers
2k views
Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios
From what I can tell, there are three possible ways of backing up your SQL Server database
Full backup
Differential backup
Log Shipping
What are the pros and cons of each strategy and in what ...
10
votes
4answers
3k views
How do I copy a table with SELECT INTO but ignore the IDENTITY property?
I have a table with identity column say:
create table with_id (
id int identity(1,1),
val varchar(30)
);
It's well known, that this
select * into copy_from_with_id_1 from with_id;
results in ...
7
votes
1answer
4k views
InnoDB create table error: “Row size too large”
We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
18
votes
2answers
3k views
How to execute SQL against all DBs on a Server
I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems:
select
so.name,
so.type,
MAX(case when sc.text like '%remote%' then '' ...
3
votes
3answers
1k views
Estimate large database size and speed
First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following :
+----------+------------------+------+-----+---------+-------+
| Field | ...
8
votes
5answers
8k views
Why do MySQL threads often show “freeing items” status when the query cache is disabled?
When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state.
The MySQL manual suggests that at least part of the reason for a thread to be in ...
7
votes
3answers
3k views
MySQL data type for 128 bit integers
I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers.
Right now, I'm using binary(16) but that involves a lot of conversion ...
1
vote
5answers
3k views
How to restore database using old full backup and current log file
What is happened? My client has lost the hard disk. He has to restore database.
He has the full backup of database 15 days old.
He has ldf (log) file of this database.
He has NOT mdf file (it was ...
1
vote
1answer
777 views
Query Linked Access database on network share via SQL Job Agent
I have an MS Access database on a shared network location which I've added as a linked server to my SQL Server instance. I'm authenticated on the network location via the server's main Windows login.
...
1
vote
1answer
1k views
What is the corresponding MySQL TimeStamp in MS SQL Server?
I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?
3
votes
2answers
295 views
What's the best way to track Updated By, Updated On
In MS SQL Server, what's the best way to update an Updated By, Updated On set of fields on a table? I've seen it done it triggers, in the code. etc. The upside I've seen to triggers is that it all ...
2
votes
2answers
267 views
Will this Database design logic work and what should be used?
A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com
What he essentially wants to do is ...
3
votes
3answers
2k views
Changed max_allowed_packet and still receiving 'Packet Too Large' error
I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
3
votes
2answers
703 views
MySQL Table Crashed Needs Repair, but Doesn't seem to Repair?
I am getting the error:
Table 'feed_data' is marked as crashed and should be repaired
So I run REPAIR TABLE feed_data (repeatedly) and get the following result:
...
4
votes
3answers
770 views
Detect Index Coalesce
Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.
Note: I am not referring to the COALESCE ...
3
votes
2answers
721 views
SQL Server 2005 : How to use pivot instead of a bunch of case statements?
Please show me how I can modify this SQL select to use pivot instead of all the case statements.
SELECT t.ProjectId,
COALESCE(MAX(ExecutiveChampion), 'n/a'),
...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
1
vote
1answer
290 views
MySQL Error 10048 on nested Delphi Queries
I'm having a problem with MySQL (Server 5.1.11) interfacing with a Delphi application
The problem occurs when trying to use the application to do a bulk export to Excel. During the application ...
