CR2008 hanging

There was a case when my Crystal Reports 2008 was hanging after opening (can't even open a file or do any other thing), so here's what you can do to fix it.

Go to Registry (Start -> Run -> Regedit)
Locate: HKEY_LOCAL_MACHINE\SOFTWARE\business objects\suite12.0\crystal reports
and look for DisableCheckForUpdates and change it to a "1".

It works for me!
Source: here

Control-M Variables

To set date -x  in control-M  variable to be in DD/MM/YYYY format

%%SET %%PREVDD = -1
%%SET %%YEST = %%$CALCDATE %%$DATE %%PREVDD
%%SET %%YYYY=%%SUBSTR %%YEST 1 4
%%SET %%MM=%%SUBSTR %%YEST 5 2
%%SET %%DD=%%SUBSTR %%YEST 7 2
%%SET %%YESTERDAY=%%DD/%%MM/%%YYYY

This will give today's date in DD/MM/YYYY format
%%SET %%TODAY = %%DAY/%%MONTH/%%$YEAR

For more default variable click here.

Black box on Word 2010

After combining documents that contained track changes and reviewer comments, heading numbers for levels five became blacked out.

 

This is definitely a bug. To resolve this, please do the following:
1. Put your cursor on the heading just right of the black box
2. Use the left arrow key on your keyboard to move left until the black box turns grey
3. Use the keyboard combination ctrl+shift+s, the dialog "Apply Styles" should appear
4. In this box, click "reapply"

This fixed the issue for me. Good luck.

Reference: here

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.