Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Compare SQL Server 2008 value to linked excel file

Posted on 2011-03-08
1
Medium Priority
?
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 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