Tag Archives: sql server

[Webinar] Grand Slam Your SSRS Reports

June Webinar

Tune in next week Friday, June 28, 2013 at 1 P.M. for our June webinar as we give you another inside look at how OfficeWriter enhances SQL Server Reporting Services (SSRS), making your reports a home run. Senior Sales Engineer, Chad Evans, will walk you through using both single and multi-value parameters and formulas in SSRS. Using baseball data on players’ batting averages and salaries, this webinar is sure to impress. Don’t miss out! Seating is limited.


*Can’t attend? Register anyway, and we’ll send a copy of the slides and recording following the webinar.

How to open a report from SQL Server Reporting Services with the Application object

Problem

You want to modify your reports with the WordApplication or ExcelApplication object after they’ve been populated with data by SQL Server Reporting Services. The ExcelApplication or WordApplication object’s Open method can’t pull a report from a report server, so how do you open a SSRS report with OfficeWriter?

Solution

For simplicity and ease of reading the code examples and text in this article refer to the ExcelApplication object exclusively. However, the exact same techniques can be used with the WordApplication object. The only strict requirement for opening reports from SSRS is for the Open method to have an overload that takes a Stream argument, which both objects have.

SQL Server Reporting Services exposes a web service that lets programs access and work with the reports on the server. The web service has a number of functions for interacting with a report server and in fact is the same API that the Report Manager tool is built on top of. One of its functions will let us retrieve a fully rendered report that we can then pass to the ExcelApplication object. Once the ExcelApplication object has opened the report, it can be used as if it were any other file.

Getting a Reference to the Report Server

There are a number of different ways to access a report server to manage your reports. The MSDN documentation has detailed instructions for all the different techniques. The most powerful way, and the one that the Report Manager application uses, is to use the report server’s web service, or SOAP API. The easiest way to use the web service is to add the report server as a web reference in Visual Studio. This will allow us to make web service calls to the report server as though it were any other object in a .NET project. I’ll provide a brief overview of the steps to add a report server as a web reference; details can be found at MSDN. Continue reading How to open a report from SQL Server Reporting Services with the Application object

How to create hyperlinks between cells in an Excel spreadsheet

Problem

Your application requires cells to contain hyperlinks to other cells in the same spreadsheet, but the Cell.CreateHyperlink function only makes links to URLs on the internet. This article discusses a workaround using Excel’s HYPERLINK function.

Solution

Excel has a function named hyperlink() that, with some special syntax, can create links to other cells in a spreadsheet.

Normally, the hyperlink function creates a link to a URL, much like the CreateHyperlink function. Simply passing the name of a cell to the function causes it to form a bad link; Excel will interpret it as a URL.

The solution is to enclose the name of the destination cell in quotes and preface it with a pound sign. For example, a cell with formula =HYPERLINK(“#Sheet2!C3”, “Link to C3”) will contain the text “Link to C3”, and function as a hyperlink to that cell in Sheet2. The formula can be set either by editing an Excel file directly, or through setting the Cell.Formula property in ExcelWriter.

When the user clicks on the link, Excel’s focus will move to Continue reading How to create hyperlinks between cells in an Excel spreadsheet

Everything You Need to Know About OfficeWriter: The Whitepaper

Looking for a comprehensive overview of OfficeWriter? You’ve come to the right place. We’ve partnered with Andrew Brust of Blue Badge Insights to give you an inside look at how OfficeWriter can benefit your company’s business intelligence.
OfficeWriter Whitepaper
Microsoft Office and Data: A Love Story
Excel and Word are the Will and Kate of the Office software world, but if you try to use them for data analysis, they’ll morph into Milli-Vanilli. OfficeWriter prevents that from happening.
In this whitepaper, you’ll learn:
  • How OfficeWriter’s API and templates provide bridges between Microsoft Office & databases
  • How OfficeWriter enhances SQL Server Reporting Services
  • How OfficeWriter turns SharePoint lists & libraries into full-fledged Office docs
That is simply the beginning. Get a full overview of OfficeWriter today.

Stories from the WIT Trenches: Jen Stirrup

[This is the eighth in a series of posts exploring the personal stories of real women in technology. 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. Author of a prestigious BI blog, Jen Stirrup (t|ln), runs a small Business Intelligence company (Copper Blue Consulting) with Allan Mitchell (t|ln) and is an active member of the SQL Server community. If reading her story inspires you to share yours, please email me.]

Meet Jen:

“I have been a SQL Server Most Valuable Professional (MVP) for nearly one year, in the SQL Server discipline. This allows me to connect more deeply with the great minds and kind hearts in the MVP community and at Microsoft. One day, I hope that I won’t be as tongue-tied when I meet the other MVPs that I’ve admired for such a long time!”

1) Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology?

When I was eight years old, my Uncle gave us a computer that he’d fixed. It was a little Sinclair ZX81, and I loved it. I learned to program in BASIC, and my love of technology has been with me ever since.

2) Growing up, did you have any preconceived perceptions of the tech world and the kinds of people who lived in it?

My perception of the tech world was shaped by older males in my family, who took the time to involve me in all things electronic and computer-focused. For example, my grandfather was one of the first television engineers, and continued to be impressed and excited by technology until he passed away in his mid-eighties.  One of my great uncles was a spy during the Second World War, and worked to code-break Japanese codes. Their experiences combined to influence me, and continue to do so until this very day.

3) When did you first consider a career in technology? What did you envision doing?

Initially, I wanted to train as a psychologist and I had a specific interest in cognitive psychology. I used my programming skills in order to set up psychological experiments and I found that I preferred it to psychology.

I moved into Artificial Intelligence, moving from my cognitive psychology and programming background. I was fascinated by the algorithms that attempted to further research into human cognitive processes. I still see Artificial Intelligence alive and well, but in a different guise (e.g. search technologies).

4) Did you experience any personal or systemic setbacks at any point of your academic or professional career?

In the first two years of my son’s life, he was critically ill on occasion. At some points, he was given an hour to live. His illness was a constant stress, and I obviously couldn’t work as he recuperated. I’m glad to say that he survived, partially due to his own tenacity and zest for life. I’m very grateful to the doctors and nurses who saved him, despite the odds.

5) Whom do you look at as mentors and/or sources of inspiration in your field?

I am inspired every day by people in the community, particularly the Professional Association of SQL Server (SQLPASS) community. There are a huge number of selfless volunteers who give up their time to create training material, give presentations, and provide help and support to people who are on the path to learning SQL Server.

6) How has your participation in both the on- and offline SQL Server communities changed the way you look at and work with these technologies?

I’ve learned a lot about business benefits and perspectives from interacting with people in the community. Someone might ask a question which seems strange, but when you start to understand the ‘why’ of the question, it becomes clear that there may be a strong business reason for doing something, even if the proposed technical response seems strange.

I’ve met members of the Analysis Services, Excel and Reporting Services teams, and I’m hugely impressed with their dedication and innovation to provide high-quality products and solutions that SMEs can afford.

7) Why do you think the rate of attrition for women in software engineering is higher than that of women in most other tech fields?

I’m not sure if this is the case, cross-culturally. From my own experience, the issue is perceptions about returning to a technical role after maternity leave. Women leave the field for awhile, and then lose confidence to come back to technology since the tech world has moved on so fast. I have to say that, after returning to work after having had my son, women should not lose confidence in coming back to technology after having had a child. Remember most of the guys you work beside are also parents. The technical skills are transferable to newer versions and editions.

8) Do you have any suggestions for how to get more girls interested in computers and computer science? Is this important to you?

I think it is important to show girls that technology can help people. For example, Microsoft uses technology to help girls across the globe, in partnership with UNESCO.

Don’t dismiss girls from technology, at an early age. Teachers need support in the classroom to make sure that girls also get attention and equal education in subjects such as math, computing science and so on.

Drupal on Windows: Using SQL Server Merge Replication

In my first “Drupal on Windows” blog post, I wrote about using Windows authentication with SQL Server. That’s a recommended best practice for SQL Server in any production environment. So with the knowledge that Drupal can run on Windows technologies in a production environment, how can we scale Drupal to achieve high availability? How do we move beyond a single web farm?

One solution is:

  • Geographically dispersed web farms using Global Load Balancing
    • Each web farm resides in it’s own data center
    • Each web farm utilizes it’s own SQL Server instance
  • SQL Server Merge Replication
    • Keep the database in each web farm in sync, so the served content is identical

SQL Server Merge Replication

Setting up merge replication (and global load balancing) is beyond the scope of this blog post, but there are lots of great instructions out there. I would recommend the following reading for those not familiar with merge replication: Continue reading Drupal on Windows: Using SQL Server Merge Replication

Joining DataTables in LINQ

[Image via Patou Fine Art]

The Problem

