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
77 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
1
vote
3answers
178 views

Best practice to query administrative views for instance level elements

I want to query a monitor element at instance level (for example the memory used for the instance) via a query like this: SELECT MEMBER, MAX_MEMBER_MEM, CURRENT_MEMBER_MEM, PEAK_MEMBER_MEM FROM ...
1
vote
2answers
1k views

Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table)

I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made ...
1
vote
1answer
178 views

creating 2 database at the same time in db2

Do you know whether it is safe to have in one shell user db2inst1 creating a database A and having another shell the same user creating at the same time database B? create database A # shell 1 ...
1
vote
3answers
4k views

How to get a list/result of best selling items

I'm trying to get the results of my best selling products using SQL. My table is order_items which has the following columns. id|product_id|qty As there is a QTY field I have to somehow count or ...
1
vote
1answer
416 views

Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way? I think that it ...
1
vote
2answers
52 views

Convert to JOIN [duplicate]

I have a correlated sub query, which is taking 16 Minutes to execute how can i convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE SSF.SM_StockCode = ...
1
vote
2answers
2k views

Optimizing removal of duplicate rows from recursive CTE query

I need to write a recursive SQL query to fetch multiple projects together with their lineage from the following table: CREATE TABLE #Project ( Name VARCHAR(10), ParentName VARCHAR(10) ) INSERT INTO ...
1
vote
1answer
612 views

counter table in mysql

i was going through the book: High performance MySql 2nd edition. In chapter 4 it says: An application that keeps counts in a table can run into concurrency problems when updating the counters. ...
1
vote
1answer
1k views

SQL Server 2008 database restore

I have database Registered and I backup that database from right click on database and backup and it is store registred.bak file. After I format the system once again I want to restore that database ...
1
vote
1answer
3k views

Override/kill when Code 3/SQLSTATE=57016 Occurs

I have encountered issues a few times when a process of mine performs a bulk load to a temp table we use for pre-processing purposes. The Bulk load fails for one reason or another (this we are looking ...
1
vote
2answers
489 views

Get a number nth bit value in oracle

How can I get nth bit value in oracle database? I am currently using this query: WITH a AS (SELECT 7 val FROM DUAL) SELECT CASE BITAND (a.val, POWER (2, n-1)) WHEN 0 ...
1
vote
2answers
314 views

Cross Join with Filter?

i need to make Sp to distribute students to their sections the procedure take 2 string parameters StuID and SecID in case I've send '1,2,3,4,5' as StuID and 'a,b' as SecID i'm using spliting function ...
1
vote
2answers
670 views

SCCM 2007 SQL Query Code - Help Needed

I am trying to write a report where I grab data showing all server collections with maintenance windows. How I want to do this is to show only those server collections where those computers are also ...
1
vote
2answers
2k views

mysql… compare and match items in same column

I have a column full of server names. For example; LouServer01 VgsServer01 LouServer02 VgsServer02 I need/want to be able to create a query that can match those two names based on everything after ...
1
vote
1answer
92 views

Count duplicate versions of databases (SQL Server 2008 R2)

I wish to pull statistics on the number of duplicate database versions we have running in our environment - most databases run with version control in the database name, so I built this query: SELECT ...
1
vote
2answers
370 views

Error While restoring DB2 db from AIX to AIX server?

I am required to migrate 1 database One AIX server to another AIX server. To achieve, I took backup from 1 machine and followed below steps 1st Command I tried is as following. db2 restore db ...
1
vote
2answers
6k views

How can I execute a PL/SQL procedure for Oracle via JDBC?

I have a Java webapp with an Oracle DB, and I try to execute this statement at the end of a data migration script: EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_SCHEMA', tabname => ...
1
vote
1answer
216 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
1
vote
2answers
463 views

ROWNUM equivalent in Centura SQLBase (aka Gupta)

Clarification: This is NOT MS SQL server. [As there is no suitable tag for centura-database and I am not allowed creating tags I had to use this generic one - sorry about that] I have a Centura ...
1
vote
2answers
1k views

Improve performance with the WHERE NOT IN sub-select clause

In the following query, I have to count transactions for each customer. [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year. ...
1
vote
1answer
158 views

Suppress repeated performance condition alerts

I have set SQL Server performance condition alert with details as follows: Object : SQL Server:Databases Counter : Percent Log Used Instance : My database name Alert If ...
1
vote
1answer
84 views

DB Certificates [duplicate]

I hope this is the right place to as this question. I just earned a Bachelor's degree in Management Information Systems and I'm interested in becoming a Database Admin. However, I'm a little confused ...
1
vote
1answer
26 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. ...
1
vote
1answer
555 views

Selecting from an excel spreadsheet into SQL Server table

This question has been asked before but I tried giving full admin rights to the SQL Server user on C:\temp\ folder location (I am using Windows authentication into SQL Server Express). So for the ...
1
vote
1answer
596 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 { ...
1
vote
2answers
542 views

Stored Procedure in MySQL

I am new to MySQL Stored Procedure, in my first attempt I got the following issues, Stored Procedure: Delimiter $$ create procedure accountstatus ( IN inmode varchar(27), IN ...
1
vote
2answers
1k views

Query for master in Postgres replication

I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication. From a slave, I can: SELECT pg_is_in_recovery() And ...
1
vote
1answer
2k views

Need to replace only first occurrence of string

I hope I worded the question right. Here are the specifics: This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I ...
1
vote
1answer
2k views

Mysql query grouping by two columns for max date takes too long

I have table that looks like this: l_p l_a l_timestamp l_n l_act l_name 123 321 2011-1-1T01:00:00Z B q das 21 23 2012-1-1T01:00:00Z C q sd 123 ...
1
vote
2answers
8k views

Query WHERE clause and JOIN

I'm having an issue using a WHERE clause and JOIN. SELECT * FROM `CallDetailRecord` WHERE `StartTime` >=1357102799000 AND `StartTime` <=1357880399000 JOIN `CallEvent` ON `EventID` = ...
1
vote
1answer
599 views

How do I refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)

I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh ...
1
vote
3answers
163 views

T-SQL Issues With Defining 'AS'

I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently ...
1
vote
2answers
7k views

How to Drop Tables using a variable in SQL Server?

DETAILS: I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table. WHY?: In my Access Database I am running a ...
1
vote
1answer
456 views

Why is CDC capture failing due to a missing dbo principal

I am having a problem running the CDC change capture job against some of my test databases. The error message I'm getting is the following: Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
1
vote
1answer
4k views

How to use array variable in query in PostgreSQL

Create table t1 ( xcheck varchar[], name text ); CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[]) RETURNS record AS DECLARE xrc as record; execute 'select name from t1 where xcheck @> ...
1
vote
1answer
2k views

Cascading Inserts in MySql

I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup ...
1
vote
2answers
282 views

How to migrate data from SQL database to MySQL including flat files?

one of my friend has asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now my friend asked ...
1
vote
1answer
25 views

Reserving dates with quantities, table schema wrong?

Okay I have a table like so. ID Productname Quantity Image QuantityOut LastDateReserved 1 productOne 5 'image.jpg' 3 2013-03-15 2 ...
1
vote
6answers
101 views

Performance about this simplified where-condition query

I have a query in SQL Server 2008 like this: SELECT a.type, a.name, a.startDate, a.endDate, b.key AS key, b.is_locked AS is_locked, ...
1
vote
3answers
1k views

How to generate TPC-DS query for sql-server from templates

I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale ...
1
vote
1answer
351 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 ...
1
vote
1answer
378 views

How to Reorganize Microsoft Access Table

I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...
1
vote
2answers
61 views

Need logic to collate data

I have a table in the below structure. phone bigint answer_time datetime I should group all phone numbers, and its total number of attempts and mention its no:of time answered and no:of time not ...
1
vote
1answer
193 views

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...
1
vote
1answer
63 views

Correct data model for users with clients

I have an application where users are granted access. Part of the system allows users to have their own 'clients'. I have a users table ******************************** users ...
1
vote
2answers
144 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...
1
vote
1answer
497 views

Set based solution to group multiple rows within a sequence

Using SQL Server 2008 and a set based solution, how could I manipulate the following data: GroupID Code StartSeq EndSeq StartDayNo EndDayNo 1755549 3506 0 0 59442 ...
1
vote
2answers
8k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
1answer
4k views

SQL Query Killed, remains in Kill/Rollback with estimated time increasing

I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output: SPID 64: transaction rollback in progress. Estimated rollback ...