DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

1
vote
1answer
621 views

Alter table move taking long time

I'm currently trying to compress a table in Oracle with the following statements: ALTER TABLE MYTABLE COMPRESS FOR OLTP; ALTER TABLE MOVE; My question is: the MOVE operation is taking forever and ...
3
votes
3answers
771 views

Simple XML .query SELECT statement times out on 1 SQL SERVER instance

I dropped and recreated a stored procedure on a DB on a SQL Server 2012 instance (SERVER1/INSTANCE1), after which the sproc started hanging on a line. Using profiler I have reduced a recreateable ...
5
votes
5answers
6k views

How to select from a table without including repeated column values?

In a previous question How to merge data sets without including redundant rows? I asked about filtering redundant historical data during import, but @DavidSpillett correctly replied that I couldn't do ...
2
votes
2answers
212 views

how to make a particular field into seperate columns?

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production. I have this table in the below format: No | User | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank ...
1
vote
0answers
48 views

A pre-existing hierarchial table as relational

Currently the database contains something like so (these are the main fields but there's a large amount more): +----------+----------------+----------+ | Agency | parent | main_term ...
1
vote
1answer
4k views

Converting ER diagram to tables and relationships in sql

Department : Branch is 1 : M Branch : Course is 1 : M Branch : Student is 1 : M Branch : Applicant is M : N Applicant : Student is 1 : 1 These are the conditions prescribed in the ER diagram. ...
1
vote
1answer
281 views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
3
votes
2answers
67 views

Viewing the SQL of a view from a backup set

I have a full backup of my database from 4 days ago. It is a standard SQL 2008 .bak file. What I'd like to know is if its possible to view the SQL to a View that is in the database I backed up without ...
1
vote
1answer
25 views

MySQL Grouping Error

I'm trying to run a query on a table to see how many unique users have a usage record in the system at a given point. I've been working with the following query, but I've yet to see a proper result. ...
0
votes
2answers
376 views

“an agent job has not been created for this subscription”

I have set up merge replication with a publisher on my SQL Server 2008 R2 box with the distributor on this box too; in Express I have created a subscriber but the subscriber won't run because of the ...
3
votes
1answer
208 views

SQL - I have just Differential backup but database exists

I have Differential backup from my database.one table is deleted.my data is lost.i create that table again but i cannot restore Differential backup because sql wants a full backup first.if i get full ...
2
votes
2answers
180 views

Is there really any benefit in wrapping random ad-hoc queries in a transaction?

I am no db expert, but I was very surprised when someone working at our client's company asked us to send all queries wrapped in a transaction like below: SET TRANSACTION ISOLATION LEVEL READ ...
1
vote
3answers
2k views

SQL Server Database grew too much too fast

I have an issue with one database. The issue is as follows: The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode. In the beginning of this summer the ...
0
votes
1answer
4k views

How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it. I need to create a table where the elements were previously selected from ...
2
votes
1answer
164 views

How can I connect to a database after its disk has come back online?

I have problems with database after reconnecting a hard disk: .Net application works with database. Disconnect hard disk with physical files. Make any request to the database (of course we will have ...
1
vote
1answer
321 views

Creating a table with parent_id from a flat table

I have a flat table (id, city, county, state), Example (1,'Beckingen', 'Merzig-Wadern', 'Saarland') A city belongs to a county. A county belongs to a state. First my attempt was to divide the ...
3
votes
1answer
646 views

Theta join explanation

Given the following question, I'd be glad if someone could help me. Take the following relational schemes: R(A, B , C) S(D, E, F) where A and D attributes are the primary keys. Assume to ...
6
votes
4answers
634 views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 ...
4
votes
3answers
3k views

Select rows after a specific ID

I have the following query, it chooses from a news articles table. Due to multiple sorting factors, I cannot locate the next page of date. The parameter I have is the last ID in the query result set, ...
2
votes
1answer
63 views

Make database use more than one file

I have to following problem: since my database runs out of memory in a few days, I am looking for a way how to make the database use more than one file. I would like to store upcoming data in another ...
8
votes
3answers
3k views

Does disconnecting the network stop a query?

I recently executed an update query against 100,000 records. I realized I'd made a mistake while the query was running and quickly unplugged the network cable. Does the update query stop ...
1
vote
1answer
585 views

postgres+JDBC: fire trigger with non autocommit transaction

i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table public foo() throws MyException { ...
4
votes
3answers
4k views

In Microsoft SQL Server 2008, syntax generates the error “The Parallel Data Warehouse (PDW) features are not enabled.”

I have the following virtual column generated from an aggregate over a sorted partition, MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC ) However, when I execute that, I get ...
6
votes
5answers
9k views

What is the easiest way to get started using databases with real data?

I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living ...
6
votes
2answers
646 views

SQL Server Tuning a table for massive inserts

I'm trying to solve a performance issue and I need advice. I have a table, which about twice a day has a massive surge of inserts coming in, and exactly at the same time everybody is trying to read ...
0
votes
1answer
2k views

How would I store the result of a select statement so that I can reuse the results to join to different tables?

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor. Below is some pseudo code, in this example I have ...
0
votes
1answer
327 views

Connecting to an SQL Azure Database from SSMS - IP Restriction

I am trying to connect to a new SQL Azure Database via SQL Server Management Studio. The client box running SSMS is hosted on a production DB so we restrict access on 1433 to just a handful of IPs ...
25
votes
3answers
13k 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 '' ...
1
vote
1answer
664 views

When MySQL table gets too large

I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the ...
3
votes
2answers
376 views

Adding an index to a system catalog in Postgres

I'm having a situation very similar to the one described here: I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different ...
2
votes
1answer
418 views

Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database. These are the tables (given by my ...
4
votes
3answers
171 views

Expanding a dataset based on a weight field

I have a table that contains records in an un-expanded form. Each record has an associated integer weight that essentially informs us how many times the record should be replicated in order to get the ...
2
votes
1answer
2k views

Sybase SQL - Truncation error occurred. Command has been aborted and find CACHE QUALITY

Need help with below code, as it fails with truncation error Truncation error occurred. Command has been aborted. create table monCacheQuality ( ServerName sysname ...
30
votes
3answers
3k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
5
votes
3answers
7k views

Return multiple rows from matching on multiple CASE matches?

I want to add a column to my query which will specify one or more categories a row matches. I want to take this: +--------------+---------------+ Row | Product | Quantity_Sold | ...
1
vote
1answer
123 views

Number of times a table is read, written to and updated

Is there a way I can find out the number of times a table is read, updated, added to or has had rows deleted during a trace? I am creating the trace in SQL using sp_trace_setstatus, not using the Sql ...
7
votes
5answers
18k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
3
votes
3answers
273 views

why would command text not execute at all?

in my report i have:: <CommandText>SELECT column1, column2, 'poop' from mytable </CommandText> i am using sql server profiler to see exactly what statement is ...
1
vote
0answers
65 views

Rows do not match Subscriber and Publisher

I have an transactional replication setup with updateable subscription that is utilized by an Application. When I insert a project into the application, the msrepl_tran_version is different on the ...
0
votes
1answer
196 views

Suitable database software for small business [closed]

First of all, I apologise if this is in the wrong area. I'm looking for a suitable database program for a small engineering business, which needs to log various jobs. Currently, we're just using an ...
7
votes
2answers
1k views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
-2
votes
1answer
318 views

Query sql-server db and insert results into PostgreSQL

I have a query that returns data from sql-server and I need to be able to put the results into PostgreSQL. I'm using C# for the task.
0
votes
3answers
213 views

Do database engines update when the updated value is the same? [closed]

Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the ...
2
votes
1answer
2k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
0
votes
0answers
149 views

Importing large .sql file stops before even midway and starts deleting

Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported any ideas? ...
1
vote
1answer
55 views

Altering strings while selecting them

I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but ...
1
vote
0answers
197 views

Producing an outstanding fees report in MySQL

This question is about a MySQL stored procedure to select the records from two tables by joining them and also do some arithmetic operations in that. I have 3 tables for fees one is to store fee ...
1
vote
1answer
30 views

Plan for storing transactions in an easily retrievable manner, indefinitely

I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the records as financial transactions. Clients need to be able to view these ...
0
votes
1answer
3k views

Query taking too long to execute

My query takes a lot of time, 22 seconds and above. After 22 seconds, we get more than 900 000 records. We have created indexes on the table which are used in the query: select * from ContactMaster ...
3
votes
2answers
1k views

Pulling autogrowth events by file name from default trace

I use below query to get auto-growth event occurred to databases in a sql server. DECLARE @trcfilename VARCHAR(1000); SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1 SELECT COUNT(*)as ...