Opendatasource problems with excel

Question

Opendatasource problems with excel

Viewing 7 posts - 16 through 21 (of 21 total)

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

Glen SSCertifiable Points: 6737 More actions · Answer 1

What sheets this file is containing? Does it contain Sheet1 etc. or does it contain 6322Missing?

I am assuming that you are still running it locally on your SQL server and you placed this Excel file on the local SQL server C:\ drive?

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 2

Glenn,

At the bottom of the excel spreadsheet it says 6322missing and not sheet1.

I appreciate all of the help.

Quinn

Glen SSCertifiable Points: 6737 More actions · Answer 3

Ultimate security guide:

Test scenario:

1. Computer 1 - SQL server 32 bit edition running on Windows 2003

2. Computer 2 - Windows XP desktop.

On computer 2 create a new folder on c:\ drive called Exc.

Copied a new Excel file in there. File name is key_contacts.xls. Excel version is Excel 2007.

Right click on Exc; Properties; Sharing. In column "Share this folder as" entered Exc

Clicked on Permissions.

Add -> Everyone (type it if neccessary)-> Full Control.

OK.

Remotely connected to SQL server. Being logged as Windows account, created a linked server by executing following statement

EXEC sp_addlinkedserver EXCEL,

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'\etworkcomputername\EXC\key_contacts.xls',

NULL,

'Excel 8.0;'

make sure that linked server is created. In order to clarify it for the case when you are logged in to SQL server as SQL account, went to Security TAB of EXCEL linked server and modify it to "Be made without using security context".

Run following queries:

EXECUTE SP_TABLES_EX 'EXCEL' ( showing list of tables defined in this EXCEL file)

SELECT * from EXCEL...SHEET1$

So far - everything successfull.

If at this point you will go back to the Exc share created on a network computer and remove share permissions - you will get all the variety of errors you were receiving so far.

I am providing you with this example ONLY for test purposes, since creating a share permissions as a Full access for Everyone is a BAD PRACTICE. But that is the point where you can start from and build the neccessary security after test passed.

Hope it helps.

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 4

Glen,

I have some good news. I created the linked server with the command that you sent me. Here is the result of exec sp_tables_ex'EXCEL':

NULLNULL'6322missing$'TABLENULL

NULLNULL'6322missing$'Print_TitlesTABLENULL

NULLNULLDatabaseTABLENULL

When I do a:

Select * from EXCEL...['6322missing$']

and

Select * from EXCEL...[database]

I get the data from the excel spread sheet.

Now is there a way to move the parameters from add linked server back into the opendatasource function?

Thanks

Quinn

Glen SSCertifiable Points: 6737 More actions · Answer 5

🙂

Quinn,

it is up to you how you are going to use it.

It is just that MS does not recommend using opendatasource for frequent accesses.

Excerpt from BOL:

"Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources that are accessed infrequently. Define a linked server for any data sources accessed more than several times. Neither OPENDATASOURCE nor OPENROWSET provide all the functionality of linked-server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided every time that OPENDATASOURCE is called."

Here is the syntacs for OPENDATASOURCE:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=\\yourexcelcomputername\exc\key_contacts.xls;Extended Properties=Excel 8.0')...Sheet1$

P.S. The trick was in setting proper security rights on share.

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 6

Glen,

I am up for whatever works best. If MS doesn't recommend it then I won't do it.

Thanks for your help and patience.

Quinn

Ervin Sevilla Newbie Points: 5 More actions · Answer 7

One thing that I saw right away is that the "Data Source" is two words not one word.