July 9, 2019 at 7:49 am
We are trying to give the wider business the ability to run an SQL Job using a .bat file. We have tried both osql and sqlcmd however neither have worked. osql keeps asking for the osql.rll file and sqlcmd is giving us a side-by-side error. We had lots of these .bat files set up at my previous workplace and I know that another file was placed in the folders along side the .bat files to get them to work however I can't recall what the file was. Could anyone help us?
July 9, 2019 at 8:14 am
This sounds like an "XY problem". I assume you want the user to be able to start a SQL Agent job? Is it a specific job you want them to be able to start, or any job? If any job, why not just use in the inbuilt roles and have the user have access to the instance via SSMS (as surely they are a user with knowledge of SQL Server). If you don't want them to have access to the instance, then why are the jobs not set up to run on a schedule instead?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 9, 2019 at 8:38 am
Thanks for coming back to me on this, its a specific SQL Agent job that needs to be run once a file comes in from a 3rd party. That file comes in at different times so we need the flexibility to run the job on the fly. We don't want to open up SSMS to the users as they have no knowledge of SQL etc. We just want them to be able to save the file on a network share and then run the .bat file to run the job to load the file into one of our databases.
July 9, 2019 at 8:48 am
Seems like it would be easier to have the user update a table to update a status to say that the file is available, and your SQL job pools the table on a regular basis. Having the user run a bat file seems like a really bad idea though; give them a button in your application or something to click.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 9, 2019 at 9:14 am
We have that for other processes that are run by jobs however I need to check if this particular user has access to our ERP. Thanks for the feedback.
July 9, 2019 at 9:38 am
We had lots of these .bat files set up at my previous workplace and I know that another file was placed in the folders along side the .bat files to get them to work however I can't recall what the file was. Could anyone help us?
I suspect that you had bat file together with sql file which had all sql commands to execute.
Example:
bat file
sqlcmd -S server -U user -P password -i sqlfile.txt -o output.txt
sqlfile.txt
exec msdb..sp_start_job @job_name=N'Job X'
go
July 9, 2019 at 10:52 am
Don't remember it being a text file but thanks Andrey, will take a look. I've just found an old Bat file from 2015 that was still being used up until 2017. That was using SQLCMD to do exactly what we are trying to achieve here. No extra files are present and I know it was working when it was being used. I've tried it today and got the same error as I'm getting on the new bat file. Has something been changed/updated in the last couple of years to stop SQLCMD from being run away from the SQL Server?
July 9, 2019 at 12:22 pm
I've tried it today and got the same error as I'm getting on the new bat file. Has something been changed/updated in the last couple of years to stop SQLCMD from being run away from the SQL Server?
If you disclose the error message it will help a bit to answer your question.
Also, it's worth to show how do you invoke sqlcmd (parameters).
July 9, 2019 at 12:38 pm
See attached error message, we managed to get around the issue by copying the sqlcmd.exe, sqlcmd.rll and batchparser.dll from the server where SQL is into the folder with the bat file.
Such error message means that either SQL Client tools (Management Studio basic) are not installed, or some its folders are not in PATH global variable.
You just cut the corner and put all needed files next to your bat files.
It helped to skip path seek.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply