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

learn more… | top users | synonyms (2)

1
vote
4answers
74 views

Setting Select Permission on 100+ tables in a database

I got the 'Select permission denied' error for some of my tables in a database. In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties ...
4
votes
1answer
15k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
0
votes
0answers
103 views

Slow query on large table after new column is added

I have a table with more than 30 million records, and I recently added a new column with type text. ALTER TABLE database.b ADD COLUMN x TEXT; CREATE INDEX CONCURRENTLY idx_x on database.b(a, x); ...
1
vote
1answer
96 views

How to find missing data from one table that is a join of 2 other tables

I have 2 tables, stock and location, with a third table that records the level of stock at each location, stock_loc_info. Each stock item should have a record for each location. What SQL query ...
0
votes
1answer
129 views

Dynamic Pivot with overlapping years [duplicate]

I have looked and cannot find how to show the last # months in a pivot table. Everything I seem to run across codes months statically. I'm seeking a solution that shows something similar to the ...
1
vote
1answer
521 views

2 Date Ranges - How many days from one range excluding weekends overlap

First - Thanks for your help. I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends. Example Data: ...
0
votes
4answers
294 views

Single SQL query to retrieve data in a particular order (not ASC/DESC)

I have a MySQL table, let's say called reviews review_id, product_id, language_id, rating, review, author language_id ranges from 1 to 10 for 10 different languages. Let's say I determine that my ...
1
vote
0answers
68 views

Non clustered indexes

I have a questions on Index Our Application is throwing the following SQL eroor Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
0
votes
1answer
61 views

convert update field second to time

I have 2 fields ( about 1000 records) name FROMTIME and TOTIME. Both fields will have time in seconds (for example FROMTIME = 20520 and TOTIME = 39360). I would like to convert and update these ...
1
vote
0answers
37 views

Did I Properly Diagnose That It's Not A SQL Server Issue?

We have a job that populates about 450,000 rows nightly in an update/insert job via a stored proc nightly. The .Net app pulls in data from a noSQL DB via an API call then pushes it out via a stored ...
0
votes
0answers
204 views

How to tell which process in SQL Server I should kill?

I am having an issue with an application as it is running supper slow. I don't know if it is a database issue or something else as the is nothing that I can find to be the issue. I tried select * ...
0
votes
1answer
3k views

Max from 2 columns with priority date col then the seq col Oracle

I have a column xdate, column seqnumber, personID I want 1 record per personID, where it is the most recent xdate max(xdate) if there is multiple same max xdates i want it to look then for the max ...
1
vote
2answers
76 views

Can an EXISTS clause be improved when the condition is checked for the same value multiple times?

Assume that I'm modelling a Blog engine. Blogs have n Posts. Each User can be granted a role on different Blogs (Admin, Approver or Editor stored as enums/ints). Each Post can have a State (Draft, ...
0
votes
0answers
30 views

Count responses based on tale structure given below

Table:Answer_Master id_answer answer id_question 1 Yes 1 2 No 1 3 Yes ...
0
votes
0answers
345 views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
5
votes
5answers
28k views

Tempdb is getting full very quickly in microsoft sql server 2008

I am developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly . Is there any solution to clean the tempdb file in microsoft sql server 2008 on ...
12
votes
1answer
15k views

Performance implications of using OPENQUERY in a view

Please see this question on stackoverflow : I am using an EasySoft ODBC driver to link a SQL Server 2008 R2 Express instance to Interbase and I am having some difficulty with getting metadata ...
0
votes
2answers
595 views

update column based on sum of data from another table

I have two tables : Table1 (col1, col2, col3, col4, val1, status) Table2 (col1, col2, col3, col4, val2) For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key. ...
0
votes
0answers
328 views

SQL Query to get the Sales Qty of the Item and the its Returned Quantity for past 6 Months

I want retrieve the Sales Qty and return qty of the Items in the following format for the past six months. From two different tables,I TxnInvoice Table :Invoice header TxnInvoiceLine Table :Invoice ...
0
votes
0answers
43 views

SQL Database query working really slowly

I have a database with two columns: 1)Word 2) Times Used When the user enters a word it find the combination of words already typed (ex: combo = hello world) and updates the database using a ...
1
vote
0answers
36 views

Enforce referential integrity for FriendRequest table that has UserA and UserB

I have a table called User that has UserName as a primary key. I want to establish a relationship with enforced referential integrity between User and FriendRequest. FriendRequest has the following: ...
0
votes
1answer
259 views

Delete duplicate records by column 1 but having differ values in column 2 and column 3 [closed]

I have a table like: SSN FName LName 1 a b 1 a b 1 b b -------------> to delete 2 c d 2 c d as above example I want to delete such records where SSN has different values in ...
0
votes
2answers
32 views

Report individual changes to data

