Tagged Questions

An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

1
vote
2answers
75 views

Getting root node for a given leafs IDs

I got the following tables: +-----------------+ | PRODUCTS | +-----------------+ | #product_id | | #company_id | +-----------------+ | | +---------------------+ ...
0
votes
0answers
24 views

Facing Error while calling DB2 stored procedure from SQL Server 2000

I am trying to execute the following to call a DB2 stored procedure from SQL Server 2000. DECLARE @ICVR_CLAIM_NBR VARCHAR(19) SET @ICVR_CLAIM_NBR= 'XXXXXXXXXXX' EXEC('call PPSPCOL.X2COVSEL (?)', ...
0
votes
1answer
74 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
3
votes
2answers
84 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
3
votes
1answer
131 views

“A severe error occurred” - Login Failed (but only occasionally)

I'm using a C# application to execute a query against SQL Server. Here's the relavent portion of an exception I'm encountering once every twenty-four hours or so: System.Data.SqlClient.SqlException ...
0
votes
1answer
71 views

How to copy database from server to local machine in SQL Server Management studio? [closed]

I am pretty much new to SQL Server Management Studio. I connect to SQL Server using Windows authentication. But not sure if my work affect the server database. How can I make sure not to mess with the ...
1
vote
2answers
159 views

Pulling Top queries returns NULL in query plan and sql text

I'm using the following code to pull our top 20 queries (ordered by CPU): SELECT TOP 20 qs.sql_handle ,qs.execution_count ,qs.total_worker_time AS [Total CPU] ,qs.total_worker_time / ...
0
votes
1answer
37 views

Add range of values under a column using rowid

How do i add range of values in a column based on row id? For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5. Additionally is it possible to divide the then ...
0
votes
2answers
46 views

How to rewrite subquery to return only 1 row

I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please? SELECT DISTINCT user.id, ...
2
votes
2answers
294 views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
1
vote
2answers
53 views

Merge Table Row Values to Header of Another Table

Table 1 id | Field | Values ---------------------------------------- 1 | 'Field01' |Name 2 | 'Field02' |Age 3 | 'Field03' |Gender 4 ...
2
votes
0answers
49 views

Service broker routes and DNS

Apologies for the long post but I've recently seen some strange behaviour with Service Broker when the routes it uses reference a DNS entry, and I am wondering if anyone has seen the same? We have ...
1
vote
1answer
29 views

Are Blob Sizes Fixed?

I'm using sqlalchemy to store objects in a database. The kind of object I'm creating is unknown until run time. I could be storing something as simple as a string. I could be storing a jpg. I ...
0
votes
2answers
49 views

A database of all combinations of 39 numbers taken 5 at a time, total combinations = 575757

I want to create a numerical database of all combinations of 39 integers(1 Through 39) taken 5 at a time. I know the total possible combinations = 575757. This would be the size of the database. I ...
0
votes
2answers
62 views

Insert trigger checking for specific column

I have got an insert trigger on a table. The table got a field named 'source'. The source field could be populated with values b or left empty as in null. i want the trigger only to fire if the source ...
-3
votes
1answer
77 views

Selecting a row and row above [closed]

I need to select a row from a table if it has "PART_NO=2" and the row just above it. What should be the query. I'm using Toad for Oracle. Thanks. **Data** *Title------Part_seq_no------*End_time ...
1
vote
1answer
206 views

Why do ASC and DESC come *after* a CASE clause?

This query works: DECLARE @Ordering NVARCHAR(50) SET @Ordering = 'aggregateid' DECLARE @OrderDirectionInt INT SET @OrderDirectionInt = 0 --1 SELECT * FROM ( SELECT ROW_NUMBER() OVER ...
0
votes
0answers
33 views

MSSQL Insert takes over 5 hours, when inserting more than specific number of rows [duplicate]

I have a question connected with MS SQL. When inserting less then about 1350000 rows to the table it all takes about 2 minutes, but when number of inserted rows is bigger, then a time needed to insert ...
3
votes
1answer
50 views

Transforming multi column table to different structure table

I am a software developer who got data in the following format: TABLE [dbo].[SensorValues]( [timestamp] [datetime] NOT NULL, [Sensor1] [float] NULL, [Sensor2] [float] NULL, [Sensor3] ...
1
vote
1answer
189 views

Best practice to shrink Tempdb in a production environment

What is best practice to use when shrinking Temporary db in SQL Server 2008? Is it risky to use the following? use tempdb GO DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers ...
0
votes
1answer
50 views

export and import all the permissions on schema objects within a database SQL Server 2008

Is it possible to export all the users roles and their permissions on schema objects within a database in a .sql file and import them later in an other database ?
0
votes
1answer
36 views

MySQL select data over time that matches pattern over several rows

We currently log data from our plants running remotely for diagnostic purposes on MySQL. I have been asked to come up with a typical running profile for several variables. For this, I would require ...
3
votes
1answer
335 views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
0
votes
1answer
70 views

Why does mysql give different result for this summations? [duplicate]

Can someone help understand the following behavior? mysql> select (1/3 + 1/3 + 1/3); +-------------------+ | (1/3 + 1/3 + 1/3) | +-------------------+ | 1.0000 | +-------------------+ ...
0
votes
0answers
39 views

How to model relation between customers, tariffs and discout

I have following tables (apart from others, that I will omit here): Customers, Tariffs, Discounts and Products (EDIT: and Orders). Each product has its price, and is kept in appropriate column in the ...
-1
votes
1answer
62 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
0
votes
0answers
23 views

Undo SQL Update Azure DB [duplicate]

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table. I've read around and found ApexSQL Log ...
2
votes
2answers
118 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
0
votes
1answer
52 views

How to JOIN more than two tables?

Following is the schema of my Tables: Marks_Table (SubjectCode,TypeID,MarksObtd) Subjects_Table (SubjectCode, Subject) Types_Table (TypeID, Type) I want to Select the Marks_Table table with ...
-1
votes
2answers
221 views

Remove a character from a records enclosed between double quotes

I have a table in which every row have data like: 0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014 What I want is to remove every comma (,) that is enclosed between double quotes ...
1
vote
2answers
147 views

How to count the rows from 1 table but from two different column

SELECT distinct Major, COUNT(Major) AS MajorCount FROM [College Management System].[dbo].[ApplicantCourseDetails] group by Major SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM [College ...
0
votes
0answers
40 views

mysql select latest per group using 2 different tables along with self join

I have a query SQL Fiddle which contains 2 tables tbl_data CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT ...
1
vote
1answer
23 views

Handling exceptions from inside a function

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
6
votes
3answers
606 views

CHECKPOINT or COMMIT writes to disk?

Let's say, for SQLServer2008R2 and higher, with full recovery mode databases. I always thought : When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM. ...
1
vote
3answers
93 views

How to query for records based on most recent version

I have the following table structure ID, ORDERNUMBER, DATE, STATUS 1, 1, 1-1-01, proposal 2, 1, 1-2-01, proposal 3, 1, 1-3-01, active 4, 2, 1-1-02, proposal 5, 2, 1-2-02, cancelled 6, 3, 1-1-03, ...
0
votes
1answer
95 views

How to generate a sequence in mysql

Consider this table in mysql create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers; +--------+ | number | +--------+ | 3 | | 2 | | 9 | ...
0
votes
2answers
59 views

Oracle GROUP BY question

I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use. ...
0
votes
2answers
24 views

mysql combine tables

I have three tables which has the same schema. all of them has 10000000+ rows and has unique data as well as duplicate keys but with a datetime column can differentiate them and get the newest row. I ...
0
votes
0answers
15 views

Query from database a set of infomation and sorting them out while mataining its structure

I am currently using mysql 5.5.32. Below is my database design. It is used for storing student infomation, however each student have 61 "entries" or more(some schools might have more options), eg. ...
1
vote
2answers
55 views

Merits of inserting to temp table and renaming versus just adding/removing columns

If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed. When I use RedGate SQL Compare to create conversion scripts its strategy ...
0
votes
2answers
70 views

inconsistent type after querying search_condition column in all_constraints table Oracle

I'm trying to get the not null constraints in my tables, so I'm using the following sql request : select * from all_constraints where owner='userx' and constraint_type='C' and ...
2
votes
1answer
170 views

Find if Column Belongs to User Table

I want to compute the number of columns with not null constraint in my database. So I thought about querying the table sys.all_columns: select * from sys.all_columns The problem is that it ...
0
votes
1answer
75 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
0
votes
1answer
25 views

equivalent of oracle sql administration query in SQL Server 2008

I'm new to SQL Server administration, and I would like to get the equivalent of this Oracle 9i administration query : select object_type,count(*) as number from all_objects where owner='ownerSID' ...
0
votes
1answer
30 views

Choosing the Most Recently Entered Non-Blank Value When Many Values Exist

I have run into a problem where some IDs have multiple names associated with them. That is, if I have a location ID from one table and need to pull the location's name from another table based on the ...
0
votes
0answers
156 views

PL/SQL Error bad bind variable

Where is the problem with my code. It should work, but Compiler says: Error(5,14): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' Error(6,1): PLS-00049: bad bind variable 'NEW.NAZWA_GRUPY' ...
2
votes
1answer
64 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
0
votes
2answers
47 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
0
votes
1answer
42 views

Can a SQL Server Subscription Database be unsubscribed and used as standalone?

We've got a new server we want to migrate to and I have setup a Publication on the current production server as a Transactional publication. Since the subscribed database is up to date, I want to ...
0
votes
1answer
25 views

dictionary database relations

I have a dictionary database (french > turkish) which has id, word, caseword, direction and descrblob columns. i don't know how it works this direction column. Because turkish words value is 2, and ...