xp_cmdshell and retrieve output from files (.vbs)
-
Hi,
in the store proc xp_cmdshell to run a vbs.script which count number of files in a folder.
Then I would like to retrieve the output file. If I run the file from cmd window the number of file are output, also in query analyzer after the store proc is completed in grid pane the output is show........
the T-SQL is:
SET @ScriptPath = @ltaSystemFilesPath + '\CountFiles.vbs ' + @ExportedFilesPath + '\' + @FolderDate
SET @CMDWSHScript = 'EXEC @Ret = master..xp_cmdshell ''CScript //NOLOGO ' + @ScriptPath + ''''
EXEC sp_executesql @CMDWSHScript, N' @Ret int output', @Ret OUTPUT
but Can I retrieve this data from T-SQL, the only data retrieved is @Ret that is 1 in case of error, but no number files as output.
the CountFiles.vbs file is:
--------------------
strInput = Wscript.Arguments.Item(0)
Set WSHShell = WScript.CreateObject("WScript.Shell")
Set objFileSystem = Wscript.CreateObject("Scripting.FileSystemObject")
Set objFolder = objFileSystem.GetFolder(strInput)
Set colFiles = objFolder.Files
for each objFile in objFolder.files
iCounter = iCounter+1
Next
wscript.stdout.write(iCounter)
---------
-
Create Table #tmp (theCount Integer)
Insert Into #tmp
Exec @CMDWSHScript
Then you can do whatever you want with the data in the temp table.
-
First of all I have no clue how to solve that problem...
But the folder should have oFolder.Files.Count property so you don't need the loop.
-
Thank for your suggestion
-
You just don't need to write a VB script to count the number of files in a directory from T-SQL. In fact, with a little clever CMD line code in T-SQL, you don't need VBS at all for the things you are trying to do... consider the following (the comments explain almost everything)...
--===== If the Command line output table exists, drop it
IF OBJECT_ID('TempDB..#CmdOutput') IS NOT NULL
DROP TABLE #CmdOutput
--===== Create the Command line output table
CREATE TABLE #CmdOutput
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
OutputLine NVARCHAR(4000)
)
--===== Get just the file names from the current or indicated
-- directory and store in the Command line output table
-- in order by name and extension.
INSERT INTO #CmdOutput (OutputLine)
EXEC Master.dbo.xp_CmdShell 'DIR C:\Temp /b /one'
--===== Count the number of files. Note that there will always
-- be at least one null which can be ignored.
SELECT COUNT(*)
FROM #CmdOutput
WHERE OutputLine IS NOT NULL
--===== This, of course, will list the file names in the correct
-- order. Because we added an identity column, you could
-- write procs to "step through" the file names for whatever
-- process you need to do so...
SELECT *
FROM #CmdOutput
ORDER BY RowNum
Ok, why NVARCHAR ? Because Books Online says that if you just use VARCHAR, the output lines will truncate at 255 characters but NVARCHAR will allow up to 4000 just fine. It's the only non-language dependent place where I actually use NVARCHAR.
Lookup "Command-line reference A-Z" in WINDOWS help for all the commands that you can execute with xp_CmdShell. If you learn all the switches for DIR and other commands like COPY, MOVE, etc, you can actually write extreme import routines (BCP or BULK INSERT may also come into play) that will also archive (MOVE) files from a "new file" area to a "processed" area. You may even rename files with .prc and .err added extensions to show the status of files... all without a lick of VBS. Just gotta learn the commands from "the old days" that most have forgotten.
Also notice that some of them can be used to wipe-out a hard drive... might wanna be real careful about who you give access to xp_CmdShell... It defaults to needing SA rights in SQL Server 2000 and you'll need to create a proxy to use it for non-SA blessed logins.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My mistake... I misread BOL... the NVARCHAR(4000) is for the command line INPUT... you can't get more than 255 characters in the OUPUT... so, the following will work just fine...
--===== If the Command line output table exists, drop it
IF OBJECT_ID('TempDB..#CmdOutput') IS NOT NULL
DROP TABLE #CmdOutput
--===== Create the Command line output table
CREATE TABLE #CmdOutput
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
OutputLine VARCHAR(255)
)
--===== Get just the file names from the current or indicated
-- directory and store in the Command line output table
-- in order by name and extension.
INSERT INTO #CmdOutput (OutputLine)
EXEC Master.dbo.xp_CmdShell 'DIR C:\Temp /b /one'
--===== Count the number of files. Note that there will always
-- be at least one null which can be ignored.
SELECT COUNT(*)
FROM #CmdOutput
WHERE OutputLine IS NOT NULL
--===== This, of course, will list the file names in the correct
-- order. Because we added an identity column, you could
-- write procs to "step through" the file names for whatever
-- process you need to do so...
SELECT *
FROM #CmdOutput
ORDER BY RowNum
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply