Everyone working on SQL Server and its components (SSIS, SSAS & SSRS) would require Business Intelligence Development Studio (BIDS) which is a really cool integrated development environment allowing us to work on different types of projects simultaneously like SSIS, SSAS & SSRS (All in One Solution). I came across an issue with the SSIS project, in which there was no option to execute the package.

Recently one of the developers in my team came to me complaining that while he was working on an SSIS package, he was unable to see the execute package option when he right clicked on the package in Solution Explorer in BIDS. When I took a look at his Visual Studio / BIDS, I found that some of the settings were messed up and the settings did not seemed to be same as that of a standard BIDS environment due to which the execute package option was not available in the solution explorer.

To fix this issue or in general to reset all the settings of BIDS to bring it to a state same as that of a new installation, follow the below steps:

1. Open Business Intelligence Development Studio (BIDS).
2. Go to Tools menu and select Import and Export Settings....

3. This will bring up the Import and Export Settings Wizard window, Select Reset all settings option and click Next in the Welcome to the Import and Export Settings Wizard page.

Note: You can use this step / page to even export your custom settings to some other environment or import your settings from a different environment by selecting appropriate options.

4. In the Save Current Settings page of the wizard, select Yes (Recommended) to save the current settings so that it can be used to get back to the previous state in case if you want to revert back to the old settings of if something goes wrong in this process, Else you can may select No if you dont want to save the current settings and click on Next.

5. Choose Business Intelligence Settings in the Choose a Default Collection of Settings page of the wizard and click Finish and then click Close once the BIDS settings are successfully reset.

And now your BIDS should get back to the initial state same as that of a new installation. This is a simple procedure to fix any unwanted settings by resetting all your BIDS settings.

This simple procedure can be used to do one of the following things:

• Export customized BIDS settings to a different server / environment.
• Import customized BIDS settings from a different server / environment.
• Reset all the customized BIDS settings to bring it back to the original state as that of a new installation.

Recently I started looking into one of the great features of Reporting Services (SSRS 2008) and that is Subscriptions. A Subscription is a feature in SSRS to deliver a report to the users at specified time in their intended format. 

There are basically two types of Subscriptions - Standard and Data-Driven Subscriptions.  Each of these Subscription types offer delivery options like Delivery via E-Mail, Delivery to a specified location on the File System etc. These Subscriptions also offer various rendering/delivery formats like Excel, PDF etc. Basically all the exporting formats that are available in Report Server/Manager. However getting into the details of Subscriptions is out of scope of this article. More information on this topic can be found on msdn at the following location:

http://msdn.microsoft.com/en-us/library/ms155911%28SQL.90%29.aspx

Today I was working on Subscription with Windows File Share/File System delivery option. While configuring this option, we need to specify the location to which the report need to be delivered/saved on the file system. I gave the location for delivery as a local drive on my personal computer.

When I gave the local path as "F:\Datta\MyTechnoBook - Files\" and got an error as

The path is not valid. The path must confirm to Uniform Naming Conventions (UNC) format.

 

This error indicates that the path should be provided in the form of Uniform Naming Conventions (UNC) format. Or in simple terms we need to provide the path in the format of Shared Drive as follows:

\\ServerName\FolderLocation\

When I gave the location as "\\SINDOL\F$\Datta\MyTechnoBook - Files\" it solved the error.

Hope you find this article helps! If yes then do leave your comments :-)

In one of my previour projects for one of the reporting requirements I wanted to replicate the data from one database (where ETL process was carried out) to another database (reports can source the data from here).

To achieve this I had setup On Demand Transactional Replication. Initially it seemed to work fine for few tables, however all of a sudden I started getting an error saying:

"Length of LOB data (78862) to be replicated exceeds configured maximum 65536"

After searching on the web I came to know that SQL Server has a default value configured maximum length of text that can be replicated.

To get rid of this error run the following command in the SQL Server Management Studio (How to launch Management Studio from Run prompt):

EXEC sp_configure 'max text repl size', 2147483647
RECONFIGURE

Hope that helps!