March 17, 2009 at 8:51 am
I need to gather SERVER COLLATION for all the instance in a network using a job and the save them
to a table of a central database.
I understand I need to do below:
1. Create a job with a TSQL which will log in to each server and then retrive the collation from SERVERPROPERTY
However, I am not sure what is the best way to achive this. Any input is highly helpful.
Also if you have a scrip which will allow me to connect to a bunch of remote servers to execute some query using TSQL (As a Job Step), could you please share the same with me?
Many thanks in advance
March 17, 2009 at 8:56 am
I think RedGate has a product that allows you to run scripts against collections of servers. Check them out, see if that will help you get this done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 9:35 am
Build a table that lists your servers, step through it and get the data. Looks like that's all you're going to be able to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 9:35 am
Utsab Chattopadhyay (3/17/2009)
Thanks. But we do not have option to intall new application on our box.So I need to take an approach which will use native technology only and will not need any third party tool (Actually not involve licence cost 🙂 )
The Redgate tool is priced very well, but if you cant stretch the budget then I guess you will be faced with setting up some Linked servers and writing some T-SQL to apply the changes to each server.
Or you could write some osql scripts and run these as a batch job form the command prompt..
March 17, 2009 at 9:51 am
Thanks. But the major concern is how can I run a query on a remote server from the local server?
For example, I want to execute SERVERPROPERTY('collation') on a remote server and then grab that value in a variable. I know the server name (they are in a table) but not sure how to connect to that server/instance and then execute the query locally....
Any idea?
March 17, 2009 at 10:05 am
Are you running in Windows, SQL server or Mixed mode for authentication?
March 17, 2009 at 10:15 am
If they are on the same or trusted domains create a domain log on for the agent of the server that is going to collect the data from the remote servers. This login will only need access to the data you want to collect and a function to collect the data.
My thought would be:
1. Create SSIS package starts gets a list of databases that need to have data collected.
2. It goes out to a server and runs a sql statement to collect the data into a recordset
3. The record set is then written back to the administration database and collated when the package finishes running
Hope that helps
March 17, 2009 at 10:18 am
I am currently doing something similar but getting more then just collation and I have achieved by:
- Choosing one server to be repository
- Creating a linked server connection on that server to all other servers on network
- Create a server list table in database on that server
- Step through each row in server table (as suggested above by GSquared) to get required info.
March 17, 2009 at 10:24 am
One thing you may want to do is check the Collation of every database on every server as some databases may differ from the server collation?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply