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

learn more… | top users | synonyms (2)

1
vote
1answer
42 views

Can I iterate a value depending if the previous is what I want?

Suppose that I have this table in PostgreSQL 9.4: CREATE TABLE trajs ( id serial NOT NULL PRIMARY KEY, obj_id integer, traj_id integer, geom geometry, t timestamp without time ...
1
vote
0answers
31 views

Can't drop table after creating table with wrong engine

I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ...
1
vote
1answer
207 views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
0
votes
1answer
36 views

convert historic local time to UTC taking into account of daylight saving

How should I convert historic local time to UTC taking into account of daylight saving? The main problem is the daylight saving doesn't occur at a fixed period every year and hence I cannot think of ...
1
vote
1answer
323 views

Get the old version of stored procedure code in sql server

We have modified one of our stored procedures code and accidentally committed it in production DB also. Now we need the older version of the code. Is there any way to get the old version of a stored ...
0
votes
2answers
471 views

Oracle Error: the sga requires more space than was allocated for it

(Oracle SQL 12c running on Windows 10) My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this ...
0
votes
1answer
57 views

How do I save result of a join to another table?

In PostgreSQL I can save result if SELECT to another table with SELECT INTO: SELECT id INTO new_record_ids FROM new_table EXCEPT SELECT id FROM old_table ORDER BY id; However this doesn't ...
7
votes
3answers
474 views

Single service account running all SQL Servers

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of ...
12
votes
6answers
9k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
0
votes
0answers
14 views

Replication issue SQL Server 2014

I set up a merge replication between one Publisher and a subscriber[ the same tables on both] and everything seems to be working fine and there was not a single conflict in the initial setup and ...
0
votes
2answers
207 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 ...
1
vote
1answer
32 views

Multiple subqueries in columns with joins to return multiple rows

I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
0
votes
1answer
300 views

How to use explode mysql function in where condition with cakephp

I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 . Now my question is that how can i check if new posted payment method id is available in ...
1
vote
0answers
18 views

How can I flatten value fields from a mapping table into a single column on an output report? [duplicate]

I have a mapping table structured similarly to the following: ID EntityID EntityName EntityCategory ValueID ----------------------------------------------------- 1 1 ...
3
votes
2answers
36 views

Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

I have 3 tables in database Task, User & assignedTo. Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to ...
1
vote
0answers
32 views

Determine kind of this SQL Deadlock

I have a Deadlock file & i can't determine the kind of this deadlock. Read-write Write-write Parallel query Index Other I stuck on this issue a week :( Here is: Deadlock File Note: open ...
4
votes
4answers
75 views

Select rows for which at least one row per set meets a condition

I have following table: create table test ( company_id integer not null, client_id integer not null, client_status text, unique (company_id, client_id) ); insert into test values (1, 1, '...
0
votes
2answers
49 views

Get row with maximum in one column [closed]

The task: What ingredients has the maximum amount of content in Pancake? The tables: Recipe (Name, group) Ingredient (Iname, price) Composition (Name, Iname, Amount) I tried this query: Select ...
0
votes
2answers
41 views

Data recovery from system or application crashes in SQL Server 2012 [closed]

Can anyone please let me know what are the steps to recover the database after system is crashed? I would really appreciate it. I am a junior DBA with not a lot of experience started as intern. ...
0
votes
1answer
341 views

Error importing SQL Azure Bacpac to SQL Server 2014 LocalDb

Downloaded a v12 SQL Azure bacpac, and trying to import it into a SQL Server 2014 LocalDB... I get: TITLE: Microsoft SQL Server Management Studio ------------------------------ Could not load schema ...
5
votes
1answer
374 views

SQL Query for cumulative sum

I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple ...
3
votes
2answers
67 views

Union of results of all database query to also include database name

I'm hoping someone can help. I have a number of servers each with multiple databases on it. All of the databases have a table I want to query with a very simple 'select count(*) from section'. I ...
3
votes
3answers
67 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
0
votes
1answer
25 views

Errors declaring variables in stored procedure?

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this. I ...
1
vote
2answers
4k views

Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as: Cannot Add Foreign Key Constraint And am I on the right track? My schema looks like this: DDL: DROP DATABASE technology_university_hospital;...
1
vote
1answer
54 views

SQL Server Replace not returning expected result

Shouldn't the below code return 0? SELECT REPLACE(ISNULL('',0),'',0) This doesn't return 0 either.... SELECT REPLACE('','',0) I have an nvarchar field in a table that contains numerical values ...
0
votes
0answers
24 views

How to automatically update a table derived from another table when the latter is updated

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...
2
votes
2answers
76 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
0
votes
1answer
43 views

SQL Server 2012 Reconnects each time I run a query

I use SSMS 2012 and each time i run a query especially the right click select top 1000 rows the database has to reconnect. I am not required to input a user and password but it is frustrating having ...
2
votes
1answer
62 views

How to cope with SQL Server 2012 vs SQL (Azure) Database sql files

I'm trying to port my system to SQL Database (Azure instance) from SQL Server 2012. I'm using Visual Studio 2013 and I have my .sqlproj with all the definition of my database inside. Given that a ...
0
votes
1answer
38 views

Insert into matching on a WHERE clause

I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (...
-1
votes
2answers
69 views

How select column does not list in Group by clause [closed]

select year,sum(mark),sum(maxmark) from table1 group by parameterno get an error Column 'table1.year' is invalid in the select list because it is not contained in either an aggregate function ...
2
votes
2answers
184 views

Find all users who rated all the movies rated by a specific user

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
1
vote
3answers
133 views

Include logical key on each table vs. join

I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the ...
2
votes
0answers
48 views

Constraint violation in a trusted constraint

I have a table with a constraint on it that has an existing record that is in violation of that constraint. While this is possible if someone applied the constraint without a check and after ...
2
votes
1answer
105 views

Compare two tables with same structure, but differing membership numbers

I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below. The ...
0
votes
1answer
146 views

Please explain why deadlock is detected in my example

I think Deadlock will happen when T1 lock tabA and T2 lock tabB. Then querying T1 to tabB (locked by T2) and T2 querying to tabA (locked by T1). So T1 and T2 will wait and wait till end of life! But ...
3
votes
2answers
44 views

Table with N associations

Suppose I have three tables: meta_tags, users, posts. (just an example) Users and Posts both could have tags, so my meta_tags table could be structured like meta_tags - user_id - post_id - ...
1
vote
2answers
69 views

Please explain the difference between row and page compression

I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community. I have read several articles, but wanted to ...
0
votes
0answers
58 views

MySQL: Log only 'select' queries executed on a specific set of tables of a database

I have a Magento site in which I want to log only SELECT queries(after SELECT it could be any column or all columns, in short all SELECT statements) executed on a specific set of tables, also as it's ...
5
votes
1answer
88 views

When you switch Table A to Table B, does the index data get switched as well?

I currently have a rather large table (5-7 million rows). This table is rebuild regularly by a procedure which builds the data in a staging table, then switches the data into the production table ...
1
vote
2answers
36 views

Scalable way to track who's viewed a post? [closed]

I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly ...
0
votes
0answers
26 views

counting and sorting join mysql

i have table like bellow it store of amount of buys of each user +--------+----------+ | userid | amount | +--------+----------+ | 1 | 1000000 | | 2 | 80000000 | | 3 | 7000000 | | ...
0
votes
1answer
40 views

Hierarchial, tree-like data model, which DB type? [closed]

I have the need for a database, I know what to store, however the question is what database to use and why. I have used relation SQL databases a lot in the past (up to a few years ago) and I know I ...
0
votes
0answers
34 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

Daily at 5:33:18 AM I am getting this alert , i checked error log and found these entry daily. what is the reason behind this and what is the fix ? Login failed for user 'NT AUTHORITY\ANONYMOUS ...
-1
votes
0answers
20 views

How to get CPU usage for the whole sql server (not single database) [duplicate]

I have collected and modified some sample codes to collect SQL CPU metrics as shown. IF OBJECT_ID('DISK.dbo.cpu_activity') IS NOT NULL DROP TABLE cpu_activity CREATE TABLE cpu_activity ( ...
2
votes
2answers
302 views

MySQL Escape Characters

I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP. This is an example: The name to ...
0
votes
1answer
15 views

Error while executing query using send attachment

when i am executing the below query, I am getting error as:- Msg 22050, Level 16, State 1, Line 4 Failed to initialize sqlcmd library with error number -2147467259. This is my query:- EXEC msdb.dbo....
2
votes
2answers
299 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
-1
votes
0answers
26 views

What is an efficient way of issuing a join query where the result from the 2nd table must be chosen from a set of rows for each row in the 1st table?

I'm modifying a question/answer forum component for a site and I want to create a page where each question is accompanied by it's top answer in a single panel. Here is an example (scroll down to the ...