- SQL Server Reference Guide
- SQL Server Reference Guide Overview
- Table of Contents
- Microsoft SQL Server Defined
- SQL Server Editions
- SQL Server Access
- Informit Articles and Sample Chapters
- Online Resources
- Microsoft SQL Server Features
- SQL Server Books Online
- Clustering Services
- Data Transformation Services (DTS) Overview
- Replication Services
- Database Mirroring
- Natural Language Processing (NLP)
- Analysis Services
- Microsot SQL Server Reporting Services
- XML Overview
- Notification Services for the DBA
- Full-Text Search
- SQL Server 2005 - Service Broker
- Using SQL Server as a Web Service
- SQL Server Encryption Options Overview
- SQL Server 2008 Overview
- SQL Server 2008 R2 Overview
- SQL Azure
- The Utility Control Point and Data Application Component, Part 1
- The Utility Control Point and Data Application Component, Part 2
- Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Performance Tuning SQL Server: Tools and Processes
- Performance Tuning SQL Server: Tools Overview
- Creating a Performance Tuning Audit - Defining Components
- Creating a Performance Tuning Audit - Evaluation Part One
- Creating a Performance Tuning Audit - Evaluation Part Two
- Creating a Performance Tuning Audit - Interpretation
- Creating a Performance Tuning Audit - Developing an Action Plan
- Understanding SQL Server Query Plans
- Performance Tuning: Implementing Indexes
- Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
- Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 2
- Performance Monitoring Tools: Windows System Monitor
- Performance Monitoring Tools: Logging with System Monitor
- Performance Monitoring Tools: User Defined Counters
- General Transact-SQL (T-SQL) Performance Tuning, Part 1
- General Transact-SQL (T-SQL) Performance Tuning, Part 2
- General Transact-SQL (T-SQL) Performance Tuning, Part 3
- Performance Monitoring Tools: An Introduction to SQL Profiler
- Performance Tuning: Introduction to Indexes
- Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard
- Performance Monitoring Tools: SQL Server 2005 Database Tuning Advisor
- Performance Monitoring Tools: SQL Server Management Studio Reports
- Performance Monitoring Tools: SQL Server 2008 Activity Monitor
- The SQL Server 2008 Management Data Warehouse and Data Collector
- Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel
- Practical Applications
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Becoming a DBA
- DBA Levels
- Becoming a Data Professional
- SQL Server Professional Development Plan, Part 1
- SQL Server Professional Development Plan, Part 2
- SQL Server Professional Development Plan, Part 3
- Evaluating Technical Options
- System Sizing
- Creating a Disaster Recovery Plan
- Anatomy of a Disaster (Response Plan)
- Database Troubleshooting
- Conducting an Effective Code Review
- Developing an Exit Strategy
- Data Retention Strategy
- Keeping Your DBA/Developer Job in Troubled Times
- The SQL Server Runbook
- Creating and Maintaining a SQL Server Configuration History, Part 1
- Creating and Maintaining a SQL Server Configuration History, Part 2
- Creating an Application Profile, Part 1
- Creating an Application Profile, Part 2
- How to Attend a Technical Conference
- Tips for Maximizing Your IT Budget This Year
- The Importance of Blue-Sky Planning
- Application Architecture Assessments
- Transact-SQL Code Reviews, Part One
- Transact-SQL Code Reviews, Part Two
- Cloud Computing (Distributed Computing) Paradigms
- NoSQL for the SQL Server Professional, Part One
- NoSQL for the SQL Server Professional, Part Two
- Object-Role Modeling (ORM) for the Database Professional
- Business Intelligence
- BI Explained
- Developing a Data Dictionary
- BI Security
- Gathering BI Requirements
- Source System Extracts and Transforms
- ETL Mechanisms
- Business Intelligence Landscapes
- Business Intelligence Layouts and the Build or Buy Decision
- A Single Version of the Truth
- The Operational Data Store (ODS)
- Data Marts – Combining and Transforming Data
- Designing Data Elements
- The Enterprise Data Warehouse — Aggregations and the Star Schema
- On-Line Analytical Processing (OLAP)
- Data Mining
- Key Performance Indicators
- BI Presentation - Client Tools
- BI Presentation - Portals
- Implementing ETL - Introduction to SQL Server 2005 Integration Services
- Building a Business Intelligence Solution, Part 1
- Building a Business Intelligence Solution, Part 2
- Building a Business Intelligence Solution, Part 3
- Tips and Troubleshooting
- SQL Server and Microsoft Excel Integration
- Tips for the SQL Server Tools: SQL Server 2000
- Tips for the SQL Server Tools – SQL Server 2005
- Transaction Log Troubles
- SQL Server Connection Problems
- Orphaned Database Users
- Additional Resources
- Tools and Downloads
- Utilities (Free)
- Tool Review (Free): DBDesignerFork
- Aqua Data Studio
- Microsoft SQL Server Best Practices Analyzer
- Utilities (Cost)
- Quest Software's TOAD for SQL Server
- Quest Software's Spotlight on SQL Server
- SQL Server on Microsoft's Virtual PC
- Red Gate SQL Bundle
- Microsoft's Visio for Database Folks
- Quest Capacity Manager
- SQL Server Help
- Visual Studio Team Edition for Database Professionals
- Microsoft Assessment and Planning Solution Accelerator
- Aggregating Server Data from the MAPS Tool
As data professionals, most of us concentrate on establishing a secure, reliable, well-performing back-end server for various applications. We seldom worry about those front-end applications, unless they cause issues with a cluster or server.
It's a different matter, however, when it comes to integration. No matter what your level of involvement with front-end applications, it's your duty to assist with the efforts to directly access data in a SQL Server database. No one is more familiar with the effects that will occur when data is imported, exported, or linked to one of your databases.
Enter Microsoft Office. It's the industry standard office tool, known by most business users, and it works on everything from Windows 95 and Windows XP all the way to Crossover Office on Linux. In short, Office is everywhere. And, in particular, Microsoft Excel is relied on by most every company that has it installed. In fact, some have called it “the world’s most frequently used database system.” And that may not be far from the truth.
Because of its easy availability, Excel is a natural choice for manipulating data. It has an amazing array of tools for applications, from statistics to engineering, and business functions besides. Excel is taught at most business schools and colleges as part of a standard business curriculum.
Another reason to integrate SQL Server data with Microsoft Excel is that the workbooks, which are composed of one or more spreadsheets, are portable. There are viewers and converters for almost all major operating systems, and other office programs can often read their format natively.
Microsoft Excel also has great presentation and graphing tools. I know of several shops that use Excel exclusively for reporting. Microsoft has even announced that the newer versions of Excel are the tool of choice for working with, displaying and reporting on even large data sets.
Add to this the ability to program right inside Microsoft Excel using Visual Basic for Applications (VBA), and you've got a very powerful argument for using this tool to access and process SQL Server data.
Now that you're convinced ☺ that Excel is a natural fit for data integration, how do you get that data in and out of your databases? There are several methods, each with their own application to consider. Before we begin, however, there are a couple of things to keep in mind.
First, because Excel is a flat-file based medium, you may have to change your way of thinking about the data stored in your relational databases. You might have to create a few more database objects, such as views and stored procedures, to format the data in a more de-normalized, pre-joined way for the spreadsheet user.
The same concepts hold true for imports and exports. When creating your design for the transactions, this difference between flat-file and relational structure will figure heavily into the design. You may have to create a process to break the flat design into a relational structure during the import or export.
The other issue to confront with Excel-SQL Server integration is data conversion. The most classic example is the way that SQL Server interprets hexadecimal values coming from Microsoft Excel. Dates can also be problematic; it's best to check the results after you import or export data between the two programs. The issue arises because SQL Server handles lots of data types, depending on the version and edition of SQL Server you’re using. Here are just a few:
- char, nchar, varchar, nvarchar
- bigint, int, smallint and tinyint
- Datetime and smalldatetime
- money and smallmoney
- decimal and numeric
- float and real
- text and ntext
- binary, varbinary and image
- bit, uniqueidentifier and timestamp
However, Excel groups data into only a few basic types, once again depending on the version you have. Some of those include Text, Values, Dates and Formulae. While both Excel and SQL Server do a pretty good job at guessing the type of data that's coming in, you can't trust the transfer between them blindly.
SQL Server and Excel Integration Methods
Even with a few limitations, Excel remains a wonderful integration tool. There are several methods of integration; divided primarily into categories I'll call "Data Copy" and "Linked Data." There are lots of ways to perform this process; I’ll focus on the more popular ones here.
The primary difference between these categories is the way they treat the data after the interaction. In a "Data Copy" method, the data is essentially copied from one place to another, and then begins to diverge from its source immediately after the transfer. In the "Linked Data" method, the data is stored in only one source, so changes are reflected immediately in both interfaces.
So, your first decision to make is which category to use, and then choose the method in that category that best suits the need. After that, test the process with sample data, and then work on measuring the throughput and writing error handlers.
You can use this category of methods when you just need to have a handoff of data stored in SQL Server or Excel. In other words, the criterion for the following methods is that you are finished with the data in one program, and need it in another.
For example, your users may need data which is stored in a SQL Server database to present in a management report. In another example, data stored in various spreadsheets is needed in SQL Server to complete a data set. I've also seen more complex situations, where the data originates in SQL Server, is transferred to spreadsheets for processing, and is re-imported back into the SQL data-stream for use by another application.
In all of these cases, there's a discreet handoff of the data ownership. The limitation is that these methods don't allow edits to the same base data, at least at the same time.
Now that you understand when to use Data Copy, I’ll drill into the specifics on how to make that happen.
Simple Copy and Paste
The first of these methods is good-old copy and paste. You can use this method to copy a small set of records quickly. Understand that this method is quite hands-on; it's definitely a "high-touch" process! Even so, it's often adequate for ad-hoc or one-time solutions.
Copying data from SQL Server to Excel using a manual process is quite simple:
- Open Query Analyzer (QA) or a SQL Server Management Studio (SSMS) query window. Set the output to "grid" with the icon in the icon bar at the top of the tool. This is the default most of the time anyway.
- Run whatever query you're interested in.
- If you're using Query Analyzer, click the small box on the top left part of the results pane to select the results of the query, and hit CTRL-C. You can do the same thing in SQL Server Management Studio, and in fact there you can select only a few cells or rows, or even hold down CTRL and click to select cells that are not contiguous.
- Open Excel and paste the data where you want it.
The reason this works is that when you copy data from the grid view in SQL Server, it is stored in the clipboard as a tab-separated value string. When you paste into Excel (or almost any Microsoft Office grid), it places the values there as tab-separated values.
The reverse direction (Excel to SQL Server) isn’t as simple. You can use Enterprise Manager’s “data view” or SQL Server Management Studio’s “Edit Data” option for a table right-click, but it may not work. You'll need to make sure that the table constraints allow the data you're trying to paste, and that you’re really sure about those data types. In most all circumstances, I recommend against the copy-and-paste method for importing data to SQL Server.
Using the Import/Export Wizard
While the copy-and-paste method is great for one-time simple transfers, it's not very sustainable. If you're after a more permanent, controlled method of data transfer, consider using SQL Server's Data Transformation Services (DTS) in version 2000 and lower, and SQL Server Integration Services (SSIS) in SQL Server 2005 and higher. These features have several powerful functions, from transferring the data to developing data maps, handling errors and notifications, and transforms. In addition, the process can be saved and used over and over, even triggered by another SQL event.
I have articles about using DTS and SSIS here on InformIT and there are several written by other folks as well. You can certainly begin to explore these powerful tools from their respective interfaces, but there’s actually a simpler way to bring Excel (or many other types of inputs for that matter) data in and out of SQL Server.
- Open Enterprise Manager (2000 and lower) or SQL Server Management Studio (2005 and higher)
- Right-click any database name and select "All Tasks" from the menu that appears.
- Select "Import and Export Data" from that submenu.
- You'll see a source selection dialog, in which you can select SQL Server or Microsoft Excel, depending on which way you want to transfer data
- Follow the rest of the wizard, which leads you through the process of field-mapping, transforms, and saving the selections (called a package) for later use
- Run the package at the last dialog.
(A reference at the end of this article goes into more depth on this process.)
Using Excel for Data Copy
To move data using Excel, you have a few choices. Under the Excel "Data" menu, you'll find "Import External Data" as well as a "Query Wizard" submenu, both of which use ODBC drivers to access data in SQL Server. In later versions of Excel, you have a lot more options – There’s even a “Get Data from SQL Server” option right in the Data option of the Ribbon.
Each option has a “refresh” capability, so you can copy the changes in data on the SQL Server side, but keep in mind this process is one-way only. Data changed in Excel doesn't automatically flow back to SQL Server.
This method is best used for small, ad-hoc queries into the database, and should normally be used through views on database tables, not through direct access to base tables.
While we're on that topic, it's important to remember that any access to a SQL Server database has locking and blocking implications. Allowing unplanned, ad-hoc access into an OLTP production system is almost always a bad idea. It's a better process to create special read-only accounts that can access a few views on a reporting system only. Using a reporting system allows proper index design and access control for unplanned queries.
Once you've considered the performance implications, here's the rundown on one of those Excel import processes – this one should work with earlier versions:
- Open Excel
- Select "Data" from the menu bar, then "Import External Data”
- Select "Import Data...”
- Select the "New Source..." button
- Select "Microsoft SQL Server" as a source, then "Next"
- Enter the server name, and then select the method of authentication fill that out as required
- Select the database you want, then the table or view, and then select "Next"
- Name the connection and then select "Finish"
- You're now returned to the "Data Sources" area
- Click "Open", and then select the area of the spreadsheet where the data should start
- Click "OK"
As I mentioned a moment ago, a couple of other methods in Excel can bring data in from SQL Server. While in step 2, you can also select the "Microsoft Query" option, which uses a wizard similar to the steps above. Microsoft Query has a more powerful interface, where you can make selections all the way down to the column level. You can also edit the query in an interface similar to the one you'll find in the table access area in Enterprise Manager or Microsoft Access. The query can be saved for later use.
Using VBA to Get At SQL Server Data
If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database.
Again, all the previous warnings about locking apply. Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it.
There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it.
You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically:
- Open Excel
- Click on “Tools,” then “Macro,” and then “Macros...”
- Name the Macro, and then click Create
- In the editor window, type the following information, substituting the proper names for the server and the tables you want in between the "Sub xxxx" and "End Sub" tags:
' Declare the QueryTable object Dim qt As QueryTable ' Set up the SQL Statement sqlstring = "select au_fname, au_lname from authors" ' Set up the connection string, reference an ODBC connection ' There are several ways to do this ' Leave the name and password blank for NT authentication connstring = _ "ODBC;DSN=pubs;UID=;PWD=;Database=pubs" ' Now implement the connection, run the query, and add ' the results to the spreadsheet starting at row A1 With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring) .Refresh End WithSave and close the macro, and run it from the same menu you accessed in step 2.
I’ll now move away from the "Copy Data" category to the "Linked Data" category of methods. I'll describe the two main methods to link data, both of which link Excel data into a SQL Server query.
There are ways to use Excel to manage data directly in SQL Server, but they involve a bit more programming, and I've found them to be a bit clumsy especially for daily use. Excel simply doesn't handle locks and connectivity issues as well as you need for large-scale production solutions.
You could also code a solution that accesses data stored in an Excel spreadsheet and update a SQL Server table as a result, but this is really event-driven and not a linked solution. (Again, there's a great deal of programming help in that vein here on InformIT.)
So returning to the methods I've found easy to implement, there are two options you can use to query data in an Excel spreadsheet in SQL Server.
Temporary Access to Excel Data from SQL Server
The first method is quite simple, and uses the OPENROWSET function. Here’s the syntax for a quick query from an Excel spreadsheet with one default tab in it, called c:\temp\test.xls:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\temp\test.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Permanent Access to Excel Data from SQL Server
If you think you might want to query the spreadsheet in multiple lines of code, you might want to create a re-usable connection to it in other words, treat it like a server. You can tell SQL Server that you want to treat another data source (like Excel, text files or Oracle databases) as a server using a “linked server.” It’s fairly simple to create one. Here's how to do that for a spreadsheet called “test.xls” in the c:\temp directory:
- Open Query Analyzer or a SQL Server Management Studio Query window
- Run the following code:
-- Here we set up the linked server using the JET provider EXEC sp_addlinkedserver N'ExcelLink', @srvproduct = N'', @provider = N'Microsoft.Jet.OLEDB.4.0', @datasrc = N'C:\temp\test.xls', @provstr = N'Excel 8.0;' GO
By doing this, you create a linked server, and give that connection a name. Notice also the name of the spreadsheet, which can also be on a network share. You only have to do this once for each spreadsheet; if you're not going to access that spreadsheet again, it's a good idea to drop the linked server after you've used it.
Now that you have a linked server, you can access the data. The process for this method is as follows:
In a query tool such as Query Analyzer or SSMS Query window, type the following:
-- Setup the user connection for the spreadsheet EXEC sp_addlinkedsrvlogin 'ExcelLink', 'false' GO -- Get the spreadsheet data – “Sheet1” is the tab name SELECT * FROM OPENQUERY(ExcelLink, 'select * from [Sheet1$]') GO
In this section, we've used the OPENQUERY function, which passes the query on to the provider. You'll find the query language is pretty limited at times, with Excel. If the queries aren't selective enough, set up another worksheet in the workbook with the data you want, and query that one.
As you can see, you have several options open to integrate data between Microsoft Excel and SQL Server. I have quite a few resources below that might be useful.
InformIT Articles and Sample Chapters
William E. Pearson, III has a good article on integrating Microsoft SQL Server 2000 OLAP and Microsoft Office, Integrating Microsoft SQL Server 2000 OLAP and Microsoft Office, Part 1: Creating an Excel PivotTable Report with an OLAP Cube. You can explore the analysis capabilities of the Excel PivotTable report to present data from an OLAP (OnLine Analytical Processing) cube. In this tutorial, learn how to build a PivotTable report from scratch, and explore the details of its cube-focused functionality.
Books and eBooks
Need something a little more current? Check out Roger Jenning’s book, Special Edition Using Microsoft Office Access 2007, that also covers Excel 2007 and SQL Server.
These links are from Microsoft. There are several others out there but these deal with some of the issues that I brought up in the article.
Here's an article that details the data type woes with DTS and Excel imports.
This article from Microsoft details the process of using DTS with Excel.
Microsoft has a larger discussion of ODBC to Excel datatype issues here.
This Microsoft article details programming with ADO against Excel files it shows you how to open one as a database.