Formatting float to varchar in non scientific notation

When trying to convert a float value to varchar "Convert" returns string that represents the float value in sceientific notation.

e.g.

DECLARE @Float as float =1900000.0

SELECT CONVERT (varchar(50),@Float)

Results
---------
1.9e+006

However if you would like a string "1900000.0" you can use one of the methods below

DECLARE @Float as float =1900000.0

SELECT CONVERT(varchar(100), CAST(@Float AS decimal(38,1)))
SELECT ltrim(STR(@Float, 50, 1))

Both of them return a string
--------------------------
1900000.0

Find dependencies on a table(MS SQL)

The following script lists all the procedures that reference a given table name, along with referenced columns. (Does not work on dynamic queries)

DECLARE @TableName varchar(100)
SET @TableName = 'MyTable'

SELECT
SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id)
,SourceObject = OBJECT_NAME(sed.referencing_id)
,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME())
,ReferencedSchema = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
,ReferencedObject = sre.referenced_entity_name
,ReferencedColumnID = sre.referenced_minor_id
,ReferencedColumn = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName

Google Apps Standard Edition

Link to set up domain for Google Apps Standard Edition is as below http://www.google.com/a/cpanel/domain/new

MyMobiler-nice tool to remote control a mobile device

Nice tool to have when developing mobile applications. It works as remote desktop for your mobile device making testing on actual device easier.


MyMobiler v1.25 (02/07/2008) - FREEWARE

* View your mobile screen on your desktop.
* Control your mobile by using desktop keyboard and mouse.
* Copy/Cut/Paste text between mobile and desktop.
* Capture mobile screen.
* Drag and drop files to your mobile.
* Support ActiveSync / IP Connection
* Support Mobile Explorer (File Browse)
* Run Command by Ctrl-Enter

This freeware can be downloaded at http://www.mymobiler.com/

Works great with Symbol handheld scanners with Windows Mobil 5

Find out free disk space on SQL server

The xp_fixeddrives returns a list of physical hard drives associated with the SQL Server machine and the number of megabytes of free space on each one.

exec xp_fixeddrives

Convert all uppercase text to propercase in MS-Word.

1. Copy and paste all uppercase text in MS-Word.
2. Select all the text by pressing CTRL + A.
3. Press SHIFT + F3 to convert all text to lowercase.
4. Press SHIFT + F3 again to convert it to propercase.

T-SQL Format DateTime

Convert SQL DATE/Times to commonly used formats

-- Date Format MM/DD/YYYY
SELECT CONVERT(varchar(10),getdate(),101)

-- Date Fromat MM/DD/YYYY HH:MM

SELECT CONVERT(char(10), getdate(), 101)
+ ' ' + CONVERT (char(5), getdate(), 108);


SELECT CONVERT(VARCHAR, GetDate(), 101) + ' ' +
CONVERT(VARCHAR, DATEPART(hh, GetDate())) + ':' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, GetDate())), 2) AS Date


SELECT CONVERT(CHAR(11),GETDATE(),101)
+ CONVERT(CHAR( 5),GETDATE(),114)


--- Date Format MM/DD/YYYY HH:MMAM/PM

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7)

Search and Replace in text column

You can not use T-SQL Replace function directly on a text field.

There are more complex ways to do this but if your filed size is < 8000 ( or <4000 for nvarchar), simple sql below may serve the purpose

UPDATE myTableSET set myField = REPLACE(SUBSTRING(myField, 1, DATALENGTH(myField)), 'searchText', 'replacementText') where DATALENGTH(myField) < 8000

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}