Is it possible to configure a Linked server in SQL Client?

Question

Is it possible to configure a Linked server in SQL Client?

sharma-691993

SSCrazy

Points: 2194
More actions
May 8, 2008 at 2:01 am

#190307

I am trying to export data into Excel file using OPENROWSET.
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO
But it shows below error
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
In SQL Server (Server PC) it is worked..
Is there is any restriction to Sql Client?
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

w.lengenfelder SSC Eights! Points: 884 More actions · Answer 1

Hi,

I checked you code and it works. But you must ensure, that the file book1.xls exists and the first row contains the column captions FirstName, LastName.

w Lengenfelder

sharma-691993 SSCrazy Points: 2194 More actions · Answer 2

No it shows same error..

I am having excel file which contains firstname & lastname..

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

in your original post you worte it works on the server but not on the client.

So now I wonder where is your Excel sheet located ? On the server or on your client.

The query you use will look on the c: drive of the machine where you execute it.

[font="Verdana"]Markus Bohse[/font]

sharma-691993 SSCrazy Points: 2194 More actions · Answer 4

In Server it is worked..

But in Client only it shows error.

I am trying in sql client machine only..

vinodoss57 Grasshopper Points: 23 More actions · Answer 5

Hello sir,

this is vinoth. i have faced the same issue you have mentioned.I ran the query INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\resource_add.xls;','SELECT * FROM [Sheet1$]')SELECT * FROM depot. in a client machine not server machine. I thought it may be successful in server machine .But client machine any solution you have please let me know.

thanks

vinoth

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

vinodoss57 (2/13/2012)
Hello sir,
this is vinoth. i have faced the same issue you have mentioned.I ran the query INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\resource_add.xls;','SELECT * FROM [Sheet1$]')SELECT * FROM depot. in a client machine not server machine. I thought it may be successful in server machine .But client machine any solution you have please let me know.
thanks
vinoth

You are responding to a thread which is more than three years old. Please create a new one.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin