Best practice

Hi
What is the prefered practice to use when I have 2 or more related
tables and I want to delete a row in the master table and I want the
child tables to automatically delete their related rows, Should I use
triggers in the database, enable cascade delete in the dataset or
somthing else ?
I use visual studio 2005 and sql server 2005.
Thanks
RolfIt depends. If you want the child records deleted, then a cascade delete is
appropriate. If you want the application to deal with the child records
first, then a foreign key constraint that will stop deletes will be valid.
Both are best practices, depending on your application requirements.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<rolf-hje@.online.no> wrote in message
news:1139864939.431879.138190@.z14g2000cwz.googlegroups.com...
> Hi
> What is the prefered practice to use when I have 2 or more related
> tables and I want to delete a row in the master table and I want the
> child tables to automatically delete their related rows, Should I use
> triggers in the database, enable cascade delete in the dataset or
> somthing else ?
> I use visual studio 2005 and sql server 2005.
> Thanks
> Rolf
>|||Cascade with error handling could be one way as you mention.
Another way wich I prefer is to use transaction when deleting data that
needs to delete related data.
I do recommend that you dont use triggers since that easily can make
things go out of control.|||It depends. Cascading deletes are the simplest to implement, but can
complicate deadlock minimization because the order in which locks are
obtained across tables is not clearly defined. In addition, INSTEAD OF
triggers cannot exist on the referencing table of a cascading referential
action. Using FOR or AFTER triggers to cascade deletes is generally a bad
idea--not because of the performance impact, but because several triggers
can exist for an action on a table and the order in which they are executed
is not deterministic, IMO they should be avoided. I prefer to perform
deletes within a transaction in a stored procedure. It is then clear from
reading the text of the proc which tables will be affected, and it's easier
to control the order in which locks are obtained to minimize the likelyhood
of a deadlock. The use of INSTEAD OF DELETE triggers instead of stored
procedures may warrant investigation because any locks applied depend on the
order in which statements appear within the trigger (none are applied as a
direct result of the trigger firing) in the same way as statements within a
stored procedure, and unlike the stored procedure method, they do not
require preventing direct access to the tables. (On the other hand, many
would say that you should always prevent direct access to the tables and
require all modifications to be performed using stored procedures.)
<rolf-hje@.online.no> wrote in message
news:1139864939.431879.138190@.z14g2000cwz.googlegroups.com...
> Hi
> What is the prefered practice to use when I have 2 or more related
> tables and I want to delete a row in the master table and I want the
> child tables to automatically delete their related rows, Should I use
> triggers in the database, enable cascade delete in the dataset or
> somthing else ?
> I use visual studio 2005 and sql server 2005.
> Thanks
> Rolf
>|||OK, thanks for all replies
I think I will avvoid triggers and use cascading delete. But what is
more efficent. Cascading deletes in the database or cascading deletes
in the dataset. Is there a performance difference between these two
options ?
Thanks
Rolf

Best eqivalent to a Data Transform Task

Hi
What is the best TSql to a Data Transform Task that just copies data from
one db to another.
Reason for asking, is that I have a DTS package that just does a number of
copies of tables from one database to another (the databases are on
different servers) and occasionally one of these task fails. When it fails,
it doesnt report any errors and the whole execution of the package succeds
without any errors. It doesn't happen very often, so that also makes is
quite difficult to find out why.
As an atempt to find the reason, I'd make these copies/transforms as TSql
jobs, so I'd like to know what would be the best way to do it with Tsql?
Have any of you any good suggestions to this?
Regards
Steen
It could simply be, something like this:
INSERT INTO DB1.dbo.Table1
(Col1, Col2, Col3, Col4, Col5)
SELECT
Col1, Col2, Col3, Col4, Col5
FROM DB2.dbo.Table1
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%2315X2ZnYEHA.1224@.TK2MSFTNGP09.phx.gbl...
Hi
What is the best TSql to a Data Transform Task that just copies data from
one db to another.
Reason for asking, is that I have a DTS package that just does a number of
copies of tables from one database to another (the databases are on
different servers) and occasionally one of these task fails. When it fails,
it doesnt report any errors and the whole execution of the package succeds
without any errors. It doesn't happen very often, so that also makes is
quite difficult to find out why.
As an atempt to find the reason, I'd make these copies/transforms as TSql
jobs, so I'd like to know what would be the best way to do it with Tsql?
Have any of you any good suggestions to this?
Regards
Steen
|||Hi Vyas
Thanks for your input - I was thinking about something like myself, but just
wanted to see if there was a smarter way I didn't knew about.
I seems to have some problems getting this so work in a DTS package though.
I've created a package with a source and destination which are 2 different
servers. I've then made a task that executes a sql statement that should
copy the data from one table in 'server1' to another table in 'server2'.
The code is :
INSERT INTO DATABASE2.dbo.Table2
(Col1, Col2, Col3)
SELECT
Col1, Col2, Col3
FROM [Server1].Database1.dbo.Table1
When I execute this in a Query analyser it works fine, but when I set it
into the data transform task, and parse the query, it gives me an "Invalid
object name DATABASE2.dbo.Table2" error.
I'm building this package on server2, so to some degree I understand that it
can't find/see server1, but why does it then work in a QA.
I've then in the same Transform task tried to do a sp_addlinkedserver -
USE master
GO
EXEC sp_addlinkedserver
'Server2',
N'SQL Server'
GO
When I try this in QA, it tells me that the server alreday exist, and when I
add it to my SQL statement in the Transform Task, it gives we a syntax error
around the "GO" statements.
It might just be me that are doing something stupid, but I just can't see
why it doesn't work. It also puzzles me a little bit why it react diffently
in QA and in the Transform Task, but most likely there are a good reason for
that.
Regards
Steen
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i en meddelelse
news:ep9wYdnYEHA.2972@.tk2msftngp13.phx.gbl...
> It could simply be, something like this:
> INSERT INTO DB1.dbo.Table1
> (Col1, Col2, Col3, Col4, Col5)
> SELECT
> Col1, Col2, Col3, Col4, Col5
> FROM DB2.dbo.Table1
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%2315X2ZnYEHA.1224@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best TSql to a Data Transform Task that just copies data from
> one db to another.
> Reason for asking, is that I have a DTS package that just does a number of
> copies of tables from one database to another (the databases are on
> different servers) and occasionally one of these task fails. When it
fails,
> it doesnt report any errors and the whole execution of the package succeds
> without any errors. It doesn't happen very often, so that also makes is
> quite difficult to find out why.
> As an atempt to find the reason, I'd make these copies/transforms as TSql
> jobs, so I'd like to know what would be the best way to do it with Tsql?
> Have any of you any good suggestions to this?
> Regards
> Steen
>
>
|||Hi
I have played a little bit with the code, and it now seems like I've got it
working without the Linkedserver option.
I then just have one more simple question - Is there an easy or simple way
to get the INSERT INTO command to take all the colunms in the table without
having to type them all in? Some of the tables I'm working on has quite a
number of columns so I'm already tired just thinking of having to type all
these in by hand.
Regards
Steen
"Steen Persson" <SPE@.REMOVEdatea.dk> skrev i en meddelelse
news:uWYCVQoYEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Hi Vyas
> Thanks for your input - I was thinking about something like myself, but
just
> wanted to see if there was a smarter way I didn't knew about.
> I seems to have some problems getting this so work in a DTS package
though.
> I've created a package with a source and destination which are 2 different
> servers. I've then made a task that executes a sql statement that should
> copy the data from one table in 'server1' to another table in 'server2'.
> The code is :
> INSERT INTO DATABASE2.dbo.Table2
> (Col1, Col2, Col3)
> SELECT
> Col1, Col2, Col3
> FROM [Server1].Database1.dbo.Table1
> When I execute this in a Query analyser it works fine, but when I set it
> into the data transform task, and parse the query, it gives me an "Invalid
> object name DATABASE2.dbo.Table2" error.
> I'm building this package on server2, so to some degree I understand that
it
> can't find/see server1, but why does it then work in a QA.
> I've then in the same Transform task tried to do a sp_addlinkedserver -
> USE master
> GO
> EXEC sp_addlinkedserver
> 'Server2',
> N'SQL Server'
> GO
> When I try this in QA, it tells me that the server alreday exist, and when
I
> add it to my SQL statement in the Transform Task, it gives we a syntax
error
> around the "GO" statements.
> It might just be me that are doing something stupid, but I just can't see
> why it doesn't work. It also puzzles me a little bit why it react
diffently
> in QA and in the Transform Task, but most likely there are a good reason
for[vbcol=seagreen]
> that.
> Regards
> Steen
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i en meddelelse
> news:ep9wYdnYEHA.2972@.tk2msftngp13.phx.gbl...
from[vbcol=seagreen]
of[vbcol=seagreen]
> fails,
succeds[vbcol=seagreen]
TSql
>

Best eqivalent to a Data Transform Task

Hi
What is the best TSql to a Data Transform Task that just copies data from
one db to another.
Reason for asking, is that I have a DTS package that just does a number of
copies of tables from one database to another (the databases are on
different servers) and occasionally one of these task fails. When it fails,
it doesnt report any errors and the whole execution of the package succeds
without any errors. It doesn't happen very often, so that also makes is
quite difficult to find out why.
As an atempt to find the reason, I'd make these copies/transforms as TSql
jobs, so I'd like to know what would be the best way to do it with Tsql?
Have any of you any good suggestions to this?
Regards
SteenIt could simply be, something like this:
INSERT INTO DB1.dbo.Table1
(Col1, Col2, Col3, Col4, Col5)
SELECT
Col1, Col2, Col3, Col4, Col5
FROM DB2.dbo.Table1
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%2315X2ZnYEHA.1224@.TK2MSFTNGP09.phx.gbl...
Hi
What is the best TSql to a Data Transform Task that just copies data from
one db to another.
Reason for asking, is that I have a DTS package that just does a number of
copies of tables from one database to another (the databases are on
different servers) and occasionally one of these task fails. When it fails,
it doesnt report any errors and the whole execution of the package succeds
without any errors. It doesn't happen very often, so that also makes is
quite difficult to find out why.
As an atempt to find the reason, I'd make these copies/transforms as TSql
jobs, so I'd like to know what would be the best way to do it with Tsql?
Have any of you any good suggestions to this?
Regards
Steen|||Hi Vyas
Thanks for your input - I was thinking about something like myself, but just
wanted to see if there was a smarter way I didn't knew about.
I seems to have some problems getting this so work in a DTS package though.
I've created a package with a source and destination which are 2 different
servers. I've then made a task that executes a sql statement that should
copy the data from one table in 'server1' to another table in 'server2'.
The code is :
INSERT INTO DATABASE2.dbo.Table2
(Col1, Col2, Col3)
SELECT
Col1, Col2, Col3
FROM [Server1].Database1.dbo.Table1
When I execute this in a Query analyser it works fine, but when I set it
into the data transform task, and parse the query, it gives me an "Invalid
object name DATABASE2.dbo.Table2" error.
I'm building this package on server2, so to some degree I understand that it
can't find/see server1, but why does it then work in a QA.
I've then in the same Transform task tried to do a sp_addlinkedserver -
USE master
GO
EXEC sp_addlinkedserver
'Server2',
N'SQL Server'
GO
When I try this in QA, it tells me that the server alreday exist, and when I
add it to my SQL statement in the Transform Task, it gives we a syntax error
around the "GO" statements.
It might just be me that are doing something stupid, but I just can't see
why it doesn't work. It also puzzles me a little bit why it react diffently
in QA and in the Transform Task, but most likely there are a good reason for
that.
Regards
Steen
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i en meddelelse
news:ep9wYdnYEHA.2972@.tk2msftngp13.phx.gbl...
> It could simply be, something like this:
> INSERT INTO DB1.dbo.Table1
> (Col1, Col2, Col3, Col4, Col5)
> SELECT
> Col1, Col2, Col3, Col4, Col5
> FROM DB2.dbo.Table1
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%2315X2ZnYEHA.1224@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best TSql to a Data Transform Task that just copies data from
> one db to another.
> Reason for asking, is that I have a DTS package that just does a number of
> copies of tables from one database to another (the databases are on
> different servers) and occasionally one of these task fails. When it
fails,
> it doesnt report any errors and the whole execution of the package succeds
> without any errors. It doesn't happen very often, so that also makes is
> quite difficult to find out why.
> As an atempt to find the reason, I'd make these copies/transforms as TSql
> jobs, so I'd like to know what would be the best way to do it with Tsql?
> Have any of you any good suggestions to this?
> Regards
> Steen
>
>|||Hi
I have played a little bit with the code, and it now seems like I've got it
working without the Linkedserver option.
I then just have one more simple question - Is there an easy or simple way
to get the INSERT INTO command to take all the colunms in the table without
having to type them all in? Some of the tables I'm working on has quite a
number of columns so I'm already tired just thinking of having to type all
these in by hand.
Regards
Steen
"Steen Persson" <SPE@.REMOVEdatea.dk> skrev i en meddelelse
news:uWYCVQoYEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Hi Vyas
> Thanks for your input - I was thinking about something like myself, but
just
> wanted to see if there was a smarter way I didn't knew about.
> I seems to have some problems getting this so work in a DTS package
though.
> I've created a package with a source and destination which are 2 different
> servers. I've then made a task that executes a sql statement that should
> copy the data from one table in 'server1' to another table in 'server2'.
> The code is :
> INSERT INTO DATABASE2.dbo.Table2
> (Col1, Col2, Col3)
> SELECT
> Col1, Col2, Col3
> FROM [Server1].Database1.dbo.Table1
> When I execute this in a Query analyser it works fine, but when I set it
> into the data transform task, and parse the query, it gives me an "Invalid
> object name DATABASE2.dbo.Table2" error.
> I'm building this package on server2, so to some degree I understand that
it
> can't find/see server1, but why does it then work in a QA.
> I've then in the same Transform task tried to do a sp_addlinkedserver -
> USE master
> GO
> EXEC sp_addlinkedserver
> 'Server2',
> N'SQL Server'
> GO
> When I try this in QA, it tells me that the server alreday exist, and when
I
> add it to my SQL statement in the Transform Task, it gives we a syntax
error
> around the "GO" statements.
> It might just be me that are doing something stupid, but I just can't see
> why it doesn't work. It also puzzles me a little bit why it react
diffently
> in QA and in the Transform Task, but most likely there are a good reason
for
> that.
> Regards
> Steen
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i en meddelelse
> news:ep9wYdnYEHA.2972@.tk2msftngp13.phx.gbl...
from[vbcol=seagreen]
of[vbcol=seagreen]
> fails,
succeds[vbcol=seagreen]
TSql[vbcol=seagreen]
>

benefits of full recovery**

Hi
What's the benefit usage to select Full recovery model for a database?
I know it's better to select simple mode for developement and test
enviroment.
But I don't know excatly how can the full recovery model be usefull for
critical enviroment?
Can any body give me an example?
Thanks alotMaryam,
Here is a nice article:
http://insight.zdnet.co.uk/software...34171-1,00.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||maryam rezvani wrote:
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull
> for critical enviroment?
> Can any body give me an example?
> Thanks alot
short:
if your DB is in full recovery model, you will have to backup the log, if
you can several times/day. In case of a restore, you probably will not lose
any data, and you can recovery to the point in time (useful if someone
deletes something or some other human error)
long:
see BooksOnLine (mssql help)|||Hi
By using 'FULL recovery' mode you will be able to restore your database at
point time
For example , you do a full backup on sunday night and every hour a log file
backup during the work day ,so on tuesday at 11 AM the database is crashed
.If you have the database in 'SIMPLE recovery' you will be able to get the
database back only from last backup (sunday night) ,that means you lost the
data of monday,tuesday . However , if you have 'FULL recovery' you have to
restore the last database backup and the apply all LOG backups till 11AM
(see more details in the BOL)
Another important point is
Let say you do a FULL backup database every sunday night and every hour a
LOG file backup. Now, you lost the FULL database backup of the second week
and the database is get corrupted now. So what would you do, you are about
to lost the data of two weeks ( if you do only FULL backup database (SIMPLE
recovery) ) ,but in that case you restore the last full backup (two weeks
ago) and the apply all LOG files you perfom till the corruption. You will
lost nothing because LOG file backup contains all info since last LOG file
backup.
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:uWJq1njwEHA.3824@.TK2MSFTNGP15.phx.gbl...
> Hi
> What's the benefit usage to select Full recovery model for a database?
> I know it's better to select simple mode for developement and test
> enviroment.
> But I don't know excatly how can the full recovery model be usefull for
> critical enviroment?
> Can any body give me an example?
> Thanks alot
>|||Thanks
Do you mean that if I found that some false insertion happenning to a
special table
in 9:00 am and my db is in full recovery model so I can restore my db to the
point
exaclty before the false insertion through the log file?
but if I set my db to simple mode have to restore the previous backup
related to the day before(cause I back up my db every night)?
"Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
news:1099553502.722414@.internet.fina.hr...
> maryam rezvani wrote:
> short:
> if your DB is in full recovery model, you will have to backup the log, if
> you can several times/day. In case of a restore, you probably will not
lose
> any data, and you can recovery to the point in time (useful if someone
> deletes something or some other human error)
> long:
> see BooksOnLine (mssql help)
>|||Correct. But for the first scenario, you also need to perform regular transa
ction log backups. When
you restore a transaction log backup, you can stop at a certain point in tim
e.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:egP72OkwEHA.4004@.tk2msftngp13.phx.gbl...
> Thanks
> Do you mean that if I found that some false insertion happenning to a
> special table
> in 9:00 am and my db is in full recovery model so I can restore my db to t
he
> point
> exaclty before the false insertion through the log file?
> but if I set my db to simple mode have to restore the previous backup
> related to the day before(cause I back up my db every night)?
> "Zarko Jovanovic" <mind_less_NOSPAM_@.bigfoot.com> wrote in message
> news:1099553502.722414@.internet.fina.hr...
> lose
>