All Questions
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
928 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 ...
9
votes
2answers
6k views
What is a Clustered Index?
I need a short explaining of clustered index.
What is a clustered index?
What are best practices for using the clustered index?
2
votes
6answers
678 views
Optimize this SQL query for performance
I'm trying to optimize the following statement:
'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR)
+'/00000' +CAST(@number+1 AS VARCHAR)
The statement produces a value ...
4
votes
2answers
984 views
EXP-00085 warnings while doing full backup
During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings
[...]
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6502 encountered
...
11
votes
4answers
1k views
Why won't some DBMS's allow rollback for certain DDL statements?
Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow ...
11
votes
3answers
6k views
Rebuilding the transaction log
We have a very large database (~6TB), whose transaction log file was deleted. We have tried:
Detaching and reattaching the database; and
Undeleting the transaction log file
but nothing has worked ...
3
votes
2answers
3k views
Calling SQL Loader From SQL Plus
I was wondering if there was a way I can execute a SQL Loader script from SQL Plus.
We are using Oracle 10g.
1
vote
1answer
720 views
What does TBSCAN GENROW mean in Explain Plan?
In a DB2 Explain Plan, what does TBSCAN GENROW (line 5 below) mean?
SELECT STATEMENT ( Estimated Costs = 1.811E+03 [timerons] )
1 RETURN
2 NLJOIN
3 [O] TBSCAN
4 SORT
...
1
vote
2answers
790 views
Connect a linked server to MS Reporting Service?
I've got a MYSQL server setup as a linked server in MS SQL server 2008. Can someone tell me if it is possible to generate reports from this server?
How do I connect it to Reporting Services?
3
votes
1answer
1k views
Error Querying MySQL server linked to SQL Server 2008
I've created a linked server in MS SQL Server 2008 to a remote MySQL server. When I try to query any tables, I get an error:
.tablename. contains no columns that can be selected or the current user ...
14
votes
7answers
1k views
What is a good way to store a large number of columns?
I have a problem deciding how to store this data in my database. Any suggestions on the best way to do it? I don't know a hell of a lot about databases, I might add.
I have data coming in formatted ...
