October 10, 2011 at 2:39 am
Hi,
I have a report which returns rows of data by customer number. The report requires the recipient of the report to edit the customers data on our financial system. I have created a hyperlink in the report which calls a batch file which opens the windows application which is our financial system.
This works fine but I want to go one step further and pass the customer number to the batch file which will then open our financial system and fill in the customer number for the user.
(am I asking for too much??)
The batch file is shown below and works fine in the command window, however in the report which outputs to html the hyperlink only underlines everything apart from the parameter which is the customer number. When the link is selected it only opens the application but does not pass the customer id.
Batch File : START \\"Program Location"\"Program EXE file" START="Screen Name" "Customer ID=%1"
hyperlink : \\"Batch file location"\"batch file name.bat" "Parameter which is like 332567"
What I am doing wrong?
If I copy and paste the hyperlink into the run command, it opens the application for the customer.
Thanks
October 11, 2011 at 8:10 am
I've never tried running a batch file from a report link, I don't even know if it's possible. What might work is running the Batch file from a stored procedure using the xp_cmdshell. You could pass the batch file parameters to the stored procedure. You could also put in some error handling and sanity checks to make sure only people who should be allowed to run the SP can run it.
I done a few things like this for my users too. Sometimes I find that the easier I try to make things for them the dumber they get. I've actually had processes that had one button labeled "Click Me' and they still couldn't do it with out help. (not a joke)
Do your users start one instance of thier program and do all the edits from there? It souulds like what you want to implement will open multiple instances of a progam.
October 11, 2011 at 8:21 am
I have a report which will go out to certain an individual so security isn't really an issue.
All I want to achieve is to be able to click a customer number in a table in a report, a hyperlink for the field will simply open a windows application and then pass the customer number to the windows application so they don't have to type it in.
Do you have a simple example of an xp_cmdshell script?
October 11, 2011 at 8:37 am
I have a real simple one that i have in one of my stored procedures
EXEC xp_cmdshell 'F:\Directroyname\Bachfilename.bat Parametername', NO_OUTPUT
My parameter choices are in the batchfile and not passed from the Stored Proc. But you could build the parameters and batchfile name into a varchar and pass that to xp_cmdshell
Declare @CMD varchar(1000)
Set
October 11, 2011 at 8:40 am
oops. Didn't mean to post that yet...
Declare @CMD varchar(1000)
, @CustomerID varchar(1000)
Set @CMD = 'f:\Directoryname\Batchfilename.bat'
Set @Customer ID='12345'
Set @cmd + ' ' + @Customer ID
exec xp_cmdShell @CMD
October 11, 2011 at 9:12 am
Thanks that makes sense I have run the following script in query analyser Which is as follows:
EXEC master..xp_CMDShell 'C:\\PARA.bat'
This returns a column output
1 'C:\\PARA.bat' is not recognized as an internal or external command,
2 operable program or batch file.
3 NULL
What am I doing wrong?
October 11, 2011 at 9:23 am
did you copy right out of SSMS? I see two slashes.
October 11, 2011 at 9:29 am
Yes I ran the query in SSMS in query analyser and copied the results from the grid.
October 11, 2011 at 9:31 am
Try
EXEC master..xp_CMDShell 'C:\PARA.bat'
October 11, 2011 at 9:34 am
Tried that and it returned the same message as before.
October 11, 2011 at 9:44 am
if you run sp_configure and scroll to the bottom of the list what are the values for xp_cmdshell? You might have to do a sp_configure 'show advanced options' to see them
October 11, 2011 at 9:59 am
Thanks I have reconfigured so that the advance options are shown.
I have reconfigured the xp_cmdshell value so that the config_value and the run_value are 1
Then I have re-run the query but it just seems to run and not finish
EXEC master..xp_CMDShell 'C:\\PARA.bat'
October 11, 2011 at 10:30 am
Does the batchfile hang or does the SSMS window stay 'Executing'?
October 12, 2011 at 1:48 am
It says it is executing at the bottom of the window but nothing seems to happen.
I am logged into the ssms on my personal machine and I have tested the script
C:\\para.bat
in the run command and it works fine.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply