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

learn more… | top users | synonyms (2)

1
vote
0answers
153 views

hStmt/cursor error caused by faulty data?

Could a busy hStmt and invalid cursor errors be caused by faulty data? ODBC and the software that connects to the database is correct and working - i have ran a test with a clean database (that has ...
3
votes
1answer
946 views

Connecting to an SQL server instance without using square brackets to avoid backslash

In our company we have an old piece of software which was written by our previous I.T manager but was never finished. The program connects to SQL server but we want it to point to a newer SQL server ...
5
votes
2answers
10k views

How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8) I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
3
votes
3answers
476 views

selecting multiple instances of a record based on record lifespan over years

I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
2
votes
2answers
320 views

remote connection

Im trying to connect to a remote sql server using windows authenication. I'm using visual studio 2012 and going to a remote sql server 2012. I have already checked in sql studio that it is set up for ...
12
votes
2answers
1k views

Postgres query plan of a UDF invocation written in pgpsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
11
votes
3answers
2k views

Use MySQL to regularly do multi-way joins on 100+ GB tables?

Background: I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus ...
5
votes
3answers
29k views

“lock request time out period exceeded” Error When Trying to See DB Hierarchies

I'm having problems with a database. I can run basic queries, albeit much slower than normal. When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I ...
2
votes
0answers
2k views

Can't Restore DB Because of Active Connections [closed]

I have a DB I would like to restore. There are other active connections on the DB. I tried using sp_who2 to find those connections and manually kill them. Unfortunately, this did not solve the ...
2
votes
1answer
492 views

Cross Apply without a function

I would like to run a query very similar to this on one of my production databases: -- Setup the table (Already there in my real scenario) create table dbo.MyTable(MyValue int, MyGroup varchar(5)) ...
1
vote
1answer
822 views

Find who will change a record in oracle DB

Someone is updating a record in a custom Oracle table, is there a way to find who is updating the record and or at least the table?
3
votes
1answer
1k views

How do you connect to SQL Server 2008 Express when SA is disabled?

I'm trying to create a database in SQL Server 2008 Express (as installed by Visual Studio) but I don't seem to have any permissions to do so. There must be a way to do this, or there would be no point ...
1
vote
1answer
216 views

Querying multiple tables

I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together. I have the following tables/fields: SR_service: stores ticket ...
7
votes
1answer
386 views

Is it possible to limit the maximum disk usage for a user

Is it possible to set a limit on the amount of physical disk space a user (or group of users) can use within a single database? For example is there some method of associating a schema with a ...
1
vote
1answer
2k views

Database Mail Issue

I am getting a strange error randomly when i try to send emails using database mail using SQL Server. The mail could not be sent to the recipients because of the mail server failure.Exception ...
2
votes
2answers
1k views

SQL Partioning on date, how does it work for future records?

I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
2
votes
3answers
535 views

MySQL Query - How to get most recent demographics?

I have a table that has duplicated cardnbr's. I need to pull out demographics that are the most recently used by the customer. I have a date field (lastvisit) that shows their most recently used card. ...
0
votes
3answers
123 views

How to convert query results into groups of common results

I have a query that returns results like this: MyValue MyGroup _______________|_______________ 56 | I _______________|_______________ 12 | I ...
2
votes
1answer
2k views

How do I persist recurring events?

I've been tasked with designing a system to recur events. Events may recur daily, hourly, monthly or yearly. Users should be able to schedule events "every 3rd Wednesday of the month" or "The 1st of ...
1
vote
1answer
187 views

Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)

I've this tables: -- -- Table structure for table `default_cmvblog` -- CREATE TABLE IF NOT EXISTS `default_cmvblog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE ...
2
votes
2answers
4k views

Querying a database efficiently for a huge chunk of data

Here is my query SELECT * FROM ( SELECT a.*, rownum rnum FROM ( SELECT id, data FROM t ORDER BY id ) a ...
6
votes
2answers
532 views

Correlated subquery and join: still the same exectution plan?

I have a correlated subquery like this (from BOL): SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = ...
4
votes
2answers
2k views

Sub-query executes 1 time for a parent query or more?

If i apply a static data in where clause is it faster then applying a sub-query. Example: (The query returns 5 records as a result set) SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = ...
0
votes
2answers
2k views

xp_cmdshell bcp wont save file

EXEC xp_cmdshell 'bcp "SELECT lastconnectip FROM RF_USER.dbo.tbl_UserAccount" queryout "C:\test.txt" -T -c' The command above runs, the output: NULL Starting copy... 1000 rows successfully ...
11
votes
4answers
5k views

Benefits of using backtick (`) in MySQL queries?

In MySQL we can create queries with or without the backtick (`) symbol. Example: SELECT * FROM TEST; SELECT * FROM `TEST`; Both works fine in mysql-console. Is there any technical difference ...
4
votes
4answers
14k views

What's better/faster? MySql or FileSystem?

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography. I'll admit my SQL queries could be better but in general what would be faster and ...
1
vote
1answer
327 views

SQL Server 2008 R2 - Weekly / Monthly Statistics

I'm new working with SQL Server 2008 R2 Express, and I´d like to gather some statistics every week / month for users who are connecting to this server. For instance, how many times they were ...
2
votes
1answer
3k views

SQL Server trigger (update or insert with inner join problem)

ALTER TRIGGER [dbo].[killertrigg] ON [dbo].[tbl_pvporderview] AFTER UPDATE AS IF (UPDATE([Kill])) BEGIN SET NOCOUNT ON; INSERT killer(serial,[Kill]) SELECT i.serial,i.[Kill] FROM ...
6
votes
3answers
19k views

How to make multiple counts in one query?

I count records with queries like SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%' SELECT COUNT(col1) FROM table1 WHERE col1 ...
2
votes
3answers
4k views

Mirroring Databases in MySQL

Is there a way to mirror databases in MySQL? In SQL Server, you have the ability to link 2 databases in a master to master relationship. One DB will accept transactions and the transactions will be ...
2
votes
4answers
10k views

PostgreSQL import CSV File causes Syntax Error

I'm attempting to import a CSV file into a database via the "COPY" command; however, I get the (what seems common) error that I need to be a superuser and that I should use "\copy" instead. However, ...
1
vote
0answers
794 views

SELECT value from database over ODBC wont reach the software [closed]

SQL Server 2008 R2 latest updates. SQL server ODBC driver version 6.01.7601.17514 PS!!! native drivers are incompatible with the software so i cannot use them. I cannot edit or change the server ...
0
votes
1answer
601 views

PL/pgSQL function + client-side lo_import

I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this: create function add_file(flag integer, sth varchar) returns void as begin ...
1
vote
1answer
205 views

EXISTS and sub-query cost

i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns? 1) SELECT id FROM Parent p ...
4
votes
1answer
245 views

How can i prevent decryption in SQL Server procedures

I came across in internet tool to decrypt procedures, I checked my database with this tool to decrypt my procedures and it works fine, now the problem is I wish to prevent this kind of tools from ...
4
votes
2answers
16k views

Updating Multiple Rows with Data from Column A into Column B?

I have about 60,000 rows that I'm needing to update the information from column_a to column_b. For example, 60,000 customers need their arrival_time to match their departure_time. I understand that ...
6
votes
4answers
5k views

SQL Cluster without a shared disk. Possible?

Is it possible to create a two node cluster using two basic network servers that only have local disks? One will act as an idle standby server. Without knowing too much about clustering I'm not ...
4
votes
1answer
974 views

Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to ...
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 ...
2
votes
1answer
492 views

Are JOINs on non-indexed columns faster than sub-queries?

I know JOIN on indexed columns are faster than sub-queries in a query result. I am confused if : JOINs on non-indexed columns are also faster than sub-queries or not.
5
votes
4answers
339 views

Why is Clustered Index on Primary Key compulsory?

Why Primary Key constraint creates Clustered Index on the PK column by default? Can we create a table which has a primary key, but NO clustered index. I need PK constraint to maintain the unique ...
5
votes
1answer
15k views

Show procedure code in MySQL

I've tried to show the SQL code of a stored procedure called add_pixel: mysql> SHOW PROCEDURE CODE add_pixel; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ...
3
votes
1answer
4k views

Import CSV File

Currently, we have a DTS package on SQL Server 2000 that imports a file called suicoweb.csv. The DTS csv file properties pages look like this. Here is a sample line from the CSV file ...
2
votes
3answers
1k views

SUM and JOIN SELF

I need help with sum and join self. I have table like Buyer Info2 LinkKey Date Credit Debit Samuel 20 S15 2012-03-15 500 0 Samuel 20 S15 ...
4
votes
2answers
152 views

Optimizing my update sequence

Whenever a user updates his profile, we conduct a set of queries like so: DELETE FROM `user_likes` WHERE `ID` = $id INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name) Is ...
5
votes
1answer
4k views

Difference between count(*) and count(1) in mysql?

In MySql we can count the total number of records by using count(1) or count(*). Is there any technical difference between them?
8
votes
2answers
204 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
5
votes
1answer
86 views

Will these queries return the same results?

I have this simple query: SELECT friend_id FROM default_friend WHERE user_id = 1 And the results are below: +-----------+ | friend_id | +-----------+ | 2250 | | 4901 | | ...
4
votes
1answer
759 views

SQL Server Agent fails to start in Failover cluter

The SQL server agent in a cluster (as a cluster resource) fails to start. I'm looking for error logs to give me a clue, but so far, all it says is: "failed to start". What could be the possible ...
2
votes
1answer
1k views

BCP error near the keyword 'as'

I am new to using BCP but I am trying to extract data generated via a stored proc. This is a continuation of my original question which was a stored procedure with temp tables. This procedure has ...