Solved

Using 'xp_cmdshell' to run a DOS command script.

Posted on 2004-04-27
16
712 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Word Template Mail merge with vb.net 4 40
Help in Understanding a SQL Query 7 28
sql server query? 6 26
sql query Help 12 17
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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

20 Experts available now in Live!

Get 1:1 Help Now