Solved

How do I import Excel Database to MS SQL 2005?

Posted on 2007-12-01
4
841 Views
Last Modified: 2012-06-21
How do I import Excel Database to MS SQL 2005?

I have two columns A,B in Excel and in MS SQL 2005, I have 2 columns named (DealerMPA,DealerName)
How do I import A,B values to MS SQL 2005?

I used below query and getting an error message
"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. "

''''''''''''''''''''''''''''''''''''Query'''''''''''''''''''''''''''''''''''''
create proc ExportToExcel
as
begin
    insert into OPENDATASOURCE
    (       'Microsoft.Jet.OLEDB.4.0'
    ,       'Data Source="D:\list.xls";Extended Properties=Excel 8.0')...[A,B]
    select [DealerMPA], [DealerName]
from Dealer
end
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
How do I correct it?
0
Comment
Question by:erin027
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
bbao earned 125 total points
ID: 20389662
FYI: How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20390156
>>How do I correct it?<< Is enabling OPENDATASOURCE an option?
0
 

Author Comment

by:erin027
ID: 20393094
Yes, acperkins.
Can i enable OPENDATASOURCE and CLOSE it after I am done importing the data.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 20393149
You enable OPENDATASOURCE using the Surface Area Configuration tool.  See Surface Area Configuration for Features - Database Engine - Ad Hoc Remote Queries
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Solar Winds can't see SQL Server Express 17 38
sql, case when & top 1 14 30
SQL Query 2 34
Getting invalid Syntax SQL. 3 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question