Change Logical Filenames

Change logical filenames of MS SQL databse

USE MASTER
GO
ALTER DATABASE [DatabaseName]
MODIFY FILE
(NAME = [OldFileName_Data], NEWNAME='NewFileName_Data')
GO

ALTER DATABASE [DatabaseName]
MODIFY FILE
(NAME = OldFileName_Log, NEWNAME=''NewFileName_Log')
GO

ObjectDataSource could not find a non-generic method

I got this error"ObjectDataSource could not find a non-generic method" when trying to update or delete a record from GridView that uses ObjectDataSourse whose data source is configured to a tableadapter of a dataset.

I fixed this by specifying parameter "DataKeyNames" in a gridView control and changing OldValuesParameterFormatString="original_{0}" to OldValuesParameterFormatString="{0}" parameter of ObjectDataSource.

Search for a keyword in stored procedures

Use either of the commands below to list all stored procedures that contain the given keyword.

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


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

Reset Identity Column Value

DBCC CHECKIDENT statement can reset/reseed the identity column value.

e.g to set the current identity value in "MyTable" to 500 statement will be

DBCC CHECKIDENT (MyTable, RESEED, 500)

Following command can also be used to reset the identity column value if all the records in table can be deleted.

TRUNCATE TABLE MyTable

Set Up Email in Outlook 2000

1. Go to Tools Accounts
2. Click on Add Mail
3. In Display Name, type your name. Click "Next"
4. Enter E-mail address
5. My incoming mail server is a "POP3".
6. Incoming Mail (POP3 or IMAP) server: mail.yourdomain.com ; Outgoing Mail (SMTP) server: mail.yourdomain.com
7. Click "Next"
8. Enter account name and password; Click "Next".
9. Finish the wizard keeping rest of the settings to default value

Can not save hosts file in Windows Vista

When you try to save Hosts or lmhosts file in Microsoft Windows Vista you get access denied error.

Work arround to this issue is specifically opening notepad as administrator even if you have logged in to computer with administrative privilages.

• Click Start -> All Programs -> Accessories -> right-click Notepad, and then click Run as administrator -> Enter administrator password if requested
• Open the Hosts file or the Lmhosts file -> make the necessary changes -> Save

http://support.microsoft.com/kb/923947

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