April 2, 2008 at 1:26 pm
Good day -
I am trying to run an SSIS package that goes thru a pre-populated list of servers, connect, and get the domain that server happens to live on. I had found something in TSQL that seemed to work, but required OLE Automation, and that is not on by default. I do not want to change any settings to get this, but would consider it.
So, I started looking at other options. A coworker told me to use a script task to use a WMI task and get the info. At this point, I have gone to an area I do not know a lot about. I have found some scripts out there on the net that appear to get the domain, however, a simple cut and paste later, I have a bunch of squiggly lines, and it looks that if I ran this, it only displays the info, and not insert it into any sort of table.
The questions I have are:
What would be a script that would get this info, including any "imports" and references?
What would be the process to insert the domain info into a table?
Has anyone done this?
Any and all help is highly appreciated.
-- Cory
April 2, 2008 at 2:06 pm
Ok, I hate to pick like this, but I have to ask.
Are your servers moving around onto different domains so often that you need an automated process to keep track of them? I have never seen a "roaming server" environment.
It seems to me that if you can make a list of servers, it would be easy enough to look a them all once and type in the domain they happen to be part of.
In addition to that, you can look at your domain controller and see if the server is part of the domain so you don't even have to connect to each server. Assuming you don't have more than 3 or 4 domains, this seems quite feasible.
April 2, 2008 at 2:14 pm
Michael Earl (4/2/2008)
Ok, I hate to pick like this, but I have to ask.Are your servers moving around onto different domains so often that you need an automated process to keep track of them? I have never seen a "roaming server" environment.
It seems to me that if you can make a list of servers, it would be easy enough to look a them all once and type in the domain they happen to be part of.
In addition to that, you can look at your domain controller and see if the server is part of the domain so you don't even have to connect to each server. Assuming you don't have more than 3 or 4 domains, this seems quite feasible.
Very good questions. The process is meant to be fully automated - the predefined list is populated at run time, and we are in the process of moving servers out of an old domain into the new. I have servers that are in a subnet that could be one of a set of domains. I, at first, assumed a subnet meant a particular domain, but that was not correct.
The list of servers is done by running "OSQL /Lc" on each subnet, and then union'ed all together to "master list". From this list, I need an automated way to get the domain each server resides in. This is an extension to this this link (an article by David Bird that he got from Rodney Landrum):
http://www.sqlservercentral.com/articles/Integration+Services/61774/
-- Cory
April 2, 2008 at 3:50 pm
I think your colleague is onto something. WMI is designed for this kind of stuff (remotely querying and possibly modifying settings, using WQL). Of course - I have enough experience at WMI to look over someone else's shoulder who has done queries like the one you're after, but - it's about the only thing with the ability to ask a server what domain it's attached to.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 6:55 pm
Matt Miller (4/2/2008)
I think your colleague is onto something. WMI is designed for this kind of stuff (remotely querying and possibly modifying settings, using WQL). Of course - I have enough experience at WMI to look over someone else's shoulder who has done queries like the one you're after, but - it's about the only thing with the ability to ask a server what domain it's attached to.
I have the following TSQL that will get the domain, however, it uses OLE automation, and when I put this in the SQL Task, it does not return any results - I am not really sure why. That may be another discussion. Anyway, here is the code, if anyone is interested:
--== Allow the query of advanced options
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
--== Create temp table to store run_value
CREATE TABLE #ole_auto
(
NAME NVARCHAR(35),
minimum INT,
maximum INT,
config_value INT,
run_value INT
)
--== populate temp table
INSERT INTO #Ole_auto
EXEC sp_configure 'Ole Automation Procedures';
--== populate variable
DECLARE @@Run_Value INT
DECLARE @SQL NVARCHAR(255)
SET @@Run_Value = (SELECT run_value FROM #ole_auto)
SET @SQL = 'EXEC sp_configure ''Ole Automation Procedures'', 1;'
--== If not turned on, turn it on
IF @@Run_Value <> 1
BEGIN
EXEC sp_executesql @SQL
EXEC sp_executesql N'Reconfigure'
END
--== Meat and potatoes
DECLARE @SysInfo INT
DECLARE @DomainName VARCHAR(128)
-- Create a Pkg Object
EXEC /*@hr = */sp_OACreate 'ADSystemInfo', @SysInfo OUTPUT
--IF @hr <> 0 RETURN 1
EXEC /*@hr = */sp_OAGetProperty @SysInfo, 'DomainShortName', @DomainName OUTPUT
--== Set the run value back if not already enabled
SET @SQL = 'EXEC sp_configure ''Ole Automation Procedures'', ' + CAST(@@Run_Value AS NCHAR(1)) + ';'
IF @@Run_Value <> 1
BEGIN
EXEC sp_executesql @SQL
EXEC sp_executesql N'Reconfigure'
END
--== Clean up
DROP TABLE [#ole_auto]
SELECT CONVERT(char(100), SERVERPROPERTY('ServerName')) AS Servername, @DomainName AS DomainName
-- Cory
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply