Deploy a wss solution from Visual Studio 2008 extension for WSS has error

It's anoying to have this error when I deploy a soltion:


The feature name XXX already exists in SharePoint. You need to rename the feature before solution deployment can succeed.

To solve this problem you can't just rename the feature as that just adds more mess. The correct approach is to go to SharePoint Central Administration webiste, in the operations web, choose Solution Management.

Find that solution that causes trouble, extract that solution. Then remove that solution from the list.

After the solution is removed, the Visual Studio 2008 can deploy it to SharePoint again.

ADO closes a recordset before any data is read from a stored procedure

Recently I was working on a legacy VB Script file which has following code:

dim conn
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = connTimeOut
conn.CommandTimeout = connCmdTimeOut
conn.Open connString

dim dbCmd
set dbCmd = CreateObject("ADODB.Command")
dbCmd.CommandTimeout = 300
dbCmd.CommandType = adCmdText
dbCmd.ActiveConnection = Conn

sqlText = "ReportScheduler '" & currentDate & "' "
dbCmd.CommandText = sqlText
dim rsScheduledReports
set GetScheduledReports = dbCmd.Execute

if not rsScheduledReports.EOF then

...


Each time when the code is executed to this line, an error is generated :

ADODB.Recordset: Operation is not allowed when the object is closed

This is strange as there are actually many records come from the database and this is proved by executing the sql in Management Studio mannually.

The VBScript code worked on SQL Server 2000, recently we upgraded to version 2005 then it breaks.

I spent a lot of time googling on the Internet and finally a trick fixed it. Just add this line to the beginning of the stored procedure: SET NOCOUNT ON.

This makes me thinking what's difference between SQL Server 2000 and 2005 version when adding set nocount on statement in a stored procedure. The behaviour must have changed for new version.

Use FMTONLY To Obtain an Query or stored procedure metadata

When executing a t-sql or stored procedure, if set FMTONLY value to ON, an zero-row resultset is produced with metadata information. The full query is not run. This feature is used for many report designers like Crystal Report and Reporting Service.

For example:

SET FMTONLY ON
select * from dbo.Items
SET FMTONLY OFF

The result is an empty row with column names and column data types.

Declare a local variable in T-Sql while loop

I have had some trouble to find a bug inside a sql server stored procedure.

I have a while loop in the stored procedure like this:

While Exists(…)
Begin
Declare @CategoryID int

// here I assign the value to @CategoryID with a CategoryID field by joining Category and Product tables, the join could return one record or no record with some where clause.

// then check value @CategoryID by printing it

// do something to make the while loop end
End

There is no problem with while loop itself, it ends as expected. The problem is the value of @CategoryID variable. I assume each time I declare the variable in the loop, a null value is assigned it by default. But the reality is not, if there is a value returned from the table join select, the value is assigned to the variable, this is how it should be. But if there is no value returned from the join, the value from the last loop is carried forward, instead of a null. This makes me thinking if the Declare command in a while loop only happen once and leave the variable available for the next loop.

Reporting Service security

I have a Reporting Service report which sits in a remote server. I need to display the report in a ReportViewer control in a dotnetnuke portal. The report was displayed fine when I test in a stand alone web page, but when I deploy the user control in the DNN portal, I got the following security message:

The permissions granted to user 'BIOLAB\AD_ALANZ$' are insufficient for performing this operation. (rsAccessDenied)

It turns out the account used in my local computer doesn't have enough permission to view the report.

I did a search through web and solved by the following steps:
Go to the report manager home page. Click on Properties tab.
Click on New Role Assignment button.
Add a new user BIOLAB\AD_ALANZ$, choose Browser role from the list and click Apply.

Then my report is showing in the ReportViewer successfully.

ASP.NET session state is not available in custom role provider

I have a custom role provider. In the method public override string[] GetRolesForUser(string username) I check the role name stored in a session variable. This didn't work, as when I put a break point in the code I found out the value of HttpContext.Current.Session is null.

I stumbled for a while and I came to this link http://forums.asp.net/p/967758/1214615.aspx. In the post ScottGu says "Unfortunately authentication and authorization happen before the session object is populated."

So I have to change to load user role from database by the user name.

How to split a string in t-sql

If a list of values are concatenated by a character like '1,2,3,4,5', we need to split the string to a temp table so each value can be used in sql query. There are many tricks of doing this but the easiest one I have seen is below:


DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'

-- Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @N INT
-- Add start and end commas to the Parameter so we can handle
-- single elements

SET @Parameter = ','+@Parameter +','

SET @N = 1

-- Loop through and find each comma, then insert the string value
-- found between the current comma and the next comma. @N is
-- the position of the current comma.
WHILE @N < LEN(@Parameter) --Don't include the last comma
BEGIN
INSERT INTO @Elements
VALUES (SUBSTRING(@Parameter, @N+1, CHARINDEX(',', @Parameter, @N+1) - @N-1))
-- Find the next comma
SELECT @N = CHARINDEX(',',@Parameter,@N+1)

END

SELECT * FROM @Elements