HTML source code wrongly appended to streamed file using Response.Write

Today while writing a small web application where I needed to download a datatable as csv file. I faced issue of page html getting wrongly appended to file being downloaded. I was using Response.Write to download file on client machine.

Solution to my problem was easy, I just needed to add an extra line telling content-length. Below is method that I finally ended up using.


        private void DownloadDataAsCsv(DataTable dt)
        {
            string tab = "";
            StringBuilder sb = new StringBuilder();
            foreach (DataColumn dc in dt.Columns)
            {
                sb.Append(tab + dc.ColumnName);
                tab = ",";
            }
            sb.Append("\n");
            int i;
            foreach (DataRow dr in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    sb.Append(tab + dr[i].ToString());
                    tab = ",";
                }
                sb.Append("\n");
            }
            Response.ClearHeaders();
            Response.ClearContent();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=Export.csv");
            Response.AddHeader("Content-Length", sb.ToString().Length.ToString());
            Response.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control restrictions have been addressed

Today I spent quite some time getting rid of a common SSRS error due to a silly mistake, so here I am with it. This might save a bit of your time on a bad day

Error I faced was,

User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed

Solution,

Add desired users to administrator group on SSRS server

Mistake I did was googling problem straightaway and crying for help before I started thinking myself. With quick google, I got a few suggestions which didn't helped me as they were pointing to same error but occurring possibly due to something else. So here is what helped me, you might need to call IT team for getting this done.

Do login on your SSRS server and open edit local users and groups

 This will open a window where you can add/remove users from a group

• Select Groups in left pane, this will give you list of groups on your server
• Now double click Administrators, this will give you a screen like below
• Here click on Add button and enter name of user to whom you wanna give access
• Press ok, then apply and there you are. 
• Problem solved :)  
• If this doesn't solves your cause, check this nice blogpost from caleb

"swarg jaana ho to khud he marna padta hai", a hindi proverb which traversed my mind when IT guy failed to help me on this :P

Thanks,
Ravi

Can´t upload a new attachement in DNN 6 with forum 5.0.3

Today one of my friend pinged me to help on an issue with DNN forum module(05.00.03), he was using it on DNN 06.01.05. Problem was "Upload new attachment failing repeatedly with some exception". He was getting below exception:

Error: is currently unavailable. DotNetNuke.Services.Exceptions.ModuleLoadException: The underlying system threw an exception. ---> DotNetNuke.Services.FileSystem.FolderProviderException: The underlying system threw an exception. ---> System.ArgumentNullException: Value cannot be null. Parameter name: content at DotNetNuke.Services.FileSystem.StandardFolderProvider.AddFile(IFolderInfo folder, String fileName, Stream content) at DotNetNuke.Services.FileSystem.FileManager.MoveFile(IFileInfo file, IFolderInfo destinationFolder) --- End of inner exception stack trace --- at DotNetNuke.Services.FileSystem.FileManager.MoveFile(IFileInfo file, IFolderInfo destinationFolder) at DotNetNuke.Common.Utilities.FileSystemUtils.MoveFile(String strSourceFile, String strDestFile, PortalSettings settings) at DotNetNuke.Modules.Forum.WebControls.AttachmentControl.cmdUpload_Click(Object sender, EventArgs e) --- End of inner exception stack trace ---

So as usual firstly I googled the error and found that many people are having similar error, but none of them were having a solution so I thought that it might be some permissions issue and messed up with folder permissions, but all failed.

So finally I debugged the code and found that line that was breaking was calling method "FileSystemUtils.MoveFile" which got deprecated in DNN 6 

So I replaced it with method call for "RenameFile" and there you go!!

Below is the culprit line under method cmdUpload_Click in file "AttachmentControl.ascx.vb"
FileSystemUtils.MoveFile(ParentFolderName + FileName, ParentFolderName + destFileName, PortalSettings)

 and here is its replacement

