November 28, 2008 at 12:42 am
hi
i m trynig to insert data from sql server 2005 into excel 2003 but copuldn't
please find below query,error occurred and linked server settings
------------------------------------------------------------------------------------------
--Query
INSERT INTO OPENROWSET
('MICROSOFT.JET.OLEDB.4.0', 'Database=D:\bhuvneshew.xls;Extended properties=Excel 8.0', 'select * from [Sheet1$]')
select top 10 * from sysobjects
----------------------------------------------------------------------
---Error Occurred
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".
--------------------------------------------------------------------------
--- Linked server settings
EXEC master.dbo.sp_addlinkedserver @server = N'EX_LINK', @srvproduct=N'excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'D:\bhuvneshew.xls', @provstr=N'excel 8.0'
GO
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EX_LINK', @optname=N'use remote collation', @optvalue=N'true'
---------------------------------------------
Please help me
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2008 at 7:14 am
Here's an OPENROWSET Excel insert which has been working for a while:
-- INSERT the results into the newly-created spreadsheet
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=\\ReportingServer\D$\EDIInvoices.xls',
'Select * from EDIInvoices')
SELECT * from #EDIInvoicesPivot
Remember, this statement is running on the SQL server, not on your desktop, so the location of the Excel file is relative to the server. In the example above, the Excel file is on another (reporting) server.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2008 at 1:42 am
so i cannot insert that SQL table data into Excel(Placed at local drive)
Am i right ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 1, 2008 at 2:11 am
bhuvnesh.dogra (12/1/2008)
so i cannot insert that SQL table data into Excel(Placed at local drive)Am i right ?
If the instance of SQL server which is executing your statement can "see" your local drive, then it can also write to it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply