December 14, 2009 at 4:36 am
Hi,
I am trying to export data in excel using the below (I am using SqlServer2005)-
SP_CONFIGURE 'show advanced options',1
GO
sp_configure 'Ad Hoc Distributed Queries',1
Reconfigure WITH OVERRIDE
GO
Create Procedure Test (@ProdXML XML)
AS
Begin
Declare @ISBN Table(PNumber XML)
Insert Into @ISBN Values(@ProdXML)
Declare @resultSet Table(Result Varchar(25))
Insert Into @resultSet Select MJ.value('@Value','varchar(25)')
FROM @ISBN
CROSS APPLY PNumber.nodes('/products/Prod_Number') as m(MJ)
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Testing.xls;HDR=Yes','SELECT * FROM [Testing]') select * from @resultSet
END
GO
Sp_Configure 'Ad Hoc Distributed Queries',0
Reconfigure WITH OVERRIDE
GO
But it gives me error -
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Testing'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Procedure Test, Line 25
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
What to do?
Is there any other method to create excel/csv file using sqlServer2005.
MJ:cool:
December 14, 2009 at 5:07 am
If the "Testing" is the name of the sheet in the excel file, you need to name it as "Testing$".
The names of the sheets needs to be followed by $ character.
If it is a named range from within an excel file, then there cannot be the $ character.
December 14, 2009 at 5:50 am
Tried but again giving the same error -
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Testing$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Procedure Test, Line 25
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Is there any prerequisite to use OpenRowSet?
MJ:cool:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply