SQL Server combining LIKE with IN (SELECT LIKE IN query)

System Requirements

  • SQL Server

 

The Problem

This was one of those “it should be obvious” moments.

Dating back to 2004 in its current iteration, the hpcfactor.com membership database has been blighted by SPAM. We are no different to any web community in this regard. A can be a particular problem as many accounts that we may miss are registered and (if they survive) become a valuable commodity to sell as trojan accounts for use in mischief at a later date.

The moderation team at HPC:Factor does a great job of ensuring that these ‘pay per post’ (usually from Indian IP’s) urchins are wasting their time, but it does not stop them from trying. At one point we were having to content with several hundred a day, and had to shutdown registration completely. A custom layer that I wrote to sit on-top of the BBS registration system has done a good job to convert that SPAM torrent into a trickle of a “couple of times a month”.

Over the years, I have added more and more banned email domains to a ban list that is maintained in the user registration database. It occurred to me that as that list has increased, the historic pool of user account has never been re-parsed against the updated banned domains list.

Sounds simple right? Fire up SQL Server Management Studio and create a SQL SELECT LIKE IN query… is it that simple?

 

The Solution

Yes actually it is.

The obvious logic that you want to follow is to take this syntax

SELECT id, username, emailaddress
FROM members
WHERE emailaddress IN (
   SELECT emailaddress FROM bannedemails
)

… and merge it with a LIKE statement to create a SELECT LIKE IN query, something like

SELECT id, username, emailaddress
FROM members
WHERE emailaddress LIKE IN (
   SELECT '%' + emailaddress FROM bannedemails
)

Unfortunately however this syntax is completely illegal.

The answer is to perform the LIKE/IN as part of an INNER JOIN

SELECT members.id, members.username, members.emailaddress
FROM members
INNER JOIN bannedemails ON members.emailaddress LIKE '%' + bannedemails.emailaddress

 

Performance

This is a not a “real time” friendly SQL expression. The inefficiency of using LIKE evaluation becomes very apparent as the record set scales. The HPC:Factor database had 2,271 banned email domains and 30,336 user. Giving a total record iteration and evaluation count of 68,893,056 rows.

The total query execution time for this on was 2 minutes 13 seconds (ouch). Having said that, on reviewing why it took that long, I realised that at some point in time I was being extremely stingy with the SQL Server VM. It only had access to 2 CPU cores and 12 GB of RAM. The hypervisor server itself has access to a 10 core hyper-threaded Xeon.

 

The Result

I have a lot of respect and sympathy for people who use VPNs and temporary email addresses and I genuinely understand why they feel the need (or in some cases must, depending on what part of the world you are in).

I saying that, I also have to deal with the issue from the other side of the equation. If the website is having a bad day, it’s usually because of something coming from one or both of these camps. As ever in life, a minority will insist on spoiling a good thing for everyone else.

After running the query, we had only missed 8 user accounts – credit to the moderation team. The oldest account was 26/09/2006 and my favourite that we probably should not have missed was “mrputin” from January 2013.

“StartServiceCtrlDispatcher failed (error 6)” is returned from attempting to start SQL Server Agent and “System.NullReferenceException: Object reference not set to an instance of an object.” is returned when attempting to start SQL Server Agent in SQL Server Management Studio

System Requirements:

  • Microsoft SQL Server 2008

The Problem:

It’s another instance of a new SQL Server installation on a newly installed Windows Server system that refuses to do what it is supposed to do. It seems to me that this is the rule rather than the exception, but I digress.

If you pull up a command prompt and attempt to manually start the SQL Server Agent process e.g.

"F:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLAGENT.EXE" -i MSSQLSERVER

You will receive

StartServiceCtrlDispatcher failed (error 6)

Similarly, if you attempt to start the SQL Server Agent inside SQL Server Management Studio you will receive the ubiquitous

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.

Server stack trace:
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.FindObjectExplorerFrame()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.GetObjectExplorer(Boolean activateWindow)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ShowError(Exception e)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

In addition if you check the SQL Server Logs, the current log for SQL Server Agent will be blank or will have nothing logged recently (this is important)

More Info

If you are seeing up to date events in the SQL Server Agent log, then this fix likely does not apply to your situation. If you have a blank or a stationary log file then chances are it does.

Basically, the SQL Server Agent is refusing to start because it cannot write TO the log file. Obviously it is extremely hard to have the process termination log or output that fact otherwise Microsoft would have done it already… sigh.

The Fix

It is probably either or both of:

  1. Check the error log file path for accuracy
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\SQLServerAgent
    Reg_SZ: ErrorLogFile
  2. Check the file / folder permissions for the path stated above, the SQLAGENT.OUT file and the SQLAGENT.# files. You can either reset them so that the SQL Server Agent’s process account has Full Control access or (depending on your logging policy) delete the files and SQL Server Agent will re-create the logs at startup

Performing WUSU 3.0 database maintenance (database re-indexing) via WsusDBMaintenance under Windows Server 2008

System Requirements:

  • Windows Software Update Services 3.0
  • Windows Server 2008, 2008 R2

The Problem:

Microsoft recommend that you perform monthly maintenance on your WSUS database to keep it in good order. The following pages represent the instruction for undertaking this activity

This document simply seeks to clarify the process when using Windows Internal Database (instead of SQL Server) under Windows Server 2008/R2.

The Fix

The following summarises the steps involved in the process.

  1. Install the SQL Server Native Client for your processor on the WSUS server
  2. Install the SQLCmd utility for your processor on the WSUS server
  3. Copy & Paste the T-SQL code from Re-index the WSUS 3.0 Database into a notepad file and save it on c:\ as WsusDBMaintenance.sql (or download here)

If you wish to automate or schedule the task:

  1. Create a .cmd file on your desktop
  2. Enter the following into it
"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" -I -i"c:\WsusDbMaintenance.sql" -S "np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"
  1. Note that the version of the SQL Server SQLCmd that you install will need to be reflected in the version number (90 above). 90 = SQL Server 2005, 100 = SQL Server 2008 and so on.
  2. Right click the .cmd file and select “Run as Administrator” to launch it through an elevated command prompt
  3. If you setup a scheduled task for this, remember to set the always run with full permissions option.

If you just want to run it once:

  1. Open an elevated command prompt
  2. Enter the command:
"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" -I -i"c:\WsusDbMaintenance.sql" -S "np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"
  1. Note that the version of the SQL Server SQLCmd that you install will need to be reflected in the version number (90 above). 90 = SQL Server 2005, 100 = SQL Server 2008 and so on.

When attempting to start the Microsoft Search service against SQL Server 2000 the event log reports error EID: 7003 “The Microsoft Search Service depends upon the following nonexistent service: NTLMSSP”

System Requirements:

  • Windows 2000, XP, 2003

The Problem:

When attempting to start the Full Text Catalogue services as part of SQL Server 2000, and trying to start the Microsoft Search service associated with it you receive an Event ID 7003 in the event log

The Microsoft Search Service depends upon the following nonexistent service: NTLMSSP

Event ID 7003

In addition the ‘Full-Text Indexing…’ option in Enterprise manager will remain unavailable.

EM Gray Option

More Information:

This is happening because there is an unstated dependency in the Service Manager’s dependency list that for whatever reason is not registering or starting on your server.

If you are seeing other service start errors you need to troubleshoot those first, particularly if they involve RPC or NTLM.

A second reason why you may be seeing this is because either your server was installed without, or the server has had removed the “Client for Microsoft Networking” and “File and printer sharing for Microsoft Networks” services from the Network interface service and protocol bindings list in the control panel.

A third reason is that the settings for a dependency have become corrupt and need to be replaced/repaired.

The Fix

In my case when I ran into this problem the system was completely missing the Microsoft networking services as it had been installed on a public facing computer. The problem dependency was the NTLM SSP or “NT LanManager Security Support Provider” which is required for SQL communications on non-named-pipe connections. The workstation service is required for named-pipe use incidentally.

The fix is to copy the registry settings for the missing service from a working computer, or in the case of the NTMLSSP copy the following into a .reg and Add it – note that this is the Windows 2000 Server SP4 version and may not be the same on Workstation, XP or 2003.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NtLmSsp]
“Type”=dword:00000020
“Start”=dword:00000003
“ErrorControl”=dword:00000001
“ImagePath”=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,00,\
74,00,25,00,5c,00,73,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,6c,\
00,73,00,61,00,73,00,73,00,2e,00,65,00,78,00,65,00,00,00
“DisplayName”=”NT LM Security Support Provider”
“ObjectName”=”LocalSystem”
“Description”=”Provides security to remote procedure call (RPC) programs that use transports other than named pipes.”

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NtLmSsp\Security]
“Security”=hex:01,00,14,80,a0,00,00,00,ac,00,00,00,14,00,00,00,30,00,00,00,02,\
00,1c,00,01,00,00,00,02,80,14,00,ff,01,0f,00,01,01,00,00,00,00,00,01,00,00,\
00,00,02,00,70,00,04,00,00,00,00,00,18,00,fd,01,02,00,01,01,00,00,00,00,00,\
05,12,00,00,00,20,02,00,00,00,00,1c,00,ff,01,0f,00,01,02,00,00,00,00,00,05,\
20,00,00,00,20,02,00,00,03,00,00,00,00,00,18,00,8d,01,02,00,01,01,00,00,00,\
00,00,05,0b,00,00,00,20,02,00,00,00,00,1c,00,fd,01,02,00,01,02,00,00,00,00,\
00,05,20,00,00,00,23,02,00,00,03,00,00,00,01,01,00,00,00,00,00,05,12,00,00,\
00,01,01,00,00,00,00,00,05,12,00,00,00

Once imported , reboot the machine and the Microsoft Search Service should start itself right up. Don’t feel tempted to change the service log-on for the Microsoft Search service away from the LocalSystemAccount, it will not work!.