Recently, I encountered a situation where I had to join two Data Tables from databases on two different servers. Had they been on the same server, I would do this in SQL with a simple JOIN statement, but since the databases were on different servers, my options were limited. I could:

  1. Use a linked server (http://msdn.microsoft.com/en-us/library/ms188279.aspx)
    But: This wasn’t really an option because I wouldn’t be able to modify production servers
  2. Use System.Data and create a DataRelation
    But: DataRelations are clunky and don’t perform LEFT OUTER JOIN’s very well
  3. Create a new DataTable and copy rows over manually (with, for example, a foreach loop)
    But: This would be painfully slow
    And: I’d have to duplicate a lot of null-row handling logic that LINQ and SQL do themselves
  4. Join the tables in the codebehind using C#’s own SQL-like syntax, LINQ.
    But: I had no idea how to use LINQ.

The Solution

After some research, I decided on option #4. To get a basic overview of LINQ, I started by going through 101 LINQ Samples and trying to learn the LINQ syntax. This was my first attempt at getting a method to join two DataTables: Continue reading Joining DataTables in LINQ

Stories from the WIT Trenches: Erin Stellato

[This is the fourth in a series of posts exploring the personal stories of real women in technology. 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. Those of you engaged in the virtual and IRL SQL communities may already know Erin Stellato from her active and informative presences at conferences and user groups, on Twitter and on her blog. Here, she talks Commodore 64s, nature vs. nuture and her evolution from Kinesiology major to Senior DBA. If reading her story inspires you to share yours, please feel free to email me.]

My name is Erin Stellato and I’m a Senior Database Engineer for a software company outside Cleveland, Ohio.  I have been working in technology for almost 11 years, and with SQL Server for over 8.  I’ve been involved in the SQL Community since 2010, and spend my time on Twitter, blogging and presenting at SQLSaturdays.  I am active in our local user group and will be presenting at my first PASS Summit this fall.

1)      Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology?

I think it starts with my dad…he always had the latest electronics.  My dad loves watching TV, especially movies.  In our house this meant that we had a big TV, a satellite and a VCR.  We also had an Intellivision, which was a bummer for me because all my friends had Ataris, but I still played it.  A lot.  We also had a Commodore 64.  I remember my mom sitting down and typing out a “Hello World!” program.  I tried it as well, and figured out how to make it type different words.  I thought that was cool.  My mom worked in the radiology department of a hospital, and when I would tag along when she got called in.  I was able to see her use the Ultrasound or CT machines, which were pretty new at that time.  It was a lot of lights and buttons, but you could see inside a person on the fly.  It didn’t require the waiting of a normal x-ray…point, shoot, develop, wait, and then see.  Technology was pervasive in my life growing up, but it wasn’t something we discussed.  It was just there. Continue reading Stories from the WIT Trenches: Erin Stellato

Stories from the WIT Trenches: Kendra Little

 

[This is the third 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. To many of you reading, the name Kendra Little may ring a bell. She’s one of the most active voices in the SQL community, and her blog is crammed with tech and workplace wisdom (and amazing illustrations). Her journey to becoming the sole female member of the Fantastic Four is described below. If reading her story inspires you to share yours, please feel free to email me.]

I love data.

I’m a founding partner of Brent Ozar PLF, LLC. We’re a team of consultants who dive in to help clients identify their biggest pain points and prescribe remedies that will work for their environment. Think of us as sports medicine trainers for the database layer—we’re experts at conditioning, recovering from and preventing failures, and helping database systems do more.

I’ve loved to draw since I was a kid. I create art for presentations and posters on topics like Isolation Levels and Table Partitioning. Everyone can download my posters for free from http://BrentOzar.com/go/posters.

1)      Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology?

One conversation changed my life. Continue reading Stories from the WIT Trenches: Kendra Little

Exporting SQL Server Database Data to Excel

Introduction

The need to get business data from a SQL Server database (or any other kind of RDBMS for that matter) into Excel for further analysis is universal.  I would venture to say that anyone in any business role from marketer to analyst to salesperson to executive to program manager has had the occasion to say: “I need this data in Excel so that I can crunch it!”

Since getting data into Excel is a common requirement, it stands to reason that there are several different ways to accomplish the task.  For simple, non-stylized, or purely tabular data imports (situations where you don’t really care about styles or fonts or corporate branding) Excel’s out-of-the-box data import solution can be an option.  For more sophisticated scenarios that require a specific look and feel, or something more complex than just straight tabular data dumps there is OfficeWriter.

This article will discuss both methods for getting your data from SQL Server into Excel.

Methods for Exporting Database Data to Excel

As I mentioned there is more than one way to skin the cat here.  Which method you choose depends on what you need to accomplish.

Method 1: Excel-based data access

Continue reading Exporting SQL Server Database Data to Excel