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

Add a Network Printer

To add a network printer from command line

To run a command from machine on which printer needs to be added

rundll32 printui.dll,PrintUIEntry /in /n \\PRINTSERVER\NETWORKPRINTER

To run a command from server or other machine on network

rundll32 printui.dll,PrintUIEntry /in /c\\CLIENTMACHINETOINSTALLON /n \\PRINTSERVER\NETWORKPRINTER

MS SQL

Some SQL queries that I find useful

Reset 'sa' password from query analyzer

Sp_password @new = 'mynewpassword', @loginame = 'sa'

Search for a string in stored_procedures

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%STRING_TO_SEARCH%'
AND ROUTINE_TYPE='PROCEDURE'

Get name of the store proceture containting the specified string

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%STRING_TO_SEARCH%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

When you restore a database backup to another server, if you experience a problem with orphaned users refer to http://support.microsoft.com/kb/274188/

Endicia ES 2200 Weigh Scale

Finally, after spending couple of days on web trying to find solution I was able to read weight from Endicia ES 2200 digital scale in my custom application.

I work for a dot com company and we use Endicia software to print postage labels for USPS shipments. So far we have been shipping USPS shipments with only Flat rate option so exact weigh was not required. Because of increase in postal rates on May 14, 2007 instead of increasing our shipping rates we decided to start shipping using First class option so we could actually reduce shipping rates and keep our customers happy. However this required me to make changes in shipping module to be able to pass exact weight to Endicia interface.

Because of high volume of orders that we need to ship on daily basis to save time we have built a custom application that works as a layer over Endicia's DAZzle and use DAZzles XML interface. So all the user needs to do is scan the order and get a label printed without having to select or answer prompts from DAZzle.

DAZzle had Weigh option which read the weight but that would have required me to set prompting ON and user would have required to click Weigh button first and then Print button to print label so 2 extra buttons to click for every package and that would have slowed down entire shipping process.

To avoid those extra steps it was required for me to be able to read weight within my application and send it in XML along with rest of the data.

We had this Endicia ES 2200 scale but no technical documentation, the documentation that came with it only specified how to connect it so that it works with in DAZzLe. Endica tech support did not have any technical documentation for the scale, only information they had which got me started was ES2200 scale is compatible with other standard serial scales from NCI

After hours on web and lots of trial error, here is what I was able to find and that worked for me to be able to read weight successfully

1. Scale is Weigh-Tronix/NCI 7010 compatible

2. I found the following driver at stamps.com
http://www.stamps.com/support/technical/hardware/10lb-scale/

3. Then I found following documentation for ActiveX control that is installed with the driver above.
http://www.salterbrecknell.com/pdfs/WTCommSclV2.pdf