best practices on maxinsertcommitsize

Hi,

I wonder if anyone knows what would be the best case scenario for the property 'maxinsertcommitsize' for the sql destination task if I want to load 6m records into a target. Is the best setting 0 (try loading all in one batch) or should I choose a different value for example 1000000 per batch?

Thanks,

Marc

This setting is up to you. How big of a batch do you want to insert? If you encounter an error in the load, do you want to roll back ALL records, or just the batch?

There is no "best practice" because this is user dependent. Everyone's situation is different.|||

Ok, but does it affect performance? For our situation the following applies:

- We don't care how big the batch will be as long as it is optimized for maximum performance
- If we encounter an error in the load it doesn't matter if ALL records roll back or just the batch.

|||I don't think there will be much of a performance difference. The one thing to watch out for in a big batch is the potential for filling up the transaction log.

Try different settings and report your results back to us.|||Actually there may be a performance difference between using batches and not. When SQL Server commits a batch it has to update any affected indexes. 6M records could be a good deal of work. I've seen it take over an hour to commit a batch that size when indexes are involved. By using smaller batches, SQL Server can get started on this work while SSIS is still sending it rows. But that advantage depends on how long your SSIS process takes. If it can generate those 6M in 30 seconds, then giving SQL a head start isn't going to do much good.

Best practices for backup and restore of large replicated databases

Hello,
I am working on a project where we have a large database that is replicated.
My task is to create a backup/restore plan for this project.
I know the basics for backup/restore, but are looking for best practices and
experiences from you who have done this already on a large scale.
Both publisher and subscribers are allowed to do updates. Restore time at
subscribers have to be short and therefore I am looking for a solution where
both publisher and subscribers are backup up.
Thanks in advance for any pointers to white papers, books, emails, etc.
Best regards,
Vemund
Hi Vemund,
Thanks for your post and I would like to take ownership of this thread and
help you with this issue.
Based on my scope, I understood that you would like to find the best
practise about large scale replicated database backup and restore strategy.
Have I understood you? Correct me if I was wrong.
First of all, please understood that it is hard to say what is the best
practice for your porject as I am not sure how much important is the data
for you and how much data lose could be acceptable. You should discussed
this with your users and find the best strategy for your project.
Generally speaking, here are some general recommendations on database
disaster recovery strategy:
1. Make regular backups that reflect your backup strategy. Ask yourselves
the question - how much data can I afford to lose? (1 min, 1 hour, 1 day
worth). Then ensure that the frequency of the backups reflects the answer
to that question.
2. Make backup to a different media from where the database file reside.
That way you protect against a central point of failure. You can consider
backing up to tape or to another disk (share). Consider moving the backed
up data off-site to another location to protect against disasters like
fires, floods, hurricanes, tornadoes, etc.
3. Test your backups on a regular basis by ensuring they are restore-able
on another system. Then run DBCC CHECKDB on those to ensure the original
database or the backup are not damaged.
4. Keep in mind that the data stored in your database(s) may be one of the
most valuable assets your organization has!
In addition, please review the following information in SQL Books On-Line
"Designing a Backup and Restore Strategy"
Here are some precautions a database administrator should take to ensure
the safety of the data:
1. It is always a good idea to ensure that your backup strategy is
sufficient to recover from a catastrophic failure. Offsite storage and
other precautions are appropriate.
2. Test the database restore operation in a secondary or test database on a
frequent basis.
3. Ensure that any caching devices can handle all failure situations (power
outage, bad sectors, bad drives, system outage, lockups, power spike, and
so forth).
4. Ensure that your caching device:
-- Has integrated battery backup.
-- Can reissue writes on power up.
-- Can be fully disabled if necessary.
-- Handles bad sector re-mapping realtime.
5. Enable torn page detection; it has little performance impact.
6. Configure RAID drives allowing for a hot swap of a bad disk drive, if
possible.
7. Use newer caching controllers that allow addition of more disk space
without
restarting the OS. This can be an ideal solution.
Check the following which covers some of this in detail:
SQL Server capacity and storage guide:
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
Windows backup guide:
http://www.microsoft.com/technet/pro...aintain/backup
rest/br04.mspx
Moreover, here is one more WebCast for you, which will discuss Microsoft
SQL Server database files, the different kinds of database backup options
that are available in SQL Server 2000, and some of the factors to consider
when you design a backup strategy. It will talk about best practices for
backing up and restoring database files. It will review some case studies
and talk about how to troubleshoot problems with backup and restore
operations.
Support WebCast: Microsoft SQL Server 2000: Understanding Backup and Restore
http://support.microsoft.com/?id=329833
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Vemund,
I am just checking on your progress regarding the information that was sent
you! I wonder whether my suggestion is useful or you would like to receive
more information on this.
If you encounter any difficulty, please do not hesitate to let me know.
Please post here and let me know the status of your issue. Looking forward
to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql

Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.
> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.
> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/technologies/integration/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.

> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.

> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx

> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/techno...on/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

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]
>

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...
> > 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
> >
> >
> >
>