Report action or Drill down or Sub report is not working in SSRS Report viewer webpart

When we have report action or drilldown or sub report which is rendered in SharePoint 2010 using SSRS report viewer web part you will run into issues that the report is not rendering after the first page load. This is caused by the Debug="True" in the compilation tag in web.config file. In our environment the report was working fine until we made this change in web.config for some other debugging. But we haven’t noticed that this has broke our report and since we had backup of all the web.config changes we were able nail this issue to debug property. Apparently we are able to find that there is a fix from MS based on the below msdn thread. Refer the blow link for more information,
http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/1329b784-d050-4d17-bb5d-f42853f420fc/ 

Enabling FIPS on Windows 2008R2-SP1 – SharePoint and asp.net 3.5 web applications stopped working

After we enable FIPS encryption in system security settings the asp.net 3.5 based applications which uses the script manager control stopped working. SharePoint application also falls under this category and there is a Hotfix from Microsoft for this (KB981119). Unfortunately this hotfix applies to W2008R2 which came before the SP1.

If you are using the OS which comes with SP1 you will not be able install this hotfix (since it’s older than SP1). At least as of now there is no newer version of this fix for the system which comes with SP1.

To fix this issue, update the <machineKey> section in your config file with the below mentioned value. I assume that’s what the hotfix does. Since we are not able to install the hotfix we have to manually update the config files. For SharePoint applications make sure you update the machinekey values in all config files including central admin.

<machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="3DES" decryption="3DES"/>

Hope this helps someone!

Loading Data to SQL Azure using BCP and SSIS

There are multiple ways you can load the data into SQL Azure DB. But I am going to write about loading data using BCP and SSIS. Refer the below article to find out the different ways to load the data to SQL Azure,
http://blogs.msdn.com/b/sqlcat/archive/2010/07/30/loading-data-to-sql-azure-the-fast-way.aspx

While writing this post i found that there is a simplified way to Import and Export the data from/to SQL azure is on it's way,  http://blogs.msdn.com/b/sqlazure/archive/2011/03/24/10145578.aspx 

Loading data using BCP : BCP utility offers lots of flexibility to load data into SQL server and if you are familiar in using this against normal SQL Server DB then it’s almost to use it against SQL Azure.

Below is the requirement that we need to accomplish,

Create a DB and tables in SQL Azure and copy the data from flat files to the newly created tables. This has to be implemented in such way that script can be executed multiple times or can be executed against different SQL Azure instance.

1)   Use batch file to create Azure DB which accepts the connection string as parameter using Sqlcmd utility. Refer sqlcmd utility to learn more about it,

Eg: sqlcmd -U loginname@servername –P ******** -S servername.database.windows.net -d master -N -m-1 -V 1 –Q "CREATE DATABASE YourDataBaseName (MAXSIZE = 1 GB)".

To connect to SQL Azure, the username has to be provided in loginname@ServerName notation and the server name has to be fully qualified name of the SQL Azure (can be copied from the Data Manager UI). I have used –N to encrypt the connection and set -V to 1 to capture if there is any error. –m can be used with –V to return values to stdout, -1 will return all error and informational messages to stdout. –Q to is execute single query with exit. This will be useful when you just want to execute a single query and exit sqlcmd. Sqlcmd parameters are case sensitive.

2)      Using the Sqlcmd in the same batch file you can create the database objects by executing the sql script file with –i parameter.

Eg : sqlcmd  -U loginname@servername –P ******** -S servername.database.windows.net –d newdatabase -N -i CreateObjects.sql

CreateObjects.sql file has the script to create tables, views, .etc.

                Use the errorlevel which was captured from the previous command to execute this command. Incase if there was an error while creating the DB then there is no point in executing this step. If there is an error then the errorlevel variable will be set to the error # from stdout value from sqlcmd.

3)      Once the database and the tables are created successfully you can use the bcputility to load the data. Refer bcputility to learn more about it,

Eg: bcp dbName.dbo.[TableName] in Filename. -U loginname@servername –P ******** -S servername.database.windows.net –c

Note that I didn’t specify a field delimiter or row delimiter \t and \r\n will be used by default. –c is used to perform this operation using char data type. In case if your input files are complicated than simple tabbed delimited file then use the format file to define the mapping b/w the flat file and the table. For this test I have exported records from the existing SQL Server as tabbed delimited file.

If you are able to execute these three commands successfully then you have inserted the data into new SQL Server DB from a flat file.

You can customize all these commands in a single batch file which accepts parameters from instead of hard coding and then the batch file can be reused to meet our requirement.

Loading Data from SSIS:

Loading data from SSIS is pretty simple as soon as you are able to create the connection with your source and destination. You have to use the user name as “Loginname@Servername” notation as you did while executing the sqlcmd or bcp commands to create a connection with SQL Azure DB.

1)      Create the connection manager for Source and Destination components.

2)      Create a dataflow task and map the source and destination.

3)      The beauty of SSIS is, you can manipulate the data before you load it and you can have multiple sources unlike bcp which is restricted to only flat files.

4)      You can use the Execute SQL Command task if you have to execute a query.

5)      In case if you have to create the database and tables dynamically, you have to create the Package with all tables created to map the source and destination and then drop the database and set the delay in validation property for the package to true. Below is the screen shot my sample package,

Things to remember:

·         Create Database should be executed separately since it will not run with any other statements.

·         Create Database has to be run before we execute the create table script since we need to specify the newly created database in connection string. SQL Azure doesn’t support USE statement so we cannot change the database after connecting to master database.

·         Use bcp if you have to load large amount of data where you don’t need to manipulate the source file. It’s faster than SSIS in my test.

·         Use SSIS if you have to manipulate the input and if you have different sources. It takes little bit of time to initiate the connection and executing the package because of validation.

·         In Execute SQL Command task you cannot pass database name as parameter to execute the “Create Database” query since it will be executed as multiline statement and it’s not supported in SQL Azure. Create Database statement should be the only statement which can be executed in SQL task. The best way to create Database dynamically is by using variable to store the SQL whole create database statement.

·         You can execute the tasks parallel to get better performance however it depends on your network speed as well.

·         In SSIS when you are working with huge amount of data or executing multiple tasks in parallel make sure you follow best practices. For Eg, changing the temp location, and buffer path from default, breaking executing tree.

·         There is a high possibility that SSIS works perfectly for smaller amount of data in your test environment and it will fail to execute or simply hang production environment when you execute with large amount of data. It’s always better to test with the highest anticipated volume of data if you choose to use SSIS.

·         As suggested in the initial article, try to split the input file for SSIS and use first row and last row attributes in bcp if you are dealing with larger data file.

·         Last but the not the least, since we are connecting to the cloud there is high possibility that we can lose our network connection in the middle. So always have plan for restartability.

Create Database in SQL Azure

In the past couple of days I am spending some time on SQL Azure and it's really exiting. If you are regular user to a SQL Server DB then you will find it real easy. In this post and we will see how to create a new SQL Azure database and what the prerequisites are,

You should be member of Windows Azure Platform. If you don’t have one click here to create one.  You need to provide the credit card info and up to 500 MB storage and 1GB web DB (90 days only) free and you won’t be charged if you are not crossing the limit.

After you create the windows azure account you will receive a confirmation email to your live id and your live id is your Account Owner (AO).  Account Owner is set to Service Administrator (SA) by default and it can be changed any time by AO.  Please refer the below link to understand how the accounts work in Windows Azure.

http://silverliningincloud.typepad.com/my-blog/2010/04/windows-azure-buying-process-and-account-management.html

Once the account is created, login to Windows Azure platform management portal and follow the below steps to crate your first SQL Azure DB,

Step1: Click “Database” from the left navigation and select the Subscription and click create from the top ribbon bar to create the SQL Server. Provide the admin credentials and use the proper firewall setting while creating the server and you can always use it to restrict to your current IP.

Once the server is created make sure you are able to access it by clicking the “Test Connectivity” and provide the proper credentials that you have used when you created the server.

Step2: By default master DB will be created when the server is created and you can create your own DB from the top ribbon bar. While creating the DB the default edition is Web and the maximum size is 1GB, leave it to the default so that your credit card won’t get charged.

Step3: Click mange from the top ribbon to connect to the database, you will be redirected to the “Database Manager” which is similar to web based SQL server management studio with lots of limitations. You need to know the database name that you are connecting and you cannot change the database after you login, you have to log out and log back in. This is the first limitation.

You can use the same connection string to connect SQL Azure DB from SQL Server Query Analyzer.

Step4: When you are connected to the database you can create objects like tables, views, stored procedures. You can also use scripts to create them by using “New Query” option under “Database” Tab. You can also save and open an existing query to/from your desktop.

There are certain limitations in using SQL azure Database, please refer the below article and I strongly recommend reading this, http://msdn.microsoft.com/en-us/library/ee336245.aspx

Things to remember,

• You need to connect to master database in case if you are creating a new database from the query window.
• Create Database query cannot be executed with any other query.
• You cannot insert data into table if it doesn’t have clustered index. It sounds weird to me, when we want the Datawarehousing to be in the cloud you cannot expect every table to have a clustered index. I am able to insert data into a temp table without the index, in case if you are writing any complex logic in your stored procedures this might be useful.
• There are also lots of limitation in the SQL query, like you cannot use “USE” statement and you can never simply export the script from your normal SQL Server database and execute them in SQL Azure DB. You need to make some modifications before you execute them successfully.
• If the connection is idle for 30 minutes, it will be closed automatically and please close the connection if you are not using it. You may be disturbing somebody else’s performance since you are not the only one who connected to the Windows Azure.

If you were able to follow the above steps then you have created a new Windows Azure subscription , new SQL Azure server, new database and some DB objects. It’s pretty simple till now, and i will write how to insert data from your in house SQL server database to SQL Azure database in the coming posts.