Search
Twitter

Entries in SQL Server DB (8)

Monday
Oct032011

Places to Download Sample Data

Creating a Microsoft SQL Server demo soon & need some sample data?  Or, maybe you need a dataset to learn more about a new version like Denali?

When I’m looking for data, I want it fast  Here’s a few possibilities:

CodePlex – The tried & true AdventureWorks samples are all available from CodePlex, for various versions of SQL Server.  You can download the SQL Server relational OLTP database, and/or the relational data warehouse, and/or the Analysis Services cube.  There’s lots more to CodePlex than you might think, so be sure to browse around.

UK Data Store – This is a gold mine for interesting data. No kidding. The Data Store is affiliated with The Guardian, and offers supporting data associated with news articles it publishes.  (I used a dataset from here about the top 100 books ever sold in order to create my first PowerPivot model.)

Azure MarketPlace – The Windows Azure Marketplace has some freely available data which may be downloaded.  There’s also an Excel Add-In to help obtain Azure data.  One nice addition I noticed is a date table intended for use by PowerPivot models.

US Government - The U.S. Government publishes lots of data here as part of its Open Government Initiative.  Some of the data available is pretty darn interesting, including geospatial. 

TechNet SQL Server Samples – This has some overlap with CodePlex, but offers some helpful links.

Analytics for Twitter – This is actually a sample PowerPivot application, but it gives Twitter data on the details worksheet.

Metropolitan Transit Authority – The MTA in New York publishes information about schedules, fares, traffic, etc.

Organisation for Economic Cooperation and Development (OECD) - A French organization which publishes a broad range of social and economic data within & outside of its region.

Stack Overflow Data Dump - Includes Stack Overflow questions, answers, and comments which are available under a Creative Commons license.

Project REAL – This one is getting aged, as it was created for SQL Server 2005 (and I’m not aware of a newer version).  This was a real Barnes and Noble project that turned into a sample dataset available for learning the BI toolset.

I’m sure there’s tons more.  CodePlex and the UK Data Store are the two I rely upon most often.  Happy data hunting.

Saturday
Sep102011

Resolving the "Cannot connect to configuration database" with Denali CTP3 Virtual Machine

A Hyper-V virtual machine has been made available by Microsoft which contains SQL Server Denali CTP3 + SharePoint 2010 + Office 2010.  I downloaded each part, extracted them, imported my VM, and launched it for the first time with great anticipation.

Then things came to a screeching halt:  Cannot connect to the configuration database.  This message was displayed when I tried to launch the "SQL Server - Home" link on its desktop.  Central Admin had the same error.

To resolve:  go to Services (Start > Administrative Tools > Services) and start the SQL Server service for the POWERPIVOT instance.  Although it's set to be automatic, it's apparently not.

Also, I found I needed to restart the VM twice.  If starting the SQL services doesn't work for you & you've only rebooted once, try one more reboot + starting of the SQL service.  That sequence of events worked for me.

Monday
May162011

Alternative to Deprecated System Tables for Finding Text in SQL Server Objects

Overview:  Usage of an Information Schema view to find text within a SQL Server 2008 R2 stored procedure, function, table, or view.

Deprecated System Tables

So, you’re used to querying SysObjects, SysColumns, SysComments?  Me too!  Bad news is the System Tables are deprecated & not supported in the next version of SQL Server:  Deprecated Database Engine Features in SQL Server 2008 R2.  Good news is we have very good alternatives.

As a BI Developer, I mostly used those system tables to search for text within a stored procedure, text in a column name, and so on.  Of course DBAs and developers have a never-ending list of good uses for this metadata.

Alternatives to the System Tables

According to MSDN, there’s 5 ways to gain access to system metadata:

  • Catalog views   (recommended)
  • Information schema views
  • OLE DB schema rowsets
  • ODBC catalog functions
  • System stored procedures and functions

For my simple needs, I’ve turned to the catalog views and the information schema views.

Searching for Text Within a Stored Procedure or Function

