June 5, 2009 at 8:27 am
The use of cmd files to execute sql is a powerful tool that is underutilized...
I absolutely agree with that. And, old or not, the idea is "there". A bit more detail could certainly have been added to the article but it provides the basis of a very underutilized technique.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2009 at 8:56 am
Example of where I could probably use this (or a similar) technique: We use SQL Server 2005. I have several SSIS packages that fail at random intervals and at random steps/tasks. The failure notice is that the poor thing just failed to acquire a connection to our own database right there in the same server as SSIS. The scheduled job that fails one night is likely to run fine the next next night with no changes. Once, however, I got the same error an entire week before the job started working again. (We re-boot our server every week.) Sometimes I get the same error when I manually run the job, and and then a minute later it works.
Fine. We can usually live with this buggy behavior. However, this problem unfortunately happened once when I was on vacation. It would have been good if I had had a solution such as the one shown in this article so that my users could have manually run the job themselves when they needed it.
I understand that security issues have to be worked out, but giving the users the ability to run a job on demand in a production environment could have some very practical purposes in my agency.
June 5, 2009 at 9:39 am
JJ B (6/5/2009)
Example of where I could probably use this (or a similar) technique: We use SQL Server 2005. I have several SSIS packages that fail at random intervals and at random steps/tasks. The failure notice is that the poor thing just failed to acquire a connection to our own database right there in the same server as SSIS. The scheduled job that fails one night is likely to run fine the next next night with no changes. Once, however, I got the same error an entire week before the job started working again. (We re-boot our server every week.) Sometimes I get the same error when I manually run the job, and and then a minute later it works.Fine. We can usually live with this buggy behavior. However, this problem unfortunately happened once when I was on vacation. It would have been good if I had had a solution such as the one shown in this article so that my users could have manually run the job themselves when they needed it.
I understand that security issues have to be worked out, but giving the users the ability to run a job on demand in a production environment could have some very practical purposes in my agency.
Why does anyone live with buggy behavior. Solve the Issue. Get Profiler running and find the Table lock or constraint that is throwing the error. It's not Buggy Behavior it's bad development and lack luster administration.
On Topic:
sp_start_job requires permission to execute higher level processes.
The best way to get a simple user to be able to fire a Job on Demand is:
1) Build a table in a public database [Jobs] (Job_name,Server,Database,RequestUser,Requestdate,StartDate,CompleteDate,JobStatus)
Then a user can insert a record when they want the job to execute. via any Client(WEB,Winform,access,SSMS,etc.....)
Create a Second Job call USer Jobs running under sql agent as a domain sysadmin
with a schedule to run every 5 minutes (or whatever meets your needs)
Cursor Select Job_name from Jobs where StartDate is null
exec msdb..sp_start_job @Job_name=@Job_name
The table can be as elaborate as you need
The user jobs Job can update the status and manage Jobs with any business rules.
I am sorry if I have offended, this is my second post here.
I won't be back
June 5, 2009 at 9:46 am
hello friend,
Why don't post the vbscript if you have any with you.
Appreciate your idea and help
June 5, 2009 at 11:27 am
Why does anyone live with buggy behavior. Solve the Issue.
Not everyone has the luxury of solving every problem. If you are overwhelmed with tasks, you have to prioritize. If this problem affected us in a more serious way, I would definitely work on it. But I have much bigger priorities. So, having a quick batch file I can throw at users for the once a year I actually take a real vacation on the off chance that they will need it (it's only ever happened once in years that a vacation and failure coincided), then that is the better use of time at this one person show.
FYI: While you could be right that there is some kind of bad design that requires a fix on my part, I think you are wrong for this situation. The very nature of this problem as originally described (different SSIS, a first generation piece of software, packages doing very different things, random failure times and frequencies, random points of failure, and I'll add: relatively small databases on an underutilized server with plenty of resources and no block reports from my monitoring system) screams a bug in SSIS/SS05 or some other kind of problem with our server/network as opposed to failures in all my databases and queries creating blocking/locking problems.
Doesn't really matter either way since I don't have the time to address it. But my instincts on these things have proven rather accurate in the past. If I'm right, not only do I not have time to deal with this one task of the hundreds I'm juggling, it would be a waste of time. I'm pushing to upgrade to SS08. I've heard lots of reports that SS08 is more stable than SS05. My problem may disappear. If so, that's one more reason for me not to spend time on it right now.
Prioritize. It's a good thing.
June 5, 2009 at 11:44 am
** Correction - osql has not been deprecated.
It is still working in SQL 2008, but according to BOL (This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use sqlcmd instead.)
Also osql and sqlcmd allow either trusted connections or embedded name and password.
June 5, 2009 at 1:35 pm
Lots of good banter. You might also want to see the results of the command. I like the architecture of Job.cmd calling Job.BAT that routes the output to a file.
Contents of job.cmd look like this:
c:\job.bat > c:\job.log 2>&1
The osql line could also use the -b switch will will abort on error.
Security concerns can be addressed with groups on the box. The type of groups and the permissions associated will depend on the purpose of the batch script.
Good info Divya
June 5, 2009 at 4:12 pm
Comming from a UNIX background, I find that OSQL is very helpfull in updating a remote MS server and monitoring activity to an application that notifies and downloads files to 7000+ clients. I installed UWIN, an UNIX environment on my XP laptop and put SQL statements into korn shells. The output can be placed into an ASCII file and manipulated through extensive UNIX tools.
June 5, 2009 at 4:50 pm
What about creating a set of stored procedures for this?
1. User executes "dbo.spuQueueJob @JobName" via OSQL to "manually schedule" the existing job by adding the job name to a queue table.
2. A Scheduled Task runs every 5 minutes (with admin privs) to see if a job was queued via the above proc. If yes, uses sp_start_job for the jobs found in the queue and deletes them from the queue table afterwards.
This way, you do not need to grant the user high privileges. Only the right to execute this dbo.spuQueueJob procedure.
Best Regards,
Chris Büttner
June 5, 2009 at 6:58 pm
The Script is very help full but it only work if the particular Backup is already configured on a particular server. it will be very helprull to run some other storeprocedure. No one is very familior how to write backup.
Ritesh
June 5, 2009 at 8:22 pm
I would not use the pause statement, if you do, the batch script will wait indefinitely for the user to depress a key.
June 5, 2009 at 10:06 pm
Yes you are right. if we are making backup from batch file then No pause in the script. by removing we can scheduled the backup automatically.
June 6, 2009 at 1:07 am
D Randall Pitkin (6/5/2009)
OLD!! osql is depricated, and not all users will have osql, isql or sqlcmd availableBetter to use a small vbs script
which allows both Trusted connections and embedded user name and password
Why don't you post us a vbscript here so we can see how it works.
This script works fine for me because I can schedule it in the Windoews' scheduler. You see, my one client has a relatively small business and at this point cannot afford a SQL Server 2008 full version so they use the express version. Therefore I cannot schedule backups and other jobs in SQL Server so I'll use this script to run a scheduled job in Windows.
Thanks for this cscript and no matter what anyone else say if they don't think it is good the they must post something better. Amen
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 6, 2009 at 6:14 am
Hey..
can u able to explain the code in little bit brief once...
because It is not working in my system.. while try to connect the remote server and as well as local system also...
check below
ECHO Executing job
ECHO.
pause
osql -S "ws-1" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "
ECHO Job execution completed
pause
CLS
EXIT
Note : ws-1 is my server name when i open sql2005 it displays (ws-1\sqlexpress).. which one is correct...
June 6, 2009 at 10:16 am
krish270486 (6/6/2009)
Hey..can u able to explain the code in little bit brief once...
because It is not working in my system.. while try to connect the remote server and as well as local system also...
check below
ECHO Executing job
ECHO.
pause
osql -S "ws-1" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "
ECHO Job execution completed
pause
CLS
EXIT
Note : ws-1 is my server name when i open sql2005 it displays (ws-1\sqlexpress).. which one is correct...
No, you did it wrong. You must not enter the server name but the instance name for example servername\instancename.
osql -S "Put instance name here e.g. serverName\instance" -E -Q"exec msdb.dbo.sp_start_job 'BACKUPTEST' "
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply