getting the latest datetime

Hi

I have a DateTime column in my table, and I want to select the first couple of rows whose DateTime values are closest to the currect serve rdatetime. Is there a function like Max() that I can use?

Also, quesion about timestamp. If I create a column of type timestamp, is the value automatically entered by the database on every insert entry to the table?
I have had problems using timestamp so I switched to using datetime now.

Help appreciated!For the first part of your question, I would use the DATEDIFF() function to find the difference between the current date (GETDATE()) and the date value stored in your column, sorted in DESC order, taking the TOP 1 or 2 (however many you want).

The Timestamp data type is actually a unique rowversion containing binary/varbinary data, and its value only has meaning to SQL Server itself. SQL Server updates the value in this column upon every INSERT, UPDATE, and DELETE. You could make use of this Timestamp value for concurrency control, but it does not contain datetime data -- it contains binary/varbinary data.

Terri

getting the identity...

Hi
I want to be able to return the identity of an insert from a linked server?
However when the code below runs I get returned a null value
...
insert into [presentationlap].scimitar.dbo.tblCoredata (lDataTypeID, sData,
lParentID)
select TOP 1 lDataTypeID, sData, lParentID from dbo.tblCoredata where
lcoreID = @.lcoreID
select scope_identity
...
BOL states
"The scope of the @.@.IDENTITY function is the local server on which it is
executed. This function cannot be applied to remote or linked servers. To
obtain an identity value on a different server, execute a stored procedure
on that remote or linked server and have that stored procedure, which is
executing in the context of the remote or linked server, gather the identity
value and return it to the calling connection on the local server."
However I have tried creating a SP on the linked server which just returns
the scope_identity but this also returns null
Any ideas?
Thanks
RippoCan you please post your table as well.
"Facts are stupid things."
Ronald Reagan
"Richard Wilde" wrote:

