Start CMD.exe from powershell with arguments in a script block? – Stack Overflow

I want to generate some excel reports using vba scripts called from stored procedure. My team got almost the same BI solution, and previously they were using VBA script that opened excel file with macros inside.

Report generation process follows this path:

There’s SQL Stored procedure executed from weekly scheduled job, there we call another procedure which is preparing and calculating data, and then we open
script presented below to run excel.

Here’s how this script looks like:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("E:PathToExcel.xlsm")

We call this script in such way:

EXEC master..xp_cmdshell 'C:WINDOWSSysWOW64WSCRIPT.EXE Z:PathToScript.vbs'

I got some problems with WSCRIPT.EXE, it’s hanging when opening reports, and when I was looking for solution I’ve read much about not using XP_CMDSHELL. And the question is: Is there any way to avoid using XP_CMDSHELL and to not to create report generation path from scratch? I’m a bit in hurry so I don’t have time to write new procedures etc.

I’ll break this down in to several distinct parts, as each part can be done individually. (I see the similar answer, but I’m going to give a more detailed explanation here..)

First part, in order to avoid typing “CScript” (or “WScript”), you need to tell Windows how to launch a * .vbs script file. In My Windows 8 (I cannot be sure all these commands work exactly as shown here in older Windows, but the process is the same, even if you have to change the commands slightly), launch a console window (aka “command prompt”, or aka [incorrectly] “dos prompt”) and type “assoc .vbs“. That should result in a response such as:

C:WindowsSystem32>assoc .vbs
.vbs=VBSFile

Using that, you then type “ftype VBSFile“, which should result in a response of:

C:WindowsSystem32>ftype VBSFile
vbsfile="%SystemRoot%System32WScript.exe" "%1" %*

-OR-

C:WindowsSystem32>ftype VBSFile
vbsfile="%SystemRoot%System32CScript.exe" "%1" %*

If these two are already defined as above, your Windows’ is already set up to know how to launch a * .vbs file. (BTW, WScript and CScript are the same program, using different names. WScript launches the script as if it were a GUI program, and CScript launches it as if it were a command line program. See other sites and/or documentation for these details and caveats.)

If either of the commands did not respond as above (or similar responses, if the file type reported by assoc and/or the command executed as reported by ftype have different names or locations), you can enter them yourself:

C:WindowsSystem32>assoc .vbs=VBSFile

-and/or-

C:WindowsSystem32>ftype vbsfile="%SystemRoot%System32WScript.exe" "%1" %*

You can also type “help assoc” or “help ftype” for additional information on these commands, which are often handy when you want to automatically run certain programs by simply typing a filename with a specific extension. (Be careful though, as some file extensions are specially set up by Windows or programs you may have installed so they operate correctly. Always check the currently assigned values reported by assoc/ftype and save them in a text file somewhere in case you have to restore them.)

Second part, avoiding typing the file extension when typing the command from the console window.. Understanding how Windows (and the CMD.EXE program) finds commands you type is useful for this (and the next) part. When you type a command, let’s use “querty” as an example command, the system will first try to find the command in it’s internal list of commands (via settings in the Windows’ registry for the system itself, or programmed in in the case of CMD.EXE). Since there is no such command, it will then try to find the command in the current %PATH% environment variable. In older versions of DOS/Windows, CMD.EXE (and/or COMMAND.COM) would automatically add the file extensions “.bat”, “.exe”, “.com” and possibly “.cmd” to the command name you typed, unless you explicitly typed an extension (such as “querty.bat” to avoid running “querty.exe” by mistake). In more modern Windows, it will try the extensions listed in the %PATHEXT% environment variable. So all you have to do is add .vbs to %PATHEXT%. For example, here’s my %PATHEXT%:

C:WindowsSystem32>set pathext
PATHEXT=.PLX;.PLW;.PL;.BAT;.CMD;.VBS;.COM;.EXE;.VBE;.JS;.JSE;.WSF;.WSH;.MSC;.PY

Notice that the extensions MUST include the “.”, are separated by “;”, and that .VBS is listed AFTER .CMD, but BEFORE .COM. This means that if the command processor (CMD.EXE) finds more than one match, it’ll use the first one listed. That is, if I have query.cmd, querty.vbs and querty.com, it’ll use querty.cmd.

Now, if you want to do this all the time without having to keep setting %PATHEXT%, you’ll have to modify the system environment. Typing it in a console window only changes it for that console window session. I’ll leave this process as an exercise for the reader. 😛

Third part, getting the script to run without always typing the full path. This part, in relation to the second part, has been around since the days of DOS. Simply make sure the file is in one of the directories (folders, for you Windows’ folk!) listed in the %PATH% environment variable. My suggestion is to make your own directory to store various files and programs you create or use often from the console window/command prompt (that is, don’t worry about doing this for programs you run from the start menu or any other method.. only the console window. Don’t mess with programs that are installed by Windows or an automated installer unless you know what you’re doing).

Personally, I always create a “C:sysbat” directory for batch files, a “C:sysbin” directory for * .exe and * .com files (for example, if you download something like “md5sum”, a MD5 checksum utility), a “C:syswsh” directory for VBScripts (and JScripts, named “wsh” because both are executed using the “Windows Scripting Host”, or “wsh” program), and so on. I then add these to my system %PATH% variable (Control Panel -> Advanced System Settings -> Advanced tab -> Environment Variables button), so Windows can always find them when I type them.

Combining all three parts will result in configuring your Windows system so that anywhere you can type in a command-line command, you can launch your VBScript by just typing it’s base file name. You can do the same for just about any file type/extension; As you probably saw in my %PATHEXT% output, my system is set up to run Perl scripts (.PLX;.PLW;.PL) and Python (.PY) scripts as well. (I also put “C:sysbat;C:sysscripts;C:syswsh;C:sysbin” at the front of my %PATH%, and put various batch files, script files, et cetera, in these directories, so Windows can always find them. This is also handy if you want to “override” some commands: Putting the * .bat files first in the path makes the system find them before the * .exe files, for example, and then the * .bat file can launch the actual program by giving the full path to the actual *. exe file. Check out the various sites on “batch file programming” for details and other examples of the power of the command line.. It isn’t dead yet!)

One final note: DO check out some of the other sites for various warnings and caveats. This question posed a script named “converter.vbs”, which is dangerously close to the command “convert.exe”, which is a Windows program to convert your hard drive from a FAT file system to a NTFS file system.. Something that can clobber your hard drive if you make a typing mistake!

On the other hand, using the above techniques you can insulate yourself from such mistakes, too. Using CONVERT.EXE as an example.. Rename it to something like “REAL_CONVERT.EXE”, then create a file like “C:sysbatconvert.bat” which contains:

@ECHO OFF
ECHO !DANGER! !DANGER! !DANGER! !DANGER, WILL ROBINSON!

ECHO This command will convert your hard drive to NTFS! DO YOU REALLY WANT TO DO THIS?!
ECHO PRESS CONTROL-C TO ABORT, otherwise..

REM "PAUSE" will pause the batch file with the message "Press any key to continue...",
REM and also allow the user to press CONTROL-C which will prompt the user to abort or
REM continue running the batch file.
PAUSE

ECHO Okay, if you're really determined to do this, type this command:
ECHO.    %SystemRoot%SYSTEM32REAL_CONVERT.EXE
ECHO to run the real CONVERT.EXE program. Have a nice day!

You can also use CHOICE.EXE in modern Windows to make the user type “y” or “n” if they really want to continue, and so on.. Again, the power of batch (and scripting) files!

Here’s some links to some good resources on how to use all this power:

http://ss64.com/

http://www.computerhope.com/batch.htm

http://commandwindows.com/batch.htm

http://www.robvanderwoude.com/batchfiles.php

Most of these sites are geared towards batch files, but most of the information in them applies to running any kind of batch (* .bat) file, command (* .cmd) file, and scripting (* .vbs, * .js, * .pl, * .py, and so on) files.

After a bit of googling and searching here, couldn’t find the answer to this silly question!

For a structure like this…

dirZero
|---dirOne
|---|---myProgram.exe

How do I run “myProgram” if my current directory is dirZero? I.E.,

C:dirZero> dirOne/myProgram.exe

…which obviously doesn’t work. Thanks in advance.

RUN gets used to build the layers in an image. CMD is the command that is run when you launch an instance (a “container”) based on the image.

Also, if your script depends on those environment variables, if it’s an older version of Docker, it might fail because those variables are not defined the way you want them defined!

In older versions of docker the Dockerfile ENV command uses spaces instead of “=”

Your Dockerfile should probably be:

FROM microsoft/mssql-server-windows-express
COPY ./create-db.sql .
ENV ACCEPT_EULA Y
ENV sa_password ##$wo0RD!
RUN sqlcmd -i create-db.sql 

This will create an image containing the database with your password inside it.

(If the SQL file somehow uses the environment variables, this wouldn’t make sense as you might as well update the SQL file before you copy it over.) If you want to be able to override the password between the docker build and docker run steps, by using docker run --env sa_password=##$wo0RD! ..., you will need to change the last line to:

CMD sqlcmd -i create-db.sql && .start -sa_password $env:sa_password 
-ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs "$env:attach_dbs" -Verbose

Which is a modified version of the CMD line that is inherited from the upstream image.