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')