CODEWITHSUNDEEP
.netvb.netsql-server-2008
user2185412
user2185412

Reputation: 91

How connect to 2 different sql servers VB.NET

I'm working with my little project, and stopped at this point I have 2 sql servers, one - is department sql server(READONLY) and my local server for replication. In VB.net i open connections with adodb lib 

    connectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security   Info=False;Initial Catalog=Work;Data Source=server1\SQLEXPRESS"
        ginfo = "Select * From base1.dbo.table1 "
        cn = New ADODB.Connection
        rs = New ADODB.Recordset
        cn.Open(connectionString)
        rs.Open(ginfo, cn, ADODB.CursorTypeEnum.adOpenStatic)
        i = 0
        With rs
            If rs.BOF = False Then
                Do While Not .EOF
                    ReDim Preserve users(i)
                    users(i).name = rs.Fields(2).Value
                    lb_sfio.Items.Add(users(i).name)
                    i = i + 1
                    .MoveNext()
                Loop
            End If
        End With

So next stage is connecting to second server and getting information from another server text is similiar. BUT, i interesting how work with 2 servers with SQL-script language for example 

ginfo = "SELECT * FROM srv1.bs1.dbo.tbl1 EXCEPT SELECT * FROM srv2.bs1.dbo.tbl1"

because in my variant is two slow. and maybe the second question how find updates in DB without full comparison(db has 4k strings, it's boring).

Upvotes: 2

Views: 2974

Answers (1)

Artem Koshelev
Artem Koshelev

Reputation: 10607

You can add Server2 as the linked server to the Server1.

Then, your query on Server1 will look like:

use base1
SELECT field1 FROM table1 EXCEPT SELECT field1 FROM server2.base1.dbo.table1

But you should be aware of performance implications with queries against linked servers.

The linking procedure requires ALTER ANY LINKED SERVER permission on Server1. You do not have to repeat this operation every time before query, the linked server becomes persistent until removed.

Upvotes: 5

Related Questions