Delete across tables

hi,

I have two tables that are related, ie I created them with;

create table cm_message (
msgid varchar(40) not null primary key,
location varchar(240),
ts timestamp default 'now' not null,
lastsent timestamp
);

create table cm_data (
pkey integer not null primary key,
subdata varchar(255),
msgid varchar(40),
foreign key (msgid) references cm_message(msgid)
);

basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

thanks,
nikUse the ON DELETE CASCADE option:
...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE :rolleyes:|||which database system is this? because i don't know of any that will support this --timestamp default 'now'|||thanks LKBrwn_DBA.

rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

nik|||wow, ya learn sumpin new every day ;)

thanks nik|||Hi,

Does it work on a MySQL database?

//M|||Does it work on a MySQL database?the ON DELETE CASCADE? only for InnoDB tables

Defragmenting for SQL server performance?

Can Anybody guide me whether DEFRAGMENTING the drive improve the SQL Server performance in general.

What should be the location of the DATABASE files and the TRANSACTION log files.

Should they be in a single drive or seperate drives?

Thanks in advance

Jacx

For best I/O performance they should reside on different drives.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

default SSIS package location

After creating a complex SSIS package, I am unable to locate it. My guess is
that it is located in a default location. Where is that?
Regards,
Jamie
Well, that was productive! Ahem! Someone was sleeping during the beta.
<snooze - wha? - oh> I'm not sure I was clear on the question. I created an
SSIS package from the export wizard in VS 2005 that is transfering tables
from one database to another. At the end of the script there is a choice to
run immediately and to save the SSIS package. I chose save. I searched
both servers and my local machine. I don't see the package.
Regards,
Jamie
"thejamie" wrote:

> After creating a complex SSIS package, I am unable to locate it. My guess is
> that it is located in a default location. Where is that?
> --
> Regards,
> Jamie
|||The default is SQL Server on the same server as the source connection,
assuming the original defaults weren't accepted and package wasn't run and
deleted.
If you register Integration Services in SSMS and look in the "Stored
Packages\MSDB" path you should see it there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

default SSIS package location

After creating a complex SSIS package, I am unable to locate it. My guess is
that it is located in a default location. Where is that?
--
Regards,
JamieWell, that was productive! Ahem! Someone was sleeping during the beta.
<snooze - wha? - oh> I'm not sure I was clear on the question. I created an
SSIS package from the export wizard in VS 2005 that is transfering tables
from one database to another. At the end of the script there is a choice to
run immediately and to save the SSIS package. I chose save. I searched
both servers and my local machine. I don't see the package.
--
Regards,
Jamie
"thejamie" wrote:
> After creating a complex SSIS package, I am unable to locate it. My guess is
> that it is located in a default location. Where is that?
> --
> Regards,
> Jamie|||The default is SQL Server on the same server as the source connection,
assuming the original defaults weren't accepted and package wasn't run and
deleted.
If you register Integration Services in SSMS and look in the "Stored
Packages\MSDB" path you should see it there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

default SSIS package location

After creating a complex SSIS package, I am unable to locate it. My guess i
s
that it is located in a default location. Where is that?
--
Regards,
JamieWell, that was productive! Ahem! Someone was sleeping during the beta.
<snooze - wha? - oh> I'm not sure I was clear on the question. I created a
n
SSIS package from the export wizard in VS 2005 that is transfering tables
from one database to another. At the end of the script there is a choice to
run immediately and to save the SSIS package. I chose save. I searched
both servers and my local machine. I don't see the package.
--
Regards,
Jamie
"thejamie" wrote:

> After creating a complex SSIS package, I am unable to locate it. My guess
is
> that it is located in a default location. Where is that?
> --
> Regards,
> Jamie|||The default is SQL Server on the same server as the source connection,
assuming the original defaults weren't accepted and package wasn't run and
deleted.
If you register Integration Services in SSMS and look in the "Stored
Packages\MSDB" path you should see it there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

default save location

How can i change the default folder to which my database files are saved? right now it's saving everything to

\program files\mssql server\mssql.1\mssql\data

uggg! i can't even find an option to use a "save as" or export the mdf file. the "save as" tries to save the solution file. Any help is greatly appreciated.

Using SSMS, right-click on the server in Object Explorer, select [Properties], then click on the [Database Settings] tab.

Change the Default Database location in the setting pane.

|||If you want to change the location of the already existing database file, you should consider using the detach / attach functionality which is described in the BOL. Be aware that during this process, the database (not the instance per se) will NOT be available for incoming connections.

Jens K. Suessmeyer.

http://www.sqlserver2005.de