Tag Archives: sql server

ASP.NET, Programming

Spamming Made Easy in ASP.NET

Receiving an auto-generated email is often unpleasant, but when deployed correctly, they can be useful for both customers and companies. C# and SQL make it easy to grab information from a database and send customized emails with little effort.

Step one: grab the data.

Before sending emails, information from the database, like email addresses, needs to be collected by a query. A SQL query is just a string that asks a database for a specific table, but users should be able to dynamically change it depending on their needs.  As such, certain lines need to be flexible.  In the past, I had used string formatting to insert text into the query, which looks like the following:

string text = "I ilke {0} more than {1}, honestly";
text = string.Format(text,"dogs",cats");

Drupal

Drupal on Windows: Using Windows Authentication with SQL Server

With the release of Drupal 7, it became easy to run the whole Drupal stack on Windows technologies. Instead of requiring a LAMP stack (Linux, Apache, MySQL, PHP), you now have the option of a WISP stack (Windows, IIS, SQL Server, PHP). The excellent Drupal 7 driver for SQL server makes using a SQL Server backend with Drupal possible. Even better, the underlying PDO (PHP Data Objects) driver for SQL Server supports Windows authentication! But wait… there are no instructions provided on using Windows authentication to SQL Server with Drupal! I knew that in theory it should be possible. After some tweaking, I finally figured it out. So how do you set it up? Here are the missing instructions…

Getting Windows Authentication working with SQL Server

Before you start, you will need either:

  • A domain user account, with both web server and SQL server joined to the domain
    -or-
  • Identical user accounts on both servers (same username and password); in this case the servers do not need to be joined to a domain

In my example below, I will use the identical user accounts method. Omit step 1 and substitute your domain account information wherever step 1 is referenced if you want to use the domain account method. Continue reading Drupal on Windows: Using Windows Authentication with SQL Server →

Excel, OfficeWriter, SharePoint

How to Create a Renewal Forecast Report with CRM, ExcelWriter and SharePoint

After finishing off our ASP.NET, SQL, and ExcelWriter training, the Technical Services Interns were given a project to create a proof of concept involving the dynamic generation of internal reports. We would use our new SQL knowledge to pull data from a customer relations database (Microsoft CRM), our ExcelWriter knowledge to create a rich Excel report from that data, and our ASP.NET skills to create a Web Form whereby users could constrain the reports they received. The reports would then be ported into SharePoint webparts. The reports were split into three categories:

  • Renewal Forecast
  • Case Distribution
  • Customer Activity

This post addresses the first of these reports. The Renewal Forecast report gives a company’s Sales and Support departments insight into customer contracts coming up for renewal.

Creating the Renewal Forecast Report

The purpose of this report is to provide a detailed overview of customers’ support contracts that are nearing expiration so employees can better manage the renewal process. The front-end in SharePoint is customizable so the report is generated completely dynamically and populated using queries from CRM. It is all ASP.NET code and uses a very sharp looking jQuery datepicker. Of course, the report generation is handled nicely using an ExcelWriter template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along! Continue reading How to Create a Renewal Forecast Report with CRM, ExcelWriter and SharePoint →

Women In Tech

Stories from the WIT Trenches: Jes Shultz Borland

Photo Credit: Jes Schultz Borland

[This is the second in a series of posts exploring the personal stories of real women in technology. Back in April I wrote a bit about my own history and about the problems, systemic and idiosyncratic, plaguing women who chose  a career in most sectors of the tech world. Writing it was surprisingly cathartic, and the response to it was powerful enough to make me want to push it further. Every woman in tech overcame at the very least statistical odds to be here; this blog series aims to find out why, and what they found along the way. This week we have the dynamic and inspiring Jes Schultz Borland, whom many of you know from her incredibly active social presence in the SQL and WIT communities. In March, Jes described her professional journey,with all its dips and twists and catalysts, for SQL University WIT Week, and reading her story was part of what encouraged me to tell my own. If reading this does the same for you, please feel free to email me.]

Hi! My name is Jes Schultz Borland. I live in central WI. I’m a runner, an avid cook, a Jaycee, and a database administrator for a Fortune 500 company. I love what I do. I love keeping SQL servers running with minimal downtime, I love writing scripts to automate or fix processes, I love writing reports, and I love helping developers tune queries. I’m also very involved in the (amazing) SQL community. I love answering people’s questions, I love speaking at user groups and SQL Saturdays, I love being on the board for my user group, and I love learning. I tweet, I blog, and I speak regularly.

1)      Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology? Continue reading Stories from the WIT Trenches: Jes Shultz Borland →

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.