Connection til 2 databaser på 2 servere med 2 logins

Du skal bare oprette 2 connections.

Dim myCommand As SqlCommand
    Dim mySQL As String
    Public myConnection As SqlConnection = New SqlConnection("Data Source=PPSSRV1;Initial Catalog=Pricelist;Persist Security Info=True;User ID=x1;Password=p1")

    Public myConnection1 As SqlConnection = New SqlConnection("Data Source=PPSSRV2;Initial Catalog=Pricelist;Persist Security Info=True;User ID=x2;Password=p2")



mySQL = "UPDATE [Pricelist].[dbo].[Account]  SET [Name] = crm_acc.[Name],[Address1_Line1] = crm_acc.Address1_Line1,[Address1_Line2] = crm_acc.Address1_Line2"
mySQL = mySQL & ",[Address1_Line3] = crm_acc.Address1_Line3,[Address1_Country]=crm_acc.Address1_Country,[Address1_City] = crm_acc.Address1_City"
mySQL = mySQL & ",[AccountNumber] = crm_acc.AccountNumber"
mySQL = mySQL & " from [Pricelist].[dbo].[Account] INNER JOIN CRMSRV1.deif_MSCRM.dbo.Account as crm_acc ON [Pricelist].[dbo].[Account].AccountID = crm_acc.AccountID"

mySQL = mySQL & " INSERT INTO [Pricelist].[dbo].[Account]([AccountID] ,[Name] ,[Address1_Line1],[Address1_Line2] ,[Address1_Line3] ,[Address1_Country] ,[Address1_City],[AccountNumber])"
mySQL = mySQL & " Select AccountID,[Name] , Address1_Line1, Address1_Line2, Address1_Line3, Address1_Country,Address1_City, AccountNumber"
mySQL = mySQL & " FROM CRMSRV1.MSCRM.dbo.Account WHERE AccountID NOT IN (select AccountID FROM [Pricelist].[dbo].[Account])"
       
myCommand = New SqlCommand(mySQL, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

myCommand1 = New SqlCommand(mySQL, myConnection1)
myCommand1.Connection.Open()
myCommand1.ExecuteNonQuery()
myCommand1.Connection.Close()

Hej Janus_007
Overser du ikke noget? Min opdeling kan måske i farten misforståes. mySQL gør to ting først opdaterer den min tabel, og derefter inserter den nye records, men det sker i et kald. I både insert og update anvender jeg tabeller fra to servere. Jeg kan ikke lige se at to executions til de to connections skulle hjælpe, eller er det mig der overser noget?

wildthing

Ja okay... kan godt se hvad du siger nu - det var ikke helt så tydeligt da jeg svarede *G*

Jeg kan se du anvender linked server, CRMSRV1.MSCRM.dbo.Account og CRMSRV1.deif_MSCRM.dbo.Account - altså CRMSRV1 er linked. Du kan under linked servers opsætte brugernavn og password til adgang fra server til server, altså en slags integreret adgang(Be made using this security context), du har også den mulighed i linked servers at opsætte 'Be made using the logins current security context' - Du finder linked servers under Security-afsnittet.
Det kan være at linked servers ikke er løsningen til dig når du siger flere logins igennem openconnection, så vil jeg anbefale dig at bruge OpenDataSource eller OpenRowset.

SELECT  *
FROM      OPENDATASOURCE(
        'SQLOLEDB',
        'Data Source=ServerName;User ID=MyUID;Password=MyPass'
        ).Northwind.dbo.Categories


SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
  'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a


Jeg ved ikke om det kan løse opgaven... :-)