Pull data from different database server

Hi,

In my application i am storing the data in a database(server A). And i have a scenario where in a middle server(server B) is there in which i need to create a table and pull the data from one of the tables from my original database server(server A) into second database server(server B). Where can i get the code to write a job script which can connect from server B to server A and pull the data from the table and delete that data from the table in server A. Can any give me the code or link where i can find the code. Please help.

Thanks

If they are SQL Server databases you can create a DTS job to copy the tables and data. Or within you SQL, you can reference the other SQL server from the following

SELECT *FROM <serverName>.<ownerName>.<table>

Where the serverName is the other server, ownerName is the owner of the table you want to access.|||

There are 2 ways of doing this.

1) You can use Linked Server.

2) Use DTS or SSIS Packages to transfer data from one server and database to another.

Hope this will help.

|||

Hi,

Could you please tell me what are the steps i need to follow in order to pull the data by using Linker Server option. or else any site which has step by step details to work with linker server and pull the data.

Thanks

|||

If i user linked server and write a stored procedure to do my job, how can i schedule the script to work for every 5 min's. I want to pull pull the data for every 5 mins. Can i close the connection every time when i finish my job and reconnect again after 5 min's when i want to pull the data again. Please help

Thanks

|||

tgopi99:

If i user linked server and write a stored procedure to do my job, how can i schedule the script to work for every 5 min's. I want to pull pull the data for every 5 mins. Can i close the connection every time when i finish my job and reconnect again after 5 min's when i want to pull the data again.

You can schedule the SP to run at a regular interval using SQL Jobs. One more thing, if you use any of the features like Jobs or DTS or SSIS, they themselves manage the connections to the server and you don't need to worry about them.

Well, linked server is one of the options. Below is a link which can be useful for linked servers:

http://msdn.microsoft.com/msdnmag/issues/04/11/DataPoints/

But there is another way around, that is to create a DTS ( if you are using SQL Server 2000 ) or SSIS ( if you are using SQL Server 2005 ) package. If your purpose is to transfer data between 2 servers then you can achieve this by writing a DTS or SSIS.

Below are some links for creating and using DTS:

http://www.databasejournal.com/features/mssql/article.php/3579941

http://www.verio.com/support/documents/view_article.cfm?doc_id=3846

Below are some links for creating and using SSIS:

http://msdn2.microsoft.com/en-us/library/ms167031.aspx

http://aspalliance.com/947_Building_a_SQL_Server_2005_Integration_Services_Package_Using_Visual_Studio_2005

http://www.databasejournal.com/features/mssql/article.php/3503996

Hope the links are sufficient and helpful.