Copy XLS (LOCAL) to SQL (Remote) table without link server or BCP or .NET code

Question

Copy XLS (LOCAL) to SQL (Remote) table without link server or BCP or .NET code

iitt2007

SSC Veteran

Points: 286
More actions
March 31, 2008 at 2:56 pm

#93998

I have a XLS file on local machine and would like to copy all the columns to a new SQL table on the 'remote' (not local sql server - SQl server 2005) using SQL scripts.
I tried following :
select * into TEMP_PLAN_CODE FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 10.0;Database=D:\Placodes.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
but it's giving me an error :
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
I can't create linked server as well and can't run BCP (no authority)
What are my available choices for copying XLS (on local) to SQL (not local) tables using SQL scripts and not using any .NET code.
Thanks in advance

Viewing 2 posts - 1 through 1 (of 1 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

iitt2007 (3/31/2008)
I have a XLS file on local machine and would like to copy all the columns to a new SQL table on the 'remote' (not local sql server - SQl server 2005) using SQL scripts.
I tried following :
select * into TEMP_PLAN_CODE FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 10.0;Database=D:\Placodes.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
but it's giving me an error :
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
I can't create linked server as well and can't run BCP (no authority)
What are my available choices for copying XLS (on local) to SQL (not local) tables using SQL scripts and not using any .NET code.
Thanks in advance

access to file...or more specifically a remote file, requires authorization you say you don't have....either linked server priviledges, xp_cmdshell,openrowset, or a procedure that does the above that you can call, but executes as a higher authority) (CLP procedure for example.)

Since you can't do that, as you said, you'd have to get the data via a programming language on the client side ,(vb6/vb.net for example) to open the file and then put the data in the recordset, then into a database as a CREATE TABLE/INSERT set of commands...assuming you have permission to CREATE TABLE)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!