Sp_configure [?] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sp_configure [?]

Author

Topic

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-27 : 02:50:31

I have enabled the permissions and execute the following code getting the following error but i can't understand can any body help me plz


SELECT * INTO Tenantdetails
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\Tenantdetails.xls', 'SELECT * FROM [Tenantdetails$]')

error is


Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [Tenantdetails$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

plz help i am confused

Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 02:55:09

Modify your query to this and try


SELECT * INTO Tenantdetails
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\Tenantdetails.xls', 'SET FMTONLY OFF;SELECT * FROM [Tenantdetails$]')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-27 : 02:58:43

Mr.Idera thanks for your post
i get this error when executing


OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SET FMTONLY OFF;SELECT * FROM [Tenantdetails$]" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 03:09:15

try this


SET FMTONLY OFF;

SELECT * INTO Tenantdetails
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\Tenantdetails.xls', 'SELECT * FROM [Tenantdetails$]')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-27 : 03:27:15

[code]
Msg 7357, Level 16, State 2, Line 3
Cannot process the object "SELECT * FROM [Tenantdetails$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
[/code]
this is the error that means any path or table name is incorrect

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 04:37:59

Can you create a linked server?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 04:44:30

If you can create a linked server then follow this steps


exec sp_addlinkedserver @server = N'test',
 @srvproduct = N'',
 @provider = N'Microsoft.Jet.OLEDB.4.0'
 @datasrc = N'Your Excel File Name'

Then in security set "Be made using the login's current security context" to true.

Also sure that RPC is set true for the linked server.And then try this


SELECT *
FROM OPENROWSET(test,'SET FMTONLY OFF;SELECT * FROM [Tenantdetails$]')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-27 : 07:32:07

yes finally my work was done thanks a lot Mr.idera

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 07:42:19

quote:
Originally posted by kashyap_sql

yes finally my work was done thanks a lot Mr.idera

With Regards
Kashyap M

So how did you do it?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-07-27 : 07:50:12

by store procedure of adding a link to the server which you have given, at first i got an error on the name of the file but finally the data was inserted to the table which i have created in sql sever. any way my heart full thanks to you for your help thanks again

With Regards
Kashyap M

Sachin.Nand

2937 Posts

Posted - 2010-07-27 : 08:33:53

quote:
Originally posted by kashyap_sql

by store procedure of adding a link to the server which you have given, at first i got an error on the name of the file but finally the data was inserted to the table which i have created in sql sever. any way my heart full thanks to you for your help thanks again

With Regards
Kashyap M

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources