Ask - Problem in Querying external server (without adding it as linked server)

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Ask

Ask questions, discuss or help others by answering


	Ask a question


	Subscribe..

Problem in Querying external server (without adding it as linked server)

May 2 2011 12:00AM by Amit Mishra

Hi,

I am trying to perform a select operation on excel file using "OPENDATASOURCE" function. I have enabled "Ad Hoc Distributed Queries" option using sp_configure.

my problem is, when I use following query

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
'Data Source = C:\Documents and Settings\am35300.IND\Desktop\MyPackTest.xls,Extended Properties=EXCEL 5.0')...[Sheet1$]

it throws an error as bellow... OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find file 'C:\Documents and Settings\am35300.IND\Desktop\MyPackTest.xls,Extended Properties=EXCEL 5.0'.".

however if I remove ExtendedProperties from my query, i.e. if I use following query...

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
'Data Source = C:\Documents and Settings\am35300.IND\Desktop\MyPackTest.xls')...[Sheet1$]

It gives

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unrecognized database format 'C:\Documents and Settings\am35300.IND\Desktop\MyPackTest.xls'.".

Will you please help me to find out what is wrong with my query and why above 2 queries giving 2 different error message??

[MyPackTest is an excel(2003) file located in desktop. C:\Documents and Settings\am35300.IND\Desktop\MyPackTest.xls]

Following is a sample of my excel file...

EmployeeID  Title	                       BirthDate	           MaritalStatus	Gender	Department
3              Engineering Manager	12/13/1964	                     M	    M	Engineering
4              Senior Tool Designer	  1/23/1965	                     S	    M	Engineering
9              Design Engineer	                      10/29/1942	M	    F	Engineering
11             Design Engineer	                        4/11/1949	                     M	    M	Engineering
12             Vice President 	                        9/1/1961	                     S	    F	Engineering
267            Senior Engineer	                        7/17/1969	                     S	    M	Engineering

Thanks & Regards, Amit Mishra

Submitted under: Microsoft SQL Server · TSQL · DBA · ·

Previous: export to excel problem
Next: PRovide Some useful SSRS and SSIS interview questions along with answers

Amit Mishra

267 · 1% · 163

6 Replies

Make sure that the file is located in Server's directory and not in your local system

commented on May 2 2011 4:45AM

Madhivanan

3 · 39% · 12441

I am trying this in my local machine (with local Database) first.

Also look at the message given by second query its "Unrecognized database format " while in case of first query it was "File Not Found".

commented on May 2 2011 4:58AM

Amit Mishra

267 · 1% · 163

I suspect that the dot in am35300.IND might cause problem. Can you renmae the folder by removing the dot and try?

commented on May 23 2011 6:10AM

Madhivanan

3 · 39% · 12441

Hi,

I used following codes, but getting the same error as mentioned above..

(I have shifted my file to D:\Test folder)

First

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
'Data Source = D:\Test\MyPackTest.xls,Extended Properties=EXCEL 5.0')...[Sheet1$]

Then

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
'Data Source = D:\Test\MyPackTest.xls')...[Sheet1$]

commented on May 23 2011 7:32AM

Amit Mishra

267 · 1% · 163

Make sure the Excel file is closed when you run the code

commented on May 23 2011 9:03AM

Madhivanan

3 · 39% · 12441

Ya its closed and not in use.

commented on May 24 2011 2:21AM

Amit Mishra

267 · 1% · 163

Your Reply

Sign Up or Login to post a comment.