The ***OLD*** way I’d have done this was  (replace BusinessEntityID with the string you are searching for):

USE AdventureWorks2008R2

GO

DECLARE @Keyword varchar(30)

    SET @Keyword = '%' + 'BusinessEntityID' + '%' 

SELECT DISTINCT

    SysObj.Name

   ,SysObj.[Type]

   ,SysObj.refdate AS 'LastAltered'

FROM sysobjects AS SysObj WITH (NOLOCK)

   INNER JOIN syscomments AS SysComm WITH (NOLOCK)

      ON SysObj.Id = SysComm.ID

      AND SysObj.[Type] IN('P','TF','IF','FN') --Proc or Function

      AND SysComm.[Text] LIKE @Keyword

ORDER BY SysObj.Name

     

The ***NEW*** way I’m doing this now is:

USE AdventureWorks2008R2

GO

DECLARE @Keyword varchar(30)

   SET @Keyword = '%' + 'BusinessEntityID' + '%'

SELECT

    SPECIFIC_NAME

   ,ROUTINE_TYPE

   ,LAST_ALTERED

FROM information_schema.routines

WHERE ROUTINE_DEFINITION LIKE @Keyword

The reason I like using the Information Schema for this purpose is because the entire stored procedure text is presented within one field when you query the routines view. 

See the following example where I retrieve the 'create procedure' syntax for one stored proc:

USE AdventureWorks2008R2

GO

EXEC

sp_helptext 'uspGetManagerEmployees'

 

We get the results from the previous query returned in rows.  While the catalog views like sys.objects, sys.columns, sys.procedures are certainly very helpful, I've not yet found a replacement for the deprecated syscomments table.  Therefore, I'm using an Information Schema view, rather than the Catalog views, when I want to search for text.  (If you've found a different way that doesn't involve iterating over the rows shown above, please leave me a comment!)

Finding More Information

Deprecated Database Engine Features in SQL Server 2008 R2

Querying the SQL Server System Catalog

Monday
Apr042011

Pros and Cons: Stored Procedures–vs- Embedded Queries–vs- Views in an SSRS Dataset

Overview:  This blog entry lists considerations for deciding how to handle data access for an SSRS report.

Recently someone at my office asked, “Do I really need to create a stored procedure for a really tiny, simple, SSRS report query?”  Great question.  In the past we had taken the position of doing all SSRS data access via stored procedures.  But…should we?  As with everything we do, there’s no simple black & white answer. 

Below are some things to consider when reporting from a relational data source.  When making the decision, ideally you can get input & agreement from both the DBAs and the Developers.

image   Stored Procedures as a Dataset Source for an SSRS Report

Pros

  • Performance:  the ability for the stored procedure to reuse a query plan (stored procedure cache)
  • Can reuse the same stored procedure for more than one report (may minimize errors or duplication)
  • DBA can tune more effectively, if needed
  • Permits the DBMS to secure the object, if needed
  • Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
  • Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
  • Provides an additional layer of security since "Execute" permissions are required for either the user running the report, or an impersonation account
  • Ability to query system tables to find usage of tables & columns (which may help with change management)
  • For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed

Cons

  • Need “Create” permissions on the source database (as an outside consultant, this is not always granted), or need another person to create the stored procedure for you
  • Slightly more knowledge is required to create a stored procedure than a simple select statement
  • Can clutter up the database with quite a few simple queries and/or redundant queries
  • Additional handling is needed to parse multi-valued parameters in SSRS
  • Two-step deployment (the stored procedure, and/or the RDL); this creates an opportunity for error if not deployed concurrently
  • Additional testing of a changed stored procedure & the effect of the change on the report (which may take slightly more time since they are separate)
  • May require additional personnel / time / coordination of efforts if the stored procedures are maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)

image  Embedded SQL in SSRS Dataset

Pros

  • Easy (less syntax for a beginner to learn)
  • No “Create” permissions needed on the source database
  • One-step deployment (unless you are using a Shared Dataset, which is stored outside of the RDL)

