Show formula in excel spreadsheet

To do this, simple, just press Ctrl + ` key.

Reference: here

Float rounding issue involving sum

Try this in MS SQL:
create table #TEST (val1 float, val2 float)
insert into #TEST(val1, val2) values(0.0002, 0.0048)
select ROUND(val1 + val2, 2), val1+val2, ROUND(0.005,2)  from #TEST
drop table #TEST

val1 = 0.0002
val2 = 0.0048

The above query will return:
ROUND(val1 + val2, 2) = 0
val1 + val2 = 0.005
ROUND(0.005,2) = 0.010


It seems not just sum and rounding, even the multiplication itself will have issue


Dunno how to solve this yet, if u know, pls let me know.
Thanks.

Find a string within stored procedure

To find string content inside stored procedure, you can use this query:

Declare @StringToSearch varchar(100)
SET @StringToSearch = '%something%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type <> 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name

Different types in sysobjects:
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

source: here

To find the column names from tables, you may use this query

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%colname%'
ORDER BY schema_name, table_name;

or use this query:

SELECT Table_Schema, Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE table_name in ( select name from sysobjects
where xtype = 'U' )
and column_name like '%colname%'
order by table_schema, table_name

.net timeout

.net default timeout when querying database (eg. calling stored procedure etc) is 30 seconds!
Please refer here.

CR Keeping together the group and the detail

In Crystal Report, in order to keep together the group and the detail, pls follow the following setting:

Not Working 

Report -> Section Expert -> Select Group -> Enable Keep together

Working 

Report Group Section -> Change Group-> Options -> Keep Group Together

If you want the Group name to be displayed on the new page, you may tick on the 'Repeat Group Header on Each Page'

Ref: here

javascript startsWith() and endsWith()

Javascript function startsWith
String.prototype.startsWith = function(str)
{return (this.match("^"+str)==str)}

Javascript function endsWith
String.prototype.endsWith = function(str)
{return (this.match(str+"$")==str)}

Usage:
var myStr = “  Earth is a beautiful planet  ”;
var myStr2 = myStr.trim();
//==“Earth is a beautiful planet”;

if (myStr2.startsWith(“Earth”)) // returns TRUE

if (myStr2.endsWith(“planet”)) // returns TRUE

if (myStr.startsWith(“Earth”))
// returns FALSE due to the leading spaces…

if (myStr.endsWith(“planet”))
// returns FALSE due to trailing spaces…
 
Ref: here

SQL Precision (Float vs Decimal) and Computation

If we have a computation of (a/b*c), in decimal SQL datatype it will not give the same result as (a*c/b)
Correct value can be obtained using this sequence: multiply first then divide ( (a*c/b).
Trying the computation in calculator, the actual value will be value of (a*c/b)

Here's the sample:

DECLARE @a as DECIMAL(38,10), @b as DECIMAL(38,10), @dec as decimal(38,10)
declare @flt as float
SET @a = 5094450
SET @b = 5225985.8002
SET @dec= 2504000
SET @flt= 2504000
SELECT @dec*@b/@a -- 2568651.855195
SELECT (@dec/@a)*@b -- 2568650.410585
SELECT @flt*@b/@a -- 2568651.85519552