September 9, 2008 at 9:35 am
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?
September 9, 2008 at 9:46 am
Glenn,
At the bottom of the excel spreadsheet it says 6322missing and not sheet1.
I appreciate all of the help.
Quinn
September 9, 2008 at 11:14 am
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.
September 9, 2008 at 1:49 pm
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
September 9, 2008 at 2:05 pm
🙂
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.
September 9, 2008 at 2:20 pm
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
January 7, 2010 at 4:59 pm
One thing that I saw right away is that the "Data Source" is two words not one word.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply