sql

Can Rails' Active Record handle SQL aggregate queries?

Hi, Just started learning active record and am wondering how to best retrieve data from multiple tables where an SQL aggregate query is involved. In the following example (from a medical app) I'm looking for the most recent events of various types for each patient (e.g. last visit, last labtest etc). As you can see from the sql query be...

3rd party sql import stops on import of sql file giving error: #1068 - Multiple primary key defined

i have a 3rd party software and i exported the database structure so that i can import it on another computer. i guess a few tables have multiple primary keys but how can i import the tables witout mysql stopping the import because it has detected multi primary keys? this is the error i get: 1068 - Multiple primary key defined i have ...

SQL 2005 - Search stored procedures for text (Not all text is being searched)

The following bits of code do not seem to be searching the entire routine definition. Code block 1: select top 50 * from information_schema.routines where routine_definition like '%09/01/2008%' and specific_Name like '%NET' Code Block 2: SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINI...

MySQL Join syntax for one to many relationship

I have a situation where I have one table of titles (t1) and another table with multiple links that reference these titles (t2) in a one to many relationship. What I want is the full list of titles returned with a flag that indicates if there is a specific link associated with it. Left Join and Group By: SELECT t1.id , t1.titl...

Best way to "materialize view"

Hi, (PostreSQL 8.2, running on WindowsXP) I have these lots of complexes queries that takes several seconds to run each. They are not "views" actually, but can be treated as so. I decided to persist the result records from those "views" into tables, that I call "aux" (auxiliary) tables. I can guarantee that there is no data change a...

Mysql migration tool, source parameter string | connection string for sql server

Hi all, I always got failed to "fetching of list failed error" this is my connection string in asp.NET "Data Source=maywood\XSQLSERVER;Initial Catalog=maywood_test;Integrated Security=SSPI" What exactly i should input at Mysql migration tool for source parameter string FYI,'maywood' is my conputer name and i am using sql sevrer 2000. ...

Advanced SQL SELECT

Hi, I have basic tables one and two. Let`s call them tbl1 and tbl2. tbl1 --------- 1 - apple | 2 - orange | 3 - banana | 4 - pineapple tbl2 --------- 1 - table | 2 - chair | 3 - sofa | Then there is tbl3 that has foreign keys that link it to both tables above. Table 3 form has two select fields: one that queries tbl1 and another tha...

Any good literature on join performance vs systematic denormalization ?

As a corollary to this question I was wondering if there was good comparative studies I could consult and pass along about the advantages of using the RDMBS do the join optimization vs systematically denormalizing in order to always access a single table at a time. Specifically I want information about : Performance or normalisation v...

Sql exists copy

Hi, I have a mssql stored procedure question for you experts: I have two tables [table1] which retrieves new entries all the time. I then have another table [table2] which I need to copy the content of [table1] into. I need to check if some of the the rows already exists in [table2], if it do, just update the Update-timestamp of [table...

How can i select multiple row and cascade them ?

Let's say I have three tables: table1 fields: memberid | name table2 fields: interestId | interestName table3 (used to make a relation between member and interest) fields: memberid | interestId and now I know I can user inner join to select one member's all interests. But how can I cascade all the interests in a single row??...

SQL (access) - add a new field to a composite primary key

I have a table called table1 which has a composite primary key using 'ScreenId' and 'ActivityItemId'. I have added a new field 'ProductId' which has a NOT NULL constraint. I want to add ProductId to the composite primary key. I thought that this would work db.execute "ALTER TABLE table1 PRIMARY KEY (ScreenId, ActivityItemId, Produc...

How do I update if exists, insert if not (aka upsert or merge) in MySQL?

Is there an easy way to INSERT an row when not exists, or to UPDATE if it exists, using one MySQL query? ...

Is there a way to have alternating column groups have different background colors in SQL Reporting Services?

I have a Report I'm designing in SQL Reporting Services that has column grouping. Is there a way to specify that each column group should be an alternating color? For example, I'd like column group one have a white background, column group 2 to have a blue background, column group 3 to be back to white, etc. ...

Copy data from tables in one db to duplicate tables in another db - What's the SQL?

Hi folks I have a situation where i have 20 Access databases. Each has the same 15 tables, but with different data. (The name and schema is identical for each table across the 20 databases). I want to make a new database with the same structure, and populate it with the contents of all of the 20 original databases, so i have one data...

strange scripts generated by SQL Server when exporting a job

Hello everyone, I am using SQL Server 2008 Enterprise. I am using Management Studio -> Jobs -> Script Job as -> Drop and Create TO feature to generate related sql statement so that from another computer I can import the job. My question is, from the generated sql scripts, from the beginning there is output like this, there is a hard co...

Looking for T-SQL scripts to delete a SQL Job

Hello everyone, If I know the database server name, instance name and the SQL Server job name, how to delete a SQL Server job by its name in a simple way? I am writing scripts which will be called by sqlcmd to delete SQL jobs. Appreciate if anyone could show me a sample? :-) thanks in advance, George ...

unable to connect to sql server through vb.net

i added the bindingsource control on my winform. here are the details of my connection: http://img43.imageshack.us/img43/3797/42119312.jpg when i test the connection, it says "the connection cannot be tested because the specified DB file does not exist" the DB is located in http://sql0234mjf-2.ordfsweb.com. how should i set up binding...

Patterns for stored procedures?

Are there any patterns for writing stored procs? like: Should we write 1 Sp for update, 1 for insert and 1 for select for each table. When is it good to create views. How to manage business rules in SP? How to reduce duplicate code etc etc.. Any good article or book to read about these patterns... thanks ...

how do i test if i am connected to sql DB through vb.net?

i have the following module and i would like to test connection. how do i test if the connection works? can you please be very specific with your answer: Imports System.Data.SqlClient Module Module1 Sub Main() ' Create a new SqlConnectionStringBuilder and ' initialize it with a few name/value pairs: Dim buil...

MySQL 'create schema' and 'create database' - Is there any difference.

Taking a peak into the 'information_schema' database and peaking at the metadata for one of my pet projects, I'm having a hard time understanding what (if any) differences there are between the 'create schema' command and the 'create database' command for MySQL. Are there any differences? If not, is this a rather typical pattern of beha...