Solved

Compare SQL Server 2008 value to linked excel file

Posted on 2011-03-08
1
201 Views
Last Modified: 2012-05-11
I have a SQL Server 2008 database that I have linked to an excel file. How can I constantly compare the value of the database to the same value in the excel file? I just need to know when a number changes. For example, lets say I have a product_id, that same product_id is in an excel file that I have my database linked to that someone else maintains and I need to know when that product_id is updated or deleted and send out emails and alert my asp application. I can handle the asp part but I am stumped out the exact query needed to compare an excel worksheet to a table.
0
Comment
Question by:matthewskyle
1 Comment
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35130160
Are you comparing an entire Excel sheet to a table, or just a single value in a single cell in an Excel sheet to some value in some table in SQL Server? Comparing the sheet to a table can be done, but it's a bit longer - a single value shouldn't be much trouble at all.

You'd use OPENROWSET to open a connection to your Excel sheet, and then you can query it just like a table and get the value you're interested in. First, make sure that "Ad Hoc Distributed Queries" is enabled, since you'll be querying an external datasource on the fly, and it's an advanced option:

EXEC sp_configure 'Ad Hoc Distributed Queries', 1 
RECONFIGURE

Open in new window


Once that's done, you can connect to your Excel sheet:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\YourFolder\YourExcelSheet.xls;Extended Properties=Excel 8.0')...[Sheet1$]

Open in new window


That will give you the contents of your sheet as a resultset. You can either modify that query to fetch only the value you want, or (and it may be easier depending on what you're doing) you can insert all those contents into a temp table and work with them there, comparing them to your internal SQL Server value.

If you still need some more helping getting this accomplished and you post some more details about what you're trying to do, I can help you refine the query.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now