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
>