Solved

stored procedure code

Posted on 2000-04-21
2
176 Views
Last Modified: 2008-02-26
This may be more visual basic than sql server but anyhow, these are steps i take from a DOS prompt.

C:\>ftp sail
Connected to sail.xxxxxx.edu.
220-FTPSRVHD IBM MVS V3R2 at cpslo.its.xxxxxxy.edu, 09:12:51 on 2000/04/21
220 Connection will close if idle for more than 5 minutes.
User (sail.xxxxx.edu:(none)): master
331 Send password please.
Password:
230 master is logged on.  Working directory is "master.".
ftp> quote site filetype=jes
200 Site command was accepted
ftp> put pcjcl
200 Port request OK.
125 Sending Job to JES Internal Reader FIXrecfm 80
250-It is known to JES as JOB08814
250 Transfer completed successfully.
366 bytes sent in 0.00 seconds (366000.00 Kbytes/sec)
ftp> quit
221 Quit command received. Goodbye.

C:\>
I would like to have a stored procedure that would do the ftp and put the
line that says 'it is known to jes as job08814'  into some little table.
I'm using 6.5


 
0
Comment
Question by:xoxomos
2 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2740927
I think you need a small script (shell script/perl or similar) that can execute all your input operations.
Then, in SQL, you may insert the whole output into a table using
INSERT INTO YouTable EXEC xp_cmdshell  "Your Dos Command to launch the Script"
After this, you can query you table to find the rows that are interesting.
Unfortunately, i don't know what script you need to use, i only know that it is possible to do (seeing our Network Adminstrators having things like that)

Good luck
0
 
LVL 7

Accepted Solution

by:
simonsabin earned 50 total points
ID: 2743030
put your commands into a file and then run ftp with

ftp -s:<filename>

and as angelIII said use someting like

CREATE TABLE #TMP (output varchar(255))

INSERT INTO #TMP EXEC xp_cmdshell 'ftp -s:MYFtpFile.txt'

INSERT INTO MyJobTable
SELECT output
 FROM #TMP
WHERE output LIKE '%It is known%'

DROP TABLE #TMP



The MyFtpFile.txt should loook like

open sail <password>
quote site filetype=jes
put pcjcl
quit

You will need to secure this file to stop people finding the password.


0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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 …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

12 Experts available now in Live!

Get 1:1 Help Now