Category Archives: SQL Server

Business Intelligence, OfficeWriter, Reviews, SharePoint, SQL Server, SSRS

Jason Thomas Reviews OfficeWriter’s SSRS Integration

The following is a review of OfficeWriter written by Jason Thomas, a BI consultant specializing in SSRS.  Read the full review here.

“As a BI consultant specializing in SSRS, I have had lots of frustrations and hard times because of Excel. Every now and then, I have some or other business user coming up to me and asking for some feature which is there in Excel but not in SSRS. If you have been following my blog, you would already know that I am more of a work-around man, trying to find some alternative for features which are not supported out of the box. But when it comes to Excel related features, most of my attempts end in disappointment. So naturally, my ears perked up when I was asked to review a plugin which claimed to build SSRS reports using Excel and Word.

So I downloaded OfficeWriter v8 and spent close to a week playing around with it. Even though I encountered some minor quirks (v8.0 doesn’t run on the 64 bit version of Office 2010 yet – luckily I had a home pc with a 32 bit version of Office; got some minor issues when editing and deploying an existing SSRS report with shared data sources – got around it by setting the data sources once again from the report manager), overall I have been very pleased and of course, excited at the different prospects that this plugin opens up.”

[Click here to read the full review]

Social Media, SQL Server

#Meme15 Assignment 2: All A’Twitter

1 Comment

sqlfamilyA new monthly blog series has entered the #sqlfamily. The brainchild of Jason Strate (b|t), “#Meme15” focuses around the ways social networks can further our professional development.  This month’s assignment is one dear to my own heart (and brain. And fingers): Twitter. I’ve written before about what Twitter can do for your company—how it can give high-tech B2Bs personality, credibility and new leads. What I haven’t covered as much is what it can do for you, the employee. There are two questions in the assignment:

 

  • Why should average Jane or Joe professional consider using twitter?
  • What benefit have you seen in your career because of twitter?

As a person whose primary job responsibilities involve social media, I’m going to go with the first option—for an excellent answer to the second, check out Stacia Misner’s response.

So, why should you, the non Social Media Marketer/Specialist/Strategist etc use Twitter? In short, there are three main reasons: build relationships, gain knowledge and enhance your public image.

In slightly longer, Twitter is a public conversation, a place to learn, share and connect. Someone posts a link to a blog post about Power View; you read it and learn something new about Power View (animated data points, oh my!). Someone asks a question about stored procedures, aka your pride and joy, and you answer them. Bonds form between the teachers and the taught, the @er and @ed, tweeter and retweeter—but they can also form, albeit more loosely, between all of the above and their networks of listeners. When you perform any activity on Twitter, from favoriting a Tweet to organizing a Tweetup, it deepens your digital profile to anyone who thinks to look or happens to listen at the right time.

