Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 145
TSQL 76
SSRS 70
SSIS 67
XML 57

Top Categories · View All
SQL Server 145
TSQL 76
SSRS 70
SSIS 67
XML 57

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 ·  · 


Amit Mishra
270 · 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 · 40% · 12947
  • 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
    270 · 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 · 40% · 12947
  • 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
    270 · 1% · 163
  • Make sure the Excel file is closed when you run the code

    commented on May 23 2011 9:03AM
    Madhivanan
    3 · 40% · 12947
  • Ya its closed and not in use.

    commented on May 24 2011 2:21AM
    Amit Mishra
    270 · 1% · 163

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]