DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

1
vote
2answers
1k views

How to troubleshoot SQL “The timeout period elapsed prior to completion of the operation or the server is not responding.”

I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers. The database responds normally for about a day until we start to see sql timeout errors. After that all ...
2
votes
3answers
162 views

Ordering by date and other columns

I have a one table Q&A like below: CREATE TABLE `totaalconceptterreinonderhoud`.`acties` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `type` int(11) NOT NULL DEFAULT '3' ...
2
votes
2answers
240 views

SQL aliasing to redirect traffic between databases on the same server

In order to upgrade a database with no client disconnects, I clone an existing database and put it in a readonly mode. Now, I want all user traffic to be redirected from the original database to the ...
1
vote
2answers
170 views

SQL Server 2005 datacube processing in high transaction system

We currently have a highly transactional SQL 2005 database which is growning by around 1.2 million rows weekly. On top of this we have a SSAS datacube which is set to process every two hours for ...
10
votes
1answer
12k 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 ...
2
votes
1answer
1k views

SQL Server Error 18483 - When Setting Up SQL Distribution

I'm trying to synchronize a couple tables in one SQL Server 2000 database into another SQL Server 2005 database using replication. When I am attempting to set up my SQL Server as a distributor, I'm ...
2
votes
2answers
418 views

What does `x` stand for in this query?

select convert(varchar(10), TotalSeconds / 3600) +':'+ convert(varchar(10), TotalSeconds % 3600 / 60) +':'+ convert(varchar(10), TotalSeconds % 60) as Seconds from (select DateDiff (second, ...
1
vote
1answer
343 views

Query optimization: Which is better in my case - CURSORs or a Set-based approach?

First of all, I can describe the schema of the table, but my table itself is around 80GB so I am hoping some expert eyes can look at any obvious optimizations that can be performed. I am carrying out ...
0
votes
1answer
888 views

Using Sync Orchestrator to Syncronize two SQL CE 3.5 Databases

I am trying to set up synchronization between two SQL Compact 3.5 databases using SqlCeSyncProvider. I am able to create a scope for the two databases, which creates a meta table. When I try to run ...
5
votes
1answer
502 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
4
votes
2answers
744 views

How to restore a SQL Server 2008 R2 database

Here's my issue. I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the ...
2
votes
1answer
8k views

Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

I've developed a timesheet recording system, that allows users to record: A Period of time (9:00 to 12:00) What they did during that time (DutyActivity: 1 hour report writing, 2 hours training) ...
4
votes
3answers
9k views

Need to Check if a Table Exists Before Deleting a Record

I have the following SQL query in my Ruby app: sql = "DELETE FROM #{database}.table1 WHERE same_id = #{some_id};" The problem is that in rare circumstances 'table1' might not exist. I need to ...
6
votes
2answers
1k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
2
votes
2answers
256 views

Improving the performance of a long query

I have a query that i have let run for a few hours. I am forced to kill the query because it is taking up 60% of the CPU. Is there anything i can do to improve its performance? select ...
2
votes
1answer
3k views

creating buckets in oracle sql

I saw another post where they showed you how to do this with a case statement and had you define your buckets in advance. how do I make it dynamic? I know about the width_bucket and ntile functions. ...
7
votes
4answers
3k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
0
votes
3answers
201 views

How can I select data and the total to cursors?

I have a table like this: ID | Name ------------ 1 | AA 2 | AA 3 | AA 4 | BB I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another ...
3
votes
3answers
62 views

Listing Non-Record

I have two tables. 1st table: called kimlik id ad --------------- 1 ahmet 2 mehmet 3 ali 2nd table: called siparis id kimlikid tarih ...
16
votes
5answers
76k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
6
votes
5answers
79k views

How to show the column names of a table with a simple sql query?

The case is simple: You have an mysql database where you have only sql query interface and you want to know the database structure with queries. You can list tables with show tables; command but how ...
8
votes
6answers
3k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE ...
3
votes
1answer
379 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...
8
votes
2answers
2k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
3
votes
3answers
1k views

Convert Oracle query to MySQL

I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error: You can't specify target table 'animal_table' for update in FROM ...
2
votes
2answers
137 views

How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?

I am using MySQL version 5.1.49 and I have now enabled UTF8 character encoding. The default character-set for MySQL is latin1. How can I change it show UTF8 characters? Even when I query a table ...
1
vote
0answers
157 views

Pulling an Image from a Database vs pulling from a link to a directory [duplicate]

Possible Duplicate: Is it better to store images in a BLOB or just the URL? Files - in the database or not? I was wondering what the benefit is of pulling a varchar out of a database that ...
0
votes
1answer
127 views

SQL Server Data types and operators clarification needed

I would like to confirm the following with respect to the basic SQL Queries: The float and real data types cannot be used with the equals and not equals operator in sql. If so then what else ...
2
votes
1answer
658 views

Estimating query time

Is it possible in MySQL to know beforehand query execution time? I mean how long it will take to execute the query. Generally its worthwhile to know how long any query can take to execute. At the ...
8
votes
6answers
433 views

Backing up SQL DB more frequently

I currently have a scheduled task that fires off each night at 2 AM that calls SQLCMD.exe and passes it a .sql script to run for the backup (shown below). We're a pretty small company with growing ...
2
votes
1answer
85 views

Bad Windows Authentication sign on

I do not have install privileges on my machine. Another user who has admin privileges installed it, but told the system to use Windows Authentication for the sign on. Is there a way they can change ...
3
votes
1answer
760 views

Is it possible to compare timestamps between rows?

I have a MySQL table reqs with (this is simplified from actual): | Field | Type | Null | Key | Default | Extra | ...
6
votes
3answers
3k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
2
votes
1answer
2k views

Error restoring SQL differential database files

Occasionally I'm having problems restoring DB differentials. I'm getting the error "the differential backup cannot be restored because the database has not been restored to correct earlier state." ...
2
votes
3answers
8k views

Return int value from boolean field in SQL select

Is there a way to return an int value for a boolean field in a sql select statement?
2
votes
1answer
1k views

Table Variables - Scope Error - Must declare the variable 'X'

This is my first post and not sure how to fix this auto format draft? Using SQL Server 2000. The table variable that is giving me this one error is '@SBLH' The following is the multi-select function ...
16
votes
4answers
1k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
59
votes
8answers
5k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
4
votes
2answers
300 views

What is the consensus on using NOT NULL constraints on all text-based fields in a table?

Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying. I'm curious what the feelings are with using something like NOT NULL ...
8
votes
3answers
2k views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
0
votes
3answers
759 views

SQL Query Declaration problem

I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem? Incorrect syntax near the keyword 'Declare' SQL Query: create ...
0
votes
1answer
246 views

How do I correctly design this database?

I am not a database designer by any means. I am a desktop programmer, but I am currently looking at implementing a website idea which will need a (possibly very large) back-end database. In my site ...
4
votes
4answers
7k views

How can I insert if key not exist with PostgreSQL?

I have a table: CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY) Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the ...
4
votes
4answers
2k views

How can I generate an Entity-Relationship diagram and SQL DDL from natural language text?

Good morning, Using LaTeX or another text-macro tool/library, how can I use natural language text to describe relationships, then generate an Entity-Relationship Diagram and generate SQL DDL to ...
1
vote
1answer
5k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
2
votes
2answers
3k views

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals ...
1
vote
3answers
436 views

Select only neighbour rows who pass a where-test

I want to apply a selection process to my data, that will not include rows which should be accepted by my WHEREclause but who follow after a row which fails the WHERE. Example. I have a list of gps ...
0
votes
1answer
162 views

sql 2000 trace : sp_releaseschemalock

For a SQL 2000 server, is it bad to see too many exec sp_releaseschemalock 1 events in a SQL trace file?
0
votes
1answer
666 views

How to create a backup schema script including System Tables in Sql Server 2008 R2?

I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I ...
1
vote
1answer
266 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...