2
votes
0answers
313 views

How to speed up SQL Server RIGHT JOIN on 100MM+ Records - use TVF? [closed]

I use a table insert to UNION a CTE of converting transactions to visits (converting or not) and then summarize the visit data (no details, just pages visited & txns completed) to get funnel ...
2
votes
2answers
1k views

QGIS PostGIS Authentication Failure with “trust” option

I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, ...
1
vote
1answer
141 views

Join Calender Table

I have a Datatable and a Calendartable. I do a join on this tables to get sequential dates if there is no data for this day. My problem now is that there a several id's and i need a count for each ...
1
vote
5answers
27k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
2
votes
3answers
787 views

Why is SQL running the same query longer from another connection? [closed]

Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely? Dont jump to any conclusion just yet, let me explain what I mean... Here is the setup: A Windows ...
4
votes
1answer
88 views

Under what circumstances can a division expression reduce the number of rows in a query's result?

I'm experiencing a problem with a query that I've written. Given a table with the schema: create table somevals(division varchar(100), subdivision varchar(100), ...
2
votes
0answers
124 views

Can't add field that begins with an underscore in Crystal Reports

There's a field in a FoxPro table that I'm trying to pull in a Crystal Report (v10), and it begins with an _ and it doesn't appear in Field Explorer->Fields->Database ...
1
vote
0answers
293 views

Troubleshooting Database Mail issues and Service Account permissions in SQL Server

I am having difficulties troubleshooting a Database Mail issue. When I try to stop and start the service using sysmail_start_sp and sysmail_stop_sp, I see a critical error in the Administrative Logs ...
1
vote
1answer
434 views

Database Relational Design for Multiple Categories

I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
2
votes
2answers
2k views

Create Oracle ASM Disks Without Installing Oracle

I have been asked to setup workstations (VMs) for an instructor's Oracle 11g R2 Workshop. I am finding the documentation that was downloaded from the Oracle Academy site extremely underwhelming. I am ...
8
votes
2answers
5k views

Why is truncate DDL?

I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ? ...
25
votes
5answers
12k views

Handling concurrent access to a key table without deadlocks in SQL Server

I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables. Each row in the table stores the last used ID LastID for the field named in IDName. ...
0
votes
0answers
4k views

Can't connect to SQL Server Windows 7 [closed]

TITLE: Connect to Server Cannot connect to localhost. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was ...
4
votes
1answer
5k views

Where's my Network Alias list in Oracle?

I can't select any Network Alias from the Network Alias Drop-down. My TNSNames.ora file is in C:\Oracle My other machine that is a fresh install doesn't have this problem. I get an error when I ...
8
votes
2answers
7k views

How to Install/Configure Oracle Instant Client on Ubuntu

Installing the Oracle Instant Client on Ubuntu/Debian based systems is awkward since Oracle is RPM centric regarding its Linux support. What's the best way to easily install and configure the Instant ...
2
votes
3answers
31k views

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

This is going to be a long story I'm afraid ;-( I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some ...
1
vote
1answer
195 views

Data Warehousing strategy

I have a busy MySQL database with 1.6 million records a day, I'm looking to replicate this data to another server, then run a clean up job on production database to maintain small database in ...
3
votes
2answers
3k views

SQL Server cluster questions please

We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment. We did a test on one of the instances, and failover test. Manual failover from node c ...
0
votes
2answers
217 views

oracle null value in quotes - yet another newbie question

I have a where clause like "and uc.completion_status in ('[P....]') " here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options ...
5
votes
1answer
8k views

How to find the schema name of a data pump dmp file?

I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line: impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp ...
7
votes
3answers
1k views

What alternatives exist when a table requires too many foreign keys?

We have a base table that defines parts and holds information like part number, description, price, weight, etc. We also have approximately 400 tables that reference the base table and provide ...
1
vote
1answer
892 views

Command line export and import CSV in SQL Server 2008 R2

I have a database xyz and 100+ tables and has to perform the following operations on the command line: I need to create CSV of all tables in one go. CSV with column separator ,, fields enclosed by " ...
1
vote
1answer
191 views

Approach to Analyzing Database Requirements

When presented with requirements for a database driven system, I always find it useful to imagine a world with no computers, where data records were kept in filing cabinets and data was collected by ...
6
votes
4answers
34k views

Oracle startup error (could not open parameter file)

I installed the Oracle 11g server on Ubuntu 12.10. But I can't start the server. When I start up the server, the following error occurs. SQL> startup ORA-01078: failure in processing system ...
3
votes
0answers
702 views

INSERT BULK in SQL Profiler without values

I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated: SQL BatchStarting: insert bulk table_name <column_list> but there is no ...
4
votes
2answers
168 views

SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct. I inherited this database and it came to ...
1
vote
1answer
11k views

Images, PDF, audio and video files in MySQL

I am designing a database for an eCommerce website. How can I store images, audio, video and PDF files which are uploaded from the online users, in a MySQL database? I am using the InnoDB storage ...
1
vote
1answer
9k views

How to return resultset from MySQL Stored Procedure using prepared statement?

DELIMITER $$ CREATE PROCEDURE List_IL() BEGIN DECLARE Project_Number_val VARCHAR( 255 ); DECLARE Temp_List_val VARCHAR(255); DECLARE Project_List_val VARCHAR(255); DECLARE FoundCount INT; ...
-4
votes
0answers
101 views

Can I move up rows in the memory itself? [closed]

Lets say i have the following table : id col2 col3 1 1 1 2 2 2 3 3 3 4 4 4 And I'm trying to move rows to different ...
4
votes
2answers
2k views

Implementing soft delete

Could someone please show or describe to me how to implement soft deletes? I have table of vouchers with these fields: id, username, password, serial. I want to display a given number of rows ...
1
vote
1answer
1k views

Alternative tools to export Oracle database to SQL Server? [closed]

I've got an Oracle database that I need to export (schema and data) to SQL Server. I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient ...
10
votes
1answer
557 views

how do I insert a default row?

If I create a table with an identity column as primary key, and all the other columns have default values, for example create table rr (id int identity(1,1) primary key, dt datetime default ...
1
vote
0answers
1k views

Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below [closed]

The error: Unable to read data from the transport connection. an existing connection was forcibly closed by the remote host. The conditions: I have the following connection string: server = ...
4
votes
1answer
1k views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
2
votes
0answers
131 views

Digital asset management

I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index ...
4
votes
0answers
111 views

Postgres TupleTable like data structure

I'm writing my own Group by operator (non-hashed implementation) and I'm currently looking on a good data structure to store the result groups. There are two requirement in my mind: the table need to ...
2
votes
1answer
966 views

unique constraint violated (ORA-00001) in concurrent insert [closed]

I have a procedure that is called from concurrent transactions: //Some actions here INSERT INTO table1 (table1_id, table1_val1, table1_val2, table1_val3) VALUES ...
5
votes
2answers
4k views

List DB2 backups

Is there a way to list the DB2 backups? All I can find is db2 list history backup all for <dbname> but I think you need to check through all of those to see if they've since been deleted. This ...
4
votes
2answers
7k views

How do I remove duplicate records in a join table in PostgreSQL?

I have a table that has a schema like this: create_table "questions_tags", :id => false, :force => true do |t| t.integer "question_id" t.integer "tag_id" end ...
8
votes
1answer
406 views

Strategies for organising SQL Server with large amount of RAM

We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if ...
1
vote
1answer
70 views

Leaf nodes for averages [closed]

I have the following MDX tuple calculation for my KPI in Dashboard Designer: AVG([Person].[Person].children,[Measures].[Hours]) This works perfectly when, for instance, I view it by Team name. ...
1
vote
1answer
149 views

DB2 critical alert “database partition availability”

I have a existing DB2 database that I'm just getting started with and I have connected IBM Data Studio (web) to it and it says that it has the alert "Database Partition Availability" but it doesn't ...
2
votes
1answer
3k views

Specify Server for DBMS_Scheduler Job in Policy Managed RAC

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that ...
1
vote
0answers
61 views

After streaming replication has failed, how to get it back again?

I have a similar problem to this: Replication has failed; how to get going on again? Essentially my slave failed, and now complains "requested WAL segment 0000000100000135000000E4 has already been ...
32
votes
6answers
17k views

What is a good, repeatable way to calculate MAXDOP on SQL Server?

When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's ...
5
votes
3answers
5k views

UNION is slow but both queries are fast in separate

Dunno what else to do about this one. I have one table that has has a start and a stop columns and I want to return the results of it joined both by start and by stop and I want a clear distinction ...
5
votes
0answers
2k views

Is there a Postgres admin GUI that can execute a master file containing multiple sql files

I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file. example: ...
3
votes
3answers
888 views

Sum Up values in a query based on other information

I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this: I get these results by running this statement: select distinct a.eventnum, ...
2
votes
0answers
153 views

Can the same database be log-shipping secondary and primary at the same time?

Here is my scenario: Database DB1 on Server1 is log shipping primary in data center. Database DB1 on Server2 is log shipping secondary; Server2 is in remote location. Logs are shipped from data ...
1
vote
2answers
769 views

Performing SELECT on EACH ROW in CTE or Nested QUERY?

This is a problem in PostgreSQL I have a table which stores the tree of users; +------+---------+ | id | parent | |------+---------| | 1 | 0 | |------|---------| ...

15 30 50 per page