Getting the correct totals

I am trying to create a report and get a specific total, but it is not working correctly. I have a group of workers and I am able to count how many Work Orders they have worked on in a given month, however when I try to get a total of how many they completed, I run into a problem because it is counting every date that they worked as 1 instead of counting the # of work orders. Someone mentioned using a Global variable, but I am unsure how to use this.Try a running total, or if your count is in your details area, you should be able to right click it and insert a summary (group level if desired and report level)

getting sum group by page number

hi all,
i got a table, how can i get the sum of total ONLY for each page?(not sum of
the whole table)
thanks in advancegot the answer from the previous post...
please refer to thread "sum by page" if you guys have the same problem
"Jasonymk" wrote:
> hi all,
> i got a table, how can i get the sum of total ONLY for each page?(not sum of
> the whole table)
> thanks in advance

Getting SQL result in a variable

Hi there

I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.

how do we achieve that?

thanks and regards

Rahul Kuamr

Hello Rahul,

You can use a row count transformation just before the destination component in your dataflow, you a have to declare a variable of type int32 and assign this variable in the row count transformation.

Regards,

Raju

|||

on the same line,another query

If we wish to get result of some SQL query say select name from emp where id='234'

thanks and regards

Rahul kumar

|||

Hi,

To get the result of a query into a variable first you have to declare a variable of the same type which is returned by the query, and use an execute sql task in the control flow, Double click the execute sql task and click on the general tab and make the result set property to "Single Row" and write the query in this way: "Select name as empname from emp where empid = '234'. " Now you can assign the name alias "empname" used in the query to a result set. To do that click on the result set which is available in the execute sql task and give the name of the result set as "empname" and assign to a variable which is of the same type.

Note: property to "Single Row" only works when the query is returning a single row. If the query is returning mutiple rows, you have to declare a variable of the type object and select the property of the result set as "Full result set". and assign the result name to the variable which has been declared as object.

Regards,

Raju

|||

Thanks a lot buddy.

It works!!

Regards

Rahul Kumar

|||

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

Thanks for any advice you can give me, maybe I do have use an Object Variable.

Chera

|||

cboom wrote:

Hello, found this post and am hoping that you can help with assigning a variable with an Execute SQL Task. My task runs, but the variable doesn't get a value, stays blank.

I'm trying to retrieve a string value from a SQL table called tblSys_Config. There's only one row in the table. The column I want is called is an nvarchar(3) field called Config_Code.

I've defined a user variable called TestVar with type as string.

I've set up my Execute SQL Task with a SingleRow result set, and the SQL Statement is "select Config_Code As ConfigCode from tblSys_Config".

The ResultSet for the SQL Task has Resultname ConfigCode (same in "As ConfigCode" in SQL statement), with variable name user::TestVar.

So far, so good.

cboom wrote:

When I execute the task, it runs but just doesn't update the variable, it stays blank. Maybe I'm not looking for the value correctly, just right clicking in design space in VS and choosing Variables to see the value of TestVar, always blank.

I think the problem is the way you are looking for the value. You may want to set a breakpoint and execute the whole package; then at the break point time, examinate the varibale by looking at the output window.

cboom wrote:

I did try setting the result set to Full Result Set, variable to Object, and the Result Name to 0, and got a very helpful value of System.Object. Maybe that's what I have to work with, but I just have a single value to retrieve that should work with SingleRow. I've also tried different naming conventions and making sure Result Name is same case as the column name.

You don't need to use a Object type variable...

|||

Thanks Rafael, you were right, just needed to find a new place to check the value of the variable, so played more with variables in Script Tasks and Componenets and got quite a bit more figured out. Had to learn about specifying the ReadOnlyVariables and ReadWriteVariables for the scripts.

Thanks, appreciate you pointing me in the right direction.

Chera

Getting RecordCount with TableDirect

Hi, I would like to know if there's any method to get the total number of records in a table without using query. I have the following codes.

Dim conn As SqlServerCe.SqlCeConnection
Dim cmd As SqlServerCe.SqlCeCommand
Dim rdr As SqlServerCe.SqlCeDataReader

conn = New SqlServerCe.SqlCeConnection("Data source=\My Documents\test.sdf")
conn.Open()

cmd = New SqlServerCe.SqlCeCommand("Staff_Table", conn)
cmd.CommandType = CommandType.TableDirect
cmd.IndexName = "Id_Ind"

rdr = cmd.ExecuteReader
rdr.Seek(SqlServerCe.DbSeekOptions.FirstEqual, int_seek_n)

If rdr.Read() Then
strResult = rdr.GetString(1)
End If

rdr.Close()
conn.Close()

I did quite a bit of searching and got no results. Thanks.No, there's no shortcut to getting the count of rows in a table. I recommend doing a SELECT COUNT(*) and use ExecuteScalar to get the best performance on this query.

-Darren Shaffer|||Thanks Darren. Looks like I have no other choice. :)

Getting Memory usage through T-SQL

I'm trying to get the machine free ram value using either T-SQL or
SQL-DMO.
I found .Registry.PhysicalMemory which takes care of the total ram but
I still need eithrt the free or the used.

Any ideas?
Thanks!"Adi Sapir" <adisapir@.gmail.com> wrote in message news:<ce7ucv$rg7@.odbk17.prod.google.com>...
> I'm trying to get the machine free ram value using either T-SQL or
> SQL-DMO.
> I found .Registry.PhysicalMemory which takes care of the total ram but
> I still need eithrt the free or the used.
> Any ideas?
> Thanks!

You should probably look at WMI for this, as it's intended for
retrieving this kind of system-level information:

http://groups.google.com/groups?hl=...news.jippii.net

Simon

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:
>

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:
> > 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
> > ----
> >
> >
>|||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=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:
> > 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.
>|||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:
> > 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.
>|||OK I got the expression thing worked out...and at run time it does create the
following connectstring
Servername=\\FinServer432;Namespace=\root\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=\root\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:
> > 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.
>|||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:
> > 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.
>|||> 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...
> 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:
>> > 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.

Getting estimated total hits of fulltextsearch

Hello,

i am making a Fulltextsearch on MS SQL Server 2005 (indexed, with "Contains").

Because of performance reasons i am only showing the first 200 rows mssql finds ("select top 200...:").

Is there any possibility to get the estimated totalnumber of all rows? i have heard something that is possible to get this in mssql-server. The server then estimates how many rows with that searchword could be in the whole database. google i.e. makes the same thing...

is that true? what do i have to do to get this?

greetings and thx

cpt.oneeye

You can use such statement to get the row number of a table:

sp_spaceused 'myTable'