sql

How do I run SQL queries on different databases dynamically?

I have a sql server stored procedure that I use to backup data from our database before doing an upgrade, and I'd really like it to be able to run the stored procedure on multiple databases by passing in the database name as a parameter. Is there an easy way to do this? The best I can figure is to dynamically build the sql in the stored ...

sp_FuzzyLookupTableMaintenanceInstall not found while running SSIS package

Hello, I have an error while running a SSIS package: DTS_E_OLEDBERROR. An OLE DB Error has ocurred..... "Could not find stored procedure sys.sp_FuzzyLookupTableMaintenanceInstall" I connect to to my db and I´m able to find the stored procedure. sp_FuzzyLookupTableMaintenanceInstall sys assembly stored procedure 2008-11-24 14:23:08.9...

MYSQL: Two Table SELECT without common values

I need a query to make a Select over table1 and table2. I want them to appear as a common result, but without any connection there is no value from table 1 that corresponds with any value from table 2. So if a result was from table1 it should contain the values table1.a , table1.b, table1.c if it comes from table2 it should have table2...

Converting varchar to decimal in sql server 2008

I have this data as varchar '00072330'. How do I convert it to a decimal that looks like '723.30' in SQL Server 2008? ...

Using an object type in a select statement in Oracle

I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn't have to call 3 very similar complex fun...

best time to SELECT value in Oracle nested query

From an optimization standpoint, I want to know when it's best to SELECT some arbitrary data as columns. Specifically, I have this query: Where do I put this part? SELECT 'String 1' AS segment FROM DUAL UNION ALL SELECT 'String 2' AS segment FROM DUAL UNION ALL SELECT 'String 3' AS segment FROM DUAL In Outermost Query I need access...

Sync two SQL Server databases

I have two databases: the source is a database from SQL Server Express by client and the target is a database from SQL Server 2005 database as backup initially. What I need is to sync the source to the target db if there is any difference between them and the sync is one-way from source to target. I am not sure what tools are available....

A covered index formed by a composite index or index with included columns

Are there any differences between these two covered indexes? A composite index with both FriendID and UserID A index with FriendID and UserID as an included column Separate MS SQL Server related point. If the answer to the above question is 'No difference', is it me or does the Database Engine Tuning Advisor (DTA) always go crazy on...

how to group by and count using MySQL

I have data which looks like this: ID post_author post_title guid 3309 21 Should somebody not yet on SQL 2008 wait for SQL 2008 R2, since it's near release? http://sql.stackexchange.com/questions/379/should-somebody-not-yet-on-sql-2008-wait-for-sql-2008-r2-since-its-near-release 1695 429 How do we politely decline well meaning a...

MySQL - Newbie question: Which are the PK and the FK for these tables?

I have 3 tables: Employees T emp_id | name | address Department T dep_id | name Salaries T emp_id | dep_id | month | year | salary For each table, what are the primary keys and the foreign keys? My answer: Name of the table | PK | FK| Employees: emp_id | dep_id Department: dep_id || emp_id Salaries: emp_id, dep_id | emp_id, d...

retrieve data from two columns with the same name but different table

I want to be able to select the column from users and dusers. example: select u.user, d.user FROM users u JOIN dusers .... etc. ...like so: echo $row['u.user'] echo $row['d.user'] I tried this, but no go. How do I specify what table to retrieve the user from? ...

Reassign ItemsSource - Items collection must be empty before using ItemsSource.

I'm using ItemsSource on a ListView. I now want to perform a search and filter the items with an SQL query, so I reassign the ItemsSource; but then an exception is thrown: Items collection must be empty before using ItemsSource. Alright, but how can I empty the items collection? Items.Clear is not allowed when using ItemsSource.. Okay, ...

SQL Grouping around gaps

In SQL Server 2005 I have a table with data that looks something like this: WTN------------Date 555-111-1212 2009-01-01 555-111-1212 2009-01-02 555-111-1212 2009-01-03 555-111-1212 2009-01-15 555-111-1212 2009-01-16 212-999-5555 2009-01-01 212-999-5555 2009-01-10 212-999-5555 2009-01-11 From this I would like...

Implementing a "distinct" select in an existing query

I have an existing, fairly lengthy, SQL query. I would like to select records which have a distinct mt.ID. I've tried inserting "SELECT DISTINCT" in various places without any success. Could anyone tell me where it should go? Thanks very much. SELECT * FROM (select ROW_NUMBER() OVER(ORDER BY " + orderField + @") as RowNum, ...

Mysql Ordering Query

Hello I want to run a query to get the last five records from a table, but in reverse order. Currently I have: $query = "SELECT * FROM Table ORDER BY id DESC LIMIT 5"; which isn't quite what I want. For example if the last five records are 15 16 17 18 19 I want them returned as 15 16 17 18 19 Not 19 18 17 16 15 which is what the ...

MySQL find authors without posts (one-to-many relationship)

Hi guys... I've a MySQL problem: I've two tables (posts and authors) in a one to many relationship (since each post is written by an author and an author can write multiple posts). So here are the tables: Authors: id:BIGINT, name:VARCHAR(255) Posts: id:BIGINT, author_id:BIGINT, body:TEXT. How can I retrieve all the authors with no po...

Dynamic LINQ with other databases

Are there any free (gratis) providers for databases other MS SQL (e.g. MySQL or SQLite) that work with LINQ and support dynamic SQL query generation? E.g. table.Count() generates something like SELECT COUNT(*) FROM table and doesn't first load the whole table and then count the rows. ...

Database design - multi category products with properties

I am designing a basic inventory system for a vendor. They have many different product categories. Each product category has many different properties. A - x1, x2, x3, a1, a2, a3; B - x1, x2, x3, b1, b2, b3, b4; C - x1, x2, x3, c1, c2; Laptop - Make, Price, Quantity, Processor, OS, Hard drive, Memory, Video Card etc Monitor - Make...

What will be the most efficient indexes and constraints to use on my SQL Server table?

We have two tables, ActivityForm and Field which are given a many-to-many relationship via the ActivityFormField table. The ActivityFormFieldValidator table will have a many-to-one relationship with the ActivityFormField table, so we are giving the ActivityFormField table an identity column ("ActivityFormFieldId"). The ActivityFormFiel...

What Sql (PHPmyadmin)Statements would I use to create the following?

I want to create two Tables. Product, Members, and Category. I want the structure of the tables to be like: Category: Two Fields: (Category_ID) (Category_Name) and I would like Category_ID to be the Primary Key Product: Three Fields (Product_ID) (Product_Name) (Product_Price),(Category_ID) and I would like Product_ID to be the Primary ...