4
votes
2answers
6k views

How do I find the median value of a column in MySQL?

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 ...
6
votes
4answers
2k views

Duplicate database and keep it up to date - Replication or SSIS?

I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally. We don't need ...
41
votes
4answers
11k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
10
votes
4answers
5k views

Tools to migrate from SQLite to PostgreSQL

I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
2
votes
1answer
982 views

master to multi-slave replication

I am looking at replication of the master database (PostgreSQL) and this is the configuration I have to setup master -- hot-standby (hs) | | --- read only slave (r1) | | --- read only slave (r2) ...
2
votes
2answers
2k views

Sp_helptext Error

I am using sp_helptext to list some stored procs instead of going through the ui. I am having a problem with the naming convention we have chosen to adopt. I have a stored proc nameed [Test.FooBar]. ...
5
votes
1answer
2k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
2
votes
1answer
170 views

Is there a way to load a sql script with a specified encoding into a running SQL Server Management Studio (SSMS)?

Just as the question says. I have scripts with 3 different encodings and feel offended by having to manually select of about 200 possibles I was never interested in. ( I didn't count them ).
9
votes
4answers
1k views

What is the best way to monitor long running queries in SQL Server?

I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and ...
0
votes
1answer
1k views

cant truncate tables with a pk/ fk relationship?

I have a table called Folder with 3 fields: Id (PK), Name, UserId (FK) and another table called User with two fields: Id (PK), UserName i am trying to run this sql: truncate table Folder truncate ...
5
votes
1answer
472 views

What are the common reasons for a Subscription to be deactivated in SQL Server 2005?

I am trying to manage database replication in SQL Server 2005. I have set up my publications, distributors and subscriptions and it seems to work properly at the start. However, after a period of ...
38
votes
12answers
4k views

How could DBAs be more 'programmer friendly'?

The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
4
votes
1answer
4k views

What does TOAD do, when I terminate script execution?

When it stops the execution of a long running script. The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad. 2nd EDIT: Just found that this is a ...
3
votes
2answers
281 views

Report model data source

Does anybody know how i could use a linked server as a data source for a report model in SQL server 2008 r2? or how i could make this linked server a data source on a report model? Please help. Thank ...
3
votes
1answer
4k views

How do I alter index rebuild an entire database?

I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'. I try the following command: ALTER INDEX all ON db REBUILD And I get the following error: Cannot find ...
1
vote
1answer
297 views

Versioning a dataset in an RDBMS using initials and deltas (xpost from SO)

I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, ...
7
votes
1answer
573 views

B-tree node split strategy in SQL Server for monotonically increasing value

Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% ...
5
votes
1answer
505 views

Which is the most performant way to copy a number of SQL Server tables to a readonly SQLite database?

I have a database with about 50 tables with use together about 1 GB space. The SQLite database will be readonly. This copying will be done once each month. EDIT: I see one possible solution using ...
41
votes
10answers
4k views

What are the arguments against or for putting application logic in the database layer?

NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting ...
50
votes
11answers
10k views

Files - in the database or not?

What is the best place for storing binary files that are related to data in your database? Should you: Store in the database with a blob Store on the filesystem with a link in the database Store in ...
11
votes
4answers
846 views

Creating centralized DBA database

We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly ...
1
vote
2answers
2k views

hierarchical query in oracle

Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume ...
1
vote
0answers
513 views

DBCC CheckDB Consistency error on an empty table [duplicate]

I restored a DB from SQL Server 2000. I ran DBCC CHECKDB on SQL Server 2000, and it did not report any errors. What could be the reason behind a DBCC CHECKDB reporting a consistency error on a empty ...
1
vote
3answers
124 views

DBCC CheckDB Consistency error on a empty table

What could be the reason behind a DBCC CheckDB reporting a consistency error on a empty table in sql server 2008? This DB is restored from sql server 2000. DBCC CheckDB on sql server 2000 doesnot ...
0
votes
2answers
1k views

Query PostgreSQL 9.0 table on foreign key value?

Noob-type question: Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code. ...
2
votes
2answers
738 views

How do I properly design a many-to-many (charges/payments) accounting system?

This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance. Basically I'm having trouble ...
5
votes
1answer
1k views

DROP PARTITION strategy using LIST-REF composite partitioning

We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
3
votes
2answers
2k views

Sql Server output parameters

Is there a way to use named parameters when getting the output parameters of a stored proc? Currently my knowledge of output parameters is very limited. It looks like I have to use them in order of ...
5
votes
2answers
739 views

SQL Server - RangeX-X and RangeI-N locks

I came to a dead point in a deadlock analyze. According to msdn: RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range. RangeI-N are Insert range, null resource ...
1
vote
1answer
2k views

“Request to run job dbxxxxx refused” error in merge replication

I'm doing merge replication whole of my database but when I create the subscription I encounter this error: SQLServerAgent Error: Request to run job DBXX-XX--bazresiostani2-2 (from User sa) refused ...
1
vote
1answer
185 views

MySQL queries in SQL-Server?

How can we best use our existing MySQL queries with SQLserver? Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?
1
vote
2answers
572 views

maintenance plan executing by agent

I have a built-in, regular maintenance plan scheduled to run weekly. The databases are SharePoint databases and a snap manager (netapp snap manager) backs up these databases. The rebuild ...
2
votes
2answers
642 views

How to hide metadata from Postgres users?

I need to provide views-only access to some subset of a database, for use by report-writing tools. It's easy enough to use permissions to prevent access to the data in the underlying tables, but I ...
3
votes
2answers
1k views

return scope_identity() question

environment sql server 2005 In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the ...
3
votes
2answers
1k views

Postgres 8.4 — connection refused (remote)

I'm new to postgres and looking to set up a small environment here with it. I've installed PostgreSQL 8.4 onto an ubuntu server, able to login to the postgres, submit commands, do queries, etc... ...
8
votes
2answers
1k views

How to best store Google ngrams in a database?

I downloaded google onegrams some days ago and it’s already a huge amount of data. I inserted the first of 10 packages into mysql and now I have a 47 million record database. I am wondering how one ...
2
votes
2answers
442 views

Use different Windows account in pl/sql

Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean: Other ...
3
votes
2answers
397 views

What are the key points to avoid table locks?

MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software ...
10
votes
8answers
24k views

SQL Server Cannot drop database <dbname> because it is currently in use… but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. ...
4
votes
2answers
2k views

SQL Server 2005 Index Rebuild/Reorganize

I have checked my huge db and run some shrink tasks for 5 or 7 times. I got about 11 GB freespace. This DB is used for offline reporting operations. Before, when we create a report it was growing ...
4
votes
3answers
2k views

When to apply normalization in database design

Hello database administrators. Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior ...
3
votes
1answer
1k views

Unable to create a tablespace, unusual error

I'm having difficulty creating tablespaces with Oracle 11gR2 on a fiber attached disk array. Permissions are correct and there is ample space available. I can create tablespaces on other mounts that ...
1
vote
2answers
4k views

Risks of Switching Between Simple & Full Recovery in SQL Server 2005

Ok, so we have several instances on several nodes in a clustered environment. One of them is for JD Edwards stuff and has had an issue lately with a job failing because there was not space in the ...
0
votes
2answers
686 views

does DBCC ShrinkDatabase do a Reorganize of files?

does DBCC ShrinkDatabase doa Reorganize of files? Basically does it mimic the same process as if i checked Reorganize in the GUI?
2
votes
1answer
372 views

How to determine the name of a process that caused a trigger to fire

Short Version: Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected? Long Version I have a ...
1
vote
1answer
424 views

Can I export a MySQL schema to latex? [closed]

I have asked this question on SO before, but did not get a good answer. I would like to be able to export a MySQL schema to a set of Latex tables. phpMyAdmin does this, but it is buggy and does not ...
4
votes
2answers
2k views

Trigger in MySQL

Please help me for few question in SQL.. I want to create the trigger which prevents insertion in the reservation table of flights (numflights), which does not exist. Tables ...
2
votes
2answers
447 views

Calling a stored procedure in a stored procedure

Environment sql server 2005 My question is Calling a stored proc within a stored proc within another stored proc. When is this type of development detrimental? In order to promote code reusablity ...
4
votes
2answers
552 views

MAX for each subset

I have a table that looks like this: +---------------------------------------------+ | EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE | +---------------------------------------------+ | 123 | 1 ...
2
votes
2answers
521 views

Encountering exception ORA-01555

I was given a problem to solve , in which there is table called Scenarios in the Master Db which contains the details of all the Tablespace for which I have to find the size. The O/P should contain ...

15 30 50 per page