2

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

user2185412
  • 91
  • 1
  • 2
  • 8

1 Answers1

5

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.

Community
  • 1
  • 1
Artem Koshelev
  • 10,548
  • 4
  • 36
  • 68
  • it's fully works if i have readonly account? sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] [ , [ @provider= ] 'provider_name' ] [ , [ @datasrc= ] 'data_source' ] [ , [ @location= ] 'location' ] [ , [ @provstr= ] 'provider_string' ] [ , [ @catalog= ] 'catalog' ] this code must run always this starting my program, or only 1 time in mssql? – user2185412 Mar 19 '13 at 08:19
  • You would need a DBA to configure the link on the ReadOnly server... Or you casn have the link in 1 direction - Eg Server2 could use `Server1.Db.dbo.T` but Server1 Couldn't do `Server2.Db.dbo.T`. Only once – Basic Mar 19 '13 at 08:20
  • This is perhaps the only method to do this. – Jeremy Mar 19 '13 at 08:24
  • @user2185412 to add a linked server you must have `ALTER ANY LINKED SERVER` permission on `Server1`. It is performed once, usually by DBA as @Basic already noted. Check the following page for details on `Server2` login mapping: http://msdn.microsoft.com/en-us/library/ms189811.aspx – Artem Koshelev Mar 19 '13 at 08:25
  • @user2185412 [When you have decided which answer is the most helpful to you, mark it as the accepted answer by clicking on the check box outline to the left of the answer](http://stackoverflow.com/faq#howtoask) – Artem Koshelev Mar 22 '13 at 12:02