SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

As some of you aware, SQL IN clause is known as “slow” operator. This is very true when you come to big number of keys to search in the IN clause. E.g.

SELECT XXX
FROM Sales
WHERE CustomerID IN (SELECT CustomerID FROM @SearchCustomer)

** @SearchCustomer is a table variable consist of all the customerid (e.g. 20,000 IDs).

To accomplish the same result, we can always write it as:

SELECT XXX
FROM Sales S
INNER JOIN @SearchCustomer SC
ON S.CustomerID = SC.CustomerID

The result can be improved significantly. For my case, old query that take 5 minutes to execute can be done in 20 seconds with the new query.

I am sure some of you might experience the annoying auto postback on SSRS report parameters. It will postback every time you change the value of the parameter even there do not have any dependencies at all. I am having this problem for several reports with quite a number of parameters. I tried googling but doesn’t help much. After some try and error, I manage to fix some of it (not 100%). This is cause by how we input the default value on the report parameters and dataset parameters. For example, you need to set the default value “100” to a parameter. Some of you might put in “=100”. Try to change it to “100” without the “=” sign. This stupid “=” sign is causing my report to keep on post back for every change. You might need to apply the same rules at your dataset parameters. I am not sure it is 100% works but at least it solves most of my reports. Hope this can help! Do let me know if you got better solution!

This is nothing new on how to generate random number as per row basis. I am using NewID() method and complied into SQL function. Just to keep as my own record. Here are the 2 functions which return integer and decimal value.

Function:

 CREATE FUNCTION dbo.udf_GetRandomNumber(
@GUID AS UNIQUEIDENTIFIER,
@MinValue AS INT = 0,
@MaxValue AS INT = 1000
)
RETURNS INT AS
BEGIN
  �
 RETURN ABS(CAST(CAST(@GUID AS VARBINARY) AS INT) % (@MaxValue - @MinValue + 1)) + @MinValue

END

 Usage:

 SELECT dbo.GetRandomNumber(NewID(),100,500) FROM TableA
– Return random number from 100 to 500 as per row basis

Function:

CREATE FUNCTION dbo.udf_GetRandomDecimal(
@GUID AS UNIQUEIDENTIFIER,
@MinValue AS INT = 0,
@MaxValue AS INT = 1000,
@GUIDDecimal AS UNIQUEIDENTIFIER = NULL
)
RETURNS DECIMAL (18,2) AS
BEGIN
 DECLARE @dResult AS DECIMAL (18,2)
 IF @GUIDDecimal IS NOT NULL
  SET @dResult = ABS(CAST(CAST(@GUID AS VARBINARY) AS INT) % (@MaxValue-@MinValue + 1)) + @MinValue + ABS(CAST(CAST(@GUIDDecimal AS VARBINARY) AS INT) % 100)/100.0
 ELSE
  SET @dResult = ABS(CAST(CAST(@GUID AS VARBINARY) AS INT) % (@MaxValue-@MinValue + 1)) + @MinValue

 RETURN @dResult

END

Usage:

SELECT dbo.GetRandomDecimal(NewID(),100,500,NewID()) FROM TableA
– Return random decimal number from 100.00 to 500.99 as per row basis

Most users always suggest to detach and re-attach DB to remove the log file when dealing with large Transaction log file. There is one proper way to truncate the transaction log file to certain size. Here are the sample steps to truncate SQL Log file:

USE AdventureWorks;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
– Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

You can find the details here: http://msdn.microsoft.com/en-us/library/ms189493.aspx

I am trying to setup SQL Session State database but encountered error as below:

An error occurred during the execution of the SQL file ‘InstallSqlState.sql’. The SQL error number is 229 and the SqlException message is: EXECUTE permission denied on object ’sp_delete_job’, database ‘msdb’, schema ‘dbo’.If the job does not exist, an error from msdb.dbo.sp_delete_job is expected.
SQL Server: sql2005.valuethai.com
Database: [vtdb_ss]
SQL file loaded:
InstallSqlState.sql
Commands failed:…

Here is the command run by me:
aspnet_regsql.exe -S [sqlservername] -U [userid] -P [userpassword] -ssadd -sstype c -d [customdatabase]

After doing some debugging, I manage to solve it by assigning enough rights to msdb database. Once I map msdb to db_owner/public, it works fine.

