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


Sharing permission vs security

The Security Tab is the NTFS permissions.
Sharing permissions only apply when a user accesses a share via the network.

If the person is not given write permissions at the sharing level, then no matter the NTFS permissions, they will not be able to write to the folder.

Folder/NTFS permissions apply to a local logon or network access. If sharing
permissions conflict with folder/NTFS permissions for a network user, the
most restrictive permission will apply to the user. In other words if a user
has only read access in the sharing permission but full control to the folder/NTFS
permissions. That user will only have to read/list/execute access over the
network for the share contents.

Reference: 1 2

System account in Windows

The system account and the administrator account (Administrators group) have the same file privileges, but they have different functions. The system account is used by the operating system and by services that run under Windows.
By default, the system account is granted full control to all files on an NTFS volume.
The system account's permissions can be removed from a file but it is not recommended.
Reference: here

Change the database source without changing the fields

In CR, in order to change the data source/sp without changing the whole crystal report format, here's what we can do.
1) Go to the menu on top, under Database, select Set Data source Location ...
2) Select your data source to be changed and select new data source
3) Click update

Viola.. that's all it takes!
Reference: here

connection string in .net

In .net we can separate the connection string from the config file (app.config/web.config).
Here's how to do it:
1) Create ConnectionString.config, place this file in the same file as your config file.
Copy and paste the connection string from your config file to this file.

<connectionstrings>
<add name="AIConnectionString" connectionString="VsE02TxJgFtwnFegSRgcJtT9lrEl4ZFtSY+"  providerName="System.Data.SqlClient" />
</connectionStrings>

2) In your config file, replace the connection string with this:

<connectionstrings configSource="ConnectionString.config"/>

Thinking of consolidating all your other batches to use one connection string file only?
This is not possible, pls see the reference below.
Reference: here

How to refresh the SP table field in Crystal Report?

When we have made changes to the SP in the database, the crystal report (version 10) does not reflect the changes I made even when I have closed and re-opened the report.
In order to refresh this, we can go to the Database in the menu, select Verify Database to update this.
Reference: here

SQL Tree Hierarchy

How to construct the tree hierarchy using SQL???
I was searching for this and found a great link, so I followed and it works!

Here's the snippet for my codes:

declare @AITree table 
(Node int NOT NULL IDENTITY(100, 1), AIType int,
parent varchar(20), child varchar(20), 
depth int NULL, lineage varchar(1000))

--insert whatever inside your table, u can ignore the AIType
insert into @AITree (AIType, child)
select AIType, ID from AI_Grouping 
WHERE effstartdate<=@treeDate and effendDate>=@treeDate
order by AIType, parent, child

--updating parent using the node (not my table)
UPDATE T
SET T.Parent=P.node
FROM @AITree T 
INNER JOIN AI_Grouping C ON T.child=C.ID and T.AIType = C.AIType 
INNER JOIN AI_Grouping PT ON C.parent=PT.child and C.AIType = PT.AIType 
INNER JOIN @AITree P ON PT.ID=P.child and P.AIType = PT.AIType 
WHERE C.effstartdate<=@treeDate and C.effendDate>=@treeDate AND
PT.effstartdate<=@treeDate and PT.effendDate>=@treeDate 

-- updating the top parent
UPDATE @AITree SET Lineage='/', Depth=0 WHERE parent Is Null

-- updating the rest of the nodes
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.Parent,6,0)) + '/' 
FROM @AITree AS T 
INNER JOIN @AITree AS P ON (T.Parent=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

WHILE EXISTS (SELECT * FROM @AITree WHERE Depth Is Null) 
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.Parent,6,0)) + '/' 
FROM @AITree AS T 
INNER JOIN @AITree AS P ON (T.Parent=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

--try this to output the treee
SELECT Space(T.Depth*2) + 
    case depth when 0 then '-' else '' end + 
    G.child AS Name
FROM AI_Grouping G INNER JOIN @AITree T 
ON G.ID = T.child 
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

Enjoy!
Reference: here

To get the number of occurrence of a character in SQL

Wonder how to get the number occurrence of a character in MS SQL?
Here's a trick on how to do it

declare @char varchar(1), @str varchar(100)
SET @char ='/'
SET @str = '/102///111/'
SELECT LEN(@str)-LEN(REPLACE(@str,@char,''))

Wala.. there u go, u will get 5 for the scenario above.

SQL Query case sensitive

How to make the case sensitive query?
There are 2 records in the database with currAbbr = ZAR and ZAr, in order to get the correct case sensitive comparision, u can use BINARY_CHECKSUM.

try this query:
select * from MYTable
where BINARY_CHECKSUM(currAbbr) = BINARY_CHECKSUM('ZAR')
select * from MYTable
where BINARY_CHECKSUM(currAbbr) = BINARY_CHECKSUM('ZAr')

SQL Joins

This is a good website to illustrate the SQL joins.
http://www.codinghorror.com/blog/archives/000976.html

check .net version installed in your computer

to do this, simply create a html page and copy the content.

<Html>
<body>
<script>
alert(navigator.userAgent)
</script>
</body>
</html>

Then open the page on IE, it will prompt the .NET version.

Reference: here

ASP.NET Dropdown SelectedValue does not change?

Problem:
My dropdown selected value does not change.
It will always return the initialized value.

Solution:
Check if you put the initialization within the page.ispostback criteria.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            ddl.SelectedValue = "1"
        End If
    End Sub