January 17, 2012 at 1:27 am
Hi guys!
We have multiple servers and need to fetch specific data after specific time interval while running specific Query on Specific DBs in SQL Server.
Its very pathetic that I go on each server:
Connect Server
Connect DB
Execute Query
Copy results
Download from Remote Server
Share with Team
Any way to do such tasks through some automated Way in SQL.
please Guide:
Waseem Bukhari
CMer
January 17, 2012 at 1:57 am
Building a package in SSIS and running it in a SQL Job could be a good option for you.
Cheers
Callum
January 17, 2012 at 2:02 am
waseem.shahzad 45937 (1/17/2012)
Hi guys!We have multiple servers and need to fetch specific data after specific time interval while running specific Query on Specific DBs in SQL Server.
Its very pathetic that I go on each server:
Connect Server
Connect DB
Execute Query
Copy results
Download from Remote Server
Share with Team
Any way to do such tasks through some automated Way in SQL.
please Guide:
Waseem Bukhari
CMer
You can try mutiple queries option from sqlserver management studio.
Please read the below link.
http://msdn.microsoft.com/en-us/library/bb964743.aspx
To make this automated as suggested by vultar please create a ssis job to do it.
--- Babu
January 17, 2012 at 3:29 am
As mentioned, there are multiple ways of accessing remote databases but to automatically gather the data using SQL Server you will need a SQL Agent job to run something.
You then need to decide on one of the following methods (that I can think of at the moment)
-Linked servers
-SSIS ETL process
-OPENROWSET
-OPENDATASOURCE
-BCP (as an outsider)
I've rarely used the rowset functions and I dont find them the most intuitive way of accessing data but the others are tried and tested. My preferred manner of doing this would be SSIS.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply