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
2k views

How to truncate all tables with a specific name in all databases

I want to delete all tables named error_log in all of my databases that have such a table. truncate table error_log I am using SQL Server 2000.
1
vote
2answers
218 views

Is possible to optimize this query or it's already optimized?

I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one: -- -- Table structure for table `default_users` -- CREATE TABLE IF NOT EXISTS `default_users` ( ...
2
votes
1answer
1k views

query generate the information one column in two columns in one query

I have a question related with a query in sql. I have the next table: id name 1 name1 2 country1 3 name2 4 country2 I need to call the results so: ...
3
votes
1answer
409 views

How can I conditionally summarize multiple rows in Microsoft Access?

I have a table of data, in Microsoft Access 2003, listing multiple rows for each person. I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = ...
0
votes
1answer
599 views

Creating a bar chart from datatable

I'm writing a console application that fetches data from database (SQL) and writes to an excel sheet. Now I want to add a chart in the workbook according to the datatable values. How can I create ...
0
votes
1answer
466 views

Escaping a single quote in SQL Server 2005 [closed]

I am using SQL server 2005 and using the northwind database. I need the supplier id of Grandma Kelly's Homestead and I used this query: select supplierid from suppliers where companyname ='Grandma ...
4
votes
4answers
42k views

SQL Server CREATE FILE encountered operating system error 5 (Access is denied.)

I'm trying to execute the following script in SQL Server Management Studio but I'm getting the error: Msg 5123, Level 16, State 1, Line 2 CREATE FILE encountered operating system error 5 ...
1
vote
1answer
108 views

group by and having query need help?

I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this: select ...
0
votes
1answer
226 views

Simple Search on 13 Terabyte Oracle Database to return relevant rows (.net app? Java?)

I am sure there are some easy ways to do this... I have a simple flat Oracle table which is 13 terabytes or so in size due to a large number of rows accumulating over the years. I need to do a search ...
4
votes
1answer
15k views

Get a fixed value on a select

I need to do a SELECT query where I get the value of the field "money". The field doesn't actually exist in the database. I just need the query to return this field with a fixed value; in this case ...
9
votes
3answers
76k views

SELECT DISTINCT on one column, while returning the other columns?

I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it. My SQL ...
4
votes
1answer
133 views

unique constraint on a set

With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005? To illustrate the problem, I make an example: A tag_bundle consists ...
1
vote
2answers
56 views

Why this query returns rows that I don't want to be there

I'm trying to running this query: SELECT * FROM (`default_users`) LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id` WHERE `default_users`.`group_id` ...
2
votes
2answers
6k views

Remove the last character from Variable String

I have a few variable data in the column "email". For example: roy2087@gmail.com123 sg123@yahoo.compori How do I get rid of the characters after .com? I used Replace(email,'.com%',gmail.com), ...
3
votes
5answers
983 views

Single query to return counts over different IDs in a single record

select count(title_id)as algodata from titles where pub_id =1389 select count(title_id)as binnet from titles where pub_id =0877 select count(title_id)as newmoon from titles where pub_id =0736 ...
8
votes
2answers
2k views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
5
votes
4answers
1k views

Composing the “ultimate database maintenance script”

I'm trying to compose T-SQL script that will: A) Shrink database size to the minimum possible size; B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a ...
6
votes
1answer
2k views

How to create a SQL Server 2008 backup without logs with mirroring

How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs? We need to do this so that when we restore our local development databases, we would have the most up to date ...
4
votes
1answer
138 views

According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?

Is it SQL92 compliant to store the number 1 in a field that is defined as NUMERIC 3,3? What about DECIMAL 3,3? Does this mean that the precision is a floating decimal place or static that must ...
5
votes
2answers
5k views

Suppress SQLCMD messages

Would it be possible to suppress all sqlcmd messages output to the cmd window? I am running a large script and it seems to be slowed down a lot with all the Processed XXXX total records messages. ...
7
votes
1answer
98 views

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
3
votes
2answers
194 views

How SQL server database reorganize memory

I am using [sys].[master_files] table to determine the size of the database, however i didn't get a sensible value yet. If i am inserting 100 rows into a table ideally db size should increase while i ...
1
vote
1answer
68 views

SQL - Need some advices for building a large db

currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach: Size: HDD has > 4tb to store filegroup if using partition tables. ...
3
votes
1answer
958 views

Send data from SQL to a serial port or IP address

I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a ...
4
votes
2answers
10k views

Local database vs Amazon RDS

I am doing large INSERT into a local MySQL database I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable I expect this database to ...
12
votes
3answers
11k views

System disk run out of space when running heavy SQL queries on SQL Server 2012

I'm quite new to SQL Server 2012, I would be grateful if someone can help. I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it. I'm trying ...
7
votes
2answers
5k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
5
votes
2answers
234 views

New Cluster Testing - Best Practice

We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS. We are looking for suggestions on a set of tests we could perform to test failover of the ...
3
votes
1answer
69 views

What does this code do?

I found one of my queries was over written with following code: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, ...
3
votes
2answers
1k views

Are these MySQL queries efficient?

It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things: 1) Are these doing what I expect 2) Are there ...
4
votes
1answer
4k views

