Home > Articles > Data > SQL Server

📄 Contents

  1. SQL Server Reference Guide
  2. Introduction
  3. SQL Server Reference Guide Overview
  4. Table of Contents
  5. Microsoft SQL Server Defined
  6. SQL Server Editions
  7. SQL Server Access
  8. Informit Articles and Sample Chapters
  9. Online Resources
  10. Microsoft SQL Server Features
  11. SQL Server Books Online
  12. Clustering Services
  13. Data Transformation Services (DTS) Overview
  14. Replication Services
  15. Database Mirroring
  16. Natural Language Processing (NLP)
  17. Analysis Services
  18. Microsot SQL Server Reporting Services
  19. XML Overview
  20. Notification Services for the DBA
  21. Full-Text Search
  22. SQL Server 2005 - Service Broker
  23. Using SQL Server as a Web Service
  24. SQL Server Encryption Options Overview
  25. SQL Server 2008 Overview
  26. SQL Server 2008 R2 Overview
  27. SQL Azure
  28. The Utility Control Point and Data Application Component, Part 1
  29. The Utility Control Point and Data Application Component, Part 2
  30. Microsoft SQL Server Administration
  31. The DBA Survival Guide: The 10 Minute SQL Server Overview
  32. Preparing (or Tuning) a Windows System for SQL Server, Part 1
  33. Preparing (or Tuning) a Windows System for SQL Server, Part 2
  34. Installing SQL Server
  35. Upgrading SQL Server
  36. SQL Server 2000 Management Tools
  37. SQL Server 2005 Management Tools
  38. SQL Server 2008 Management Tools
  39. SQL Azure Tools
  40. Automating Tasks with SQL Server Agent
  41. Run Operating System Commands in SQL Agent using PowerShell
  42. Automating Tasks Without SQL Server Agent
  43. Storage – SQL Server I/O
  44. Service Packs, Hotfixes and Cumulative Upgrades
  45. Tracking SQL Server Information with Error and Event Logs
  46. Change Management
  47. SQL Server Metadata, Part One
  48. SQL Server Meta-Data, Part Two
  49. Monitoring - SQL Server 2005 Dynamic Views and Functions
  50. Monitoring - Performance Monitor
  51. Unattended Performance Monitoring for SQL Server
  52. Monitoring - User-Defined Performance Counters
  53. Monitoring: SQL Server Activity Monitor
  54. SQL Server Instances
  55. DBCC Commands
  56. SQL Server and Mail
  57. Database Maintenance Checklist
  58. The Maintenance Wizard: SQL Server 2000 and Earlier
  59. The Maintenance Wizard: SQL Server 2005 (SP2) and Later
  60. The Web Assistant Wizard
  61. Creating Web Pages from SQL Server
  62. SQL Server Security
  63. Securing the SQL Server Platform, Part 1
  64. Securing the SQL Server Platform, Part 2
  65. SQL Server Security: Users and other Principals
  66. SQL Server Security – Roles
  67. SQL Server Security: Objects (Securables)
  68. Security: Using the Command Line
  69. SQL Server Security - Encrypting Connections
  70. SQL Server Security: Encrypting Data
  71. SQL Server Security Audit
  72. High Availability - SQL Server Clustering
  73. SQL Server Configuration, Part 1
  74. SQL Server Configuration, Part 2
  75. Database Configuration Options
  76. 32- vs 64-bit Computing for SQL Server
  77. SQL Server and Memory
  78. Performance Tuning: Introduction to Indexes
  79. Statistical Indexes
  80. Backup and Recovery
  81. Backup and Recovery Examples, Part One
  82. Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
  83. SQL Profiler - Reverse Engineering An Application
  84. SQL Trace
  85. SQL Server Alerts
  86. Files and Filegroups
  87. Partitioning
  88. Full-Text Indexes
  89. Read-Only Data
  90. SQL Server Locks
  91. Monitoring Locking and Deadlocking
  92. Controlling Locks in SQL Server
  93. SQL Server Policy-Based Management, Part One
  94. SQL Server Policy-Based Management, Part Two
  95. SQL Server Policy-Based Management, Part Three
  96. Microsoft SQL Server Programming
  97. An Outline for Development
  98. Database
  99. Database Services
  100. Database Objects: Databases
  101. Database Objects: Tables
  102. Database Objects: Table Relationships
  103. Database Objects: Keys
  104. Database Objects: Constraints
  105. Database Objects: Data Types
  106. Database Objects: Views
  107. Database Objects: Stored Procedures
  108. Database Objects: Indexes
  109. Database Objects: User Defined Functions
  110. Database Objects: Triggers
  111. Database Design: Requirements, Entities, and Attributes
  112. Business Process Model Notation (BPMN) and the Data Professional
  113. Business Questions for Database Design, Part One
  114. Business Questions for Database Design, Part Two
  115. Database Design: Finalizing Requirements and Defining Relationships
  116. Database Design: Creating an Entity Relationship Diagram
  117. Database Design: The Logical ERD
  118. Database Design: Adjusting The Model
  119. Database Design: Normalizing the Model
  120. Creating The Physical Model
  121. Database Design: Changing Attributes to Columns
  122. Database Design: Creating The Physical Database
  123. Database Design Example: Curriculum Vitae
  124. NULLs
  125. The SQL Server Sample Databases
  126. The SQL Server Sample Databases: pubs
  127. The SQL Server Sample Databases: NorthWind
  128. The SQL Server Sample Databases: AdventureWorks
  129. The SQL Server Sample Databases: Adventureworks Derivatives
  130. UniversalDB: The Demo and Testing Database, Part 1
  131. UniversalDB: The Demo and Testing Database, Part 2
  132. UniversalDB: The Demo and Testing Database, Part 3
  133. UniversalDB: The Demo and Testing Database, Part 4
  134. Getting Started with Transact-SQL
  135. Transact-SQL: Data Definition Language (DDL) Basics
  136. Transact-SQL: Limiting Results
  137. Transact-SQL: More Operators
  138. Transact-SQL: Ordering and Aggregating Data
  139. Transact-SQL: Subqueries
  140. Transact-SQL: Joins
  141. Transact-SQL: Complex Joins - Building a View with Multiple JOINs
  142. Transact-SQL: Inserts, Updates, and Deletes
  143. An Introduction to the CLR in SQL Server 2005
  144. Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
  145. Design Elements Part 2: Controlling SQL's Scope
  146. Design Elements Part 3: Error Handling
  147. Design Elements Part 4: Variables
  148. Design Elements Part 5: Where Does The Code Live?
  149. Design Elements Part 6: Math Operators and Functions
  150. Design Elements Part 7: Statistical Functions
  151. Design Elements Part 8: Summarization Statistical Algorithms
  152. Design Elements Part 9:Representing Data with Statistical Algorithms
  153. Design Elements Part 10: Interpreting the Data—Regression
  154. Design Elements Part 11: String Manipulation
  155. Design Elements Part 12: Loops
  156. Design Elements Part 13: Recursion
  157. Design Elements Part 14: Arrays
  158. Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
  159. Design Elements Part 16: Event-Driven Programming
  160. Design Elements Part 17: Program Flow
  161. Forming Queries Part 1: Design
  162. Forming Queries Part 2: Query Basics
  163. Forming Queries Part 3: Query Optimization
  164. Forming Queries Part 4: SET Options
  165. Forming Queries Part 5: Table Optimization Hints
  166. Using SQL Server Templates
  167. Transact-SQL Unit Testing
  168. Index Tuning Wizard
  169. Unicode and SQL Server
  170. SQL Server Development Tools
  171. The SQL Server Transact-SQL Debugger
  172. The Transact-SQL Debugger, Part 2
  173. Basic Troubleshooting for Transact-SQL Code
  174. An Introduction to Spatial Data in SQL Server 2008
  175. Performance Tuning
  176. Performance Tuning SQL Server: Tools and Processes
  177. Performance Tuning SQL Server: Tools Overview
  178. Creating a Performance Tuning Audit - Defining Components
  179. Creating a Performance Tuning Audit - Evaluation Part One
  180. Creating a Performance Tuning Audit - Evaluation Part Two
  181. Creating a Performance Tuning Audit - Interpretation
  182. Creating a Performance Tuning Audit - Developing an Action Plan
  183. Understanding SQL Server Query Plans
  184. Performance Tuning: Implementing Indexes
  185. Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
  186. Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 2
  187. Performance Monitoring Tools: Windows System Monitor
  188. Performance Monitoring Tools: Logging with System Monitor
  189. Performance Monitoring Tools: User Defined Counters
  190. General Transact-SQL (T-SQL) Performance Tuning, Part 1
  191. General Transact-SQL (T-SQL) Performance Tuning, Part 2
  192. General Transact-SQL (T-SQL) Performance Tuning, Part 3
  193. Performance Monitoring Tools: An Introduction to SQL Profiler
  194. Performance Tuning: Introduction to Indexes
  195. Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard
  196. Performance Monitoring Tools: SQL Server 2005 Database Tuning Advisor
  197. Performance Monitoring Tools: SQL Server Management Studio Reports
  198. Performance Monitoring Tools: SQL Server 2008 Activity Monitor
  199. The SQL Server 2008 Management Data Warehouse and Data Collector
  200. Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel
  201. Practical Applications
  202. Choosing the Back End
  203. The DBA's Toolbox, Part 1
  204. The DBA's Toolbox, Part 2
  205. Scripting Solutions for SQL Server
  206. Building a SQL Server Lab
  207. Using Graphics Files with SQL Server
  208. Enterprise Resource Planning
  209. Customer Relationship Management (CRM)
  210. Building a Reporting Data Server
  211. Building a Database Documenter, Part 1
  212. Building a Database Documenter, Part 2
  213. Data Management Objects
  214. Data Management Objects: The Server Object
  215. Data Management Objects: Server Object Methods
  216. Data Management Objects: Collections and the Database Object
  217. Data Management Objects: Database Information
  218. Data Management Objects: Database Control
  219. Data Management Objects: Database Maintenance
  220. Data Management Objects: Logging the Process
  221. Data Management Objects: Running SQL Statements
  222. Data Management Objects: Multiple Row Returns
  223. Data Management Objects: Other Database Objects
  224. Data Management Objects: Security
  225. Data Management Objects: Scripting
  226. Powershell and SQL Server - Overview
  227. PowerShell and SQL Server - Objects and Providers
  228. Powershell and SQL Server - A Script Framework
  229. Powershell and SQL Server - Logging the Process
  230. Powershell and SQL Server - Reading a Control File
  231. Powershell and SQL Server - SQL Server Access
  232. Powershell and SQL Server - Web Pages from a SQL Query
  233. Powershell and SQL Server - Scrubbing the Event Logs
  234. SQL Server 2008 PowerShell Provider
  235. SQL Server I/O: Importing and Exporting Data
  236. SQL Server I/O: XML in Database Terms
  237. SQL Server I/O: Creating XML Output
  238. SQL Server I/O: Reading XML Documents
  239. SQL Server I/O: Using XML Control Mechanisms
  240. SQL Server I/O: Creating Hierarchies
  241. SQL Server I/O: Using HTTP with SQL Server XML
  242. SQL Server I/O: Using HTTP with SQL Server XML Templates
  243. SQL Server I/O: Remote Queries
  244. SQL Server I/O: Working with Text Files
  245. Using Microsoft SQL Server on Handheld Devices
  246. Front-Ends 101: Microsoft Access
  247. Comparing Two SQL Server Databases
  248. English Query - Part 1
  249. English Query - Part 2
  250. English Query - Part 3
  251. English Query - Part 4
  252. English Query - Part 5
  253. RSS Feeds from SQL Server
  254. Using SQL Server Agent to Monitor Backups
  255. Reporting Services - Creating a Maintenance Report
  256. SQL Server Chargeback Strategies, Part 1
  257. SQL Server Chargeback Strategies, Part 2
  258. SQL Server Replication Example
  259. Creating a Master Agent and Alert Server
  260. The SQL Server Central Management System: Definition
  261. The SQL Server Central Management System: Base Tables
  262. The SQL Server Central Management System: Execution of Server Information (Part 1)
  263. The SQL Server Central Management System: Execution of Server Information (Part 2)
  264. The SQL Server Central Management System: Collecting Performance Metrics
  265. The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
  266. The SQL Server Central Management System: Reporting the Data and Project Summary
  267. Time Tracking for SQL Server Operations
  268. Migrating Departmental Data Stores to SQL Server
  269. Migrating Departmental Data Stores to SQL Server: Model the System
  270. Migrating Departmental Data Stores to SQL Server: Model the System, Continued
  271. Migrating Departmental Data Stores to SQL Server: Decide on the Destination
  272. Migrating Departmental Data Stores to SQL Server: Design the ETL
  273. Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
  274. Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
  275. Tracking SQL Server Timed Events, Part 1
  276. Tracking SQL Server Timed Events, Part 2
  277. Patterns and Practices for the Data Professional
  278. Managing Vendor Databases
  279. Consolidation Options
  280. Connecting to a SQL Azure Database from Microsoft Access
  281. SharePoint 2007 and SQL Server, Part One
  282. SharePoint 2007 and SQL Server, Part Two
  283. SharePoint 2007 and SQL Server, Part Three
  284. Querying Multiple Data Sources from a Single Location (Distributed Queries)
  285. Importing and Exporting Data for SQL Azure
  286. Working on Distributed Teams
  287. Professional Development
  288. Becoming a DBA
  289. Certification
  290. DBA Levels
  291. Becoming a Data Professional
  292. SQL Server Professional Development Plan, Part 1
  293. SQL Server Professional Development Plan, Part 2
  294. SQL Server Professional Development Plan, Part 3
  295. Evaluating Technical Options
  296. System Sizing
  297. Creating a Disaster Recovery Plan
  298. Anatomy of a Disaster (Response Plan)
  299. Database Troubleshooting
  300. Conducting an Effective Code Review
  301. Developing an Exit Strategy
  302. Data Retention Strategy
  303. Keeping Your DBA/Developer Job in Troubled Times
  304. The SQL Server Runbook
  305. Creating and Maintaining a SQL Server Configuration History, Part 1
  306. Creating and Maintaining a SQL Server Configuration History, Part 2
  307. Creating an Application Profile, Part 1
  308. Creating an Application Profile, Part 2
  309. How to Attend a Technical Conference
  310. Tips for Maximizing Your IT Budget This Year
  311. The Importance of Blue-Sky Planning
  312. Application Architecture Assessments
  313. Transact-SQL Code Reviews, Part One
  314. Transact-SQL Code Reviews, Part Two
  315. Cloud Computing (Distributed Computing) Paradigms
  316. NoSQL for the SQL Server Professional, Part One
  317. NoSQL for the SQL Server Professional, Part Two
  318. Object-Role Modeling (ORM) for the Database Professional
  319. Business Intelligence
  320. BI Explained
  321. Developing a Data Dictionary
  322. BI Security
  323. Gathering BI Requirements
  324. Source System Extracts and Transforms
  325. ETL Mechanisms
  326. Business Intelligence Landscapes
  327. Business Intelligence Layouts and the Build or Buy Decision
  328. A Single Version of the Truth
  329. The Operational Data Store (ODS)
  330. Data Marts – Combining and Transforming Data
  331. Designing Data Elements
  332. The Enterprise Data Warehouse — Aggregations and the Star Schema
  333. On-Line Analytical Processing (OLAP)
  334. Data Mining
  335. Key Performance Indicators
  336. BI Presentation - Client Tools
  337. BI Presentation - Portals
  338. Implementing ETL - Introduction to SQL Server 2005 Integration Services
  339. Building a Business Intelligence Solution, Part 1
  340. Building a Business Intelligence Solution, Part 2
  341. Building a Business Intelligence Solution, Part 3
  342. Tips and Troubleshooting
  343. SQL Server and Microsoft Excel Integration
  344. Tips for the SQL Server Tools: SQL Server 2000
  345. Tips for the SQL Server Tools – SQL Server 2005
  346. Transaction Log Troubles
  347. SQL Server Connection Problems
  348. Orphaned Database Users
  349. Additional Resources
  350. Tools and Downloads
  351. Utilities (Free)
  352. Tool Review (Free): DBDesignerFork
  353. Aqua Data Studio
  354. Microsoft SQL Server Best Practices Analyzer
  355. Utilities (Cost)
  356. Quest Software's TOAD for SQL Server
  357. Quest Software's Spotlight on SQL Server
  358. SQL Server on Microsoft's Virtual PC
  359. Red Gate SQL Bundle
  360. Microsoft's Visio for Database Folks
  361. Quest Capacity Manager
  362. SQL Server Help
  363. Visual Studio Team Edition for Database Professionals
  364. Microsoft Assessment and Planning Solution Accelerator
  365. Aggregating Server Data from the MAPS Tool
  • Print
  • + Share This

If you’re looking for more up-to-date information on this topic, please visit our SQL Server article, podcast, and store pages.

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.

Data Copy

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:

  1. 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.
  2. Run whatever query you're interested in.
  3. 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.
  4. 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.

  1. Open Enterprise Manager (2000 and lower) or SQL Server Management Studio (2005 and higher)
  2. Right-click any database name and select "All Tasks" from the menu that appears.
  3. Select "Import and Export Data" from that submenu.
  4. 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
  5. 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
  6. 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:

  1. Open Excel
  2. Select "Data" from the menu bar, then "Import External Data”
  3. Select "Import Data...”
  4. Select the "New Source..." button
  5. Select "Microsoft SQL Server" as a source, then "Next"
  6. Enter the server name, and then select the method of authentication — fill that out as required
  7. Select the database you want, then the table or view, and then select "Next"
  8. Name the connection and then select "Finish"
  9. You're now returned to the "Data Sources" area
  10. Click "Open", and then select the area of the spreadsheet where the data should start
  11. 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:

  1. Open Excel
  2. Click on “Tools,” then “Macro,” and then “Macros...”
  3. Name the Macro, and then click Create
  4. 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:
  5. ' 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.

Linked Data

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:

  1. Open Query Analyzer or a SQL Server Management Studio Query window
  2. Run the following code:
  3. -- 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.

Online Resources

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.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.