VB.Net - Connect to multiple SQL instance

Hello Experts,

I have multiple SQL server across the States that I need to connect to and pass data from one to another.  All the databases are the same scheme/Configuration/etc.  

What I had done was link all the other SQL instances to the main SQL server and connect to it that way.  However, it seems to be very slow.

Current Situation:

INSERT INTO [WI-SVR2].DB2.DBO.EMPLOYEE(ID, NAME)
SELECT ID, NAME FROM [NY-SVR1].DB1.DBO.EMPLOYEE


Is it possible to do something with connection string connecting directly to each database and passing data that way?

Dim conWI AS String = "data source=WI-SVR2;persisty security info=false;initial catalog=DB2;UID=sa;PWD=Password1"

Dim conNY AS String = "data source=NY-SVR1;persisty security info=false;initial catalog=DB1;UID=sa;PWD=Password1"


Not sure how to incorporate that so that it would open one database and pass data from one to the other.  Any ideas how to do this or can it be done?
holemaniaAsked:
Who is Participating?
 
Bob LearnedCommented:
Are you talking about the full CRUD, or just inserts?  If you just need inserts, then you might be able to use the System.Data.SqlClient.SqlBulkCopy class to do a bulk insert from a source DataTable to the target server.
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
You don't say that you are using replication, only that you are linking the servers.

Why don't you use replication on the SQL Server side of things to propagate the data? That would at first glance seem to be more enterprise centric.

0
 
holemaniaAuthor Commented:
Because I'm not really replicating data.  Data is somewhat unique to each site.  I only need to transfer specific information base on the need of the user and then modify the rest to be as generic as possible.  My query was just a short simple version of what I'm trying to do.  

Each database has unique location, entity, etc that needs to be reset to a generic that exists in all databases before I do the transfer of data.  With the query example, I can do it fairly well except with performance.  It takes about 3 minutes or so to insert something across servers verse seconds if I have both databases in the same box.  

Just curious if there's a way to open both database similar to my sql query and then select from one database and insert it to another?
0
 
holemaniaAuthor Commented:
Yes, that's what I was looking for.  Found an example online after you post about sqlbulkcopy.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.