PROBLEM WIHT OPENROWSET FUNCTION

HI FRIENDS THIS IS AMIT. THE PROBLEM IS I M TRYING TO EXPORT DATA FROM SQL SERVER TABLE TO EXCEL FILE USING FOLLOWING CODE SNIPPET,

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 7.0;Database=c:\testing.xls;',
'SELECT * FROM [Sheet1$]') select * from TABLE1

BUT IT IS SHOWING SOME ERROR WHICH IS AS FOLLOWS,

Invalid object name 'OPENROWSET'.

I FOUND OPENROWSET FUNCTION IN T-SQL REFERENCE BUT STILL THE ABOVE MESSAGE IS COMING PLS HELP IN THIS MATTER ASAP.

REGARDS,

AMIT.

What version of SQL Server are you using (show the output of SELECT @.@.VERSION)?

Steve Kass
Drew University
http://www.stevekass.com|||

Make sure you have "Ad Hoc Remote Queries" enabled for your instance.

|||Kindly provide the version of the SQL Server you are using, it should ideally work without problem in SQL Server 2000 and above. You can however try the same using DTS (Data Transformation Services)|||Excel 7.0??

Try Excel 5.0 or Excel 8.0

Problem using openrowset to query foxpro VFP 9 tables

Hi,

I have been trying to query VFP Foxpro table using following command.

Select * from OpenRowSet('VFPOLEDB','C:\ShareBridge\Dbf\WLK\Dbf\';'';'','SELECT * FROM SOM.DBF' ) as A

I have upgraded latest oledb driver for visual FoxPro 9 but, all the time it gives me following error message.

/**************************************************************

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "VFPOLEDB" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "VFPOLEDB" for linked server "(null)".

************************************************/

and after receiving above message i could not even open dbf file in FoxPro. it says File access denied

Can anyone help me solve this.

Thanks,

Kashyap

The account that the SQL Server service is running under probably doesn't any permissions to access the folder where the FoxPro file is located. Check what account your SQL Server service is running under and give it read/write access to the folder where the database is located.