Solved

Compare SQL Server 2008 value to linked excel file

Posted on 2011-03-08
1
202 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

776 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