Atypical problem with ORA-01445

I have problem with following query Q1: SELECT ADDRESS.STREET FROM MYSALES.MYADDRESS ADDRESS INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID INNER JOIN ...
1
vote
2answers
1k views

COUNT with set DIFFERENCE and UNION

Fairly simple question that I can't seem to find an answer to - I'm working with unions and differences, and I would like to perform a COUNT on the results. Currently I'm having to pipe out to a file ...
1
vote
1answer
135 views

Verify if I can use a given directory

Admin creates an Oracle directory: CREATE OR REPLACE DIRECTORY EXAMPLE AS '\\server\share\test'; GRANT READ, WRITE ON DIRECTORY EXAMPLE TO JOHN; Admin can test whether the directory exists: SELECT ...
3
votes
1answer
4k views

Why does altering a table and adding a foreign key create an extra constaint? [closed]

Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually ...
1
vote
2answers
16k views

sql server management studio backup error [closed]

hello, i want a back up for my database in sql server management studio. i am using ms sql. but when i try to back up , i take the following error: =================================== Backup failed ...
4
votes
2answers
4k views

SQL Server - In Recovery

Was uploading a few hundred thousand rows in my SQL 2008 R1 database and have come in this morning to an "In Recovery" message next to the database name in SQL Management Studio. I've tried a DBCC ...
2
votes
1answer
3k views

Extract some columns data from one table and insert into multiple tables

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables tbl_1: id, name, lastname tbl_2: id, ...
2
votes
3answers
780 views

Refactoring Series of UNIONs in single table (SQL/SYBASE)

I am trying to query a database and reformat the rows/columns that I am interested in. Each row in the current table (EQ) represents a field in a form. In reality, there are several hundred FieldNum ...
2
votes
2answers
4k views

Deleting Entries from Table not reducing my DB file

Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file. When i Entered 512 Record in my users table its size get increased to 200kb from 96 ...
1
vote
1answer
153 views

Best solution for search scenario

I have the database structure: | Tag | | Article_Tag | | Article | --------------- --------------- ----------------- id tag_id ...
1
vote
0answers
39 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
765 views

Cube Designer Aggregation Wizard Optimization Zero Percent Every Time

I am pretty new to SSAS. I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an ...
4
votes
2answers
3k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
1
vote
2answers
709 views

How to DELETE duplicate entries on a field

I have a huge table that has 1,990,546 users with numerous data. Sometimes I get email lists and dump them into this table also. I want to delete users with duplicate email addresses. However, I ...
1
vote
1answer
181 views

Help me convert SQL statement to MySQL

I have an application called Moodle version 1.9 and was migrated from Windows platform and Microsoft SQL Server to Linux and MySQL. Now, when I click save changes the SQL query statement from Custom ...
32
votes
7answers
18k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
1
vote
1answer
1k views

2 similar queries in large sql server table - 1 takes too long

I have a huge table in my database that contains distances between cities. This enables my application to find nearby cities around the world when a starting city is selected. It contains 4 columns ...
4
votes
1answer
179 views

repeatable read and nolock

Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock. Does the nolock become invalid ...
9
votes
3answers
3k views

Used Memory not freed up after a SQL BULK Insert / BCP Export

I have created a very basic SQL table as following CREATE TABLE [dbo].[TickData]( [Date] [varchar](12) NULL, [Time] [varchar](12) NOT NULL, [Symbol] [varchar](12) NOT NULL, [Side] [varchar](2) ...
3
votes
1answer
466 views

ETA: translate Oracle SQL queries to MySQL

task: replace Oracle 11g with MySQL current setup: Oracle 11g SE One about 80 tables stored procedures, PL/SQL are in place but out of scope for this question client applications do not use a ...