Restoring SQL Server databases from .mdf files

USE master;
GO

-- first detach the exisitng databse if already exists

EXEC sp_detach_db @dbname = 'AdventureWorks';

-- attach .mdf file to the database
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',
@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf';

T-SQL's IsNumeric function problem

IsNumeric function in T-SQL can be used to determine if a value is a number.

However, ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

e.g. Select IsNumeric (' . ') returns 1

But if you try to convert it to decimal as

Select convert(decimal(9,2), ' .' ) it returns error " Error converting data type varchar to numeric"

As MSDN documentation (http://msdn.microsoft.com/en-us/library/aa933213(SQL.80).aspx) " ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types."

Work around is to try converting it to money as

select CAST(' .' as money)

select CONVERT (money, ' .')

The result is 0.0

If you need to convert number to decimal to fractions more than money can return then you may need to write user defined function as described at link below to check if number is really numeric.

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

An error occurred while validating. HRESULT = '80004005'

You get "An error occurred while validating. HRESULT = '80004005'" error while building Setup Project(S) in Visual Studio 2008 where as project (A) included in the setup build successfully.

This error occures if something is wrong with references of the project(A)

Follow steps below to troubleshoot.

• If you have multiple project outputs in your solution, identify the project that is giving the problem. You can do that by removing one project at a time from the Setup Project(S) until error goes away.
  


• Once project is identified, identify the reference that could be giving the problem.
  
  
  
  • Check if the project(A) is referencing to a project that has been removed from solution. - Remove such references if any.
  
  
  • Check if the Project(A) is referencing to a project that was moved to a diffenrent physical location after it was added as a reference. - Remove and add such references.
  
  


• Rebuild setup project after fixing the reference accordingly to see if error goes away.

Control Panel in Windows 7 OS

Create a new folder in windows explorer and rename it to
GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}.
This folder will then contain a lot of control panel functions.

Update: It is functional on Windows 7 64bit, 32bit and Windows Vista 32bit. Do not try it on Vista 64 bit as Windows Explorer may crash.

Vista x64 users, make a shortcut and set it to point to: explorer.exe shell:::{ED7BA470-8E54-465E-825C-99712043E01C}

Row_Number

In older SQL versions , to create row numbers we had to create a temprari tble with identity column

SELECT ROWID=IDENTITY(int,1,1) , OrderNo
INTO Order2 FROM Orders ORDER BY OrderDate


SQL 2005 and later support Row_Number that returns a squential number of a row in specifed order.

SELECT OrderNo, ROW_NUMBER() OVER(ORDER BY OrderDate DESC) AS 'RowNumber'
FROM Orders

Partition clause takes us one step further and numbers Rows in squential order for each patition.

SELECT OrderNo, State, ROW_NUMBER() OVER(Partition by State ORDER BY OrderDate DESC) AS 'RowNumber'
FROM Orders

1001, IL, 1
1005, IL, 2
1007, IL, 3
1002, IN, 1
1003, IN, 2

Modify productName, manufacturer in VS 2008 deployment project

Productname and manufacturer for deployment project can be modified through properties from setup project but if you right click the setup project and select Properties it pops up Property Pages that have only Build configuration properties. and no option to change product name or manufacturer.

A workaround for this is after selecting the Setup Project to open Property Window from Main menu select View->"Property Window" and it shows different set of properties to edit that contain product name and manufacturer.

IP Subnet Calculator

Very useful tool while configuring a subnet.

http://www.subnet-calculator.com/subnet.php