INSERT SELECT from different server

[Manikrag]

Hi Team,

I have 2 tables on two database servers and I want table1 to feed table 2.
I have a database user "WEB" on both the databases with diff passwords.
I open a SqlConnection connection for database2 and execute following thru ExecuteNonQuery.

Query = "INSERT INTO table2 SELECT * FROM Server1.Database.dbo.table1

On running this query, I am getting below error


TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user 'WEB'. OLE DB provider "SQLNCLI" for linked server Server1 returned message "Communication link.

I searched a lot but I did not find related with my scenario.
Please let me know if this is not the way or if I am doing anytihng wrong.

Thanks in advance!!

[tlhintoq]

Database How-to parts 1 and 2
Database tutorial Part 1
Database tutorial Part 2

[Manikrag]

thanks you very much! It would be great if you can direct me what exactly I should look for in above tutorials!

Thanks again!!

[tlhintoq]

Without knowing your skills, what you know and what you don't...
All I can say is "learn all that you can. Do all of the tutorials."
Combined the two can't take an hour. You should breeze over what you know and learn new things along the way.

[Manikrag]

:) great idea - I will go thru it and refresh myself.

But I would like to know about the standard approach for such cases. I have following options

1. how about linking the servers. What are the +ves and -ves.
2. Updating the tables using dataset or datareader.

It would be quick if I can run it thru a single query as written below.

INSERT INTO table2 SELECT * FROM Server1.Database.dbo.table1

please let me know if its possible and if yes what kind of setup is required.

Thanks again for your prompt response.

[tlhintoq]

I'm afraid that queries and such aren't my thing. I'm just familiar with the tutorials on the site and try to point people in the right direction for research - until an expert with more specific experience can jump in.

[Manikrag]

great - thanks man, I am searching on it hope I will get smthing.