An acronym for "Structured Query Language", 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 access privileges.
5
votes
1answer
77 views
Efficiently Filter Large Set With Disjunctions
Let's say I have a single table
CREATE TABLE Ticket (
TicketId int NOT NULL,
InsertDateTime datetime NOT NULL,
SiteId int NOT NULL,
StatusId tinyint NOT NULL,
AssignedId int NULL,
...
4
votes
1answer
84 views
CTE memory space
How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query?
I have a CTE that is ...
4
votes
1answer
92 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
3
votes
1answer
39 views
tempdb logs on same drive as tempdb data or with other logs?
For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files:
D: Data
E: Logs
F: Tempdb
Should the tempdb log file go on F: with the data file(s) or on E:?
...
3
votes
1answer
97 views
Azure SQL Administrator Can't Access Master Database
I created an SQL server and database. I can log in to the database manage page with my administrator account, and manage the database I created, but when I try to look at the master database, it says:
...
3
votes
1answer
39 views
Create Scheme that allows for fluidity between entities
We are a small rapidly evolving company and recently investigated switching databases from SQL Server to a graph database. We like the graph approach because of the fluidity and openness that it ...
3
votes
1answer
205 views
query processor ran out of internal resources and could not produce a query plan
This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.
Thank you
2
votes
1answer
46 views
What are the risks for logging across databases via a trigger?
I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here.
On SQL Server 2005, we currently log certain table changes via triggers using ...
2
votes
1answer
121 views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
2
votes
1answer
594 views
How to Convert Horizontal to Vertical Array?
I need to create a query (suitable for Standard Edition) that has data from multiple columns (Columns 1-6 with corresponding Date Started and Date Completed data) displayed vertically, but also has ...
1
vote
1answer
50 views
How do I refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)
I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh ...
1
vote
1answer
104 views
Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard
I understand that this is not a supported path, but does anyone have any insight about how to accomplish this? I can't seem to find any documents on the subject. I need my QA (2005) to match my ...
1
vote
1answer
99 views
SQL Server stored procedure working when run manually, not running from SQL Server Agent
I have a procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error.
line 9, character 9, unexpected end of input
The code ...
1
vote
1answer
60 views
dead lock when updating
Update Operation.TrTable
Set RecordId = RecordTABLE.newRecordId
From Operation.TrTable tr
Inner Join (
SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
From ...
1
vote
1answer
276 views
SQL Server Login failed for user Error: 18456, Severity: 14, State: 11
I have an AD group XYZ that I have added to SQL Server security with data_reader permissions.
The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I ...
1
vote
1answer
72 views
Database Designing for a Ecomerce website
I am new to database designing. I am designing a database for an eCommerce website, there is a lot of products to be updated, but while designing for product specification table I do not understand ...
1
vote
1answer
51 views
Query to find Maximum friends not giving correct resuts
Table Schema
Likes table
id,id1
1,2
1,3
2,1
I have count of total number of Likes for each student through this
select id,count(*)
from friends
group by id
Now I have to find ...
1
vote
1answer
115 views
Delete word, its meanings, its meaning's example sentences from DB
I have three tables as below (simplified for demonstration):
words
=====
integer id
text word
meanings
========
integer id
integer word_id
text meaning
examples
========
integer id
integer ...
1
vote
1answer
289 views
Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error
we are using a Sybase SQL Anywhere 12 db.
In the db there are indices, which are unique, but shouldn't be unique.
Therefore I search for a quick way to list all tables with unique primary keys.
I ...
0
votes
1answer
21 views
createdb command error in postgres sql
I am using the following command for creating database in postgresql.
createdb -D pg_default -E UTF8 -h localhost -p 5432 -U pramil -W pramil mydb
but this command results prompt for password.When ...
0
votes
1answer
58 views
Complicated join with where clause
I have four tables:
sales
sales_thumbs
sales_images
sales_sizes
sales table:
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | ...
0
votes
1answer
48 views
Efficient way to perform approximated search?
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.
As it happens ...
0
votes
1answer
14 views
Running a Job from a Stored Procedure in another server?
How can I run a job from another server using a stored procedure?
Let's say
server 1 : db1
server 2 : db2
username : testssis
password : testssispass
sqljob found in db2: job1
Here is the part ...
0
votes
1answer
33 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment i have decided to create separate database just for stored routines storage. Mainly i am using them for reporting. ...
0
votes
1answer
41 views
Generic SQL Job Scheduler for multiple RDBMS's?
I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex ...
0
votes
1answer
94 views
Oracle Undo tablespace users
Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.
0
votes
1answer
28 views
How to migrate data from SQL database to MySQL including flat files?
one of my friend has asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now my friend asked ...
0
votes
1answer
64 views
Second time query execution using different constants makes faster?
Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
56 views
query taking long time to execute.we are expecting result in very quickly anybody please rewrite it
Query is taking long time to execute.we are expecting result in 0 sec.If possible Please help me to rewrite it. Also suggest me to add any indexes on table.Also suggest me if optimization is needed.If ...
0
votes
1answer
10 views
content types of insertion
I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
0
votes
1answer
102 views
Conditionally INSERTING columns into Table
using SQL Azure and like to insert a data to a table using dynamic Query.
No of columns in select statment is dynamic. It will be based on some conditions.
Ex: Salary, Code.
How can I create a ...
0
votes
1answer
136 views
FETCH API_CURSOR causing open transaction in tempDB
A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when ...
0
votes
1answer
66 views
Want to Get Brands In Wordpress Database Structure Saved as Custom Post types
I am having difficulty in fetching the brands per category in the sidebar of my website.
Here is the description:
I am using the feeds and feed uploader to upload feeds and create brands and product ...
0
votes
1answer
80 views
How can I optimize this query and support multiple SKUs?
My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
-1
votes
1answer
104 views
Tutorial in performance tuning
Where can I find about tutorial of performance tuning in SQL server 2012 or 2008 R2?
The purpose is to improve my knowledge in performance tuning.
0
votes
0answers
510 views
0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake
I have hopelessly attempted to install SQL server 2012 onto my 64 bit Windows 8 machine.
The database engine always fails to install with error 0x84BB0001. Native reporting also fails, I reckon from ...
0
votes
0answers
441 views
Dynamically Converted rows into columns (Pivoting)
Query
SELECT
Performparam.performparam_Name,studfeedback.studfeedback_Rating, SUM(studfeedback.studfeedback_Rating) AS total,
COUNT(studfeedback.studfeedback_Rating) AS totastudents, ...
0
votes
0answers
196 views
with using PIVOT table sql server
I am facing a small issue while using PIVOT table.
My problem is as following.
I have a single table with the procedures id and their steps and i want all the steps needs to be displayed in 5 column ...
0
votes
0answers
37 views
“optimizer was unable to construct a valid access plan” after Update ASA9 to SA12
After an update from ASA9 to SA12, there seems to be a problem with several procedures.
This occurs only if the user does not have DBA authority. In ASA9 everything worked fine.
SELECT * FROM ...
0
votes
0answers
99 views
GROUP_CONCAT with ORDER BY , but results are not orderd
SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5
above query works fine but ...
0
votes
0answers
99 views
How do I get the date that each user last logged onto the server / database in SQL Server 2005
Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the
LASTLOGIN_DATE.
ALTER ...
0
votes
0answers
275 views
Sqlsrv sqlsrv_num_rows FALSE, adding cursor type times out
$sql = "SELECT * FROM lastdeath";
$result = sqlsrv_query( $con, $sql);
if( $result === false )
{
die( print_r( sqlsrv_errors(), true));
}
$row_count = sqlsrv_num_rows($result);
echo $row_count;
...
0
votes
0answers
232 views
busy with results for another hstmt - one table only issue
321 08/08/12 06:40:51 : Query : select MemberCount from tbl_guild where serial = 6
322 08/08/12 06:40:51 : SqlState:HY000, NativeError:0 Msg:[Microsoft][ODBC SQL Server Driver]Connection is busy ...
0
votes
0answers
101 views
Can I do a MySQL subquery across criteria with different number of results
I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
...
0
votes
0answers
50 views
Spatial Database over smaller rectangle takes longer
I have to be very careful here. Both queries are so similar that the only different is the area searched.
What's surprising is the query that run on BIGGER AREA, and naturally yield MORE ROWS, is the ...
0
votes
0answers
139 views
Nearest neighbor search for 1.6 million points in myisam mysql
Say I want to find 20 closest business near me. Latter I want to see the next 20 closest, etc. Yes I have myisam. I have spatial indexes on the point. I am looking for actual command.
Note:
I am ...
0
votes
0answers
53 views
SQL to update incrementall
Have a table photos
photos.id
photos.user_id
photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the ...
0
votes
0answers
141 views
oracle sql dependency extractor
Is there a tool which given a bunch of data extraction SQL scripts will produce a dependency graph?
Basically given scripts which take input tables, create a bunch of interdependent intermediary ...
0
votes
0answers
289 views
Update Statistics Maintenance Plan - Ambiguous column name error
Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86)
Windows 2003 R2 SP2
I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user ...
0
votes
0answers
54 views
Replicating Results of Queries on Oracle
I wan to setup a database such that its data come from queries run on a remote database. Basically the source database is very slow and we have no control over it. So, I want to create a second ...