Twitter allows you to join  (or start!) non-geographically-restricted communities grouped around any interest or combination of interests. It lets you play pin the avatar on the body at conferences. It’s a virtual kickstarter for eventual IRL relationships. For all the banality of some of its content, Twitter’s function as a connector is far from trivial.

 [#Meme15 logo by Matt Velic]

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SQL Server, SSRS, WordWriter

Reporting Services Error: Could not load file or assembly ‘x’ or one of its dependencies

Problem

You want to use a newer version of ExcelWriter or WordWriter with OfficeWriter’s Reporting Services integration. A common scenario is upgrading to an intermediate build of ExcelWriter or WordWriter which fixes a bug or adds a new feature.

To provide context, OfficeWriter’s Reporting Services integration consists of the following components:

  • ExcelWriter (SoftArtisans.OfficeWriter.ExcelWriter.dll)
  • WordWriter (SoftArtisans.OfficeWriter.WordWriter.dll)
  • A custom rendering extension (SoftArtisans.OfficeWriter.RS2008.dll for SQL Server Reporting Services 2008)

When upgrading to a different version of ExcelWriter or WordWriter, normally you also have to upgrade to the matching version of the custom rendering extension. This is because the custom rendering extension is built against a specific version of ExcelWriter and WordWriter and only works with that version. If the version of ExcelWriter or WordWriter does not match the version of the custom rendering extension, you will get this error when exporting the report using ExcelWriter or WordWriter:

Could not load file or assembly ‘x’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference.

If you are performing a regular upgrade in which you replace the custom rendering extension DLL as well as the ExcelWriter or WordWriter DLL, use the manual installation instructions as a guideline.

However, if there are no changes to the custom rendering extension, it is convenient to use the same custom rendering extension DLL and replace just the ExcelWriter or WordWriter DLL. This approach also allows you to easy test any version of the ExcelWriter or WordWriter DLL. In this case, follow the below instructions.

Solution

To upgrade the ExceWriter or WordWriter DLL without having to replace the custom rendering extension, you can create an assembly binding redirect so that request for the old DLL are redirected to the new DLL.

The solution is also applicable to any ASP.NET application for which you want to replace a dependent assembly with another version. Use the instructions below as guidelines for making modifications to the application’s web.config.

The process for creating an assembly binding redirect is slightly different for ExcelWriter and WordWriter, because by default WordWriter is installed into the Global Assembly Cache (GAC).

Upgrading ExcelWriter

1. Navigate to the Reporting Services directory, typically `DRIVE:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services’

2. Copy SoftArtisans.OfficeWriter.ExcelWriter.dll to ReportServer\bin, replacing the existing DLL

3. Determine the version of the new DLL; for example, 7.5.1.2770

4. Edit ReportServer\web.config and add the following section under the section:

XML Config Script

 <configuration>     <runtime>         <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">             <dependentAssembly>                 <assemblyIdentity name="SoftArtisans.OfficeWriter.ExcelWriter" publicKeyToken="f593502af6ee46ae" culture="neutral" />                 <bindingRedirect oldVersion="7.5.0.1-7.5.1.9999" newVersion="7.5.1.2770"/>             </dependentAssembly>         </assemblyBinding>     </runtime> </configuration>

Note: If there is an existing section, add only the ” section inside it, so that there is only one assembly binding definition.

In the example above, set the newVersion attribute to the version of the new DLL. Set the oldVersion attribute to a single version or a range of versions which are to be mapped to the new version.

5. Perform the same steps for ReportServer\bin\ReportingServicesService.exe.config

6. Restart the Report Server

Upgrading WordWriter

If WordWriter is installed in the GAC, as done by the installer, you must also install the new DLL into the GAC. Because the GAC supports multiple versions of a DLL, you do not have to uninstall the old DLL. However, by using assembly binding redirect, you can force Reporting Services to use the new DLL. Follow the procedure below to upgrade WordWriter:

1. Install the new DLL into the GAC using, for example, the gacutil tool.

2. Navigate to the Reporting Services directory, typically ‘DRIVE:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services’ Copy SoftArtisans.OfficeWriter.WordWriter.dll to ReportServer\bin

3. Determine the version of the new DLL; for example, 4.5.1.1648

4. Edit ReportServer\web.config and add the following section under the section:

XML Config Script

 <configuration>     <runtime>         <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">             <dependentAssembly>                 <assemblyIdentity name="SoftArtisans.OfficeWriter.WordWriter" publicKeyToken="f593502af6ee46ae" culture="neutral" />                 <bindingRedirect oldVersion="4.5.0.1-4.5.1.9999" newVersion="4.5.0.1567"/>             </dependentAssembly>         </assemblyBinding>     </runtime> </configuration>

Note: If there is an existing section, add only the section inside it, so that there is only one assembly binding definition.

In the example above, set the newVersion attribute to the version of the new DLL. Set the oldVersion attribute to a single version or a range of versions which are to be mapped to the new version.

5. Perform the same steps for ReportServer\bin\ReportingServicesService.exe.config

6. Restart the Report Server

If WordWriter is not installed in the GAC, skip step 1 and follow steps 2-7 in the above procedure.

SQL Server

Unexpected Issues with the New Office 2007 File Format MIME types

One of the differences between the new OOXML file formats (e.g. .xlsx, .xlsm, .docx, .docm) that were released with Office 2007 and the older binary file formats (eg. .xls, .doc) is that each OOXML file extension has a unique Multipurpose Internet Mail Extension (MIME) type. This is a departure from the MIME types for the binary file formats, where the same MIME type could apply to several file extensions.

For example, the binary “application/vnd.ms-excel” MIME type applies to the .xls, .xlt, and .xla file extensions, but OOXML .xlsx and .xltx have separate MIME types: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet” and “application/vnd.openxmlformats-officedocument.spreadsheetml.template,” respectively.

An unexpected issue

The change in MIME types can cause unexpected issues to occur. I recently helped a customer who was experiencing strange behavior in his upload application. In his application, he was using FileUp to upload files to a SQL server database, in addition to some information about the files. When the customer tried to upload an XLS file, the upload completed successfully, but if he tried to upload an XLSX file, he started getting error messages. Continue reading Unexpected Issues with the New Office 2007 File Format MIME types →

SQL Server, SSRS

How to create a dynamic Reporting Services Shared Data Source Using Linked Servers

1 Comment

When using SQL Server Reporting Services (SSRS), wouldn’t it be nice to be able to specify the data source at run time? For example, you could deploy a report on a single Report Server, but have the ability to retrieve data from any database on any server. Without a dynamic data source, you would have to deploy multiple instances of the report, one for each database server you want to use. Additionally, if you are using stored procedures, you must also duplicate them on each database server.

SSRS already allows for a dynamic data source through an expression-based connection string. The main limitation is that an expression-based connection string can only be used with an embedded data source; i.e., one that is contained in the report definition. You cannot use it with a shared data source. However, a shared data source offers advantages over an embedded data source and is often a necessity. Imagine having to modify the data source for hundreds of reports. With an embedded data source you would have to edit every report, while with a shared data source you could do so in just one place.

This post discusses a solution that lets you specify the database server at run time, while still being able to use a shared data source. The solution uses linked servers. A linked server is essentially an alias for a remote server on a local server. Once defined, a linked server allows you to execute queries on the remote server locally. This solution consists of the following steps: Continue reading How to create a dynamic Reporting Services Shared Data Source Using Linked Servers →

Mobile, SharePoint, SQL Server

Using SQLite in BlackBerry Applications

We recently decided to add some basic persistence to Pylon, our latest mobile application for accessing SharePoint. Nothing complicated, just logging levels, username and SharePoint URI.

We decided to use SQLite for persistence instead of the BlackBerry Persistent Store  because of our previous experiences with the latter – where we encountered some stability issues such as the insidious 523 error which requires removing the battery to fix. We were also looking for an approach that would be more compatible when we port our application to iOS and Android.

The BlackBerry code samples for basic SQLite operations are straight-forward: http://docs.blackberry.com/en/developers/deliverables/8682/Code_samples_702046_11.jsp

However after basing our code on the samples we did run into a couple of issues.

SQLite not working on some phones – “Error: file system not ready”

The first issue we ran into was that the database simply didn’t work for certain phones. The confusing thing was that it wasn’t even consistent for phones of the same model.

It turns out the key was this line in the BlackBerry documentation:

“You can create database files in eMMC memory, on devices that support it, by specifying the corresponding file system path.”

Our application would use an SD Card if there was one, otherwise we would use device memory. If the device didn’t have an SD Card AND it didn’t have eMMC memory then we would get a “file system not ready” error when creating a database.

Phones that support SQLite DB in device memory (eMMC)

  • Torch
  • Storm 2

Phones that DON’T support SQLite DB in devicememory

  • Bold
  • Curve

Even with an SD Card, SQLite might not work if Media Card Support is turned off or if the phone is tethered to a computer and Mass Storage Mode is turned ON.

Intermittent Error – “Error: File system error (12)”

Another error we ran into was that sometimes opening the database soon after reading or writing to it would fail with the error “File system error (12)”. Just for testing I added code that would try to open the database a second time in case of failure after sleeping 100 milliseconds. This second attempt to open the database would always succeed.

Looking for “File system error (12)” online I found a few instances that were determined to be caused by bad queries or by failing to close the database or a SQL Statement. However the exact same query that worked most of the time was being called when the error would occur and after reviewing the code we determined that all databases and SQL statements were being correctly closed.

It turns out that we were following the BlackBerry examples a little too closely and opening/closing the database for every read/write operation. The overhead of this meant that the database wasn’t ready to be opened in time for the next operation.

We fixed the problem by only opening the database once the first time and then reusing that handle all subsequent times until we close the database on application exit.

SQL Server

T-SQL Tuesday 016: When is yesterday, exactly?

This is my contribution to the “recurring, revolving blog party” that is T-SQL Tuesday. Thanks to this month’s host, Jes Borland, for the topic!

On a consulting project some months ago, our client had a database full of daily financial data. For every day that the markets were open, we had a row with some data about the trades that had happened that day. If we ignore all the sticky details, it looked something like this:

date high low
3/8/2011 10 8
3/9/2011 11.5 9
3/10/2011 10.5 8.5

One of the reports we wanted to generate from this data was a day-on-day comparison, where each day’s high and low were compared with the previous day’s to see how it was changing. The goal was something that looked like:

date today’s high yesterday’s high change today’s low yesterday’s low change
3/9/2011 11.5 10 +1.5 9 8 +1
3/10/2011 10.5 11.5 -1 8.5 9 -.5

Continue reading T-SQL Tuesday 016: When is yesterday, exactly? →

SQL Server

SQL Blogger Superlatives

1 Comment

When I get into the office each morning, the first thing I do is fire up the ole Google reader and revel in (and tweet about) the bounty of new reading material. In both quantity and quality, the amount of information members of the SQL community share on a regular basis is staggering. As a former full-time blogger?, I know just how much mental effort a well-constructed blog post can demand, and the fact that many bloggers are doing this for free, on top of their not exactly insignificant day jobs, is beyond impressive.  Sooo…where I am going with this? Well, I decided (in a bout of gratitude and high school nostalgia) do write up a superlatives list, yearbook-style. Of course, as I was the sole decider, many of you may disagree with my choices. (And, as my memories of senior year are, um, rather hazy, I may have missed some vital categories.) If so, feel free to let me know in the comments!

And now, without further ado, I present the Superlative SQL Class of 2011!

Most likely to be in a rock band: Thomas La Rock

 

 

 

 

 

Life of the party: Brent Ozar

 

 

 

 

Continue reading SQL Blogger Superlatives →

SQL Server

Add Remote Network Service Account to SQL Server

1 Comment

I make extensive use of virtual machines for testing purposes. I recently needed to allow the Network Service account of one of my VMs to connect to SQL Server on another of my VMs. Both machines are on the same domain, so I thought I could use the search feature on the new login dialog to find the Network Service for that machine. Unfortunately it was not so simple. I also had quite a difficult time find information about it on Google.

It turns out to be rather simple though. You just append $ to the name of the remote machine. Suppose my remote machine is on the domain “OURS”, and the machine’s name is “MINE”. Then the SQL login for the Network Service account on MINE is “OURS\MINE$”. You won’t be able to verify this with “Check Name” though. You’ll have to actually try connecting the the SQL Server from the remote machine to make sure it works properly.

Hopefully this will save someone else an hour or two of searching on Google.