I need to produce a daily extract on the individual changes to a dataset. So, there are 14 tables containing product and customer information and the extract must only show records from each table ...
1
vote
1answer
1k views

PostgreSQL, UPDATE CASE statement uses huge amount of space

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well. I am using ...
1
vote
1answer
198 views

MySQL created_by or updated_last-by [closed]

I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take Created_By VARCHAR2(30) DEFAULT ...
2
votes
1answer
410 views

Will MyISAM prevent multiple users inserting data at the same time?

I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL ...
3
votes
1answer
14k views

Column name or number of supplied values does not match table definition error

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get an error: Column name or number of supplied values ...
0
votes
3answers
194 views

How can I speed up an sql query that round trips between two tables

These tables are not that big, but the time taken to process this query is long. What can I do with the following queries to speed up the process taken to return results? $msc=microtime(true); ...
5
votes
2answers
10k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
0
votes
2answers
617 views

Excel - How can I use a SQL query to retrieve data on a database sever?

I have connected to a SQL server in Excel . I want to be able to import data from a table located on the server. However this table is too big to import into Excel, I'm talking millions of rows. I ...
3
votes
1answer
200 views

Why is casting a string to DATETIME not accurate to the ms? [closed]

Why does SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME) return: 2014-11-28 15:06:02.167
-1
votes
1answer
60 views

Creating Triggers to insert into a table where the table is altered by other application

I have a database in my local machine which is actually updated by an online application. I want to create triggers for a particular table ( say table1) such that it inserts values (From Table1) ...
4
votes
1answer
1k views

One foreign key for two columns

I have two tables, Results and Teams Teams has the following columns: team_id team_name Results has the following columns: match_id home_team (foreign key references teams [team_id]) away_team ...
2
votes
1answer
82 views

How can I speed up an sql query that round trips between two tables

I first posted this question on stackoverflow, and now I see this site, seems more relevant. These tables are not that big, but the time taken to process this query is long. What can I do with the ...
3
votes
1answer
360 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...
-1
votes
1answer
48 views

MySQL Multi Count [closed]

How can i get this result? ╔═════════╦═══════╦═══════╗ ║ EventID ║ User1 ║ User2 ║ ╠═════════╬═══════╬═══════╣ ║ 1 ║ 1 ║ 2 ║ ║ 2 ║ 2 ║ 3 ║ ║ 3 ║ 3 ║ 1 ║ ...
1
vote
1answer
124 views

I need help migrating data through databases

I have a web page with some data about the users. Recently, I made a complete change and start developing a new portal and of course a new DB structure. My question : What is the best way to migrate ...
2
votes
2answers
3k views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
2
votes
1answer
589 views

Mysql update adding subquery field value to field

I want to update a field adding it the value of a subquery field. I'm trying this: UPDATE table a SET a.field=(a.field+b.subfield) INNER JOIN (SELECT subid, subfield FROM subtable WHERE ..... ) AS ...
1
vote
1answer
3k views

need to replace the blank value as 0

need to replace the blank column into 0 for example the table is i have tried this query to bring the output but it is taking more number of time because i have put this table in to procedure ...
1
vote
0answers
50 views

Veryfing integrity of custom order column with pure SQL

I have two tables, one stores structure (using so called parent field), the other entities that can be embeded into that structure. As certain level of structure can contain both entities and deeper ...
5
votes
4answers
1k views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
4
votes
2answers
9k views

Reduce Clustered Index seek cost SQL Server 2008 R2

I am running a query and it has records in the hundreds of thousands, it takes more than 20 mins to fetch data. After running the execution plan i noticed that clustered index seek cost can be be the ...
4
votes
1answer
579 views

Get list of characters in column

I have a database table (UTF-8) with a lot of names in different languages with lots of special characters. In another script I use these names and one part is to replace the special characters. To ...
0
votes
1answer
432 views

SQL Server - Error 3202 during Restore

While attempting to restore a 4 TB MDF/data database with a 1.8 TB LDF/log file, I'm receiving the following error: Write on '???' failed: 1117 (The request could not be performed because of an I/O ...
4
votes
4answers
15k views

Execute permission denied on object sp_start_job

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified): ALTER PROCEDURE ...
0
votes
0answers
32 views

preventing FullTextSearch from using wildcards

I am using FTS in a sql2008 sql server and had a user complain about some searches breaking. after root cause analysis we found only searches like U.S.A. fail because FTS tries to run wildcard matches ...
6
votes
2answers
543 views

Is this update, select combination query thread safe?

I have a table called seats, which has a schema like this id,taken For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am ...
0
votes
1answer
46 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...
0
votes
1answer
433 views

self referencing tables with SQL and entity framework

I have many tables in a database and the tables are nearly identical. There are limitations currently on what objects reference other objects. For example I have company,contact and project. Contact ...