SQL Server 2008 – Find which stored procedure is part of a scheduled

Published November 25, 2017 / by Chris Smith / Leave a Comment

Find a stored procedure (or any text) inside a scheduled on on SQL Server 2008 and 2012 by querying the sysjobs and sysjobsteps system tables. The following provides the name of the job and the step name. It uses the standard % wildcard so replace myjob in “%myjob%” below

SELECT name, "description", "enabled", database_name,step_name,step_id,command
       FROM "msdb"."dbo"."sysjobsteps" t1
              INNER JOIN "msdb"."dbo"."sysjobs" t2 on t1.job_id = t2.job_id
       where t1."command" LIKE '%myjob%'

SELECT name, "description", "enabled", database_name,step_name,step_id,command

FROM "msdb"."dbo"."sysjobsteps" t1

INNER JOIN "msdb"."dbo"."sysjobs" t2 on t1.job_id = t2.job_id

where t1."command" LIKE '%myjob%'

More info on SQL system […]

Getting a Kodak i160 to work on a KEC 1582t card in Windows 7

Published November 12, 2017 / by Chris Smith / Leave a Comment

We had an issue where a Kodak i160 scanner would appear when a Windows 7 machine booted but then disappear after the Kodak i160 scanner went to sleep. The Kodak scanner was using a Kofax Virtual Rescan driver, after some checking it turned out the machine was using a standard fire wire driver for the […]

Maintain MSDB email tables

Published October 29, 2017 / by Chris Smith / Leave a Comment

Credits: SQL Server Central SQL Authority Microsoft Docs Microsoft Docs We recently had an issue where MSDB was growing very quickly, it turned out it was due to a huge increase in data we was directly emailing to users. We now run a weekly job just to maintain the MSDB email tables as whole copies […]

Find processes which have been using tempdb

Published October 29, 2017 / by Chris Smith / Leave a Comment

Credit: Microsoft TechNet On some occasions a few MS SQL databases have exhausted tempdb space, this was due to users leaving computers running for weeks on end and the program using different isolation levels or open transactions. I use the following in SQL in see which process ID have been running the longest, as its […]

Sage Line 500 EDI IN Intermediary tables inner join on sql query

Published August 12, 2017 / by Chris Smith / Leave a Comment

On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many […]

Display advanced properties of scheduled tasks in command line

Published July 22, 2017 / by Chris Smith / Leave a Comment

The following command requests a detailed display of the tasks on the local computer. It uses the /v parameter to request a detailed (verbose) display and the /fo LIST parameter to format the display as a list for easy reading. You can use this command to verify that a task you created has the intended […]

A simple batch file which compresses every folder into a 7z Archive

Published July 22, 2017 / by Chris Smith / Leave a Comment

For backing up old files I tend to sort into lots of different folders, anything I am finished with I put in a 7zip Archive The following batch makes use of a For loop in the batch, uses 7-zip with Ultra Compression and names the archive the same as the folder.

for /D %%X in (*) do "c:\Program Files\7-Zip\7z.exe" a -mx9 "%%X.7z" "%%X\"
pause

1 2	for /D %%X in (*) do "c:\Program Files\7-Zip\7z.exe" a -mx9 "%%X.7z" "%%X\" pause

Credits: SS64

Rebuild a V1 DBCapture Database Queue

Published July 16, 2017 / by Chris Smith / Leave a Comment

We have an issue where on a rare occasion a scan from V1 DBScanner to DBCapture can corrupt the queue and cause DBCapture Admin to state it is unable to open a document rendering the whole application unusable. I have a batch script to perform some maintenance. Over time I have commented out lines which […]

Updating a network location in Windows 2012

Published May 14, 2017 / by Chris Smith / Leave a Comment

I had an issue where a server which was joined to a domain had incorrectly set its network as Public. A TechNet article and TenForum article provided the answer (As it states Windows 10 it also applies to Server 2012) so I have placed credits above the steps I followed. Following a re-occurrence I have […]

Filtering in Event Viewer Windows Server 2008 onwards

Published May 14, 2017 / by Chris Smith / Leave a Comment

The Event Viewer from Server 2008 onwards is XML based. Filters based on XML syntax can be used such as:

<QueryList>
  <Query Id="0" Path="System">
    <Select Path="System"> 
                 *[EventData[Data and (Data='<Search Parameter>')]] 
              </Select>
  </Query>
</QueryList>
 
<QueryList>
  <Query Id="0" Path="Application">
    <Select Path="Application">
                 *[EventData[Data and (Data='<Search Parameter>')]] 
              </Select>
  </Query>
</QueryList>

*[EventData[Data and (Data='<Search Parameter>')]]

</Select>

</Query>

</QueryList>

*[EventData[Data and (Data='<Search Parameter>')]]

</Select>

</Query>

</QueryList>

You can search for anything which appears in the data section such as service names, error messages etc..

- <EventData>
  <Data Name="param1">Service Display Name</Data> 
  <Data Name="param2">Details</Data> 
  <Data Name="param3">Details</Data> 
  <Data Name="param4">Service Name</Data> 
  </EventData>
  </Event>

- <EventData>

<Data Name="param1">Service Display Name</Data>

<Data Name="param2">Details</Data>

<Data Name="param3">Details</Data>

<Data Name="param4">Service Name</Data>

</EventData>

</Event>

Notes: MS Technet

CJSmith dot me

I dump stuff I find useful here