sql

Efficient way of phrasing multiple tuple pair WHERE conditions in SQL statement

I want to perform an SQL query that is logically equivalent to the following: DELETE FROM pond_pairs WHERE ((pond1 = 12) AND (pond2 = 233)) OR ((pond1 = 12) AND (pond2 = 234)) OR ((pond1 = 12) AND (pond2 = 8)) OR ((pond1 = 13) AND (pond2 = 6547)) OR ((pond1 = 13879) AND (pond2 = 6)) I will have hundreds of thousands pond1-po...

"string or binary data would be truncated" when deleting row

Im using Microsoft SQL Server Management Studio Express to connect to our SQL Server 2005 (think its 2005, its version 9.000 something something). I have a table with one column that saves a lot of text. I have set the column type to text. When i have a row with lots of text in this column i cannot delete it. I get the message "string or...

sql exception (Incorrect syntax near ')'.)

what is the above error please correct it string cmd = @"SELECT * FROM [tbl_students] WHERE course_id=@courseId AND branch_id IN(" + branchId + @") AND (@firstYrPercent is null OR first_year_percent>=@firstYrPercent AND (@secondYrpercent is null OR second_year_percent>=@secondYrPerce...

backup database in schedule or not how to find through t-sql

Through SINGLE T-sql query can we find : 1. Database Backup mode - [full,simple,bulk] 2. IS database is scheduled for backup [yes, no] 3. If backup is enabled the size of the DB backup and Daily transaction log backup size ? any help ...

"Parent Key Not Found" although it exists (within the TX)

I just observed a strange behaviour (of course Oracle is probably supposed to behave this way, but it didn't fit in my world view yet): I try to insert two rows into a parent and a child table, both within the same transaction: INSERT INTO V_Parent (ID, Name) VALUES (777, 'Hello World'); INSERT INTO T_Child (ParentID, Name) VALUES (777...

Partitioned Running Total in a Text File

Given a tabbed delimited text file with two columns "name" and "date": Sam 20100101 Sam 20100101 Sam 20100102 George 20100101 George 20100101 George 20100102 Can I use awk to add third column with a partitioned running total, such as starting a counter at 0 for each distinct pattern of column one and two then increment...

TSQL RAND random values

Hi, I need populate with dummy values a table. I need create a random generated value "RANDOM_VALUE" for every single row created. Random value must be a string and its range could be only 'A' or 'B'. The script should be able to list a series of row with value A or B randomly generated Here an example of how should work: ROW RAND...

Check username/email availability design question

Hi guys, I'm pretty new to programming and html design and would like to ask a question some might consider silly. Here goes: I have a registration form that is validated with jQuery validation plugin. Within this form, there is an email and password input which will be used as login credentials. What I would like to do here is che...

What is the mysterious 'timestamp' datatype in Sybase?

I recently discovered a table in our Sybase database at work that uses a column of type 'timestamp'. If I create a table using this mysterious timestamp datatype like this create table dropme ( foo timestamp, roo int null ) insert into dropme (roo) values(123) insert into dropme (roo) values(122) insert into dropme (roo) valu...

SQL: How to improve performance when filtering rows based on foreign data?

Say I have a schema that represents a fixed-depth hierarchy like this: CREATE TABLE level0 ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL ) CREATE TABLE level1 ( id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT NOT NULL, level0_id INTEGER NOT NULL ) CREATE TABLE level2 ( id INTEGER PRIMARY KEY AUTOINC...

SQL Express Connection string - Relative to application location

I am using a SQL express databases as part of a unit test project in c#. My databases is located in ./Databases/MyUnitTestDB.mdf I would like to use a relative path or variable in the app.config rather than having my connection string defined as: .. AttachDbFilename=C:\blah\blah\blah\yea\yea\yea\MyApplication\Databases\MyUnitTestDB.md...

Conditionally sql query

I have this simple query select * from users where name = 'User1' I'd like to extend the query's functionality whatever the query returns 0 records the query will fetch the data by other clause. where name = 'Default' If the first clause will fetch some records the second clause will be ignored. EDIT Oracle ...

Select random row from Safaris client-side database.

I'm experimenting with the built in SQL support in the Safari Browser and I want to select a random query via Javascript. SELECT * FROM questions ORDER BY random() Returns not authorized to use function: random See this screenshot. Any suggestions? ...

Dynamic t-sql quotes in string

Hi, I have the following in a stored procedure: DECLARE @new_column_name varchar(9) DECLARE @table_name varchar(16) DECLARE @SQLString nvarchar(2000) SET @new_column_name = N'name' SET @table_name = N'tbl_test_table' SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @table_name + '] WHERE [' + @new_column_name + '] = ''' + @de...

Projecting simultaneous database queries

I’m after some thoughts on how people go about calculating database load for the purposes of capacity planning. I haven’t put this on Server Fault because the question is related to measuring just the application rather than defining the infrastructure. In this case, it’s someone else’s job to worry about that bit! I’m aware there are a...

MySQL Refactoring with GROUP BY and HAVING

Hi, I'm doing some refactoring work on PHP/MySQL code and stumbled across an interesting problem, and as of right now, I can't find a proper solution for it. I have this table: CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, userID INT UNSIGNED NOT NULL, timestamp INT UNSIGNED NOT N...

Find rows in table that fall under minimum and maximum range.

Following is the table and script of this table. DECLARE @temp TABLE (PPId INT, SVPId INT, Minimum INT, Maximum INT) INSERT INTO @temp VALUES(1,1,8,20) INSERT INTO @temp VALUES(2,1,21,100) Minimum & Maximum are passed in as parameter. I want to find all rows that fall in the given range. E.g.; If @minimum = 9 and @maximum = 15 t...

Mysql Select to find duplicates

Hi all, can somebody help me to write a sql select to perform a task. So the problem is that we have a table and there are some duplicates, so i need to find where name, street and house are the same and to group somehow them. I have almost this situation but the difference is that i would like to group them to find what is duplicate o...

How to short curcit null parameter (SQL)

Hello Friends i have the following query which defeat the very purpose. A day ago i asked suggestion for to sort circuit the parameter which are null for that i got the suggestion to use (@firstYrPercent is null OR first_year_percent>=@firstYrPercent) like this to deny null parameter but today when i am running actual query i think that...

SQLAlchemy ProgrammingError - how to debug?

I'm using SQLAlchemy 0.5.8, and seeing this error: ProgrammingError: (ProgrammingError) can't adapt 'INSERT INTO enumeration_value (id, key_id, code, name, notes) VALUES (%(id)s, %(key_id)s, %(code)s, %(name)s, %(notes)s)' {'key_id': 'aac6fc29-4ccd-4fe4-9118-cfbbd04449fe', 'notes': '', 'code': (u'Barnet',), 'id': 'd0540c97-882e-4a5b-b...