March 18, 2009 at 12:22 am
Hi all,
I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using SQL Sever Machine.but it doesn't work using SQL Client machine.I need to do it using SQL query.
it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.
Please tel me how I do this.Following are my code.
------------------------------------------------------------------------------------------------------------------------------------
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='C:\Documents and Settings\HARSHA\My Documents\ToExportExcel\Book1.xls;HDR=yes'
set @query='Select Name,ID from [Personal$]'
set @datasource=@dbtype+@path
EXEC(
'
SELECT Name,ID
FROM
OPENROWSET
(
'''+@service_provider+''',
'''+@datasource+''',
'''+@query+ ''') a')
------------------------------------------------------------------------------------------------------------------------------------
March 18, 2009 at 12:45 am
What errors do you get when executing the query?
Has the Login/Service Account has read permissions on that folder? If the file is in the network drive (i.e. on a client machine) then you have to specify the UNC path or map the network location to a local drive on the server and use that path instead.
--Ramesh
March 18, 2009 at 2:20 am
This is the error was occur
Server: Msg 7302, Level 16, State 1, Line 2
Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply