Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Execute SQL job through batch file Expand / Collapse
Author
Message
Posted Saturday, June 6, 2009 12:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 1, 2013 7:53 AM
Points: 1,219, Visits: 73
Since osql is being depricated, probably the better route is to create a backup SQL script for the targeted databases to backup such as:

BACKUP DATABASE [TestDB] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\TestDB.bak' WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CONTINUE_AFTER_ERROR
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'TestDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'TestDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''TestDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\TestDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Save it to a directory on the harddrive and then script out a sqlcmd batch file to automate the backup. This can be scheduled via the Windows Scheduler.

The syntax would be similiar to this:

@echo off

sqlcmd -H avt-oak-01 -S avt-oak-01\SQLEXPRESS -E -i "E:\Docs\SQLQueries\Back
upTestDb.sql" -o "C:\BackupTestDb.log"

EXIT



The log output would then have the following output:

14 percent processed.
24 percent processed.
34 percent processed.
44 percent processed.
54 percent processed.
64 percent processed.
74 percent processed.
84 percent processed.
94 percent processed.
Processed 160 pages for database 'TestDB', file 'TestDB' on file 1.
100 percent processed.
Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 162 pages in 0.505 seconds (2.492 MB/sec).
The backup set on file 1 is valid.
Post #730286
Posted Sunday, June 7, 2009 12:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:58 PM
Points: 1,207, Visits: 928
Normally, when a person posts a different solution than was originally given, I would think that an explanation of that solution would automatically be posted as well. So, I had to do my own bit of searching to get the meaning of all the code in this solution. You see, not everybody here is good with all types of coding here. Anyway for those who would like an explanation of the sqlcmd script simply open command prompt or powershell and type sqlcmd /? and all the codes and explanations will be given. For your convenience I attached a screenprint of this function.

Manie 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)


  Post Attachments 
sqlcmd help.jpg (23 views, 72.20 KB)
Post #730406
Posted Monday, June 8, 2009 10:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:02 PM
Points: 12, Visits: 211
The reason I don't highly recommend this article is because the author simply ignore the importance of SQL security. As this article let technical even non-technical people run scheduled sql jobs(owner of jobs might be sa or other people) in a batch file(on-demand), it requires the person's window domain id should have SQLagentoperatorRole in MSDB. If domain user account or local NT account has SQLagentoerpatorRole in MSDB, it means that user not only could run the job, but also could view existing jobs, create new job and schedule job (sp_add_job, sp_help_job, etc). There is potential security threat(bring down your SQL)if you allow anyone creates job in your sql server.

just my thought.

Post #730776
Posted Monday, June 8, 2009 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 23, 2012 9:46 AM
Points: 3, Visits: 28
Until I get up to speed with MS powershell, I do most of my SQL work in a UNIX environment on my XP laptop. I use OSQL to run stored procedures as well as the syscmd statement. I like to know when an SQL job ends so I can look at the results.
I run OSQL in the background, capture the job number and use the "wait" comamnd to
determine when the job is finished. This is how I would run a database backup script, wait for it to end and show the results in an ASCII log

C:/mssql7/Binn/osql ......... -o dblog -Q "exec sp_db_backup" & # run job in background
PID=$! # capture job number
wait $PID # wait for job to end
echo "job database backup has just ended"
cat dblog # show the results after the job ends

Post #730987
Posted Wednesday, March 13, 2013 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 9:47 AM
Points: 2, Visits: 15
So I just tried doing something similar and it says it runs but nothing happens when the process is completed. At all.


ECHO Executing Job
ECHO.
osql -S "server" -E -Q"exec msdb.dbo.sp_start_job 'job'"
ECHO Job execution executed.
Pause
CLS
EXIT

Thing is, it works fine from my local machine. If i run it from a remote computer it does the fake run.
Post #1430473
Posted Wednesday, March 13, 2013 7:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 8:34 AM
Points: 7, Visits: 142
ssj_goemon,

You might need to add an alias through sql server configuration manager.

Could be a networking problem. Open a command prompt and run this:

telnet server.ip.address sqlport
e.g. 192.168.2.235 1433
If the screen goes blank you have network connectivity to the remote server.

Does the account have permissions to connect and/or run jobs on the remote server?

osql -S "remote server" -E -Q"select getdate()"
or:
sqlcmd -S "remote server" -E -Q"select getdate()"

You should get the date.

Create this job on your remote server:

USE [msdb]
GO

/****** Object: Job [getdate] Script Date: 03/13/2013 21:29:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/13/2013 21:29:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'getdate',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [run getdate] Script Date: 03/13/2013 21:29:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run getdate',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select getdate()',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Then execute this:

osql -S(remote_server_name) -E -Q"exec msdb.dbo.sp_start_job 'getdate'"
or
osql -S(remote.ip.address) -E -Q"exec msdb.dbo.sp_start_job 'getdate'"

sqlcmd -S(remote) -E -Q"exec msdb.dbo.sp_start_job 'getdate'"
or

Then run this query from the remote server using ssms.

select [jh].[message], [jh].[run_date], [jh].[run_time] from sysjobs j join sysjobhistory jh on [j].[job_id] = [jh].[job_id]
where [j].[name] = 'getdate' and step_name = '(Job outcome)'

The message field should give you the security context. e.g. the account that tried to run the job.

smokey
Post #1430710
Posted Monday, March 18, 2013 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 9:47 AM
Points: 2, Visits: 15
Hello,

Before I saw your post I created a login for processes and gave it a proxy and was able to run it from one of my servers. The server that I ran it from had bids on it. I then tested it from a server that did not have BIDS on it and it does not run. Not sure where to go from here. I think the reason is my user and the other server do not have osql as they do not have a build of sql server on them. We do not want to have to install it on the user's machine. IS there any other way around this?
Post #1432203
Posted Tuesday, March 19, 2013 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 8:34 AM
Points: 7, Visits: 142
If you want a client computer initiate a server process then they require some interface. OSQL, SQLCMD, and BIDS are all interfaces. You can limit the sql installation features to say management tools - Basic or Complete. You will have to play around with it to figure out which component permits a client to execute the job and any other features you require.

An alternative is to create a scheduling system that removes the need to execute jobs on the fly. I don't recommend allowing users kick off jobs willy nilly. It starts with one job then inevitably escalates.

good luck
Post #1432687
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse