Recently in SQL Compact Category

A third-party report generator (Pebble Reports) sent me this IBM presentation (see page 71) when I asked if their tool supported 2005, 2008 or 2010 RDL (they only support 2005).

“We depend on ReportViewer control for printing purposes and we are as miffed as you are that ReportViewer is always out of sync with ReportServer. Even MS competitors are taking advantage of this situation, for example see slide 71 of this IBM presentation where they talk about Microsoft's version incompatibilities.”

This further adds to the argument that Microsoft is not seen as capable of keeping their various development paradigms and the metadata they support in sync. I pounded on this issue two years ago when Visual Studio 2008 was getting ready to ship. However,  two years later Visual Studio BI and ReportViewer developers are facing the same lack of compatibility with the latest RDL (about to ship with SQL Server 2008 R2). Are developers expected to wait another two years before the ReportViewer control will work with RDL 2010 in local mode? By then I expect additional innovations to be out of reach as RDL evolves—leaving the ReportViewer control perpetually one version behind.

Progressive Business and Beta V have partnered together to present another series of developer-centric webinars. The current series focuses on SQL Server and Reporting Services and consists of six 90-minute talks given two a day for three days. The next scheduled offerings are September 9-11, October 13-15 and November 2-5th. See the following link for an outline and pricing details. Note that this series includes a copy of Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition) as well as a copy of Hitchhiker’s Guide to SQL Server 2000 Reporting Services.

Visual Studio, SQL Server & Reporting Services: 6 High Impact Training Sessions
This is a set of 6 seminars given in two parts one that focuses on SQL Server, the second on Reporting Services. This high-impact series of training webinars is for anyone who wants to leverage Visual Studio, SQL Server and Reporting Services best practices- learning what works, what doesn't and why. These sessions are for developers, architects and managers who want to know how and (more importantly) when to leverage the power and benefits of SQL Server and Reporting Services.

The webinars I’ve already recorded (and those I’m going to present) can be found by visiting this site. Look under the “IT” heading to see links to the content. The recorded sessions might be repeated in the future but the recording are available:

CLR Executables: Stored Procedures, Functions, Aggregates, & User-Defined Types
(presented May 13, 2009)

• This session will discuss how to create CLR executables in Visual Basic.NET and C#. We'll see how to create CLR stored procedures, functions, aggregates as well as user-defined types. The session demonstrates CLR executable development through use of Visual Studio as well as SQL Server Management Studio.

Hitchhiker's Guide to Visual Studio & SQL Server Reporting 

(presented April 7, 2009)

• This session discusses how Visual Studio developers can leverage the power of the Report Definition Language to manage and generate client-side reports or launch SQL Server Reporting Services reports. We'll discuss the latest MicrosoftReportViewer control as well as the Business Intelligence toolset exposed in Visual Studio 2008.

Managing and Writing High-Performance SQL Server Stored Procedures
(presented March 12, 2009)

• Stored procedures have been recognized by database administrators and developers as the most efficient mechanism to access and protect SQL Server databases. When written and executed correctly, these server-side blocks of code can significantly improve performance, security and developer efficiency.

I wrote a sample application that illustrates how the SQL Compact (what Microsoft calls “Microsoft SQL Server Compact Edition”) database used with the Local Data Cache gets out of sync (which I would expect it to) with the host database and how the application can get it back in sync. However, it seems that when they are really out of sync, the only thing to do is to clear out the client database and start over.

Being a realistic guy, and based on past experience, I guess I need to put my sights on versions much further ahead to get anything incorporated into Visual Studio. Assuming Microsoft is still in business and Visual Studio still exists in 5 years, here is a clarification of a feature I’ve been asking for since VS 2000.

Despite the fact that you’ve used the encryption option on your SQL Compact database, if you don’t hide the password, it’s really easy to figure it out. I suggest you (at least) use an obfuscator or better yet, your own (private) technique to hide the value. Consider that there are ways to “reflect” the underlying source of .NET programs because they are stored as CLR intermediate language on your client’s system.

Patrick, I could not agree more with your editorial this month. Based on what I hear when I leave my cave is that Microsoft just doesn’t get it. Because of politics (no, not the Bush/Obama kind) and an old-school marketing and sales plan, Microsoft has also forced its own development teams to pump out new versions of SQL Server and other serious platform engines on a ever-shortening cycle. The same is true for Visual Studio and the languages teams whose development cycles seem to be totally consumed trying to get the Entity Framework working. This means the teams place less emphasis on fixing existing long-term bugs and making development of existing architectures easier. Over and over again we hear that “Microsoft knows what’s best for developers”.

If you’re using the Visual Studio 2008 (SP1) Data Source Wizard or Local Data Cache wizard and you get a message like this:

Synchronizing the database failed with the message:
'Unable to initialize the client database, because the schema for table ...[list of tables]... could not be retrieved by the Getschema() method of DvServerSyncProvider.

Be sure to check the compatibility level of the targeted database to make sure it’s set to SQL Server 100 (2008). This should have been tested before the database was listed as supporting automatic sync support, but in any case, the exception message should show the reason for the error, not the cause.

To set a database’s compatibility level, open SQL Server Management Studio, open the database and choose Properties | Options. Use the drop-down to choose the right compatibility level. Of course, this assumes that the database is closed elsewhere (like in the Visual Studio Server Explorer). Once you change compatibility level, the engine rechecks the schema etc. to make sure there aren’t any compatibility issues.

Also, make sure you try to compile your project with x86 or x64 explicitly. I’ve discovered some disconnects with the default <AnyCPU> configuration setting. You can ignore the warnings when using x64 that complain that mscorlib.dll, system.data.dll and system.transcations.dll target a different processor. This is a known bug.

In some cases you might have to get the Local Data Cache wizard to rebuild the .SDF cache database to properly sync with the server-side table(s). Simply open the LocalDataCache.sync and make a property change and set it back once the sync has completed.

hth