Cons

  • For large datasets, may not perform as well as a stored procedure
  • Greater possibility that individual report queries are redundant or handling logic in different ways (a great way to combat this is to use Shared Datasets, a new feature in SQL Server 2008 R2)
  • SSRS Query Designer doesn’t retain formatting well
  • SSRS Query Designer removes comments (a big shortcoming in my opinion)
  • SSRS Query Designer renames aliases in some circumstances
  • The report developer may need additional permissions to database objects to construct the queries (i.e., if direct table access is being utilized instead of views)
  • Need to open the report in BIDS (or Report Builder) in order to make a change
  • Much more difficult to monitor the database objects being accessed by these queries (i.e., not as easy as querying the system tables with a stored procedure)

image   Views as a Dataset Source for an SSRS Report

Usage of Views may be done in conjunction with either Stored Procedures or Embedded SQL, discussed above.

Pros

  • Can reuse the same view for more than one report (which facilitates the coveted ‘one version of the truth’ and may minimize errors or duplication)
  • Particularly helpful when exposing fields for end-user ad-hoc reporting (i.e., can create a “friendly” list of fields, with related fields pre-joined)
  • DBA can tune more effectively, if needed
  • Permits the DBMS to secure the object, if needed
  • Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
  • Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
  • Ability to query system tables to find usage of tables & columns (which may help with change management)

Cons

  • May have some query plan reuse issues
  • Limitations when compared to Stored Procs (ex: cannot use a variable within a view)
  • Auto-joins don’t always work seamlessly in report creation tools (i.e., if foreign key constraints are enabled on the tables, auto-joins are often done for you; with views the best you’ve got is field names which match)
  • Need “Create” permissions on the source database (as an outside consultant, this is not always granted), or need another person to create the view for you
  • Slightly more knowledge is required to create a view than a simple select statement
  • Two-step deployment (the view, and/or the RDL, or both); this creates an opportunity for error if not deployed concurrently
  • Additional testing of a changed view & the effect of the change on the report (which may take slightly more time since they are separate)
  • May require additional personnel / time / coordination of efforts if the view is maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)

Conclusion

We concluded that the critical reports will continue to be driven by stored procedures.  However, we also determined that smaller, less critical reports (such as a set of internal IT audit reports) will be permitted to have embedded queries for the sake of simplicity & to save time. 

Views are used significantly in the facilitation of ad-hoc reporting for end users.  (As a sidenote: we also utilize views as the source for Analysis Services.)

Personally, I like to have a line drawn in the sand at the BIDS project level:  if a set of reports is being deployed to destination ABC, then I know that whole set of reports in the project will have its data access handled the same way.  What I don’t want to do is have to look at each individual report to figure out if its query is in a stored procedure or embedded in the SSRS report.

Finding More Information

Adam Haines Blog:  SSRS – Should I Use Embedded TSQL or a Stored Procedure?

 

Saturday
Mar122011

Scripting Out Data in SQL Server 2008 R2

Overview:  SQL Server 2008 makes it very easy to script out the data from one or more tables.  I find this technique to be useful for moving data between environments (ex: from my laptop development environment to a shared development environment), for sharing data with a client or coworker, or for creating data scripts for source control.  This technique is helpful for data movement I plan to do only once (i.e., meaning an SSIS package would be a bit of overkill).

The below screen shots are from SQL Server 2008 R2.

Steps to Create Data Scripts

1.  In SQL Server Management Studio, right-click the database name, select Tasks, then Generate Scripts.

     image

2.  The Introduction screen lists the steps.  Next.

     SNAGHTML1ec994d1

3.  At the Choose Objects screen, you can choose the entire database, or specific objects (Tables, Users, Schemas).  Scripting the entire database is the default. 

In this example, I have chosen 9 specific tables.  Next.

     SNAGHTML1ecbd4bd

4.  The Scripting Options page lets you choose a file, clipboard, or a new SSMS query window.  There’s also a Web service radio button.  Save to file is the default.  In this example, I have selected a new query window.  Don’t click Next yet!

     SNAGHTML1ec9c3fb

