CJSmith dot me – Page 2 – I dump stuff I find useful here

Display the old USB Device Removal Tab

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

“rundll32 shell32.dll,Control_RunDLL hotplug.dll”

1	“rundll32 shell32.dll,Control_RunDLL hotplug.dll”

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

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.

	REM This script will need to be passed an Access Front End Name, it will then based on the Windows version launch the correct Access Front End
	REM Call with <scriptname.bat> <AccessFrontEndName> without an extension (Assume compiled as an accdr)
	set arg1=%1
	REM Check Windows Version is 10
		ver | findstr /i "10\.0\." > nul
		IF %ERRORLEVEL% EQU 0 goto sub_windows10
	REM Check Windows Version is 7
		ver | findstr /i "6\.1\." > nul
		IF %ERRORLEVEL% EQU 0 goto sub_windows7
			goto sub_wrongos			
:sub_windows10
	ECHO Windows 10 version launching
	start <Accessfilelocation>\%1.accdr
	EXIT
:sub_windows7
	ECHO Windows 7 version launching
	start <Accessfilelocation>\%1.accdr
	EXIT
:sub_wrongos
	ECHO THIS SHOULD NOT SHOW!
	EXIT

REM This script will need to be passed an Access Front End Name, it will then based on the Windows version launch the correct Access Front End

REM Call with <scriptname.bat> <AccessFrontEndName> without an extension (Assume compiled as an accdr)

set arg1=%1

REM Check Windows Version is 10

ver | findstr /i "10\.0\." > nul

IF %ERRORLEVEL% EQU 0 goto sub_windows10

REM Check Windows Version is 7

ver | findstr /i "6\.1\." > nul

IF %ERRORLEVEL% EQU 0 goto sub_windows7

goto sub_wrongos

:sub_windows10

ECHO Windows 10 version launching

start <Accessfilelocation>\%1.accdr

EXIT

:sub_windows7

ECHO Windows 7 version launching

start <Accessfilelocation>\%1.accdr

EXIT

:sub_wrongos

ECHO THIS SHOULD NOT SHOW!

EXIT

Generate a text file with subfolders in a location on explorer

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).

dir /b /s /a:d "<Location>\*" > <TextFile>

1	dir /b /s /a:d "<Location>\*" > <TextFile>

Credit: SS64

Find unused MSSQL indexs on a database

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.

SELECT o.name,i.name,u.*
FROM [sys].[indexes] i
INNER JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID)
    AND i.[index_id] = u.[index_id]
    AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
    AND i.[type_desc] <> 'HEAP'
    AND i.[name] NOT LIKE 'PK_%'
    AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
    AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC

SELECT o.name,i.name,u.*

FROM [sys].[indexes] i

INNER JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)

LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID)

AND i.[index_id] = u.[index_id]

AND u.[database_id] = DB_ID() --returning the database ID of the current database

WHERE o.[type] <> 'S' --shouldn't be a system base table

AND i.[type_desc] <> 'HEAP'

AND i.[name] NOT LIKE 'PK_%'

AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0

AND u.[last_system_scan] IS NOT NULL

ORDER BY 1 ASC

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

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 tables here:
MS Docs
MS Docs

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

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

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).

use msdb
go
declare @DeleteToDate datetime
--I have set this to keep 60 days
set @DeleteToDate = DATEADD(d, -60, getdate())
--Print the date for logs
print @DeleteToDate
print 'Using inbuilt script to maintain tables'
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteToDate
--Line below is SQL Server 2008 only
--EXECUTE msdb.dbo.Sysmail_delete_log_sp @logged_before = @DeleteToDate
--maintain sysmail_allitems
print 'maintain sysmail_allitems'
DELETE FROM msdb.dbo.sysmail_allitems
   where sent_date < @DeleteToDate
--maintain log file
print 'maintain sysmail_log'
DELETE FROM msdb.dbo.sysmail_log
   where log_date < @DeleteToDate
--remove old emails
print 'maintain sysmail_mailitems'
delete from msdb.dbo.sysmail_mailitems
   where sent_date < @DeleteToDate
--remove retries (Hopefully this shouldn;'t be to high a number)
print 'maintain sysmail_send_retries'
delete from msdb.dbo.sysmail_send_retries
       where last_send_attempt_date < @DeleteToDate
--remove attachments (If you are removing the email no point in keeping the attachments)
print 'maintain sysmail_attachments'
DELETE FROM dbo.sysmail_attachments
       WHERE last_mod_date < @DeleteToDate

use msdb

declare @DeleteToDate datetime

--I have set this to keep 60 days

set @DeleteToDate = DATEADD(d, -60, getdate())

--Print the date for logs

print @DeleteToDate

print 'Using inbuilt script to maintain tables'

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteToDate

--Line below is SQL Server 2008 only

--EXECUTE msdb.dbo.Sysmail_delete_log_sp @logged_before = @DeleteToDate

--maintain sysmail_allitems

print 'maintain sysmail_allitems'

DELETE FROM msdb.dbo.sysmail_allitems

where sent_date < @DeleteToDate

--maintain log file

print 'maintain sysmail_log'

DELETE FROM msdb.dbo.sysmail_log

where log_date < @DeleteToDate

--remove old emails

print 'maintain sysmail_mailitems'

delete from msdb.dbo.sysmail_mailitems

where sent_date < @DeleteToDate

--remove retries (Hopefully this shouldn;'t be to high a number)

print 'maintain sysmail_send_retries'

delete from msdb.dbo.sysmail_send_retries

where last_send_attempt_date < @DeleteToDate

--remove attachments (If you are removing the email no point in keeping the attachments)

print 'maintain sysmail_attachments'

DELETE FROM dbo.sysmail_attachments

WHERE last_mod_date < @DeleteToDate

Find processes which have been using tempdb

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).

SELECT *
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

SELECT *

FROM sys.dm_tran_active_snapshot_database_transactions

ORDER BY elapsed_time_seconds DESC;

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

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.

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
 FROM "scheme"."eiorhdm" t1
  INNER JOIN "scheme"."eiordtm" t2
   ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity

FROM "scheme"."eiorhdm" t1

INNER JOIN "scheme"."eiordtm" t2

ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

Display advanced properties of scheduled tasks in command line

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 recurrence pattern.
Before running increase the command prompts screen buffer (Right click the Window, go to properties, go to Layout and set the Screen Buffer Height to 9999). If this is not done you will find due to the amount of Windows scheduled tasks, that the tasks you want are cleared from thje screen buffer.

schtasks /query /fo LIST /v

1	schtasks /query /fo LIST /v

SchTasks.exe displays a detailed property list for all tasks. I normally will then copy everything into the clipboard (Ctrl + A) and pout it into a text editor, I then delete anything after “Folder: \Microsoft” so I then only have my list of scheduled tasks.

Credit: Technet