Sunday, July 18, 2010

Report Viewer - Hide PDF Export

I always like to share simple but interesting solutions to the users who are in urgent need of fixing it.Here I am going to share, how one can achieve hiding some of the options available from the Export options of Report Viewer from SQL Reporting Services.

I did the following to achieve this.

Steps:

1. Go to file "rsreportserver.config".

This file you can find in location "C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer"

2. Go to the section <Render>.

3. Add the Visible="false" attribute to the end of each Extension tag to which you wish to hide.

For Example:

Here I am hiding PDF option and keeping all other options enabled.

<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering" Visible="false"/>

After applying these settings you will see only Excel option enabled.

Note: Since these settings are applied to the report server configuration file so changes will be reflected to all reports.

:) Is it not easy?

You comments are always appreciated.

Thursday, July 15, 2010

SQL Reporting using Visual Studio 2005

Dear all,
I was in need to develop a report using SQL reporting. I started with VS 2005 but could not ind report project to start with.
I was little confuse, I went through some blogs and googled how to add "Business Intelligence Project" templates to VS 2005.
I applied many things but none of them worked, later I found installing with workgroup components from SQL server and now I was able to find it.
I developed the report and it was working fine. I also needed ReportViewer to render the report.
So I added an assembly Microsoft.ReportViewer.WebForm(10.0.0.0) and i got report viewer as well. Soon after this, I started getting run time JavaScript error like.

1. the base class includes the field 'ScriptManager1', but its type (System.Web.UI.ScriptManager) is not compatible with the type of control (System.Web.UI.ScriptManager).
2.I get the 'Sys' is undefined Javascript error whenever I place a ScriptManager object to my page.
3. Client side framework failed to load ASP.NET ajax

The resolution after doing R&D came the application should be Ajax enabled. I was surprised why a ReportViewer requires an Assembly System.Web.Extension(3.5.0.0) and why the application web.config file should have all settings defined as defined for Ajax enabled site.

I am frustrated now, after searching a lot I could not find anything good so I decided to develop a crystal report. That day, I left my office little soon and after reaching home, I discussed this annoying issues with my friends that why a ReportViewer needs System.Web.Extension assembly. They said that they will also try to do it.

It was a fresh morning for me. I asked my lead to give me some time before we switch to Crystal report. Let me analyse the issue.
I undo the changes and started from beginning, The following blog helped me a lot to understand why it was causing issues and what was required to solve all those issues.
So finally if you don't want to use ajax enables website and want to use report viewer to display report. Use 8.0.0.0 version of Microsoft.ReportViewer.WebForms.dll.
Installing the Microsoft.ReportViewer.WebForms assembly for MS Visual Studio 2005 and MS Visual Studio 2008

http://www.kodyaz.com/articles/assembly-Microsoft.ReportViewer.WebForms.aspx

You need to installed Microsoft Report Viewer Redistributable 2005

This redistributable package contains Windows Forms and ASP.NET Web server control versions of the Report Viewer.

And refer the

Microsoft.ReportViewer.Common.dll(8.0.0.0)

Microsoft.ReportViewer.WebForms.dll(8.0.0.0)


I hope you will enjoy it.

Tuesday, July 13, 2010

Copy an assembly from the GAC

Recently I were in need copying some assembly from GAC.
After trying myself and google I could not able to do it. I asked one of my friend and he suggested the following which was not even easy but perfect one.

1. Go to Start->Run
2. Type "C:\WINDOWS\assembly\gac"

You will see list of folder with installed assemblies from GAC.

Also there is a tool provided by Accelerated Ideas, this if free



Installing Microsoft SQL Server 2005 Reporting Services on a non-default web site.

A good presentable article is given by Greg Van Mullem for Installing Microsoft SQL Server 2005 Reporting Services on a non-default web site with complete screen shots.

Sunday, July 11, 2010

Active Directory Authentication from ASP .NET

Any ASP.Net application which requires authenticating a user from Active directory would required LDAP (Lightweight Directory Access protocol).
To achieve this you would require the following.

1. Add the below two namespaces.
using System.DirectoryServices;
using System.DirectoryServices.ActiveDirectory;

2. Use the below function to authenticate the user with Active directory.

public static bool AuthenticateWithAD(string activeDirectoryServer, string userName,string password)
{
bool authentic = false;
try
{
DirectoryEntry entry = new DirectoryEntry(activeDirectoryServer, userName, password);
object nativeObject = entry.NativeObject;
authentic = true;
}

catch (DirectoryServicesCOMException)
{
return false;
}

catch (Exception ex)
{
return false;
}
return authentic;
}

//Below lines
AuthenticateWithAD("LDAP://spserver","username","password");


This does fantastic, I hope it will help you a lot when doing forms authenticate with AD.
Thanks

Reading Image or files like .xml,.txt as embedded resource from Assembly

As we requires images in our .Net application, There are many ways people make use of these resources to use .
Some of just read images or files from physical location, which seems more inconvenient than just reading from complied Assembly.
The metadata and all other information of the assembly is easily accessible. So it is easy to read it as embedded resource.
The only concern it increases the size of the assembly.
Reading an image from the assembly when the image is added to the project as
Embedded Resource,
I will explain here step by step.

1. Add an image to the solution




















2. Set the Build Action as "Embedded Resource".
3. Now build the project, the image will be complied into your project assembly which you can refer whenever required.
4. Make sure you add the following two namespaces
using System.Reflection; using System.IO;
4. Now the following lines of code can get you your complied image from the assembly.
Assembly assembly = Assembly.GetExecutingAssembly();
Stream streamObject = assembly.GetManifestResourceStream("WindowsFormsApplication1.images.Untitled.png");             Bitmap myImage= new Bitmap(streamObject);

Hope it works for you as expected.



SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have come across this error many a times, I thought why people to make struggle to find out the solution over google and then after reading all forums views and then getting into solution.You know sometime you spend much time than expected just to look into solution and then applying it which may work or may not. I found the way how to make someone happy by just providing a quick and relible solution which works for all.

“Since it a common scenario when you work with more than one machine where one is your application sever and another is your database server”. I was getting the below error messages …

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (Provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

This error encountered when you put Network Library property with your connectionstring. Adding the network library to indicate that your connection will use TCP/IP and not Named Pipes.


"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) "

Look into the following solution either will surely work for you. The solutions basically depends upon the type of configuration used while installing SQL Server.

Solution 1:
Go to, Start >> Programs >> Microsoft SQL Server 2005 >> Configuration
Tools >> SQL Server 2005 Surface Area Configuration >> Surface Area
Configuration for Services and connections.

Within this check whether "Local and remote connections" is choosen.
If not choose it :)

· Open the "SQL Server Configuration Manager" (under Configuration Tools)

· Expand the "SQL Server 2005 Network Configuration"

· Select the "Protocols for "

· Set the Named Pipes To Enabled

SOLUTION 2:

· Click Start, click Run, type cliconfg, and then click OK.

· In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.

· In the Add Network Library Configuration dialog box, under Network libraries, click TCP/IP.

· In the Server alias box, type the IP address of the computer or the name of the computer that is running SQL Server, and then click OK.

SOLUTIONS 3:

· Enable the TCP/IP protocol using the Surface Area Configuration Utility

· Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility

· Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances

· Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.

· Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports that starting and stopping the SQL Server and Browser software is not enough.

SQL Server common errors and their solutions.

Hi All,

I am just sharing my solutions for the errors which might come to you while dealing with SQL 2005. I come across these issues and solved them by referring forums, some blogs, MSDN. I realised it is also better if we get all our solution at one place instead just getting frustrated when things doen’t work.

Error 1: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Solution: This error occurs when firewall on the server refuses the connection. You should be careful and should configure the firewall on the server to accept connections.

Error 2: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1

Solution: There could be many possible reason of the above error.

The firewall on the server if refuses the connection.

If the SQL Server instance name given is not valid.

If the sqlbrower service is not started,

To fix the error you need to appaoch the following actions as part of your solution.

Configure the firewall on the server instance of SQL server to open SQL Server browser port.

Run the server Sql Server browser

Check the Sql Server instance name which you have specified in your connection string.

Enable the Sql Server remote conection using Sql Server Surface Area configuration

Error 3: Case expressions may only be nested to level %d.

Solution: SQL Server 2005 allows only 10 levels of nesting in CASE expressions, So you should be careful while writing a Sql script. You have to reduce the case statements to 10 or less.

Error 4: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233

Solution: This is very common error usually appears for each developer once in a lifetime. You just need to use Sql Server area configuration to accept remote connections.

Error 5: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

Solution: Dear you fogot to start your server, start it now and you are done.

Error 6: Could not obtain exclusive lock on database '%.*ls'. Retry the operation later.


Solution: Sometime a database operation may require exclusive access to it. In that case you need to make sure that the connections to the database is not shared., Make sure you disconnect all the existing connectiosn to that database and trye again, I hope you are done it.

Error 7: Cannot open user default database. Login failed.

Solution: The SQL Server login was unable to connect because of a problem with its default database. Either the database itself is invalid or the login lacks CONNECT permission on the database, Use ALTER LOGIN to change the login's default database. Grant CONNECT permission to the login.

Error 8: Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

Solution: Starting with SQL Server 2005, The RESTORE VERIFYONLY statement checks the available storage space on the disk to which the database is to be restored.

The available disk space may be insufficient to restore the backup being verified. Restore the backup to a location with sufficient disk space, or provide more space on the disk.

Error 9: The repair statement was not processed. The database cannot be in read-only mode.

Solution: This error happenes when one try to repair the database and the database is in readonly mode. You should make sure that the database also have write access to. To do it set the databasse to read-write by using Alter Database and then return the DBCC command