CJSmith dot me

I dump stuff I find useful here

Author: Chris Smith

Use T-Sql to find triggers on specific database

Published / by Chris Smith / Leave a Comment

Credit: Joe Stefanelli on Stack Overflow

I wanted a simple way to find triggers on a specific database, Joe Stefanelli posted SQL which did exactly what I wanted.

Display the old USB Device Removal Tab

Published / by Chris Smith / Leave a Comment

Credit:
Waseemulla S on Microsoft Answers

I had a USB device that I could not unload, I prefer the old dialog box anyway and found it is still possible to show this box in Windows 10. The forum post above has the answer, however according to someone was a very poor answer. For me the answer from “Waseemulla S” worked exactly as expected, it was then I found the USB Caddy I had plugged in didn’t support stopping the USB device in this way. Waseemulla S’s steps are below (I see no need to change it)

Create a batch file that will prompt you to “Safe to remove Hardware” with just a click.
Open notepad and type

Click save as and change file type to all files, type the file name Filename.bat (Where Filename is the name of your choice)
Double click the file anytime you want to get “Safe to remove Hardware” prompt.

Batch script to help with launching files for correct version of Windows

Published / by Chris Smith / Leave a Comment

We encountered an issue during a migration to Windows 10 where I had missed a program which had hard coded links, these no longer worked due to Microsoft Access being a newer version.
I generated a simple script so we could change the hard coded links to allow it to select the correct version, all we do is call the script and add the access filename. It only handles Windows 7 and 10, but you can easily add you own section before goto sub_wrongos by using version numbers from MSDN. It can also be amended to launch something else.
I used start instead of call or simply the command line as this would leave the Windows Command Prompt window in the background until the application it launched was closed.
This isn’t ideal and meant to be temporary, so when fully migrated we will hard code them back to our Office 2016 versions of Access Front ends.

Generate a text file with subfolders in a location on explorer

Published / by Chris Smith / Leave a Comment

I needed to generate a list of folders and their subfolders, I used a simple command for dir command and slapped into a text file.
Below replaced with the folder you want to get the list from (i.e C:\Users\Default) and replace with where you want to save a textfile (i.e C:\textfile.txt).

Credit: SS64

Find unused MSSQL indexs on a database

Published / by Chris Smith / Leave a Comment

Credit: Basit’s SQL Server Tips

The following is very handy to check for any unused index’s on a MSSQL database, this should only be run once the database has been up for at least a week or when all scheculed jobs have been allowed to run at least once. The only change I made is to display the table and index name.

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

Published / 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

More info on SQL system tables here:
MS Docs
MS Docs

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

Published / 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 KEC 1582t PCI card which worked fine on Windows XP Professional SP3.

The way around this was to use a legacy driver which is included as part of Windows 7 (It appears to work with a number of other fire wire cards as well for slow performance).

Windows 8 doesn’t have this driver as part of the OS and it can be obtained from Microsoft Support
The original MS article announcing this seems to show it doesn’t work for Windows 10, however a website called Studio 1 seems to show it does. I haven’t tested this as the Kodak i160 Scanner has no Windows 10 support, the parts are difficult to get hold of and therefore we are replacing them.

Maintain MSDB email tables

Published / 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 of emails, attachments and logs are kept. I first run a stored procedure designed to maintain the tables and then directly delete from the tables (I would expect these to delete nothing).

Find processes which have been using tempdb

Published / 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 a select statement it can of course be amended to your requirements (Adding an order by clause or just selecting the columns you require).

You should see something like the following, rather than kill of the transaction if it is a user try and educate the user to come out of transactions when finished.

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

Published / 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 characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.