Invoke LARGE SQL Script from a SQL Job

Question

Post reply

Invoke LARGE SQL Script from a SQL Job

Express12

SSCoach

Points: 18514
More actions
July 7, 2005 at 9:04 am

#167444

Need to invoke a long (1500 line) SQL Script from a SQL Job. The script contains all sorts of stuff including "If Exists.. drop Procs.. ", "Create Procs ..." , "GO" statements galore etc.
I am familiar w/ the invocation of a stored proc from a Job via the command:
EXECUTE master..xp_cmdshell 'osql -E -Qadministrative..spr_Updatestats_all_db -oC:\CERSAsql\SQLReports\UpdateStatsAllDB.rpt'
How do I build a SQL JOB to invoke a HUGE SQL Script that will not terminate when it encounters GO's etc. (I can't build the Script into a SQL Proc because the script itself CREATEs dozens of Procs)
thx in advance.
BT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Try Saving the sql script to file, having the job call osql and osql runs the script.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Bill, Ray :

Not really sure OSQL allows GO. The parameter description of OSQL Utility in BOL says:

......(Note that the query statement should not include GO).....

Did you try a job with many steps? Tell us more what you are trying to accomplish, maybe we will have other ideas

Yelena

Regards,Yelena Varsha

Express12 SSCoach Points: 18514 More actions · Answer 3

Yelena - it worked for me. (w/ the dozens of GO statements embedded throughout the script) I too was surprised that it completed successfully.

BT

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

Hello,

The SQL agent will also support batch separators (GO) in the T-SQL type of job step.

JG

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

try this :

BEGIN TRANSACTION

DECLARE @JobID BINARY(16)

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'Run_OSQL') > 0

PRINT N'The job "Run_OSQL" already exists so will not be replaced.'

ELSE

BEGIN

-- Add the job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Run_OSQL', @owner_login_name = N'sa', @description = N'I hope i m not the onlyone using these comments', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0