The value provided for the report parameter 'LastUploadDate' is not valid for its type

 

I have a data driven subscription set up that passes a date value from a query to the report for processing. After processing an email is sent. However I did not receive the email. When the report runs the following error occurs in the report server log.

Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterTypeMismatchException: , Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterTypeMismatchException: The value provided for the report parameter 'LastUploadDate' is not valid for its type.;


The report server log can be found the installation directory of SQL Reporting Server. In my case: 
C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles


The field in the database is of type Date, as is the report parameter definition which is DateTime. If I pass  the date parameter as '2010-06-23' it works fine. On my previous reporting server this subscription work without any problem. I do not understand why it does not work anymore. It is a SQL2008 R2 Reporting Server which connects to a SQL 2008 server. After one day struggling with it I implemented next work around.
The parameter is called: LastUploadDate
The database field is called: Statdate

My previous query for the dat driven subscription:
SELECT Statdate, CustomerID
FROM Mytable

My workaround:
SELECT Cast(YEAR(Statdate) as char(4)) + '-' + CAST(MONTH(Statdate) as CHAR(2)) + '-' + CAST(DAY(Statdate) AS CHAR(2)) AS CASTDATE,

CustomerID
FROM Mytable

So I made a hard coded string of the date I needed. It is indeed not so nice, but it works.
If you have had the same experience? Please let me know how you have fixed it.

Troubleshooting and optimizing queries on SQL Azure.

In one of my previous blogs I wrote about some usefull DMV's to analyze SQL Azure performance.  SQL Azure is a cloud based relational database with SQL Server 2008 engine at its core. In the first release of SQL Azure most useful DMVs have been disabled. As part of the scheduled Service Updates (SUs) to SQL Azure, these DMVs are enabled in phases. Since SQL Azure is a shared infrastructure model, the DMVs have to be modified to filter the output and show information only as appropriate. In this effort, the following DMVs have been enabled in the first phase. These DMVs being released typically require VIEW SERVER STATE permissions in an on-premise SQL Server. The new permission level required on SQL Azure would be VIEW DATABASE STATE to query these DMVs.

Transaction related DMVs

• sys.dm_tran_active_transactions - returns information about transactions for the SQL Azure server 
• sys.dm_tran_database_transactions - returns information about transactions at the user database level 
• sys.dm_tran_locks - returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request. 
• sys.dm_tran_session_transactions - returns correlation information for associated transactions and sessions.

Execution related DMVs

• sys.dm_exec_connections - returns information about the connections established to SQL Azure and the details of each connection. 
• sys.dm_exec_query_plan - returns the showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing. 
• sys.dm_exec_query_stats - returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. 
• sys.dm_exec_requests - returns information about each request that is executing within SQL Azure. 
• sys.dm_exec_sessions - returns one row per authenticated session on SQL Azure.
• sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
• sys.dm_exec_text_query_plan - returns the showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing. This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences:  1) The output of the query plan is returned in text format. 2) The output of the query plan is not limited in size.

Database related DMVs

• sys.dm_db_partition_stats - returns page and row-count information for every partition in the current database.

As you can see, the number of DMVs is growing but unfortunaltely still no SQL Azure Profiler available. At this moment I got 204 votes for my idea for a SQL Azure Profiler on mygreatwindowsazureidea.com. So let's hope that a SQL Azure Profiler will come available in one of the coming Service Updates (SUs).

Enjoy it!

Whitepaper: Analysis Services Operatings Guide (SSAS)

Microsoft has published the whitepaper: Analysis Services Operatings Guide. In this guide you will find information on how to test and run Microsoft SQL Server Analysis Services in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 in a production environment. The focus of this guide is how you can test, monitor, diagnose, and remove production issues on even the largest scaled cubes. This paper also provides guidance on how to configure the server for best possible performance. It is the goal of this guide to make your operations processes as painless as possible, and to have you run with the best possible performance without any additional development effort to your deployed cubes. In this guide, you will learn how to get the best out of your existing data model by making changes transparent to the data model and by making configuration changes that improve the user experience of the cube.However, no amount of operational readiness can cure a poorly designed cube. Although this guide shows you where you can make changes transparent to end users, it is important to be aware that there are cases where design change is the only viable path to good performance and reliability. Cubes do not take away the ubiquitous need for informed data modeling. Fortunately, this operations guide has a companion volume targeted at developers: the Analysis Services Performance Guide. We highly recommend that your developers read that white paper and follow the guidance in it.

Enjoy reading the whitepaper Analysis Services Operatings Guide. To directly download the whitepaper from the Microsoft Download Center click here.

What is said about your business on Twitter? PowerPivot will help you.

Social media, like Twitter, is a powerful medium in which users can express thier emotion about what they are doing. Emotions can be positieve and negative. As a company, it is interesting to know what is said about your business. Of course you are happy if people post positive tweets about your business, but it will also happen that people post negative experiences. Microsoft has made a nice application which will help you to analyze what is said about your business on twitter. It is called: Analytics for Twitter.
You specify the hashtags, twitternames etc, you want to analyze and PowerPivot will do the rest for you. Analyze will be done on the tweets of the last 7 days. After downloading all tweets, you can start analyzing the tweets with the slicers of PowerPivot. Click on the next picture to enlarge the example of Analytics of Twitter.

Analytics for Twitter can be downloaded here.
Requirements: latest version of Microsoft Excel 2010, Microsoft PowerPivot for Excel 2010 

Enjoy analyzing your business on Twitter.

Dashboard design rules, the do's and don'ts

If you are going to build dashboards you need to read the book: Information Dashboard Design written by Stephen Few. Dashboards are very popular because they can be very powerful. However, this potential is rarely realized. A dashboard should directly tell you what you need to know. If that is not the case the dashboard will never be used again. This book will teach you the visual design skills you need to have to create dashboards that communicate clearly, rapidly and compellingly. It will explain how to:

• Avoid the thirteen mistakes common to dashboard design
• Provide viewers with the information they need quickly and clearly
• Apply what we know about visual perception to the visual presentation of information
• Minimize distractions, cliches, and unneccessary embellishments that create confusion
• Organize business information to support meaning and usability
• Create an aesthetically pleasing viewing experience
• Maintain consistency of design to provide accurate interpretation
• Optimize the power of dashboard technology by pairing it with visual effectiviness

The book start with the definition of a dashboard. Without knowing the definition you do not know what to build.

Visual display

of

the most important information needed to achieve on or more objectives

which

fits entirely on a single computer screen

so it can be

monitored at a glance

I will tell some observations I had when I read the book with a lot of pleasure.

• We do not see with our eyes. We see with our brains. If you need to think how to read the data, you have not used the best display media.
• Dashboards display information needed to achieve objectives. What do you prefer? 1) Nice fancy dashboard which is not be re-used. 2) Functional dashboard which help you to reach your goal
• A dashboard fits on a single computer screen. No scroll bars etc.
• Colors have a function. Do not use them because it looks nice. Colors should tell something for instance to grab attention .
• Blank space is better than meaningless decoration. Do not use pictures, logos'. It is a waste of valuable space. If you need to use a logo, make it small and visually subtle, and place it somewhere out of the way.
• Use gridlines will care. Mostly the do nothing but distract from the data.  
• Think about what you want to show or compare. Based on that you need to choose the best display media. Do not use a pie chart because it looks nice. Mostly a bar graph is much better.
• 3D Graphs are nice but 2D are easier to read and understand.
• Dashboards are used to monitor information at a glance. Information is abbreviated in the form of summaries or exceptions.
• Numbers should not be center-justified in the columns. Right-justified is easier to compare when scanning up and down a column.
• The drop shadows on a graph are visual fluff. These elements serve only to distract.
• In general Keep It Simple.

To test your dashboard design skills you can do the Graph Design IQ Test on the website of the writer Stephen Few.

Enjoy reading the book and do not forget to do the Graph Design IQ Test

SQL Azure Data Sync CTP2 My first experience.

SQL Azure Data Sync enables creating and scheduling regular synchronizations between SQL Azure and either SQL Server or other SQL Azure databases. It is a cloud-based data synchronization service built on the Microsoft Sync Framework technologies. It provides bi-directional data synchronization and data management capabilities allowing data to be easily shared across SQL Azure databases within multiple data centers. The current release is Community Technical Preview 2 (CTP2).

For a Proof of Concept I want to synchronize a subset of my on premise database to a SQL Azure database. In this blogpost I will explain what need to be done to synchronize on premise data to a SQL Azure database.

Requirements before you can start:

• You must have a Windows Live ID
• You must have a SQL Azure account
• SQL 2005 SP2 or later.
• Request a registration code for SQL Azure Data Sync CTP. This can be requested here.  After registration you can start using SQL Azure Data Sync
• On premise SQL database.

The synchronization of data is handled by the SQL Azure Data Sync Agent. In the SQL Azure Data Sync UI you can define the agent.

The agent itself can be installed with the AgentServiceSetup.MSI. This MSI can be found at the bottum of the SQL Azure Data Sync UI where you have defined your agent. After installation of this agent a local service is created named: SQL Azure Data Sync Agent.

The UI of this services can be started via Start, All Programs, Microsoft SQL Azure Data Sync, Microsoft SQL Azure Data Sync.

To configure the agent, press the Edit Agent Key and paste the agent key which is generated when you created the agent in the  SQL Azure Data Sync UI
Use the Add Member button to add your on premise database to the agent.
Use the Ping Sync Service to test the agent.

In the SQL Azure Data Sync UI in the Tab Databases, you will see your on premise database. Press the add button to add your SQL Azure database.

Now we are ready to create and configure a Sync Group.A Sync Group is a collection of SQL Azure and SQL Server databases that are configured for mutual synchronization by the SQL Azure Data Sync Services

• Press the New Sync Group button to add a Sync Group.
• Add your on premise database and SQL Azure database to the Sync Group.
• Press the next button and select the tables you want to synchronize. Some tables are not possible to select because:
• They do not have primary keys defined.
• They have clustered indexes defined on non-primary key columns.
• Enable the Sync Group and configure the synchronization schedule.

As mentioned not all tables are possible to synchronize. In a later blog I will explain what you can do for these tables and also how you can synchronize a subset of a table.

What is not possible in CTP2:

1. Changes in data model are not supported. For instance in my on premise database we create a new column to a table which is synchronized. This new column can't be added to the synchronization.
2. Changes to a Sync group can't be made. If you want to add an additional table to the synchronization, you can't add it to the existing synchronization group.
3. Synchronize tables for which the clustered index is not the primary key. In a lot of situations the primary key will differ from the clustered index. This is because of performance reasons.

Overall conclusion:
My first impression of the SQL Azure Data Sync is good. Big progress is made. It is easy to setup. To use it in a commercial product I think that the 3 things I mentioned are essential for a commercial solution.
: Tweet