November 24, 2015 at 4:31 am
I have a servernames table with list of servers.
SERVERNAMES_TABLE
ABCD
EFGH
IJKL
MNOP
QRST
UVQX
YZYZ
I have this
sample query:
select * from employees
How can I run the above SQL query against each of the servers in the above
SERVERNAMES_TABLE.
Connect to the specific server
and run the sample query against it and
get the results into another table (companyemployees) on the current server
Similarly we loop through the entire SERVERNAMES_TABLE connect to the respective server and execute the query and get the results into (companyemployees) table on my
current server(DATACOLLECTIONSERVERDATABASE).
I want this to be scheduled as part of a scheduled sql job.
I would like to do this using only T-SQL.
Thanks
November 24, 2015 at 4:41 am
There's two ways I'd recommend:
(1) Set up a Central Management Server (CMS) in SSMS and then register all the servers in your list in a group under the CMS. You can then run the script against each server
(2) Write a script to connect to each server and execute the commands. Run the script in SSMS in SQLCMD mode
John
November 24, 2015 at 6:14 am
I did think of doing the Central management sway.But can we schedule SQL jobs if we use this option?
November 24, 2015 at 6:20 am
No. If you're going to do it regularly, create an SSIS package or do it with the sqlcmd utility. You can run either of those from a job.
John
November 24, 2015 at 6:58 am
I want to do this using only SQL/T-SQL.
November 24, 2015 at 7:21 am
sqlcmd is nothing but a utility that runs SQL scripts. If it doesn't meet your requirement, please provide more information on that requirement and the reason for it. This is beginning to sound like a homework question.
John
November 24, 2015 at 2:38 pm
Not really I already have a working model using SSIS.
And would like to replicate the same in t-sql.
Any sample codes are appreciated.
November 24, 2015 at 10:36 pm
Can we convert an SSIS package to t-sql code ?
November 25, 2015 at 1:34 am
An SSIS package is an XML file, it cannot be converted to T-SQL.
What is the overall goal here?
1 Script to connect to multiple hosts, to download a specific table?
SSIS would be ideal for this unless you want to start looping and creating multiple linked servers everywhere
November 25, 2015 at 10:28 am
Yes,the goal is to collect information from 100+ servers in a domain.
I will have a sql script which will exist/run on one source server.
It will take the server names from one table ..connect to that server...extract the required data(simple select statement) from that server and dump it into a table on the source server(ideally on the same server where the script is being executed).
Likewise it will loop through the entire table(server names) till it ends and gathers the data from all servers and store it on the source server.
I have an SSIS package built doing this.I would like to get a sample t-sql script with the required details.
An outline to get an idea is good enough.
No cursors please.
Thanks
November 26, 2015 at 2:15 am
No cursors? Why not? You're looping through 100 servers, so even if you don't have an explicit cursor, the effect is going to be the same. This is the kind of thing SSIS was designed for - why do you want to ditch that solution and reinvent it? If you must have plain T-SQL scripts, I've already told you how to do it - use sqlcmd.
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply