Running the below script will output the T-Sql required to disable all enabled jobs on the SQL Server instance where the job name contains a specific keyword. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.
For example, if you change the text value ‘PLACEHOLDER’, for the variable @KeyWord, to ‘Backup’ then any job which has ‘Backup’ in its name will have T-Sql generated to disable it. You could change the PRINT to EXEC but I wouldn’t recommend it.
DECLARE @KeyWord AS VARCHAR(256)
SET @KeyWord = 'PLACEHOLDER'
SET NOCOUNT ON
DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)
SET @JobName = ''
INSERT INTO @Job (JobName)
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
AND j.NAME LIKE '%' + @KeyWord + '%'
ORDER BY j.NAME ASC
WHILE @JobName IS NOT NULL
SET @JobName = (
WHERE JobName > @JobName
SET @Sql = '
EXEC msdb.dbo.sp_update_job @job_name = ' + '''' + @JobName + '''' + '
,@enabled = 0;
Using visual studio left click just under design to focus on the report.
Now the report properties window should be visible in the bottom right of the screen.
Set the interactive height to 0 as shown below.
You will get the following warning sign below. Limiting the report to one page, by setting the interactive height to zero, means everything needs to be loaded all at once. This will create lag when a user loads the report and interacts with it.
If you are basically using SSRS as a means for end users to pull a data dump via excel, heed this warning. If you just have a few aggregated tables and you want them all on the same page you should be fine.
If you’ve been working towards a new deployment to a live database chances are you have written several scripts (possibly dozens) that have been developed/tested against the the development server.
Now the time has come to put the update live. Which would require executing each script against the live database.
This task can be automated by using a very handy batch script to run against the directory the files are saved in.
Caveat: This process does not take into account error handling or rollbacks, it’s just a simple example people can build on.
In order for this to work the files must have been named in a manner that the necessary order of execution corresponds to ascii sort order, i.e. 001_CreateTable.sql, 002_PopulateTable etc. This is standard practice for sql file naming conventions.
Simply create a .BAT file with the following command:
(Swap servername and databaseName for your required server and database names, TIP: SELECT @@servername can provide you with the full server name.)
for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and the command will loop and execute every SQL script in the folder.