Solved

Using 'xp_cmdshell' to run a DOS command script.

Posted on 2004-04-27
16
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IF SQL Query 12 29
SQL Server Resume 5 46
Access #Deleted data 20 45
Estimating my database size 7 21
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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