erin027
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";Exten ded Properties=Excel 8.0')...[A,B]
select [DealerMPA], [DealerName]
from Dealer
end
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '''''''''' '''''''''' ''''''''
How do I correct it?
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
''''''''''''''''''''''''''
create proc ExportToExcel
as
begin
insert into OPENDATASOURCE
( 'Microsoft.Jet.OLEDB.4.0'
, 'Data Source="D:\list.xls";Exten
select [DealerMPA], [DealerName]
from Dealer
end
''''''''''''''''''''''''''
How do I correct it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>How do I correct it?<< Is enabling OPENDATASOURCE an option?
ASKER
Yes, acperkins.
Can i enable OPENDATASOURCE and CLOSE it after I am done importing the data.
Can i enable OPENDATASOURCE and CLOSE it after I am done importing the data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.