Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (1)

2
votes
1answer
434 views

SQL Server Replication - setting up scheduler issue

We have tasked to set up a scheduler to synchronize data between a publisher and subscriber on daily basis. The subscription was set using windows sync manager and it runs just fine when run manually. ...
0
votes
2answers
907 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
0
votes
1answer
174 views

Merge two tables on partly matching column

I have below 2 tables. old_name old_value Tom1 100 Kate1 80 Jim1 70 new_name new_value Tom2 70 Kate2 100 Jim2 80 I want to make a new table look like ...
0
votes
1answer
41 views

Combine 2 SQL Queries

How do I combine these 2 queries? select a, b, c from MY_TABLE where date < sysdate+1 AND c is NULL order by id select a, b, c from MY_TABLE where date < sysdate AND b is NULL order by id
0
votes
1answer
1k views

Datetime Difference in MySQL when format is Varchar

I have two columns in a table: STOP and START, of type varchar. I want to have the difference between both columns, and I'm using : SELECT DATEDIFF(stop, start) AS difference from T1; It is ...
1
vote
2answers
113 views

Finding the reason of the lost data on oracle database

My application uses Oracle database. At the same time Oracle ERP application and another Windows application (like mine) use same database. Everything looks fine and works synchronized but sometimes ...
2
votes
1answer
687 views

Checkdb giving error for master database

When i run dbcc checkdb on master database i get error like Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, ...
0
votes
0answers
2k views

SQL Server 2012 - The application has failed to start because its side-biy-side configuration is incorrect

When opening Sql Server Management Studio I'm getting the following error message: Then when I have a look at the Event Viewer the following message is displayed related to this: Activation ...
0
votes
1answer
133 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
1
vote
1answer
80 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 ...
0
votes
0answers
186 views

Oracle - How can I improve this query?

We have performance problems with some queries. We always use the rank() function to get the latest entries with a status. The question is, how to speed up things. CREATE TABLE PAYMENT ( CLIENT ...
0
votes
0answers
505 views

Alternatives for UPDATE statement Oracle 11g

I'm currently using Oracle 11g and let's say I have a table with the following columns (more or less) Table1 ID varchar(64) Status int(1) Transaction_date date tons of other columns And this ...
0
votes
0answers
700 views

Snapshot transaction throws random conflict. Should I choose something else? [closed]

I've got a problem with my transaction. Currently it runs in IsolationLevel.Snapshot. The action it does, can take some time depending on the amount of data, it has to import. What it does: Create ...
2
votes
0answers
26 views

optimizing a CTE Hierarchy [duplicate]

I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, and on the ...
0
votes
1answer
759 views

MySQL: Error while reverse engineering database from a sql file

I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error: ERROR: Line 1: syntax ...
-1
votes
1answer
145 views

how to update multiple records for single user in database? [closed]

I want to update multiple roles for user when admin update user roles I have table as follow user_roles username || role_name user1 | role1 user2 | role2 users username || ...
1
vote
3answers
193 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 ...
0
votes
1answer
59 views

Access: search for more than one parameter?

I need to perform several queries, looking for more than one parameter. for example: in my field "lenght" I want to extract values matching both 4.6 and 4.7. then, next time I need 5.8 and 5.9, and ...
2
votes
2answers
1k views

How to define alias in an ARRAY_AGG expression?

I'm trying to return pure JSON from a Postgres 9.2 table. SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW)) FROM ( SELECT album, max(release_year) AS release_year, ...
0
votes
2answers
94 views

Finding duplicates

The scenario is as follows, a while ago a colleague accidentally ran a query similar to DELETE FROM app_i18n WHERE disabled = 0 and translation is null or translation = ''; This is the table ...
0
votes
1answer
149 views

MySQL 5.6.14 will not use proper key/index in query

OS: ubuntu 12.04 amd64 DB: MySQL 5.6.14-community amd64 I cannot figure out why this query is using the wrong index and doing a full table scan on the ar_batch (ab) table. Perhaps someone can see ...
0
votes
2answers
187 views

high traffic mysql hosts

I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user. and for ...
0
votes
1answer
47 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
1answer
78 views

SQL - Referential integrity

seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question: I have three tables or more, refer to those as ...
2
votes
2answers
360 views

Oracle upgrade, v$version what is it actually doing?

There are 42 threads tagged as oracle8i, none of them answer my question... This is Oracle 8i (8.1.5.0.0) SQL> select * from v$version; BANNER ...
4
votes
1answer
124 views

Update old database with SQL dump

I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump. Is ...
2
votes
2answers
1k views

T-SQL - How to filter data from table

I'm new in DB world, so if you can help me it would be great. My problem: I have filtered some data and got a table with data, but need to filter them again. Picture might help you. For me, it is ...
1
vote
2answers
2k 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
708 views

Why DBCC CHECKDB is multiple times in error log

I have an issue with dbcc checkdb command . In the errorlog file I find the dbcc checkdb command multiple time within the interval of 30-45 minutes. I have scheduled a job which grep keyword error ...
0
votes
2answers
224 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
3
votes
3answers
44k views

Oracle password has expired, however I have no way of changing it

When I connect to my local development database, I get an error saying the password has expired. I've been trying to change it with sqlplus to no avail: C:\>sqlplus ...
0
votes
0answers
87 views

Is there a syntax to access the table name in the select query?

In MYSQL. I want to select the records which has the same contents with the table name. for example, consider the following table. <TABLE : tbl_1> +-------+------+ | id | no | ...
1
vote
1answer
275 views

.NET error when trying to create snapshot for transactional Replication

I encountered a bizarre prob today while creating a transactional publication on a SQL 2012 server running on Windows Server 2008R2. I had just had some success creating and initializing some Merge ...
2
votes
2answers
416 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
2
votes
1answer
51 views

Order results from query to have a certain result at the top

I'm not sure there is even a way to do this, but thought I would ask anyway. I have the following example output from a query: Name Country Number -------------------------- Harry Germany ...
0
votes
1answer
2k views

Query: select all rooms which are not booked

I have a simple room booking system consisting of following tables: booking | id | user | begin | end | room | id | name | booking_map | id | room_id | I can list all rooms that are booked at ...
0
votes
1answer
137 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
0
votes
1answer
1k views

MySQL select query result set changes based on column order

I have a drupal 7 site using the Views module to back-end site content search results. The same query with the same dataset returns different results from MySQL 5.5.28 to MySQL 5.6.14. The results ...
1
vote
2answers
362 views

Databases: are there universal datatypes?

While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
-1
votes
1answer
56 views

Relation that combines entries with their subsequent entries, in order of date

I need to come up with a query that can do the following: I have a table with a bunch of transaction records, whose attributes are not important, other than the attribute Date. I need to come up ...
0
votes
3answers
627 views

Handling case-insensitive queries with case-sensitive data

What are some practices for designing a table, or queries, when it comes to case-sensitive data and case-insensitive queries? For example, I have a Products table that holds household goods, and ...
4
votes
1answer
91 views

Query uses Primary Key Index instead of Nonclustered index defined

I have this simple query and I am confused why the index is not working. SELECT lop.RecordID ProspectusID, c.c_code CourseCode, c.c_desc CourseDescription, COALESCE(c.c_major, '') ...
1
vote
2answers
582 views

Cannot add an account [non domain account ] to SQL Server 2012

Background: I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012. When I ...
2
votes
4answers
440 views

Which of these queries is best for performance?

Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
0
votes
1answer
460 views

Party Relationship Model in Practice - Insertions

I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community. Here is a basic representation of what I'm doing (in pseudo sql): Parties - ID, ...
0
votes
0answers
74 views

All databases restoring in Suspect Mode in SQL Server 2012

I've just re-installed SQL Server 2012 (removed the evaluation copy and installed a licenced version) and now every database I try to restore, it fails to completely restore and the database sits in ...
0
votes
1answer
524 views

Setting Audit Events in SQL Server 2012

We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited. So I ran these queries: select ...
2
votes
1answer
3k views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
1
vote
0answers
103 views

MySQL: What criteria should I base my table column order/arrangement/grouping on? [closed]

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
0
votes
1answer
209 views

Not exists/Where NOT in

Doing a pie chart in crystal. Items are Inquiries Referrals Followups A referral has to have an inquiry, A followup has to have a referral. I want to show pie chart of referrals to ...