Download reports from Sql Server Reporting Services

I know that this is what the reporting services didn’t give us an available option. There are many scenarios we require to get the reports to the local server and edit the changes. For example, I have 10 reports and they aren’t available right now in my server or they are under source control and it is corrupted. Then only place they are available is Reporting services. From web service we can view the reports but cannot get them to the local machine. 
By using Report Manager site, we can download one report at a time but not all reports. So, I found a way which is really helpful and saves so much of time. Thanks to Gaurav, my colleague for telling me about this.
There is a free tool available for this. Which is called “Reporting Services Scripter”. This is very good tool. Which needs the report web service url and it will bring up all the reports in the window. Once you select what reports you needed and give save location, hit on script button, that’s it. It will download all selected reports to the given location.

Download it here. http://www.sqldbatips.com/showarticle.asp?ID=62
OR
http://web.archive.org/web/20120830141644/http://www.sqldbatips.com/showarticle.asp?ID=62

Remember that, before you do anything please make sure you have given the correct report web service url as shown below.

From options, you will see above screen. And from Servers tab, you have to give the correct servername.
Note: The RS Scriptor is using default windows logged in credentials. So, make sure the user logged  in has access to the reports and the user role should be “Content Manager”.

Add web reference to a console application in visual studio

This is somewhat confusion to many people. We can add a service reference to console application, but when you right click on project, you didn't see a direct "Add Web Reference" option. But, there are so many requirements we have to add a web reference. So, I thought of posting it in my blog.
Solution:

1. Create a console application using visual studio.
2. Right click on the project and click on "Add Service Reference...".
3. On the window, you will find the "Advanced" button at the bottom. 
4. Click on the button and it will open service reference settings window. It has a button at bottom called "Add Web Reference".
5. Click on it and add web reference as shown below.

That's it. We are done!!!

Publish Nintex workflow file to all sites and libraries using code

Nintex workflows - They are easy to build workflows and customize. I used them in couple of projects and it supports many activities which we can use and build workflows according to requirements. But, this time I got a big project which has plenty of sub sites in a site collection. All sub sites are having the same site template and it has the same structure. Each sub sites has many libraries and out of them 8 are having workflow enabled.
When I got a requirement to change something in workflow then I am in trouble like how to publish the new changed workflow to all libraries. Right now I have 148 sub sites. It means I have to publish the nintex workflow to 148 * 8  = 1184 libraries. Which is not at all possible with the manual upload process. So, the only way would be writing code to publish them automatically by running it.

Few days back, I have written the post which describes the same without coding here.  But, that needs lots of prerequisites and will apply to only one site. The solution which I have written needs the updated nintex workflow file [.NWF] as input, web url and site url. It loops through all sites in the site collection and updates the each and every library with the updated nintex file successfully.

Below is the code which does that for one site:

private static void UpdateWokflowToOneSite()
{
    string siteUrl = ConfigurationManager.AppSettings["SiteUrl"];
    List<string> listNames = new List<string>() { "List - 1", "List - 2", "List - 3", "List - 4", "List - 5"};
    using (SPSite site = new SPSite(siteUrl))
    {
        string webUrl = ConfigurationManager.AppSettings["WebUrl"];

        if (string.IsNullOrEmpty(webUrl))
        {
            webUrl = "/";
        }
        using (SPWeb web = site.OpenWeb(webUrl))
        {
            byte[] rawData = File.ReadAllBytes("UpdatedNintexWorkflow.nwf");
            NintexWorkflowWS.NintexWorkflowWS ws = new NintexWorkflowWS.NintexWorkflowWS();
            ws.Url = web.Url + "/_vti_bin/NintexWorkflow/workflow.asmx";
            ws.UseDefaultCredentials = true;

            int i = 1;
            foreach (string listName in listNames)
            {
                ws.PublishFromNWF(rawData, listName, string.Format("NintexWorkflow-{0}", i), false);
                i++;
            }
        }
    }
}        

Below are the prerequisites for running above code.

1. I have created a console application and writing code in it. So that I will get EXE as output and running it wherever needed [different servers by changing configuration file]. 
2. We have to add the Nintex workflow web service reference to the project. So that we will call it and use it in code. The below line in the code is web service instantiation.
   

   NintexWorkflowWS.NintexWorkflowWS ws = new NintexWorkflowWS.NintexWorkflowWS();
   

3.  The user who is logged in has the permissions needed to publish the nintex workflow. The administrator access are needed to run the above code.
4. The NWF file location according to above code should be in the same location where EXE is present. By default it will be project location/bin/debug.
5.  listNames is the variable which has all list/library names in the site to which we have to publish the workflow. In case if you want to publish to all list/libraries then replace the listNames in foreach with web.Lists.

Now, the app.config entries are configurable. Below are the configuration changes needed.

<appSettings>
    <add key="SiteUrl" value="http://sharepointsite"/>
    <add key="WebUrl" value="/"/>
  </appSettings>
    <system.serviceModel>
        <bindings />
        <client />
    </system.serviceModel>
    <applicationSettings>
        <DeployWorkflowNWF.Properties.Settings>
            <setting name="DeployWorkflowNWF_NintexWorkflowWS_NintexWorkflowWS"
                serializeAs="String">
                <value>http://sharepointsite/_vti_bin/NintexWorkflow/Workflow.asmx</value>
            </setting>
        </DeployWorkflowNWF.Properties.Settings>
    </applicationSettings>

In this, change the configuration as needed and use it.

You can use the same code loop through each and every web and publish it to all webs. I mean one more loop is enough to do that job as shown below.

private static void UpdateWokflowToAllWebs()
{
    string siteUrl = ConfigurationManager.AppSettings["SiteUrl"];
    List<string> listNames = new List<string>() { "List - 1", "List - 2", "List - 3", "List - 4", "List - 5"};
    using (SPSite site = new SPSite(siteUrl))
    {
        foreach(SPWeb web in site.AllWebs)
        {
            byte[] rawData = File.ReadAllBytes("UpdatedNintexWorkflow.nwf");
            NintexWorkflowWS.NintexWorkflowWS ws = new NintexWorkflowWS.NintexWorkflowWS();
            ws.Url = web.Url + "/_vti_bin/NintexWorkflow/workflow.asmx";
            ws.UseDefaultCredentials = true;

            int i = 1;
            foreach (string listName in listNames)
            {
                ws.PublishFromNWF(rawData, listName, string.Format("NintexWorkflow-{0}", i), false);
                i++;
            }
        }
    }
}

There we are done. Just run the EXE and it will update the workflow to each and every library in a web.

Complete project is available for download here. Please let me know, if you need any more help.

STSADM - Object reference not set to instance of an object

I know this is the error which developers see most of the times. This is very basic error and it raises when we tried to access NULL reference object. But, What is if it comes while accessing STSADM? In SharePoint everyone knows the role of STSADM and the advantage of it. If you try to do some operation using STSADM tool and ends with the error "Object reference not set to instance of an object" then it is very difficult to trace as it is not giving us the enough information. But, with the experience, we get some ideas and solutions. Most of us know the resolution but I want to place everything what I know to my readers on this blog.

Resolution:
The user who runs the STSADM on the server should have access to the SharePoint Admin content database. If he don't have access, but he is administrator on server, has full access to central administration and farm administrator access then still no use. He should have access to the admin content database. Because whatever we do using STSADM then it is indirectly dealing with database only. So, user logged in should have access to database.

This is one of the main thing which we need to remember in administration side. Hope you liked it and you remember it forever. And mainly this scenario will come on environment where the farm server setup and database server is different from SharePoint server.

Operation is not valid due to the current state of the object when making changes to SPListItem object using elevated previliges

This is known error to sharepoint developers that when try to update/delete a SharePoint list item using system account. Means using SPSecurity.RunWithElevatedPrivileges() method. I am not sure, why it is not allowing to update or delete a file in this code block. But, there is work around for it. You can still edit the item using elevated privileges. Here is a wonderful post, which helped me great and all credits to him.
Make changes to SPListItem using elevated privileges
I am really surprised by seeing the code in his article. It is completely unexpected and it is working great. I am really not believed when I saw the code very first time, but it is working.

But, make sure you are disposing the objects correct. Hope this will help you too to fix the problem "Operation is not valid due to the current state of the object".

Table of contents web part remove max 50 limit

We use the "Table of contents" web part on SharePoint landing pages to give access to users to easily navigate to the sites/pages. But, there is a default limitation on the display items in left navigation or table of web part contents  in sharepoint. By default it shows only 50. If you have more than 50 items [either  sub sites or pages] in a site then it will not show up. To increase the limit or to remove limit, please do below changes.

1. Go to the web.config of the SharePoint site through file system. Usually, it is located at "C:\inetpub\wwwroot\wss\virtualdirectories\port number".
2.  Please take the back up of the web.config file before do any changes.
3. Find the below tags in the web.config file.
   

   <add name="GlobalNavSiteMapProvider" description="CMS provider for Global navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Global" EncodeOutput="true" />
   <add name="CombinedNavSiteMapProvider" description="CMS provider for Combined navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Combined" EncodeOutput="true" />
   <add name="CurrentNavSiteMapProvider" description="CMS provider for Current navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Current" EncodeOutput="true" />
   <add name="CurrentNavSiteMapProviderNoEncode" description="CMS provider for Current navigation, no encoding of output" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Current" EncodeOutput="false" />
   

4. All the above tags are belongs to the navigation providers. They are the sources for the navigation controls. We can add any attributes which it recognizes to control the navigation.
5. Now, according to our problem, we have to add an attribute which can control the number of items to display in navigation. The attribute is, "DynamicChildLimit".  Which accepts integer value. You can give any value in there to control navigation items. For example, if you give 20, then it will show you the top 20 items sorted by created. If you give 0, then it means no limit. Display how many items it has. 
6. So, we have to add the attribute to all the above tags, so that it should looks like below.
   

   <add name="GlobalNavSiteMapProvider" description="CMS provider for Global navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Global" EncodeOutput="true" DynamicChildLimit="0" />
   <add name="CombinedNavSiteMapProvider" description="CMS provider for Combined navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Combined" EncodeOutput="true" DynamicChildLimit="0" />
   <add name="CurrentNavSiteMapProvider" description="CMS provider for Current navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Current" EncodeOutput="true" DynamicChildLimit="0" />
   <add name="CurrentNavSiteMapProviderNoEncode" description="CMS provider for Current navigation, no encoding of output" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Current" EncodeOutput="false" DynamicChildLimit="0" />
   

Note: For removing the limit, I have used 0 in the above example. Please use the number according to your requirements.

Once you have changed the web.config file, save it and check the site. It should display the navigation items according to the setting you have given. Hope it helped.

SharePoint 2007 Limitations complete list

There are so many posts in the Internet describes about this. But, there is no place where I found all at once. So, I am planning to keep this post up to date with whatever I find. If I miss anything then please add here or let me know. That will be a great help.

1. SharePoint maximum limitations 
2. Performance limits 
3. Scaling limits 

I think, the above three posts covers most of them. But, below are some of the things which we need to consider as well.

1. Unsupported characters: These characters are not allowed in SharePoint or WSS /\:*?"<>|#{}%&~ or tab characters and multiple periods.
2. File or folder names with Trailing spaces: File or folder names in SharePoint or WSS won't support any trailing spaces.
3. File and folder names length: File or folder names longer than 255 characters will not be allowed.
4. Number of columns limitation in a list:

• 64 text fields, including the following field types: Single line of text, Choice, Hyperlink, or Picture.
• 16 Lookup fields
• 16 Date and Time fields
• 16 Yes/No fields
• 32 Number and Currency fields
• 32 Multiple lines of text fields 

I believe these are enough for a SharePoint architect to get good idea on limitations of SharePoint. Hope these helps and please update me if you find any.

Template Explorer in SQL Server 2008

I know, most of us do not know what it is... But, there are nice advantages of it and everyone should know about it. Template Explorer contains so many templates which helps us to understand many things and these templates have many examples on how we can write or do things through SQL queries. There are lot of default templates available like Backup, Restore, Database Mail, Trigger, user, login etc...

To view the Template Explorer, follow the steps below.

1. Open Sql Server.
2. From "View" menu, select "Template Explorer".

This opens up the template explorer and there are lot of folders which helps you to understand and do some magic. To give the template parameters, no need to go to the sql code and edit. You can directly change them in the editor as described below.

1. Open some template.
2. Now, click on Query menu and select "Specify Values for Template Parameters".
3. It will show you a windows with all parameters the template is looking for for the selected template.

That's it!!! This helped me to understand many things. Hope this helps to you too..

Find table size used in SQL Server

In a database, when size is growing then we have to find which table is taking more size. Then we have to query database to find what is the table taking maximum size. Log is also one of the factor which causes the database size grow in very huge amount. But, except logs, if you want to find the table which takes max size is, then use below command.

Command:
EXEC sp_spaceused 'Student'

Output:

name	rows	reserved	data	index_size	unused
Student	918	464 KB	256 KB	152 KB	56 KB

So, using the system stored procedure "SP_SpaceUsed", we will find the table size used. The only input parameter the SPROC expects is table name.
Hope you enjoyed this post.