I like the idea of using the "Schedule Job" function when scripting the creation of objects. However, in experimenting with this, I found that the script that is passed to the SQL Server Scheduled Job is incorrectly formatted - perhaps not including the correct CR and LF - not sure. Inevitably the job will fail because the formatting is wrong with the following error:
Line 2: Incorrect syntax near 'GO'. [SQLSTATE 42000] (Error 170) Line 4: Incorrect syntax near 'GO'. [SQLSTATE 42000] (Error 170) Line 6: Incorrect syntax near 'GO'. [SQLSTATE 42000] (Error 170) Line 16: Incorrect syntax near 'GO'. [SQLSTATE 42000] (Error 170). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
After copying the T-SQL from the command window and pasting into a Text Editor, I manually insert the carriage returns at the end of each line using the Enter key and then paste it back into the command window. This fixes the issue and the object is created.
Is there a formatting step in Options that I am missing?
Here is the script that has been created within the Job, copied from the Command window in SQL Server and pasted below. When pasted, it looks like this:
USE [Northwind]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE TABLE [dbo].[TerritoriesTest] ( [TerritoryID] nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [TerritoryDescription] nchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RegionID] int NOT NULL, PRIMARY KEY NONCLUSTERED ([TerritoryID] ASC) WITH FILLFACTOR = 100 ON [PRIMARY], FOREIGN KEY ([RegionID]) REFERENCES [dbo].[Region] ( [RegionID] ) ) ON [PRIMARY]; GO
If this is run, in a Query Editor window, it will work fine. But, notice in the attached image that the actual formatting within the Job Command of is much different - perhaps it is missing the proper CR/LF, etc. - not sure.
I would really like to use this feature, but at the moment it does not appear to be working correctly.