Link to home
Start Free TrialLog in
Avatar of erin027
erin027Flag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of bbao
bbao
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>How do I correct it?<< Is enabling OPENDATASOURCE an option?
Avatar of erin027

ASKER

Yes, acperkins.
Can i enable OPENDATASOURCE and CLOSE it after I am done importing the data.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial