Open an excel file automatically by using a bat file
-
Good Morning everyone,
I am trying to run a .bat file in SQL that open an excel file automatically but it does not work for me.
Here is the code I have:
***************************
This is the SQL code:
declare @cmd_str varchar(100),
@path varchar(500)
set @path='\\wwcd\test\'
set @cmd_str = @path+'Auto_Refresh.bat'
exec master.dbo.xp_cmdshell @cmd_str
And This is the .bat file:
"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2003.lnk" \\wwcd\test\filename.xls
*********************************
This code worked fine for me last week but for some reason it does not work now. I am trying to run it in a Query Analyzer that getting an "ACCESS DENIED" error.
Does anyone have any clue what has happened?
I just want to open the filename.xls automatically because there's Macor in this excel to do something for me.
Thank you very much for your help.
-
ACCESS DENIED is usually a permissions error. Can you open up \\wwcd\test\filename.xls outside of Query Analyzer?
-
This excel file is the file I created, so I have access to it. I tried to copy the whole path and past it to my browser to open the file and it opened just fine.
If i click on the bat file manually, it is working fine for me; however, if I put it in the SQL code as I had in my first post, it does not work.
Any clues? Thanks for your help.
-
Windows authentication
is that an issue?
-
No, I just wanted to make sure that the account that QA is using has rights to the file, but it sounds like it does since you are using Windows authentication. What happens if you run the .bat file outside of QA, like from another command prompt?
-
It's working just fine for me if I run it from DOS cmd promt.
What's wrong to my SQL code?
here is the error that I get if I call the .bat file through SQL
NULL
C:\Program Files\Common Files\SYSTEM\MSMAPI\1033>"C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office\Microsoft Office Excel 2003.lnk" \\wwcd\test\filename.xls
Access is denied.
NULL
Why it's added something to the front of the path?
-
What you are seeing is the directory from which the command shell is opening up. By default, this should be C:\windows\system32. I don't think anything is wrong with your SQL code. It is a permissions problem somewhere. Is your Windows account set up as DBO?
-
What is DBO?
I have no clue on how my windows account set up because I did not set it up.
mang... this problem is killing me!
-
Ask your sysadmin what did he changed in access rights last week.
There are possibly 3 different accounts:
SQL Server started;
SQL Server Agent started;
Your local your Command Prompt started.
Find out all 3 of them (probably 2 of them are the same, but do not be so sure!) and check if every one has rights for full access of the directory with your Excel file.
And than you kill sysadmin.
_____________
Code for TallyGenerator -
Might be nothing to do with anything...
But why does the batch file run the desktop menu shortcut to Excel rather than the Excel executable? This adds another piece to your permissions chain - the user context running the sp now needs permission on the shortcut as well as Excel itself.
-
how do you write a .bat file to run Excel executable? Please help!
Thanks!
-
Do you think if the server where I run my query has problem with .bat files?
I once got error with ACCESS DENIED, but one time yesterday, I re-tested it again and the query worked fine. But later yesterday and today, the .bat file did not work at all. It did not do anything. The query ran and finished but did not do anything other than gave me an output NULL after it sucessfully executed.
The excel file did not get opened at all. I debugged the query by add select statements to return me the path and the cmd_str strings, and they were all returned. If I copy the cmd_str string and pasted it to windows browser and the .bat file opened the excel file and do what I expected it to do.
What do you think? Where is the error? Is that because the server does not work with .bat files?
thanks!
-
Make sure the sqlagent has access to the folder\share and the file.
-
Can you run the following?
EXEC Master.dbo.xp_CmdShell 'Dir C:\'
If not, your SYSADMIN got the "religion of security" and disabled access to xp_CmdShell.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply