MS SQL Wildcard matching

Well, after writing so many queries in MS SQL over the years, normally I don't have to think to form queries that have conditions with wildcard characters for pattern matching in LIKE clause. "%" is somewhat favorite and have used it countless times. But when I needed it today I was surprised to notice so far I had never required to write a query to match wildcard as a literal character.

Today I needed to find all records in a table that contained string "%" in a field title and then replace "%" sign with word "-percent"

Doing this turned out to be much simpler than I thought. Just enclose the wildcard character in brackets to use it as a literal character

My select clause looked something like below

Select * from tablename Where title like '%[%]%'

Notice the percent sign that I would like to be checked as literal in title is enclosed in [] while % on both sides of [%] is used as wildcard to match any string before and after % sign

For replacing wildcard percent simply use the it as you would use any other character

update tablename set title = replace(title, '%', '-Percent' ) Where title like '%[%]%'

Note: Enclosing wilchard character in [] worked for me but as per microsoft article at link below
to search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided
So my Select statement should have been

select * from tablename where title like '%!%%' ESCAPE '!'

Reference: http://msdn2.microsoft.com/en-us/library/ms179859.aspx

Print job stuck and cannot delete

I sent few print jobs to our HP 1200 printer connected to home network via print server and noticed printer is showing Out of paper error. I think problem is somewhere in network connection which I decided to look into later but when I tried to cancel all jobs that were in the pritner queue all except one got deleted.

The stuck job can be deleted by stopping and restarting the spool service. To do this

1. Open Command Prompt
2. Run following command
   net stop spooler
3. Restart the service by running command
   net start spooler

Alternately try below

1. In Control Panel->Services, browse to the Spooler service and stop it. If you are using TCP/IP Printing Services, stop it also.
2. Delete the .spl and .shd files from %SystemRoot%\System32\Spool\Printers that have the approximate time and date of the print job causing the problem.
3. Restart the sevices that you stopped.

NOTE: if *.spl file(s) can not be deleted or renamed. Renaming the *.shd file and restarting the spooler service may be enough to clear the problem.

"Out of Range" Error

Monitor shows 'Out of Range' Error Message when computer starts:

My husband and I work as a team on a lot of computer hardware problems that I encounter at work. A big advantage for me having a hubbie working in the same field. He is always at the rescue when I am stuck at silly (or difficult) problem :-)

We use a couple of assembled computers that came with 3rd party hardware used in the manufacturing process of our company.

We upgraded video card driver and everything seemed OK but after restarting the computer it showed "Out of Range" error message on monitor.

Microsoft has resolution for this error at http://support.microsoft.com/kb/286331 but it did not work for me. There was no option to change refresh rate in safe mode or set adapter defaults nor did it allow us to roll back the driver.

However from safe mode we could delete video card device so when restarted the computer it detected a new hardware and installed windows default driver for the video card device allowing us to work in normal mode.

Here is what we did

1. Restart the computer, and while it is restarting, press F8 until the Startup menu appears. Press 3, and then press ENTER to start the computer in Safe mode
2. Go to Control Panel->System->Hardware->Device Manager-> Expand Display Adaptors->Delete device for the display card
3. Restart the computer in normal mode

Paypal Payflow Pro setup

If you get "The certificate chain did not validate, no local certificate found " error, the application needs access to "certs" folder under Verisign folder of the extracted SDK

You can make it accessible under windows OS using one of the following 2 methods

1. Copy entire "certs" folder to System32 ( C:\WINNT\Sytem32 or C:\Windows\system32) OR

2. Add Sytem Environment variable PFPRO_CERT_PATH with value set to complete path to "certs" folder

Managing System Environment Variable

OS: Windows 2000 or higher

System Environment Variables can be managed using one of the ways below

1. Start->Control Panel-> System->Under Advanced tab->Click Environment Variables button->Add, modify, delete system or user variables by clicking New, Edit or Delete buttons respectively . Reboot the system for changes to take an Effect.

2. System Environment Variables can be managed by making changes to the registry key Below

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment

• Run command->regedit
• Browse to the above registry key
  
• Add Variable -> Create a New Value under this Key
• Modify Variable-> Modify Value if it already exists under this key
• Delete Variable -> Delete a registry value for the variable
  
  Modifying registry requires a reboot for changes to take an effect

3. SetEnv command can be used to manage system environment variables and avoid reboot

Download a free tool at link below

http://www.codeproject.com/tools/SetEnv.asp

• Add or modify a system variable
  SetEnv –a [VARIABLENAME ] [VARIABLEVALUE]
  
  
• Add to a variable with multiple values, prefix value with % character
  SetEnv –a [VARIABLENAME ] %[VARIABLEVALUE]
  
  
• Add User Variable
  SetEnv –ua [VARIABLENAME ] [VARIABLEVALUE]
  
  
• Deleting a variable
  SetEnv –d [VARIABLENAME ] [VARIABLEVALUE]
  
  
• Deleting a value from multiple value variable
  SetEnv –d [VARIABLENAME ] %[VARIABLEVALUE]
  
  
• Deleting a user variable
  SetEnv –ud [VARIABLENAME ] [VARIABLEVALUE]

____________________________

Control Service from command line

You can remotely start, stop and query status of service from command line using NETSVC.EXE tool

For more information refer to
http://support.microsoft.com/default.aspx?scid=kb;EN-US;166819

Taking ownership of a file or folder in Windows XP

To take ownership of a folder

1. Right-click the folder that you want to take ownership of, and then click Properties.
2. Click the Security tab, and then click OK on the Security message (if one appears).
3. Click Advanced, and then click the Owner tab.
4. In the Name list, click your user name or group. If you want to take ownership of the contents of that folder, select the Replace owner on subcontainers and objects check box.
5. Click OK, and then click Yes when you receive the following message: You do not have permission to read the contents of directory folder name. Do you want to replace the directory permissions with permissions granting you Full Control?
All permissions will be replaced if you press Yes.Note folder name is the name of the folder that you want to take ownership of.
6. Click OK, and then reapply the permissions and security settings that you want for the folder and its contents.

To take ownership of a file
1. Right-click the file that you want to take ownership of, and then click Properties.
2. Click the Security tab, and then click OK on the Security message (if one appears).
3. Click Advanced, and then click the Owner tab.
4. In the Name list, click Administrator, or click the Administrators group, and then click OK.
The administrator or the Administrators group now owns the file. To change the permissions on the files and folders under this folder, go to step 5.
5. Click Add.
6. In the Enter the object names to select (examples) list, type the user or group account that you want to give access to the file. For example, type Administrator.
7. Click OK.
8. In the Group or user names list, click the account that you want, and then select the check boxes of the permissions that you want to assign that user.
9. When you are finished assigning permissions, click OK

For more information refer to microsoft article
http://support.microsoft.com/kb/308421