• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 788
  • Last Modified:

Using 'xp_cmdshell' to run a DOS command script.

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
htavakko
Asked:
htavakko
1 Solution
 
danblakeCommented:
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
 
PhilAICommented:
Try this?

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

EXEC master.dbo.xp_cmdshell @cmdLine
0
 
htavakkoAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
danblakeCommented:
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
 
htavakkoAuthor Commented:

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
 
danblakeCommented:
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
 
danblakeCommented:
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
 
bukkoCommented:
...hope that address and phone number aren't real!
0
 
danblakeCommented:
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
 
htavakkoAuthor Commented:
To 'danblake'
---------------

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



Rgds

Hamid




0
 
danblakeCommented:
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
 
danblakeCommented:
Can you give it a go, as indicated above, and let me know if there are any problems...
0
 
htavakkoAuthor Commented:

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
 
danblakeCommented:
I'm always trying to get down to the bottom of an issue to produce the best solution where possible.
0
 
htavakkoAuthor Commented:
You've done an excellent job.
0
 
danblakeCommented:
I had some help...;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now