CODEWITHSUNDEEP
sql-servervb.nett-sqlconnection-string
Lucas Juan
Lucas Juan

Reputation: 371

Connection String For Local and Remote MSSQL SQL Insert Into

I have this code that will insert into the data from one table to another. The problem, is I am now trying to insert into a table from remote mssql server to my local mssql server. How will be the sql script for it, using VB.net?

I am presently getting an error:

network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

This is my code as of the moment:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

   Dim rowsAffected As Integer = 0


 Dim myConnectionString = "Data Source=(IP Address),1433;Network Library=DBMSSOCN;Initial Catalog=dbase;User ID=sa;Password=password;"

   Dim myconnectionstring2 = "Data Source= PC-Name;Initial Catalog=dbase;Integrated Security=True;Pooling=False"

   'Dim myConnection As New SqlConnection(myConnectionString)





   Dim myQuery As String = "INSERT INTO dbase.dbo.tbltransactions1 SELECT * FROM dbase.dbo.tbltransactions1"

   'Dim myQuery As String = "insert INTO (table)"

   'select

   'from LinkedserverName.DatabaseName.SchemaName.TableName"

   Dim myCommand As New SqlCommand(myQuery, myConnection, myConnection2)



   Try

       myConnection.Open()

       rowsAffected = myCommand.ExecuteNonQuery()

   Catch ex As Exception

       MsgBox(" ")

   Finally

       myConnection.Close()

   End Try



   Label1.Text = rowsAffected.ToString()
End Sub

*The connection string that has an IP address is the server I want to connect while the other one is the connection string for my local server..

Upvotes: 1

Views: 3009

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Your best shot would be a Linked Server to your local server pointing to the remote server.

Another way to do it is fetch the remote data localy in a DataTable for example, and then push it to your local DB from the DataTable.

Currently you are ordering your local sql to communicate with the remote server. This is not going to happen without setting up a Linked Server.

In order to setup a Linked Server:

  1. Open SSMS and connect to your local server.
  2. Expand your server node.
  3. Expand Server Objects.
  4. Right Click on Linked Servers.
  5. Select New Linked Server
  6. Type the IP of the remote server on the Server Name
  7. Select SQL Server
  8. Add a Login mapping in the Security tab. You will have to provide credentials for the remote server.
  9. In the Local Login column add the user name you use to connect to the Local Server.
  10. Leave the Impersonate col unchecked if the credentials to the remote server are different than the ones in the Local server (which should be the case)
  11. Enter the user name and the password that you would use to login to the Remote Server under Remote User and Remote Password respectively.
  12. For security reasons i would select the Not Be Made in the radio group on the bottom of that tab.

Then you will be able to run queries like:

SELECT * FROM [RemoteServerIP].[RemoteServerDB].[RemoteServerSchema].[RemoteServerTable]

Upvotes: 1

Related Questions