SSRS provide table and matrix control to let user populate data in different scenario. Table control let user to populate simple format of data where fixed columns applied and rows grow dynamically. Matrix provide more powerful features where it columns and rows can be dynamically grow. In that case, writing a store procedure to support a matrix become easier in some scenarios.

E.g. you have a source table Sales with data below:

You need to generate a sales report with the following format:

You can simply write a SP as below and bind the columns to products and rows to ClientID:

SELECT ClientID, Product, Sum(SalesAmount) FROM Sales GROUP BY ClientID, Product

This solution seems pretty simple and straight forward. However, this will only work fines if you have small set of record in your tables. If you have hundred thousand of records with >20 type of products in your table, this solution no longer perfect. You will be facing performance issues. Yes, Matrix control very slow when deal with large amount of data.

Read the rest of this entry »

I got error “Could not access the Search service configuration database” to start the search service after filling in the account and database info while seting up SharePoint (MOSS 2007) in a server farm. After googling, apparently SPSearch(or WSS) doesn’t like its server name to be a FQDN, instead, it prefers NetBIOS. To solve this, you have to change the FQDN name to a NetBIOS name in the SharePoint_Config database.  IMPORTANT!! Please do a database backup before proceed!!

Here is the solution I found:

Open your SQL Query Analyzer and execute this command. This command will replace the “NETBIOS” and “FQDN” string to your server settings.
use SharePoint_Config
UPDATE Objects Set Name=’NETBIOS’ WHERE Id=(SELECT Id FROM Objects WHERE Name=’FQDN’)

Do you ever get this error for normaly user when accessing your scorecard in sharepoint?

“There is a problem with this scorecard. Ensure that it and all dependant items have been published correctly and that you have adequate permissions to view them. If the problem still persists then please contact a system administrator.”

You do not have any problem when you are local administrator. You already set all the permission in your scorecard and datasource. But it still raising the error when you login as end user to view the scorecard. Since I am still new to BSM, it take me hours to solve to the problem. The trick is you must set the “global reader” permission to “NT AUTHORITY\Authenticated Users” in your BSM’s “Server Administration”->”Permission” setting. By default, it only set the local administrator’s rights.

Yes! Its happen to me. I wish to clarify that I am not from other hosting companies who wish to mess up their name. This is the true story happen to me.

Recently I was looking for window VPS hosting. After do some googling and visited some popular web hosting forum, I decided to choose dotster. There are neither the most expensive nor cheapest. Before I sign up, I did chat online with their sales staff to find more details on the plan (I choose plesk standard). There are friendly and helpful. At the end I decide to give a try.

Day 1 – Sign up run smoothly. After payment has been made, I receive a receipt email and another mail indicates my VPS is ready! It is fast! I manage to login to virtuozzo control panel and check all my allocated resources. Everything looks good! Then I try to login to plesk control panel with admin login. Too bad, it always prompt me invalid login! Try to recover through “Forget password” but no help! I immediate send a support request to their support team. After 2-3 hours still no reply, I guess there may be busy out there. So just give them another day. Meanwhile, I also found that my VPS window is 64bit version but with a 32 bit .net 2.0 framework installed. I have no idea how there do it. With virtuozzo, I guess, may be possible? I try to install MS ASP.NET Ajax 1.0 but get prompt to install .net 2.0 framework to continue. I guess window cannot find the 32bit .net 2.0 frameworks. Now the problem comes … I try to remove 32 bit .net 2.0 frameworks in the OS but failed and give me is not a valid version… and stop to uninstall it. Then, I try to install another 64 bit .net 2.0 framework. Too bad, the installation stops unexpectedly. What can I do now? Try to chat with online support. Guess what their reply? VPS support is not applicable to online chat support! Please send in support request or call us. Come on I am outside US. Call is not a good method for me. So I send another support request regarding these issues… besides I also asking is it possible to reinstall the VPS..

Read the rest of this entry »

I am having  difficulties to set the dynamic defaultmember for a dimension. Eg. I got a time dimension which use in BSM. I want it to always get today’s date as the dimension member so that i can get the MTD data from my cube. But some how it didnt works. I did try on vba!date() function as found in google as well but no help. The BSM time intelligence member only work and display data if i hard code the defaultmember in cube. Setting manually time dimension in BSM also no help. Does anybody facing this problem before? Will appreciate if can point out the tricks…. =)