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
48 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
22 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 ...
0
votes
0answers
12 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 ...
-1
votes
2answers
64 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 ...
0
votes
1answer
18 views

How to display name on a column chart itself in SSRS, not on a legends

How can I display legends name on a column chart itself in SSRS?
1
vote
0answers
46 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 ...
3
votes
2answers
62 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 ...
0
votes
0answers
51 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 ...
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 ...
5
votes
1answer
87 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 ...
0
votes
0answers
33 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 ...
0
votes
1answer
145 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 ...
-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 ( ...
-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 ...
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....
0
votes
0answers
25 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 | | ...
3
votes
2answers
43 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 - ...
0
votes
1answer
39 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 ...
1
vote
2answers
55 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
2answers
40 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
0answers
32 views

Postgres hstore or jsonb to store many to many relations

There is a table 'Products' with columns (id, tile, price, description, shop_id, timestamps) Column price and shop_id are dynamic. The rest of column are static. Also there is a table 'Shops' with ...
0
votes
1answer
74 views

A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I have the following tables in my postgres database - power_relations - (id, properties) - power_relation_members - (id, power_relation_id, member_id, member_type) - power_lines - (...
-1
votes
1answer
53 views

What are static tables & dynamic tables?

I heard the term "static table" when I'am learning about dirty reads in database. Can anyone explain what is a static table and what is a dynamic table ? Thanks in advance.
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 (...
5
votes
1answer
370 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 ...
0
votes
0answers
7 views

MDX query with multiple dimensions on date, including “previous business week”

What is the simplest MDX that satisfies this query: The frequency of an event during each daily 10 minute interval, comparing today and the previous business week (Mon-Fri). The output should look ...
0
votes
1answer
21 views

SQL Error: Must be GROUPed or used in aggregate function

I have just created a SQL statement in Crystal Reports using the command feature, but I am having issues with displaying the data. The error reads: Attribute FUND_ACCT_REG_VIEW.FUND._ACCT_NO must ...
1
vote
0answers
18 views

Two Dimensional Partition

We have a new application that we anticipate will grow substantially and we're exploring partitioning now. If we have a database that includes information, for example, across continents, and we want ...
2
votes
0answers
49 views

Select Query with S lock wins on Insert with IX lock in deadlock

Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2. Attaching the deadlock xml file.Please help! http://pastebin.com/nY9EXbS8 This is from the error log by ...
1
vote
1answer
30 views

How can I use an array variable inside the IN operator for Oracle SQL?

I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();. I insert some varchars into it, and then attempt to use it with an IN ...
2
votes
1answer
227 views

Is there a better alternative than LIKE for this query?

I am trying to retrieve data from a table where there are codes for projects which are identified by the first three numbers, the rest are unique. So, for example, the query is: SELECT * FROM TableA ...
4
votes
1answer
765 views

How can I handle unwanted, non-erroneous situations better?

When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error. Let's say, if I don't want John to be ...
2
votes
0answers
32 views

How works getting the 5 more and 5 less expensive prices of the avg of all prices

I am currently doing a few exercises on SQL. In one exercise on was supposed to find the 5 more and 5 less expensive products to a mean of all products. The code below is working and giving the ...
0
votes
0answers
18 views

Modify records in a query list and adding back the modified records into the master table

We have a MS Access 2016 database. We queried the DB to find some parts in inventory. The first few rows of the query results are below. We have a primary key called DBID with a unique number for all ...
0
votes
1answer
14 views

Visual Studio 2013 and up - Database Project Schema Compare File

Is there any reason to save the schema compare file once you are done comparing, creating scripts or just simply updating?
2
votes
1answer
104 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
0answers
113 views

Which is preferable With clause or formulas in from clause

Query with with clause with temp_table(fName,sCode,sDesc) as( select case left(shipping_info.ship_description, (len(shipping_info.ship_description) - 11)) when 'John Doe' then 'Johnathan'...
-3
votes
2answers
76 views

Case statement by using IsNull and IsNotNull [closed]

ALTER FUNCTION [dbo].[addC]() RETURNS varchar(255) AS BEGIN DECLARE @desc varchar(255); DECLARE @desc1 varchar(255); SET @desc = '' SET @desc1 = '' Declare c1 Cursor for ...
1
vote
2answers
36 views

Inserting BLOBs through PHP are always 0 bytes

I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB. The first row there was inserted manually through phpMyAdmin. The second row was ...
1
vote
0answers
35 views

Return Maximum Rolling/Moving Count For Each Person For Any 90-day Period

Basics I have a table of patients with visit dates. There can be an arbitrary number of patients each with an arbitrary number of visits. A visit is recorded by saving the date of the visit. Only one ...
0
votes
0answers
22 views

Using both NoSQL and SQL in a Project

I am working on a project and would like some advice on my data structure for my backend. Currently I am writing a SaaS Multi-Tenant application hosted in Azure for customers who use the same legacy ...
2
votes
3answers
119 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
3
votes
1answer
55 views

Auto created Statistics vs Manually created Statistics

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement. Will the manually created statistics get ...
3
votes
1answer
75 views

Find items matching only values from a specific list

I have a table like this: Id Status 1 Closed 1 In progress 2 Rejected 3 Cancelled 4 Rejected 4 Closed 5 In progress I want to show rows that have the ...
17
votes
1answer
215 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...