Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using 'xp_cmdshell' to run a DOS command script.

Posted on 2004-04-27
16
Medium Priority
?
751 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
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!

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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