5.  Click the Advanced button - here’s where all the good stuff is!  In the “Types of data to script” you can choose Schema only (which is the default), Data only, or Schema and data. 

There are also a number of other really useful options, such as whether or not to include logins, indexes, foreign keys, statistics, default values, etc. within the script.  You can also choose whether or not to script just the CREATE, or a DROP and CREATE.

In this example I chose “Schema and data,” and left all other defaults in place.

     image

6.  The Summary screen lists the selections made.  Next.

     SNAGHTML1eca1cd4

7.  The final screen.  Choose Save Report if you’d like, then Finish.  That’s it!  Pretty cool, eh?

     SNAGHTML1eca2af7

If you opted to click the "Save Report” button, the report produced looks like this:

     image

Output from the Script

Above, I requested it script the Schema and Data for me.  Therefore, my SSMS window contains (1) CREATE TABLE scripts, and (b) INSERT scripts.

     image

     image

Each row to be inserted is formatted all on one row, which is difficult to read with all the scrolling to the right.  For a short script I might take the time to reformat it.  Alternatively, you can turn on word wrap (Edit menu > Advanced > Word Wrap).

Finding More Information

MSDN:  Documenting and Scripting Databases

MSDN:  How to Generate a Script (SQL Server Management Studio)

Saturday
Nov132010

Loading and Using A SQL Server Database Image

This blog entry discusses how to add an image to SQL Server for storage within the database, followed by how to use it in SSRS.

When including an image within a SQL Server Reporting Services report, you have 3 options:

  1. Embedded.  The image is embedded within the report.  This is my least favorite choice, as it’s not easily reusable and maintenance of a change would be a big headache if very many reports existed with individual embedded images.
  2. External.  The image is loaded to the Report Project and may be reused among multiple SSRS reports.  This approach was discussed in this ImagePaths in SSRS entry.  
  3. Database.  The image is loaded to a SQL Server database and, like option 2, may be reused among multiple SSRS reports.  Because it’s stored in the database, it can be used for many other types of solutions as well.  This is the most flexible of the 3 options - and the topic of the rest of this entry.

Using OPENROWSET to Insert Image Into Table

1.  First, let’s create a table in SQL Server Management Studio to hold the image file.

CREATE TABLE dbo.Images

(

      [ImageID] [int] IDENTITY(1,1) NOT NULL,

      [ImageName] [varchar](40) NOT NULL,

      [OriginalFormat] [nvarchar](5) NOT NULL, 

      [ImageFile] [varbinary](max) NOT NULL

 )    

Note that the data type we’re using for the image is varbinary(max) instead of the image data type.  That’s because the image data type is being removed in a future version of SQL Server.

2.  Right-click this dog image and save it to your c:\ drive as “MyImage.png”.

  

3.  Now let’s insert the dog image into the database table we created in step 1.

INSERT INTO dbo.Images

(

       ImageName

      ,OriginalFormat

      ,ImageFile

)

SELECT

      'Sample Image'

      ,'png'

      ,ImageFile

FROM OPENROWSET(BULK N'C:\MyImage.png', SINGLE_BLOB) AS ImageSource(ImageFile);

That’s it!  Now you’re ready to use it.  I like to include an “Original Format” field so that I can easily remember what to set for the MIME type in SSRS.

SSRS Image Properties

In this section we’ll discuss how to use the database image in SSRS.

1.  First, create a dataset within your report which contains the following query.  I’m calling my dataset “HeaderImage.”

SELECT ImageFile

FROM dbo.Images

2.  From the Toolbox, drag an Image Report Item onto the body of the report.  Configure it as follows:

Select the image source:  Database

Use this field:  =First(Fields!ImageFile.Value, “HeaderImage”)

Use this MIME type:  image/png    (this is the MIME type & subtype, which classifies file types on the web for consistent handling between browsers & O/S)

And voila, you have a drooling puppy on your report - just what you've always wanted! 

Finding More Information

About OPENROWSET

Adding Images to a Report