August 26, 2011 at 2:52 pm
I want to combine data for 4 databses with three queries:
Heres what I have so far:
Const sTextFilePath = "D:\Software\OBOLX Invoicing\Joes OBOLX queries and scripts\ComputerList.txt"
Const sResultFilePath = "D:\Software\OBOLX Invoicing\Joes OBOLX queries and scripts\foldersize.txt"
Const ForReading = 1
Const ForWriting = 2
dim objFSO,objFolder,foldersize, txt
set objFSO=createobject("scripting.filesystemobject")
Set objTextFile = objFSO.OpenTextFile(sTextFilePath, ForReading)
Set objResultFile = objFSO.OpenTextFile(sResultFilePath, ForWriting, True)
objResultFile.WriteLine ""
While Not objTextFile.AtEndOfStream
strComputer = objTextFile.ReadLine()
If objFSO.FolderExists(strComputer) Then
Set objFolder = objFSO.GetFolder(strComputer)
'WScript.Echo "Folder found on " & strComputer & vbtab & Left(objFolder.Size/1024/1024/1024, 5) & "GB"
objResultFile.WriteLine "Size on Disk as reported by the Operating System in GB "
objResultFile.WriteLine strComputer & vbtab & left(objFolder.Size/1024/1024/1024, 5) & "GB"
Else
WScript.Echo "Folder not found on " & strComputer
End If
Wend
objTextFile.Close
objResultFile.Close
Here's my computerlist.txt for reading:
\\wvsjnas01\DSData\Massco
\\wvsjnas01\DSData\RmSupply
\\wvsjnas01\DSData\Shepley
\\wvsjnas01\DSData\Starbucks
This works great, but I want to add two queries for all four databases;
This one;
use dbname
set nocount on
SELECT CAST((ISNULL(SUM(CAST(hsi.itemdatapage.filesize AS Numeric(15,4))),0)/1024/1024/1024) AS NUMERIC(15,6)) as Storage
FROM hsi.itemdata, hsi.itemdatapage, hsi.doctype
WHERE hsi.itemdata.itemnum = hsi.itemdatapage.itemnum
AND hsi.doctype.itemtypenum = hsi.itemdata.itemtypenum
--AND hsi.doctype.itemtypenum > 100--exclude system docs
--AND hsi.doctype.itemtypename NOT LIKE 'zzz%'
--uncomment the line below for a date range
and datestored between '7/1/2011 12:00 am' and '7/30/2011 11:59:59 pm'
AND
use dbname
set nocount on
--this is the one we have always used in the invoicing. it includes everything including system docs.
--in MB
Select ISNULL(SUM(CAST(filesize AS BIGINT))/1024/1024/1024.0,0)
FROM hsi.itemdatapage
--WHERE itempagenum = 0
Both the queries work great when they a run against their respective databases.
This is mostly a looping question, I know.
I also would like to strip out the Database name from the UNC and use it as a variable, i.e., \\wvsjnas01\DSData\Starbucks == just Starbucks so I can plug it in to the USE command.
I've got all the data massaged right and I've spent a lot of time on this, any ideas?
August 29, 2011 at 9:25 am
Something like
declare @UNC varchar(50) = '\\wvsjnas01\DSData\Starbucks'
select right(@UNC, CHARINDEX('\', reverse(@UNC), 0) - 1)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 1:10 pm
That looks great, but will in work in a VBScript?
August 29, 2011 at 1:14 pm
Well oddly enough since this is a sql forum I posted sample code for sql. The same concept will work in VBScript. Just look up vbscript string functions, then look at the examples.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 1:18 pm
Sorry, new to switching back and forth. I kind of own this bit since the other guy quit. It looks great. I knew there was a char function that could do it, thanks
August 29, 2011 at 2:04 pm
It works sick in Query Analyzer, thanks...
August 29, 2011 at 2:07 pm
You're welcome. Thanks for letting us know you figured out a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 2:10 pm
How do I pass @UNC to another query?
use @UNC
set nocount on
SELECT CAST((ISNULL(SUM(CAST(hsi.itemdatapage.filesize AS Numeric(15,4))),0)/1024/1024/1024) AS NUMERIC(15,6)) as Storage
FROM hsi.itemdata, hsi.itemdatapage, hsi.doctype
WHERE hsi.itemdata.itemnum = hsi.itemdatapage.itemnum
AND hsi.doctype.itemtypenum = hsi.itemdata.itemtypenum
--AND hsi.doctype.itemtypenum > 100 --exclude system docs
--AND hsi.doctype.itemtypename NOT LIKE 'zzz%'
--uncomment the line below for a date range
and datestored between '7/1/2011 12:00 am' and '7/30/2011 11:59:59 pm'
September 1, 2011 at 3:17 pm
jdowling 65301 (8/29/2011)
How do I pass @UNC to another query?use @UNC
set nocount on
SELECT CAST((ISNULL(SUM(CAST(hsi.itemdatapage.filesize AS Numeric(15,4))),0)/1024/1024/1024) AS NUMERIC(15,6)) as Storage
FROM hsi.itemdata, hsi.itemdatapage, hsi.doctype
WHERE hsi.itemdata.itemnum = hsi.itemdatapage.itemnum
AND hsi.doctype.itemtypenum = hsi.itemdata.itemtypenum
--AND hsi.doctype.itemtypenum > 100 --exclude system docs
--AND hsi.doctype.itemtypename NOT LIKE 'zzz%'
--uncomment the line below for a date range
and datestored between '7/1/2011 12:00 am' and '7/30/2011 11:59:59 pm'
I don't believe the USE Statement takes parameters.
Are these statements simply run from ssms or are they part of a Stored Procedure?
September 1, 2011 at 3:21 pm
That is correct. You would have to use dynamic sql for that. Make sure that your use statement AND the query(ies) you want to run are all in the same exec statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply