Copying Database Tables Across Servers via C#

[MartinS]

OK, so this is easy in SQL, but not so easy in C#.
The idea is to take a table on Server1 and make a copy of it on Server2, and having tried it using DTS, I'm still not sure it's doing it right.
Does anyone know of a surefire way to do this in C# .Net, and if so, some sample code to get me started?
Regards
Martin

[RonProwse]

Martin its best using DTS to move the table otherwise you have a link between source and destination to do this with SQL

R

[MartinS]

Hi Ron
It's being done via DTS, which is being 'run' by a C# web application but the table is not arriving at the other end as expected, even though it seems to complete the task.
Hunting about, it seems this is not an unusual result to using DTS via C#, so was just wondering if anyone here had done this and knew a way to complete the task successfully.
Thanks for the advice (have a green, if I am able)
Martin

[Conor]

I've had enough of this pseudo racist biggoted s&%t!!!

oops, wrong thread

[MartinS]

Originally Posted by Conor

I've had enough of this pseudo racist biggoted s&%t!!!

oops, wrong thread

Am still chuckling as I leave you green

[RonProwse]

Martin soory for the late ness in replying but its best if the two servers are linked and you use the sql agent to fire the job at a specific time during the day. Im not to up on firing DTS packages from with C#.

You say that its not arriving as you expect it, if you expand on that i might be able to help.


you could call the dts job from a stored proc that way your code can just fire that off, di you have a way of working out if the job failed etc
R

[MartinS]

Ron
Looks like i may have found a solution - .NET 2 has a new class called SQLBulkCopy which allows the copy of tables from one server to another in about five lines of code - once I have organised the installation of the .NET 2 framework, I will be having a play and seeing how to integrate the new .NET 2 classes into my project.
Thanks anyway for yur help - I may be back if it all goes to pot!
Martin

[RonProwse]

Not too sure but you may have to upgrade to VS 2005, no i think its ok.

but to get the best out of .Net 2.0 you should try out sql server 2005.

Ron

[MartinS]

Ron
As a development team we are looking into the best edition of VS2005 for us, and the implementation of SQL 2005 is also iminent - it's all down to the managers actually giving the OK to go ahead and do it.
Thanks again
Martin

[Garf]

bcp is a hell of a lot quicker than DTS anyway, but it does have its own limitations.

Personally, I'd have just written a SELECT/INSERT into a stored procedure and then fired it from a SQL agent job or called the stored procedure from your code.