sql

How can multiple Stored Procedures update same row at same time?

I am using SSIS 2008 to execute multiple stored procedures in parallel in control flow. Each SP is supposed to ultimately update 1 row in a table. The point to note is that each SP has a defined responsibility to update specific columns only. It is guaranteed that the different SPs will not update each other's columns. So the columns t...

Looking for a Strategy to get multiple tables from a MySQL Query in PHP

I'm trying to build a set of results from a query in a PHP page. My where clause is complicated and potentially expensive. In my usual languages I'd do something like: CREATE TEMPORARY TABLE ShortList (ID INT); INSERT INTO ShortList SELECT ID FROM Table1 WHERE {Super long query}; SELECT * FROM Table1 JOIN ShortList ON ShortList.ID ...

SQL Identity with leading padded zeros

I have marked a column as Identity in my table create table Identitytest( number int identity(1,001) not null, value varchar(500) ) I need the identity column to be incremented as 001,002,003, etc. The database shows that it is inserting as 1,2,3, etc. How can this be done? ...

VS2010 ASP.NET Deployment

How do I deploy to a shared hosting environment... code and database (full blown SQL). What is your favorite way to get a 50MB single project ASP.NET Web Application Project and database to a live server with FTP and SQL Management Studio ports accessible? After a day of exploration mine is: - Use the Web.Debug.config to easily allow t...

What, if any, are the disadvantages of SQL::Interp over SQL::Abstract?

I'm currently looking at some light-weight SQL abstraction modules. My workflow is such that i usually write SELECT queries manually, and INSERT/UPDATE queries via subs which take hashes. Both of these modules seem perfect for my needs and i have a hard time deciding. SQL::Interp claims SQL::Abstract cannot provide full expressivity in ...

How to extract a distinct list of records from SQL or Excel

Hi, I have an excel spreadsheet with 15 columns, one of which is EmailAddress and then 100,000+ records.. In my data i know that there are many duplicate email addresses. Can someone tell me how can i extract a distinct list where each record is represented only once by emailaddress? Alternately, if i import the data into SQL, how ca...

Different SQL:s between Postgresql and Mysql?

Does Postgresql and Mysql use different SQL:s? I have a book, Head First SQL. If they are different, how do I know which database SQL it is? ...

SQL Server Full-Text-Search FREETEXTTABLE search multiple columns

I'm using the below query to return results from a table using Full-Text-Search. In SQL2000 it was only possible to search one or all columns in a table. Is it possible in SQL 2008? I would like to search two tables, Problem and Solution (Both indexed and in the same table): DECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FRE...

Views VS Tables For "facebook" like feed

Hey Guys, I am currently designing a dashboard feed that functions similar to the facebook feed. The feed will contain the following: Notify user of new users Show referrals sent to users, referrals received News from the administrator Up comming events. Each of the above is stored in it's own table but on the feeds page, they shoul...

How to insert X amount of rows at the beginning of a pre-existing table of data in sqlite

I am relatively new to sql, so I had a question about insertion. I have a table of data that I need to import above the existing content of another table. For example, the table I am bringing in has 100 rows, and the table I'm bringing the data into has 100. I need to make the table I am bringing new data into have 200 rows, and have the...

Store XML in XML type field or using FILESTREAM

Hi All, I am trying to figure out what would be the best method for storing xml documents in the db...if we should use an XML type field or if we should use the newly introduced FILESTREAM? We would have a need to shred those documents for queries and possibly use the FULL TEXT SEARCH to allow for user defined searches on those document...

Stop SQL query in python

Is there a way to let the user stop the execution of a sql query in python if it takes some long time? I am thinking of using a progress bar with a cancel button, but I wonder if there is a way to stop it in a clean way instead of killing abruptly the associated thread? (I am using both pysqlite2 and MySQLdb packages) ...

What is the most flexible design for a table of physical addresses in some variety of SQL?

What is the most flexible design for a table of physical addresses in some variety of SQL? I assume there is something better than { street address, building address, city, state/province/region, country, zipcode }. Also are there standard names for various components of addresses, especially international standard names? Further, wha...

Process for pulling data from a sql database

I want to make a program that pulls objects from a sql database and processes the objects against a set of functions before returning the data to the user. It will work like this: User specifies a date range in a javascript form. The date range is sent to a php file on the server that converts it to a sql query. The sq...

sql date hour conversion

I have SQL like this: `table1`.`DateField` >= DATE_SUB(NOW(), INTERVAL {$days} DAY My table1.DateField is in the format of 2010-03-10 10:05:50. The value of {$days} could be 4 hours or 3 days. How can I make the above sql smart enough to know that? Should I use `table1`.`DateField` >= DATE_SUB(NOW(), INTERVAL {$days} DAY_HOUR ...

SQL 2005 database roles and security

I Hope someone can clarify and help. I've moved to creating/using schemas in Sql Server 2005, where I've also created database roles and assigned schemas to the roles. The problem I have is one of the db roles I have should have full access - say SuperSysRole. However, when I assign a login to that role, it cannot execute specific comma...

SQL 2008: returning data rows as JSON?

I think this question is like clay pidgeon shooting.. "pull... bang!" .. shot down.. but nevertheless, it's worth asking I believe. Lots of JS frameworks etc use JSON these days, and for good reason I know. The classic question is "where to transform the data to JSON". I understand that at some point in the pipeline, you have to conver...

Get MAX() on column in two tables

I have two tables which both have the column DateTime. How can I get the MAX() DateTime? The shorter/simpler the better, because this is just part of a larger query. ...

sql, checking if a value appears more then 2x in a table

if i have a schema that goes somthing like this peoples(personNumber, etc) jobs(personNumber, jobNumber) and a person can have more then one job, how can a write a query that tells me if a person has less then two jobs select personNumber from peoples where personNumber not in ( select personNumber from jobs where ??personNumb...

Oracle query problem

Please let me know why this query is not working together. They are working separately: ( (select item_name, sum(p_qty) from stock where p_date between '08/06/2010' and '08/07/2010' group by item_name) - ( select item_name, sum(p_r_qty) from stock where p_r_date between '08/06/2010' an...