Solved

Using 'xp_cmdshell' to run a DOS command script.

Posted on 2004-04-27
16
716 Views
Last Modified: 2012-06-27
Hi,

Is it possible to perform the following in a SQL script using the system stored procedure 'xp_cmdshell':

1- Run the following in a SQL script

EXEC master.dbo.xp_cmdshell " c:\test.cmd @variable1" (not sure about the syntax)

The variable '@variable1' iwould be defined in the SQL script, and actually contains the name of a text file and I want it passed to the DOS command script 'test.cmd' when 'xp_cmdshell executes.

2- The DOS command script should then open up the text file, manipulate its contents (ie add/remove characters) and finally save it in another text file. (Is this somthing that can be done using a DOS command script?).

Thank you for your help in advance.

Regards

Hamid


0
Comment
Question by:htavakko
16 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10926602
Why not hard code- the output string, using dynamic sql ?

EXEC master.dbo.xp_cmdshell "c:\test.cmd " + @variable1 + " "

OR use something like this:
DECLARE @cmd sysname, @var1 sysname
SET @var = 'dir *.* /p'
SET @cmd = 'echo ' + @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

Have a look at SED, to manipulate a text file (works very well for me..):
http://www.student.northpark.edu/pemente/sed/

Example 1-Line manipulations
http://www.student.northpark.edu/pemente/sed/sed1line.txt

0
 
LVL 6

Expert Comment

by:PhilAI
ID: 10926801
Try this?

Set @cmdLine = 'c:\test.cmd "' + @variable1 + '"'

EXEC master.dbo.xp_cmdshell @cmdLine
0
 

Author Comment

by:htavakko
ID: 10926834
Thank you for your comments.

To 'danblake'
---------------

Would SED work with with any text file format (ie ANSI, Unicode, etc)?


To 'PhilAI'
-----------

Thank you. I'll try it.





Rgds

Hamid

 
0
 
LVL 13

Expert Comment

by:danblake
ID: 10927017
I've tested SED with ANSI, and not worked with any Unicode outputs for SED -- I cannot see any reason why it would not work for unicode, the read / write mechanism is the same (file i/o) its just you may need to specify the data in/out search/replace add/delete codes in Unicode.

The example xp_cmdshell I have given outputs the directory structure from the default path to an output file dir_out.txt

where @cmd is storing the cmd line text to be executed in the xp_cmdshell.

The reason why your @variable1 does not work within the xp_cmdshell 'do something @variable1' is because the @variable1 is outside scope when placed within the execute line and you require dynamic sql- to make this work.
0
 

Author Comment

by:htavakko
ID: 10927035

To 'danblake'
----------------

I have a text file, a sample of which is shown below:

UPD|000013|5705877|20040224|11460000|
PATIENT_NOS|5705877|H|999999H |A|
PATIENT_NHS|5705877|NHS|5555555555|A|
PATIENTS|DUSTBIN         |DUMMY       |RED|5705877|19820614|0|F|MRS |10294|
PATIENT_ADDRS|5705877|P|19 ELLINGTON ROAD, HOUNSLOW, |MIDDX | | ||TW3 4HX |Y|0208 231 6197   | |   |20040302| |
PATIENT_NOK|5705877|SMITH          |    |            |AA                              |                                |  |  |AA              |                |  | |
UPD|000013|5705877|5|

Would I be able to manipulate this text file using SED as follows:

1- Add a sequence no, starting from '1' to the beginning of each line and seperate this from the next field using the '|' (pipe) character.
2- Count the no. of occurances of '|' on each line, and then add enough '|' to the end of each line to make a total of 19 '|'s.

Thank you for your help in advance.


Rgds

Hamid
 
0
 
LVL 13

Expert Comment

by:danblake
ID: 10927166
Yes, it should be give me 5 mins...

D:\Documents and Settings\Daniel Blake\My Documents\Experts-Exchange\wip>sed-3.5
9 = SEDManipulation.txt | sed-3.59 "N;s/\n/|/"
1|UPD|000013|5705877|20040224|11460000|
2|PATIENT_NOS|5705877|H|999999H |A|
3|PATIENT_NHS|5705877|NHS|5555555555|A|
4|PATIENTS|DUSTBIN         |DUMMY       |RED|5705877|19820614|0|F|MRS |10294|
5|PATIENT_ADDRS|5705877|P|19 ELLINGTON ROAD, HOUNSLOW, |MIDDX | | ||TW3 4HX |Y|0
208 231 6197   | |   |20040302| |
6|PATIENT_NOK|5705877|SMITH          |    |            |AA
        |                                |  |  |AA              |
 |  | |
7|UPD|000013|5705877|5|

Adds the Line numbers you require this just needs to be piped out to a different file...
Let me work on your next problem of your | 's...
0
 
LVL 13

Expert Comment

by:danblake
ID: 10927201
Yes -- it is possible to do this, I'm looking at creating a construct using the t by checking the number of fields already present within the line, then substituting for the missing items... I'm in the middle of a couple of things at the mo, its going to be about 30 mins for me to generate a working construct, I'll be back in a mo..
0
 
LVL 8

Expert Comment

by:bukko
ID: 10927795
...hope that address and phone number aren't real!
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 13

Expert Comment

by:danblake
ID: 10927810
I've got 3/4 of the SED stuff, required its just proving a bit difficult to put this lot all together... give me a little bit more time, (this will be a very useful tool once completed... allows a file structure to have the same number of columns to ensure input/output works every time once completed.)

0
 

Author Comment

by:htavakko
ID: 10929105
To 'danblake'
---------------

I appreciate your efforts. This would be extremely useful once it's done. Thank you.



Rgds

Hamid




0
 
LVL 13

Expert Comment

by:danblake
ID: 10936456
Right, got it...
(This is my output from my input file called SEDManipulation.txt -- your given input, and a required input file to allow SED to know what to do called MAPIP.SED)
D:\Documents and Settings\Daniel Blake\My Documents\Experts-Exchange\wip>sed-3.5
9 = SEDManipulation.txt | sed-3.59 -f MAPIP.SED
1|UPD|000013|5705877|20040224|11460000||||||||||||||
2|PATIENT_NOS|5705877|H|999999H |A||||||||||||||
3|PATIENT_NHS|5705877|NHS|5555555555|A||||||||||||||
4|PATIENTS|DUSTBIN         |DUMMY       |RED|5705877|19820614|0|F|MRS |10294||||
|||||
5|PATIENT_ADDRS|5705877|P|19 ELLINGTON ROAD, HOUNSLOW, |MIDDX | | ||TW3 4HX |Y|0
208 231 6197   | |   |20040302| ||||
6|PATIENT_NOK|5705877|SMITH          |    |            |AA
        |                                |  |  |AA              |
 |  | ||||||
7|UPD|000013|5705877|5|||||||||||||||



D:\Documents and Settings\Daniel Blake\My Documents\Experts-Exchange\wip>type mapip.sed
/^[0-9]\{1,\}$/{
$!N
s/\n/|/
}
s/$/|||||||||||||||||||/
s/|/Z/19
s/|Z|\{1,\}$/||/

The input file is not that complicated to generate but basically it is ensuring that there is 19 | present in the output file.

0
 
LVL 13

Expert Comment

by:danblake
ID: 10936462
Can you give it a go, as indicated above, and let me know if there are any problems...
0
 

Author Comment

by:htavakko
ID: 10938012

To 'danblake'

Thank you very much. This has been extremely useful for me. I am now testing your code within my program. You're getting all the points. Thanks again.
0
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
ID: 10938028
I'm always trying to get down to the bottom of an issue to produce the best solution where possible.
0
 

Author Comment

by:htavakko
ID: 10939524
You've done an excellent job.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10939551
I had some help...;)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now