March 2, 2009 at 1:14 am
Hi all,
I created a SQL query for import data from excel sheet.
as follows
---------------------------------------------------------------------------------------------------------
create table #MYTABLE(Name varchar(8),ID varchar(3))
declare
@service_provider varchar(300),
@path varchar(300),
@dbtype varchar(500),
@query varchar(300),
@datasource varchar(300)
set @service_provider='Microsoft.Jet.OLEDB.4.0'
set @dbtype='Excel 8.0;DATABASE='
set @path='\\Amaniew folder\ToExportExcel\Book1.xls'
set @query='Select * from [Personal$]'
set @datasource=@dbtype+@path
EXEC(
'
Insert Into #MYTABLE(Name,ID)
SELECT *
FROM
OPENROWSET
(
'''+@service_provider+''',
'''+@datasource+''',
'''+@query+ ''') a')
select * from #MYTABLE
---------------------------------------------------------------------------------------------------------
it is run local database server(Local MSSQL 2000 server) without any error.but it is try run connecting on any other SQL server(remote server in my LAN) this error occur
----------------------------------------------------------------------------------------------------------------------------
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
----------------------------------------------------------------------------------------------------------------------------
then I copy the source Excel file in to another PC which that remote SQL Server installed.and run this Query in that PC it's run without any error.
Therefor I need to run this Query in my PC connecting to remote SQL Server in my LAN.Please tell me how I do it.
Thanks,
Harsha
March 3, 2009 at 7:59 am
Wild guess, but it could be a permissions issue. Is SQL running under the system account? If so, does the SQL Server computer account have access to the share where the Excel sheet is?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply