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

learn more… | top users | synonyms (1)

1
vote
3answers
67 views

How to create a non-sa sql server login / access without control server permission

I have an ms sql server, and I need to give access to other users to run basic queries (just select on 1 database). I have created a login for a user, and have amended their permissions, but once I ...
4
votes
1answer
112 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
1
vote
2answers
104 views

Are these database indexes correct for my search

I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/ I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows ...
1
vote
0answers
59 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
0
votes
1answer
54 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
0
votes
1answer
40 views

Update row contents on some condition

For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also ...
1
vote
1answer
47 views

Oracle: using “as of” clause with table aliases?

I can run this flashback query with no problem: select x from a as of timestamp sysdate; But if I use a table alias I get an error. select foo.x from a foo as of timestamp sysdate; ORA-00933: SQL ...
0
votes
0answers
30 views

Display Complete Month With or Without TIMESTAMP field

Please edit the title if you have a better, I didn't find other words to describe this. Environment Fields: |id|time|text| Situation I have to make a report with the total of records per day, ...
1
vote
1answer
39 views

Restoring an Oracle database from files

I am running into a problem now, i have an oracle database that is stored inside a server and now because of the sudden blackout the c drive is messed up. The drive is partitioned, windows on c and ...
2
votes
1answer
61 views

Get max url from PostgreSQL statement

I have a very complex query which generates a view in PostgreSQL. Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. Now we still want to group ...
0
votes
3answers
70 views

How to update one column with the value of another if one of their columns match. SQL

I've found a lot of similar questions, but not exactly what I need.I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in ...
2
votes
1answer
90 views

SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job. Now it fails intermittently with the ...
1
vote
1answer
66 views

How to get max value of union query?

My database schema is as follows Department (dept_id, dept_name) Student (student_id, student_name, major, level, age) Professor (prof_id, prof_name, dept_id) Class (name, meets_at, ...
0
votes
0answers
55 views

Could not open file control bank (FCB) for invalid file id 0 in database [closed]

Has anyone come across this error before? I have recently encountered this error when running a query on one of our databases. I ran a checkdb and found no errors in the database itself. The db ...
1
vote
1answer
25 views

Help writing a query that select the min column in a group by

I have two simple table CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, nickname varchar(35) NOT NULL, PRIMARY KEY (id), ); CREATE TABLE user_service ( id INT NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
37 views

Limit number of user logins in SQL Server

I have a user login named 'manager' currently logged in PC1, I want to prevent this user from login in PC2. So I created a trigger, and I got an error Msg 4621, Level 16, State 10, Line 2 ...
0
votes
1answer
40 views

ODBC connection fail when running AD account for SQL Server services

I am following up a question from Remote backup to folder path in domain I asked earlier. I could not get my proxy SQL Server Agent user to work to back up to a remote location and have given up for ...
1
vote
1answer
42 views

Jetbrains 0xDBE : How can I load a SQL Server .bak file?

I'm trying this new Jetbrains IDE and I can't figure out how to load a SQL Server 2008 R2 .bak file. Maybe I'm looking in the wrong menus. Is it possible?
2
votes
1answer
81 views

How to refactor this subquery to be written only once?

procedure foo(p1 IN INTEGER, p2 IN INTEGER, ooo out sys_refcursor) is begin open ooo for select T1.* from T1 where T1.x=p1 and ( p2 in (SELECT z from T2 where ...
1
vote
1answer
34 views

Multiple inserts across tables with serial primary keys

I asked this question on stack overflow, but I'm thinking it's better on this stack. Apologies if I've got that wrong. The motivation for this question is to prepare some sql scripts I can run during ...
3
votes
3answers
97 views

Having both a integer Primary Key and a GUID/UUID collumn on same table-is this bad

I have my MySQL database having tables where on each table, we have a ID (int,auto increment) Clustered Primary key and a UUID(varchar36) collumn thats just a GUID. The ID is there just for indexing ...
4
votes
1answer
83 views

Getting Unavailable dates for renting a product that has stocks

Database queries, normally so simple, yet sometimes so difficult. (brain trainer) So I have products, stocks and rentStockOrders. These products can be rented for a set of days. The stocks also have ...
1
vote
1answer
33 views

Cursor with Begin and End Transaction

I've got the following script that keeps hanging on the 2nd iteration through the loop. BEGIN BEGIN TRY BEGIN TRANSACTION DECLARE @itemID int DECLARE LoopCursor CURSOR FOR ...
1
vote
2answers
70 views

Join on multiple columns with OR

I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ...
0
votes
1answer
23 views

Modify table constraint in PostgreSQL

Say I have a constraint ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); that needs to be modified so that zipcode length is 6. Do I have the drop the constraint ...
0
votes
2answers
46 views

More Efficient Use of Union

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list ...
0
votes
1answer
30 views

Catching Errors in procedure

I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has ...
1
vote
3answers
195 views

Replication taking too much time between requests

We are using transactional replication in SQL Server 2014 with one master (publisher), 1 distributor (dedicated server), and 3 slaves (subscribers). All writes are made to master and reading is done ...
1
vote
1answer
65 views

what is the formal statement terminator with db2 sql

I have added / character as statement terminator all in my db2 statements. I can run sql file in data studio client without any issue. But when I run the sql file in db2 command prompt by issuing db2 ...
1
vote
2answers
64 views

would it be possible in postgres to get a count of sql hits on a select statement

Say I have the following table in Postgres 9.4: items id | name | tag_ids 1 tire -1--2- 2 wheel -1--3- 3 transmisson -3- I'd ...
0
votes
0answers
32 views

How do I design a database for a registration system?

The concept is something like this: Organiser creates event. User registers for events. Events can be for multiple days. One event can have different competitions. User can enter into any ...
6
votes
1answer
170 views

How to find values with multiple consecutive upper case characters

For a project that I am working on, I need to identify values that have incorrect uppercase. For example, I need it to identify the following types of values: Mr JOHN Smith MR John Smith Mr John ...
1
vote
2answers
46 views

Displaying a value associated with a maximum value

I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product ...
3
votes
0answers
27 views

Network Traffic increase with Small Result Set

I am currently executing a stored procedure that is using up a ton of network traffic when it executes. The stored proc executes multiple stored procs within itself as well as functions and ...
0
votes
1answer
38 views

A weak entity with many to many relationship with its owner SQL

I have this patient table which is an owner entity: CREATE TABLE PATIENT (FILE_NO NUMBER(4) PRIMARY KEY, NAME VARCHAR2(25), SEX VARCHAR2 (7), BLOOD_GROUP VARCHAR2(3), ALLERGIES VARCHAR (30), ...
0
votes
1answer
101 views

Multiple criteria for including / excluding rows

CLARIFICATION UPDATE Unfortunately we report based on the 3rd party application and database so we cannot modify the datatypes since we do not own it. To help clarify, This report basically shows us ...
0
votes
2answers
53 views

Missing rows in Visual DBA, exist when queried specifically

Greetings fellow DB Admins, I have an interesting issue on which Google has failed me. Background: I am working with medical records managed with Ingres SQL. The other day I wrote a simple query in ...
1
vote
1answer
41 views

Unknown column 'actions_in_group' in 'field list'

I keep getting: Unknown column 'actions_in_group' in 'field list' Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19 SELECT *, SUM(actions_in_group) AS total_rows_in_group, ...
0
votes
2answers
104 views

How to tune a SQL query?

I have a question about SQL query tuning. How can rewrite that SQL query for better performance? Query: select * from ( select ( select max(rn) from ( select ...
2
votes
1answer
64 views

Updating table based on conditions (overlapping dates)

Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update ...
3
votes
1answer
92 views

Misaligned Log IO Issues and Unmatched LUN sector sizes in AlwaysOn

I'm running into a little bit of a snag on a high log send queue in a 2014 SP1 Asynchronous Mode AlwaysOn Environment with a secondary replica in Azure. For one of our higher transaction volume DB, ...
1
vote
2answers
46 views

SQL Query to find Records companywise

I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes See the Sample Data below : addon status amt1 amt2 company ...
0
votes
1answer
47 views

Slow query with JOIN

I have a stored procedure which runs pretty slow (6 sec) using the following JOIN: JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange) Without ...
0
votes
1answer
17 views

How does the correlation statistic parameter affect the cost of index scan?

I have the table customers having customerid SERIAL as a primary key. Initially I had the following statistic for that column: attname n_distinct correlation customerid -1 ...
0
votes
2answers
26 views

Visual studio 2010 missing BI 2010 (SSDT 2010)

Where can I find a link to install SSIS 2010 ( SSDT 2010 or Business Intelligence 2010) I have VS 2010 on my machine.
2
votes
1answer
39 views

Display equal ranking if count is the same

I am trying to figure out how to display ranking correctly where if multiple authors have the same article count then they get the same ranking and then the ranking resume at the correct spot. For ...
0
votes
1answer
36 views

Combine column from multiple rows into single row with id

I have on table which store enter and exit of people in multi rows like: EorL 1 = enter EorL 2 = exit or leave id | date_ |time |EorL| 1 | 150201 | 09:14 | 1 | 2 | 150201 | ...
0
votes
2answers
116 views

Stored procedure optimization

In my application I am using the below stored procedure, but it is taking a long time to execute. Both tables contain millions of records. Please guide me to optimize this procedure. I have searched a ...
1
vote
1answer
44 views

I get a query error 'unknown column' while the column exists

I get error (1054): Unknown column 'wp_jpnx_survey_code.supplier_id' in 'field list'But that column does exist... Does anyone know why or how to fix this? This is the code I used: CREATE VIEW ...
0
votes
2answers
45 views

What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity?

I am new to Database. I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile. What is the most efficient way of doing so? This is ...