By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,830 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

Copying XLS (local) directly to SQL (remote) Table without Link srver and BCP

P: 13
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) 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) tales using SQL scripts and not using any .NET code.

Thanks in advance
Mar 31 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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) 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) tales using SQL scripts and not using any .NET code.

Thanks in advance
You can create a client-side apps that will read the excel file and connect to your SQL-Server and INSERT your excel columns into that table. I have tried with EXCEL before but that was a much earlier version. Since Microsoft products have built-in OLE's in them, you can use Excel's VBA to connect to your sql server and populate your table.

Actually whatever tool (maybe C#) that can read excel files and can connect and run t-sql to a sql-server can do this task.

-- CK
Mar 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.