Solved

How do I import Excel Database to MS SQL 2005?

Posted on 2007-12-01
4
835 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:
Bing CISM / CISSP earned 125 total points
Comment Utility
FYI: How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686
0
 
LVL 75

Expert Comment

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

Author Comment

by:erin027
Comment Utility
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
Comment Utility
You enable OPENDATASOURCE using the Surface Area Configuration tool.  See Surface Area Configuration for Features - Database Engine - Ad Hoc Remote Queries
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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 setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now