> Hi
> I want to be able to return the identity of an insert from a linked server
?
> However when the code below runs I get returned a null value
> ...
> insert into [presentationlap].scimitar.dbo.tblCoredata (lDataTypeID, sData,
> lParentID)
> select TOP 1 lDataTypeID, sData, lParentID from dbo.tblCoredata where
> lcoreID = @.lcoreID
> select scope_identity
> ...
> BOL states
> "The scope of the @.@.IDENTITY function is the local server on which it is
> executed. This function cannot be applied to remote or linked servers. To
> obtain an identity value on a different server, execute a stored procedure
> on that remote or linked server and have that stored procedure, which is
> executing in the context of the remote or linked server, gather the identi
ty
> value and return it to the calling connection on the local server."
> However I have tried creating a SP on the linked server which just returns
> the scope_identity but this also returns null
> Any ideas?
> Thanks
> Rippo
>
>|||ok no problem
CREATE TABLE [dbo].[tblCoreData] (
[lCoreID] [int] IDENTITY (1, 1) NOT NULL ,
[lDataTypeID] [smallint] NOT NULL ,
[sData] [nvarchar] (3900) COLLATE Latin1_General_CI_AS NULL ,
[lParentID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCoreData] WITH NOCHECK ADD
CONSTRAINT [PK_tblCoreData] PRIMARY KEY CLUSTERED
(
[lCoreID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_tblCoreData] ON [dbo].[tblCoreData]([lDataTypeID]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tblCoreData_1] ON [dbo].[tblCoreData]([lParentID]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[tblCoreData] ADD
CONSTRAINT [FK_tblCoreData_tblCoreData] FOREIGN KEY
(
[lParentID]
) REFERENCES [dbo].[tblCoreData] (
[lCoreID]
),
CONSTRAINT [FK_tblDeviceData_tblDeviceDataTypes] FOREIGN KEY
(
[lDataTypeID]
) REFERENCES [dbo].[tblDataTypes] (
[lDataTypeID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO|||Richard,
Create a stored procedure with an output parameter in the linked server and
invoke it from your server.
AMB
"Richard Wilde" wrote:

> Hi
> I want to be able to return the identity of an insert from a linked server
?
> However when the code below runs I get returned a null value
> ...
> insert into [presentationlap].scimitar.dbo.tblCoredata (lDataTypeID, sData,
> lParentID)
> select TOP 1 lDataTypeID, sData, lParentID from dbo.tblCoredata where
> lcoreID = @.lcoreID
> select scope_identity
> ...
> BOL states
> "The scope of the @.@.IDENTITY function is the local server on which it is
> executed. This function cannot be applied to remote or linked servers. To
> obtain an identity value on a different server, execute a stored procedure
> on that remote or linked server and have that stored procedure, which is
> executing in the context of the remote or linked server, gather the identi
ty
> value and return it to the calling connection on the local server."
> However I have tried creating a SP on the linked server which just returns
> the scope_identity but this also returns null
> Any ideas?
> Thanks
> Rippo
>
>|||I have still not able to return the identity of an insert from a linked
server.
I have a table on the linked server
CREATE TABLE [dbo].[test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[test] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
and have this stored procedure on the linked server
CREATE PROCEDURE dbo.uspReturnScope_Identity
AS
select scope_identity()
GO
on another server i have created a link to the above server and have
tried to run the following code in QA.
insert into [presentationlap].synctest.dbo.test (test) values('A test')
exec [presentationlap].synctest.dbo.uspReturnScope_Identity
However the identity is never returned. Can anyone help me trace the
problem further. Thank you
Richard|||On 22 Mar 2005 02:31:45 -0800, Rippo wrote:

>I have still not able to return the identity of an insert from a linked
>server.
(snip)
>and have this stored procedure on the linked server
>CREATE PROCEDURE dbo.uspReturnScope_Identity
>AS
>select scope_identity()
>GO
>on another server i have created a link to the above server and have
>tried to run the following code in QA.
>insert into [presentationlap].synctest.dbo.test (test) values('A test')
>exec [presentationlap].synctest.dbo.uspReturnScope_Identity
>However the identity is never returned. Can anyone help me trace the
>problem further. Thank you
Hi Richard,
I don't know very much about linked servers, but based on what you
write, I think that the remote call to the stored procedure runs in it's
own, seperate scope. That would explain why SCOPE_IDENTITY won't work.
Can't you change the logic to encapsulate the insert in a stored
procedure on a linked server, and have that procedure return the
scope_identity after performing the insert?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Identity values do not span its scope across servers so you cannot get
identity values back using queries with 4-part naming conventions. One
workaround in such cases is to use sp_ExecuteSQL like:
EXEC presentationlap.synctest.dbo.sp_ExecuteSQL N'
INSERT test ( test ) VALUES ( ''A test'' )
SELECT SCOPE_IDENTITY()'
Anith|||Anith
Thank you for the workaround. This will be perfect for what I need. I
misinterpited what BOL stated!
Thanks again
Richardsql

Getting relationship data

Hi
I am trying to create a query or set of querys, which will allow me to retrieve the relationship data of a database. In sql Server 2000 the user can create diagrams which show these relationships, what i want to be able to do is get this data, but i am h
aving trouble finding a starting place. Could anyone point me in the right direction?
Thanks in advance
You can start by querying INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view.
If you are interested in viewing the reference constraints that exist in
your database, you can do:
SELECT o1.name AS "tablename",
OBJECT_NAME( f1.constid ) AS "constraintname",
OBJECT_NAME( f1.rkeyid ) as referencedtable
FROM sysobjects o1
LEFT OUTER JOIN sysconstraints c1
ON o1.id = c1.id
AND c1.status &3 = 3
LEFT OUTER JOIN sysforeignkeys f1
ON c1.constid = f1.constid
WHERE o1.type = 'u'
ORDER BY "referencedtable", "tablename" ;
Anith

Getting Fixed Drives Total Space and Free Space

Hello Hi
I have a procedure I use sp_diskspace which uses OLE Automation to get a
list of total disk space and free space on all fixed drives on a server.
This works fine on our SQL 7 and SQL 2000 servers but on our SQL 2005
servers, we have OLE Automation disabled as a security standard, hence this
script doesn't work on SQL 2005 servers.
I am wondering, is there another way to get this date? I am aware of
xp_fixeddrives, but this displays only the free space on drives, not the
total space.
Below is the script using OLE Automation that I use for SQL 7 and 2000.
Any help is appreciated.
Thanks.
Cheers.
Kunal.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create PROCEDURE sp_diskspace
/*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20) DECLARE @.MB Numeric ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @.hr=sp_OACreate
'Scripting.FileSystemObject',@.fso OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
@.fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @.drive
WHILE @.@.FETCH_STATUS=0
BEGIN
EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso EXEC @.hr =
sp_OAGetProperty
@.odrive,'TotalSize', @.TotalSize OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
@.odrive UPDATE #drives SET TotalSize=@.TotalSize/@.MB WHERE
drive=@.drive FETCH NEXT FROM dcur INTO @.drive
End
Close dcur
DEALLOCATE dcur
EXEC @.hr=sp_OADestroy @.fso IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
SELECT
drive, TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
ORDER BY drive DROP TABLE #drives Return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
sp_diskspace
go
drop proc diskspace
go
> I am wondering, is there another way to get this date?
You might consider gathering this info directly using an ActiveX script in a
scheduled job rather than Transact-SQL. Another option is a CLR proc or
function.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:CE948DD9-585E-458E-B32E-A58EA8E724CA@.microsoft.com...
> Hello Hi
> I have a procedure I use sp_diskspace which uses OLE Automation to get a
> list of total disk space and free space on all fixed drives on a server.
> This works fine on our SQL 7 and SQL 2000 servers but on our SQL 2005
> servers, we have OLE Automation disabled as a security standard, hence
> this
> script doesn't work on SQL 2005 servers.
> I am wondering, is there another way to get this date? I am aware of
> xp_fixeddrives, but this displays only the free space on drives, not the
> total space.
> Below is the script using OLE Automation that I use for SQL 7 and 2000.
> Any help is appreciated.
> Thanks.
> Cheers.
> Kunal.
> ----
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> create PROCEDURE sp_diskspace
> /*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
> AS
> SET NOCOUNT ON
> DECLARE @.hr int
> DECLARE @.fso int
> DECLARE @.drive char(1)
> DECLARE @.odrive int
> DECLARE @.TotalSize varchar(20) DECLARE @.MB Numeric ; SET @.MB = 1048576
> CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
> TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
> master.dbo.xp_fixeddrives EXEC @.hr=sp_OACreate
> 'Scripting.FileSystemObject',@.fso OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
> @.fso
> DECLARE dcur CURSOR LOCAL FAST_FORWARD
> FOR SELECT drive from #drives ORDER by drive
> OPEN dcur FETCH NEXT FROM dcur INTO @.drive
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
> IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso EXEC @.hr =
> sp_OAGetProperty
> @.odrive,'TotalSize', @.TotalSize OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
> @.odrive UPDATE #drives SET TotalSize=@.TotalSize/@.MB WHERE
> drive=@.drive FETCH NEXT FROM dcur INTO @.drive
> End
> Close dcur
> DEALLOCATE dcur
> EXEC @.hr=sp_OADestroy @.fso IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
> SELECT
> drive, TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',
> CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
> ORDER BY drive DROP TABLE #drives Return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> sp_diskspace
> go
> drop proc diskspace
> go
> ----
>
|||Also you could use an SSIS package to capture this via WMI...
"Dan Guzman" wrote:

> You might consider gathering this info directly using an ActiveX script in a
> scheduled job rather than Transact-SQL. Another option is a CLR proc or
> function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:CE948DD9-585E-458E-B32E-A58EA8E724CA@.microsoft.com...
>
|||Dan,
I don't know ActiveX. Is there a script around for the same?
Ben,
I tried the SSIS package using WMI queries. Works well. Problem is...when
connecting to some servers for WMI query in the package I get the error:
"Failed to connect to the specified server with the following error: "Access
is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". The server
name may be invalid."
Some of these server are complete identical. I have even checked the WMI
security on these servers.
Additionally, is there a way for me to have a text file with server name,
and then run the WMI Data Reader task for each server mentioned there.
Because currently, I am manually creating individual WMI Data Reader tasks
for each server. For 100-150 server, it can get quiet tedious. Additionally,
if one of the servers is not connectable the whole package would fail.
Thanks a lots. You guys have been really helpful. I would really appreciate
it if you could help me sort out these WMI problems for me.
Thanks.
Kunal.
|||> I don't know ActiveX. Is there a script around for the same?
Here's a simple VBScript example that runs a WMI query and displays the
results.
Option Explicit
Dim SQL, Results, ServerName
Dim oWin32_LogicalDisks, oWin32_LogicalDisk
'list local disks on remote server
SQL = "SELECT Caption, FreeSpace, Size " & _
" FROM Win32_LogicalDisk WHERE DriveType = 3"
Set oWin32_LogicalDisks = _
GetObject("winmgmts:{impersonationLevel=impersonat e}!//" & _
"MyServer" & _
"/root/cimv2").ExecQuery(SQL, , 48)
For Each oWin32_LogicalDisk In oWin32_LogicalDisks
Results = Results & _
oWin32_LogicalDisk.Caption & "," & _
oWin32_LogicalDisk.FreeSpace & "," & _
oWin32_LogicalDisk.Size &_
VbCrLf
Next
MsgBox(Results)

> Additionally, is there a way for me to have a text file with server name,
> and then run the WMI Data Reader task for each server mentioned there.
One method is to create an XML file with your server list. For example:
<ServerList>
<Server>SERVER1</Server>
<Server>SERVER2</Server>
<Server>SERVER3</Server>
</ServerList>
Create a file connection for the server list xml file.
Create a Foreach Loop Container with the following properties:
Collection:
Enumerator: Foreach NodeList Enumerator
DocumentSourceType: FileConnection
DocumentSourceSource: server list file connection
Enumeration Type: NodeText
OuterXPathStringSourceType: Direct Input
OuterXPathString: ServerList/Server/text()
Variable Mappings:
create a user variable for the server name
Place your WMI Data Reader inside the Foreach Loop Container.
In the WMI Connection properties, specify an expression to map the
ConnectionString to the variable specified in the Foreach Loop varaible
mapping.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
> Dan,
> I don't know ActiveX. Is there a script around for the same?
> Ben,
> I tried the SSIS package using WMI queries. Works well. Problem is...when
> connecting to some servers for WMI query in the package I get the error:
> "Failed to connect to the specified server with the following error:
> "Access
> is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". The
> server
> name may be invalid."
> Some of these server are complete identical. I have even checked the WMI
> security on these servers.
> Additionally, is there a way for me to have a text file with server name,
> and then run the WMI Data Reader task for each server mentioned there.
> Because currently, I am manually creating individual WMI Data Reader tasks
> for each server. For 100-150 server, it can get quiet tedious.
> Additionally,
> if one of the servers is not connectable the whole package would fail.
> Thanks a lots. You guys have been really helpful. I would really
> appreciate
> it if you could help me sort out these WMI problems for me.
> Thanks.
> Kunal.
|||Wow. That is some cool stuff.
I'm not sure I'll be able to implement all that but I'm going to try my
best. Probably will need lots and lots of debugging and help.
Thanks a lot.
Kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonat e}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>
|||hi dan,
thanks again for ur help. but i'm trying to setup the xml parsing for wmi
connection string to pickup server names from there.
i've set up everything as u mentioned below, but stuck at the following:
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
In the WMI connection proerties i specified
Servername=\\@.[User.servername1];Namespace=\root\cimv2;UseNtAuth=True;UserName=;
But doesn't work.
Can you please help me out on how to pass the value read from xml file into
the variable and then from variable to connectstring.
i don't recall setting xml to read into the variable either.
thanks.
kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonat e}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>
|||OK I got the expression thing worked out...and at run time it does create the
following connectstring
Servername=\\FinServer432;Namespace=\root\cimv2;Us eNtAuth=True;UserName=;
But it errors out saying:
[WMI Data Reader Task] Error: An error occurred with the following error
message
: "The connection
"Servername=\\FunServer432;Namespace=\root\cimv2;U seNtAuth=True;UserName=;"
is not found. This error is thrown by Connections collection when the
specific connection element is not found. ".
I'm guessing it is looking for a connection by that name. Do I have to
create all 150 server connections first? I thought the whole point was to
avoid that.
cheers.
Kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonat e}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>
|||sorry for the msg bombing. i figured out everything. im a little slow i
apologize.
when creating a wmi connection, it does ask for values...so i enter some
dummy values and then create an expression to reflect the variable @.sn1 that
im using.
the expression field reads,
ConnectionString -
"Servername=\\\\"+@.sn1+";Namespace=\\root\\cimv2;U seNtAuth=True;UserName=;"
in the ConnectionString filed itself, there is the same value. same applied
for servername which reads \\@.sn1
but when i execute the foreachloop container, i keep getting the message
'rpc server not available'. i'm guessing its because it keeps on using "@.sn1"
as the servername. if i substitute @.sn1 with a server name, it runs 10
iteration on the same server (xml has 10 values).
what am i missing here?
cheers.
kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonat e}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>
|||> but when i execute the foreachloop container, i keep getting the message
> 'rpc server not available'. i'm guessing its because it keeps on using
> "@.sn1"
> as the servername. if i substitute @.sn1 with a server name, it runs 10
> iteration on the same server (xml has 10 values).
I was able to reproduce your problem but I haven't yet identified the cause.
As a workaround, try moving the WMI Data Reader to a separate package and
then executing from the ForEach loop with an Execute Package task. You'll
need to pass the server name from parent to child package using a child
package configuration. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:3A4C8197-C7BB-4F5B-8E0C-C41E7936006D@.microsoft.com...[vbcol=seagreen]
> sorry for the msg bombing. i figured out everything. im a little slow i
> apologize.
> when creating a wmi connection, it does ask for values...so i enter some
> dummy values and then create an expression to reflect the variable @.sn1
> that
> im using.
> the expression field reads,
> ConnectionString -
> "Servername=\\\\"+@.sn1+";Namespace=\\root\\cimv2;U seNtAuth=True;UserName=;"
> in the ConnectionString filed itself, there is the same value. same
> applied
> for servername which reads \\@.sn1
> but when i execute the foreachloop container, i keep getting the message
> 'rpc server not available'. i'm guessing its because it keeps on using
> "@.sn1"
> as the servername. if i substitute @.sn1 with a server name, it runs 10
> iteration on the same server (xml has 10 values).
> what am i missing here?
> cheers.
> kunal.
> "Dan Guzman" wrote:

Getting Fixed Drives Total Space and Free Space

Hello Hi
I have a procedure I use sp_diskspace which uses OLE Automation to get a
list of total disk space and free space on all fixed drives on a server.
This works fine on our SQL 7 and SQL 2000 servers but on our SQL 2005
servers, we have OLE Automation disabled as a security standard, hence this
script doesn't work on SQL 2005 servers.
I am wondering, is there another way to get this date? I am aware of
xp_fixeddrives, but this displays only the free space on drives, not the
total space.
Below is the script using OLE Automation that I use for SQL 7 and 2000.
Any help is appreciated.
Thanks.
Cheers.
Kunal.
----
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create PROCEDURE sp_diskspace
/*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20) DECLARE @.MB Numeric ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @.hr=sp_OACreate
'Scripting.FileSystemObject',@.fso OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
@.fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @.drive
WHILE @.@.FETCH_STATUS=0
BEGIN
EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso EXEC @.hr =
sp_OAGetProperty
@.odrive,'TotalSize', @.TotalSize OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
@.odrive UPDATE #drives SET TotalSize=@.TotalSize/@.MB WHERE
drive=@.drive FETCH NEXT FROM dcur INTO @.drive
End
Close dcur
DEALLOCATE dcur
EXEC @.hr=sp_OADestroy @.fso IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
SELECT
drive, TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
ORDER BY drive DROP TABLE #drives Return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
sp_diskspace
go
drop proc diskspace
go
----> I am wondering, is there another way to get this date?
You might consider gathering this info directly using an ActiveX script in a
scheduled job rather than Transact-SQL. Another option is a CLR proc or
function.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:CE948DD9-585E-458E-B32E-A58EA8E724CA@.microsoft.com...
> Hello Hi
> I have a procedure I use sp_diskspace which uses OLE Automation to get a
> list of total disk space and free space on all fixed drives on a server.
> This works fine on our SQL 7 and SQL 2000 servers but on our SQL 2005
> servers, we have OLE Automation disabled as a security standard, hence
> this
> script doesn't work on SQL 2005 servers.
> I am wondering, is there another way to get this date? I am aware of
> xp_fixeddrives, but this displays only the free space on drives, not the
> total space.
> Below is the script using OLE Automation that I use for SQL 7 and 2000.
> Any help is appreciated.
> Thanks.
> Cheers.
> Kunal.
> ----
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> create PROCEDURE sp_diskspace
> /*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
> AS
> SET NOCOUNT ON
> DECLARE @.hr int
> DECLARE @.fso int
> DECLARE @.drive char(1)
> DECLARE @.odrive int
> DECLARE @.TotalSize varchar(20) DECLARE @.MB Numeric ; SET @.MB = 1048576
> CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
> TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
> master.dbo.xp_fixeddrives EXEC @.hr=sp_OACreate
> 'Scripting.FileSystemObject',@.fso OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
> @.fso
> DECLARE dcur CURSOR LOCAL FAST_FORWARD
> FOR SELECT drive from #drives ORDER by drive
> OPEN dcur FETCH NEXT FROM dcur INTO @.drive
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> EXEC @.hr = sp_OAMethod @.fso,'GetDrive', @.odrive OUT, @.drive
> IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso EXEC @.hr =
> sp_OAGetProperty
> @.odrive,'TotalSize', @.TotalSize OUT IF @.hr <> 0 EXEC sp_OAGetErrorInfo
> @.odrive UPDATE #drives SET TotalSize=@.TotalSize/@.MB WHERE
> drive=@.drive FETCH NEXT FROM dcur INTO @.drive
> End
> Close dcur
> DEALLOCATE dcur
> EXEC @.hr=sp_OADestroy @.fso IF @.hr <> 0 EXEC sp_OAGetErrorInfo @.fso
> SELECT
> drive, TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',
> CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
> ORDER BY drive DROP TABLE #drives Return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> sp_diskspace
> go
> drop proc diskspace
> go
> ----
>|||Also you could use an SSIS package to capture this via WMI...
"Dan Guzman" wrote:

> You might consider gathering this info directly using an ActiveX script in
a
> scheduled job rather than Transact-SQL. Another option is a CLR proc or
> function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:CE948DD9-585E-458E-B32E-A58EA8E724CA@.microsoft.com...
>|||Dan,
I don't know ActiveX. Is there a script around for the same?
Ben,
I tried the SSIS package using WMI queries. Works well. Problem is...when
connecting to some servers for WMI query in the package I get the error:
"Failed to connect to the specified server with the following error: "Access
is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". The serve
r
name may be invalid."
Some of these server are complete identical. I have even checked the WMI
security on these servers.
Additionally, is there a way for me to have a text file with server name,
and then run the WMI Data Reader task for each server mentioned there.
Because currently, I am manually creating individual WMI Data Reader tasks
for each server. For 100-150 server, it can get quiet tedious. Additionally,
if one of the servers is not connectable the whole package would fail.
Thanks a lots. You guys have been really helpful. I would really appreciate
it if you could help me sort out these WMI problems for me.
Thanks.
Kunal.|||> I don't know ActiveX. Is there a script around for the same?
Here's a simple VBScript example that runs a WMI query and displays the
results.
Option Explicit
Dim SQL, Results, ServerName
Dim oWin32_LogicalDisks, oWin32_LogicalDisk
'list local disks on remote server
SQL = "SELECT Caption, FreeSpace, Size " & _
" FROM Win32_LogicalDisk WHERE DriveType = 3"
Set oWin32_LogicalDisks = _
GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
"MyServer" & _
"/root/cimv2").ExecQuery(SQL, , 48)
For Each oWin32_LogicalDisk In oWin32_LogicalDisks
Results = Results & _
oWin32_LogicalDisk.Caption & "," & _
oWin32_LogicalDisk.FreeSpace & "," & _
oWin32_LogicalDisk.Size &_
VbCrLf
Next
MsgBox(Results)

> Additionally, is there a way for me to have a text file with server name,
> and then run the WMI Data Reader task for each server mentioned there.
One method is to create an XML file with your server list. For example:
<ServerList>
<Server>SERVER1</Server>
<Server>SERVER2</Server>
<Server>SERVER3</Server>
</ServerList>
Create a file connection for the server list xml file.
Create a Foreach Loop Container with the following properties:
Collection:
Enumerator: Foreach NodeList Enumerator
DocumentSourceType: FileConnection
DocumentSourceSource: server list file connection
Enumeration Type: NodeText
OuterXPathStringSourceType: Direct Input
OuterXPathString: ServerList/Server/text()
Variable Mappings:
create a user variable for the server name
Place your WMI Data Reader inside the Foreach Loop Container.
In the WMI Connection properties, specify an expression to map the
ConnectionString to the variable specified in the Foreach Loop varaible
mapping.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
> Dan,
> I don't know ActiveX. Is there a script around for the same?
> Ben,
> I tried the SSIS package using WMI queries. Works well. Problem is...when
> connecting to some servers for WMI query in the package I get the error:
> "Failed to connect to the specified server with the following error:
> "Access
> is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". The
> server
> name may be invalid."
> Some of these server are complete identical. I have even checked the WMI
> security on these servers.
> Additionally, is there a way for me to have a text file with server name,
> and then run the WMI Data Reader task for each server mentioned there.
> Because currently, I am manually creating individual WMI Data Reader tasks
> for each server. For 100-150 server, it can get quiet tedious.
> Additionally,
> if one of the servers is not connectable the whole package would fail.
> Thanks a lots. You guys have been really helpful. I would really
> appreciate
> it if you could help me sort out these WMI problems for me.
> Thanks.
> Kunal.|||Wow. That is some cool stuff.
I'm not sure I'll be able to implement all that but I'm going to try my
best. Probably will need lots and lots of debugging and help.
Thanks a lot.
Kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||hi dan,
thanks again for ur help. but i'm trying to setup the xml parsing for wmi
connection string to pickup server names from there.
i've set up everything as u mentioned below, but stuck at the following:
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
In the WMI connection proerties i specified
Servername=\\@.[User. servername1];Namespace=\root\cimv2;UseNt
Auth=True;Us
erName=;
But doesn't work.
Can you please help me out on how to pass the value read from xml file into
the variable and then from variable to connectstring.
i don't recall setting xml to read into the variable either.
thanks.
kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||OK I got the expression thing worked out...and at run time it does create th
e
following connectstring
Servername=\\FinServer432;Namespace=\roo
t\cimv2;UseNtAuth=True;UserName=;
But it errors out saying:
[WMI Data Reader Task] Error: An error occurred with the following error
message
: "The connection
" Servername=\\FunServer432;Namespace=\roo
t\cimv2;UseNtAuth=True;UserName=;"
is not found. This error is thrown by Connections collection when the
specific connection element is not found. ".
I'm guessing it is looking for a connection by that name. Do I have to
create all 150 server connections first? I thought the whole point was to
avoid that.
cheers.
Kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||sorry for the msg bombing. i figured out everything. im a little slow i
apologize.
when creating a wmi connection, it does ask for values...so i enter some
dummy values and then create an expression to reflect the variable @.sn1 that
im using.
the expression field reads,
ConnectionString -
"Servername=\\\\"+@.sn1+" ;Namespace=\\root\\cimv2;UseNtAuth=True;
UserName=;"
in the ConnectionString filed itself, there is the same value. same applied
for servername which reads \\@.sn1
but when i execute the foreachloop container, i keep getting the message
'rpc server not available'. i'm guessing its because it keeps on using "@.sn1
"
as the servername. if i substitute @.sn1 with a server name, it runs 10
iteration on the same server (xml has 10 values).
what am i missing here?
cheers.
kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||> but when i execute the foreachloop container, i keep getting the message
> 'rpc server not available'. i'm guessing its because it keeps on using
> "@.sn1"
> as the servername. if i substitute @.sn1 with a server name, it runs 10
> iteration on the same server (xml has 10 values).
I was able to reproduce your problem but I haven't yet identified the cause.
As a workaround, try moving the WMI Data Reader to a separate package and
then executing from the ForEach loop with an Execute Package task. You'll
need to pass the server name from parent to child package using a child
package configuration. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:3A4C8197-C7BB-4F5B-8E0C-C41E7936006D@.microsoft.com...[vbcol=seagreen]
> sorry for the msg bombing. i figured out everything. im a little slow i
> apologize.
> when creating a wmi connection, it does ask for values...so i enter some
> dummy values and then create an expression to reflect the variable @.sn1
> that
> im using.
> the expression field reads,
> ConnectionString -
> "Servername=\\\\"+@.sn1+" ;Namespace=\\root\\cimv2;UseNtAuth=True;
UserName=;
"
> in the ConnectionString filed itself, there is the same value. same
> applied
> for servername which reads \\@.sn1
> but when i execute the foreachloop container, i keep getting the message
> 'rpc server not available'. i'm guessing its because it keeps on using
> "@.sn1"
> as the servername. if i substitute @.sn1 with a server name, it runs 10
> iteration on the same server (xml has 10 values).
> what am i missing here?
> cheers.
> kunal.
> "Dan Guzman" wrote:
>