Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

How do I import Excel Database to MS SQL 2005?

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
erin027
Asked:
erin027
  • 2
2 Solutions
 
bbaoIT ConsultantCommented:
FYI: How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686
0
 
Anthony PerkinsCommented:
>>How do I correct it?<< Is enabling OPENDATASOURCE an option?
0
 
erin027Author Commented:
Yes, acperkins.
Can i enable OPENDATASOURCE and CLOSE it after I am done importing the data.
0
 
Anthony PerkinsCommented:
You enable OPENDATASOURCE using the Surface Area Configuration tool.  See Surface Area Configuration for Features - Database Engine - Ad Hoc Remote Queries
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now