DotNetNuke.Services.FileSystem.FileManager.Instance.RenameFile(DotNetNuke.Services.FileSystem.FileManager.Instance.GetFile(DotNetNuke.Services.FileSystem.FolderManager.Instance.GetFolder(PortalId, BaseFolder), FileName), destFileName)


If you are also having this error, then just download the module's source code from codeplex, open it in visual studio, replace lines as I mentioned, build this module. Now go to your filesystem, grab new DLL for forum module and replace it in your DNN apps bin directory.

Sorry I forgot to write earlier, please take backup before doing this to your production site.

Hope this will help!!

Thanks,
Ravi

Hello World SSRS Report using BIDS 2008

Below is a simple step by step illustration for creating a Simple SSRS report using Buisness Intelligence Development Studio 2008

• Create a new SSRS project on BIDS (you can find it under Business Intelligence tab under installed templates)
• You will get something like this in a new project, I have added a couple of Shared data sources and sample report (you won't get them obviously ;)

• Now right click on "Shared Data Source" folder icon and select add new data source
• This will prompt you with a dialog box to enter server, userid, password, etc. Get these things filled and you will get a new shared data source ready to be used. Something similar to SampleDBDataSource.rds as in above image
• You can skip this step if you dont wanna create a new shared data source. you can later embed this in report directly.
• Next click on "Reports" folder and add new report, this will launch Report Wizard. At first step you need to select an existing datasource or create a new one. 

• Next step you need to enter your query. For my sample it’s something like below:

• Enter your desired query or stored procedure name (using query builder) and follow the wizard to reach “Design The Table” screen, as below

• At this step you have to divide your available fields in  Group and Details section (just drag and drop), for my sample report I did like below:

• Now follow the wizard and you will have a report ready. In my case it was something like below:

• Press preview tab to preview how your report looks like.

• Now if you can make any design changes needed. Lets say we want to add string "Employee Name: " before Employee Name displyed(as in preview above)
• For this we need to add expression in place of Employee Name.
• Right Click textbox you want expression to be added(Emp Name textbox in this case)
• Select "Expression" from the context menu, you will get a screen as below. Enter desired expression and press OK(See below).

• This will give you below result.

Hey we are done with this post. Enjoy!! :)


Edit 15th Jan, 2013

I wrote this post to answer one of the question on codeproject and that question wants to show employee date once per group, so here are the steps to do so:

1. Add a new row below employee name row, as shown in below image. 



2. Add expression for Employee date, you may just select DOB field as shown below or can add some expression as shown a couple of images above. I used below expression in my sample
• ="DOB: " + Fields!Employee_DOB.Value



3. and there you are!

I hope you guys won't mind some of my employee being minor DOB(1/8/2013) ;)

Thanks,

Ravi

DNN UserController.GetUsersByProfileProperty

Today while traversing through DNN code I found that method UserController.GetUsersByProfileProperty returns all available records if we pass -1 as pageIndex!

DNN handles pageIndex -1 at AspNetMembershipProvider class. Below is its implementation present in that class.

Public Overrides Function GetUsersByProfileProperty(ByVal portalId As Integer, ByVal propertyName As String, ByVal propertyValue As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ArrayList
            If pageIndex = -1 Then
                pageIndex = 0
                pageSize = Integer.MaxValue
            End If

            Return UserController.FillUserCollection(portalId, dataProvider.GetUsersByProfileProperty(portalId, propertyName, propertyValue, pageIndex, pageSize), totalRecords)
        End Function

SQL Server Template Explorer

Today while working I created a new Stored Procedure using SSMS 2008, through 

Database => Programmabilityv=> Procedures => Right Click Select New procedures 

This returned me a well defined new SP template as usual. Here I was supposed to add some extra lines of comment like modified by, date, purpose etc. This prompted me to think how nice it would have been if I could get these automatically written for me every time I create a new SP. So I googled and straightaway found a stack-overflow link telling about Template Explorer in SSMS. From template explorer you can view all the default SSMS templates and can edit any of them. 

I edited template for new stored procedure to add new comment lines as per my requirement and also changed number and type of default parameters.

You can also use it to add some other commonly used lines like TRY/CATCH block :)

Shortcut to open template explorer: CTRL + ALT + T

These templates are stored in your file system as plain SQL files which you can edit without SSMS as well. Location for it is below:

On my XP machine having SQL Server 2005 Express:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql

On my windows 7 machine having SQL Server 2008 R2 Developer Edition:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

Rawan Dahan

Just for fun (working best in chrome and safari, works but is slow in IE and Firefox)

Missiles Fired: 0

Burai par acchai ki jeet hui. Happy Dusshehra.

A simple game made just for fun.

How to Play:

• Click missiles to fire them, time them such that they fall on Rawan's main head
• You get 10 missiles by default and you have to strike rawan's head 5 times to win
• Click "Reload drones" button to reset missiles, this will not restore number of rawan's killed
• Click "Reset Game" button to reset game to initial values
• Try this on Chrome or safari, as game speed is slow on Firefox and IE.

Images used here are not of mine.
Rawan's image can be found here.
Drone's image can be found here.

windows service couldn’t be started

A few days back while working on a windows service, I used below line for debugging purpose:

System.Diagnostics.Debugger.Launch();

This line worked fine and I got my service debugged. Then I tried to deploy service on our server machine. I used below two commands for installing my service

InstallUtil.exe -i 

NET START

InstallUtil worked fine but when I fired NET START, it failed saying, service couldn’t be started. I didn’t have any proper explanation of this but by hit and trial I found that line I used for diagnosing (System.Diagnostics.Debugger.Launch()) was creating problem somehow, I just commented that line and everything just worked fine.

If you have some windows service that is having some trouble in starting, then try to check this case.

Thanks.

Disable hyperlinks/javascript when exporting SSRS report

"Disable hyperlinks/javascript when exporting SSRS report", this is a very common question and I was expecting a straight forward answer from google like enabling/disabling a property at report or server level. But I was unable to find any such thing and hence I took below turnaround to solve my problem.

Problem:
In my case I had a column where some names were coming and they were linked to some external URL, this was fine and as required when rendered through report-viewer control on my web-application's page but when I export this to report PDF/Excel I didn't want them as then these links become non-functional and show error message like invalid URL.

Solution:
I duplicated my column containing link, and removed action from newly created column. Now I set their visibility with below expressions.

Visibilty of column with action link associated:
=IIF(Globals!RenderFormat.Name = "PDF" Or Globals!RenderFormat.Name = "EXCEL", true, false) 

Visibilty of column without action link associated:
=IIF(Globals!RenderFormat.Name = "PDF" Or Globals!RenderFormat.Name = "EXCEL", false, true)

Please consider: This might not work for older versions of SSRS

Hope this will save a few minutes of someone. If you have some better approach for this issue, please add it in comments and I'll update that in this post with due credits. Thanks.

SQL Server Temp table vs Table variable

Hi All,

Some time back I was in an interview where one of the question was "Whats the difference between Temporary tables and Table variables".

What I knew at that time was:

• Table variables are a newly introduced concept  and are faster.
• Table variable exists only exist in current session.

So after interview I googled a bit and as-usual found answer on SQL Authority spread in couple of articles.  These articles have a lot of comments and some interesting points, so I thought to summarize them here for those who want it quick:

• In case of large amount of data temp table is advised because SQL Server creates, maintains and uses the statistics of temp table while generating the execution plan. Besides this we can create index in temp tables if that is needed.
• Table variables performs good only for small record-set. Because there data statistics is not used while creating the execution plan and because of that sometimes fully optimized plan are not created. Table variable should be used where they are joined with small tables only.
• Temp table has the global option which is very useful too when having two connections open and running multiple tests at the same time.
• Table variables are not transactional and do not roll back. Temp tables are transactional and do roll back.

For more details, please visit below links:

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/