Is it possible to insert the data from excel into a tabe of database on a remote server?

Question

Is it possible to insert the data from excel into a tabe of database on a remote server?

lindsaywang

SSCrazy

Points: 2569
More actions
April 7, 2011 at 2:09 am

#238153

Hi,
I have some data got from excel via OPENROWSET, we hope to insert them into a table which located on another server. So I use "Execute (@sqlquery) at linkedserver" to insert it. But I got the following error when I execute it. is there any setting I missed?
BTW, the @sqlquery should work if I execute on the remote server directly. so I guess there are something setting I missed on machine. Really appreciated if you can share anything with me. Thanks a lot.
Thanks
Lindsay
Error message:
sg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
EXECUTE('SELECT * INTO Test.dbo.sheet2 FROM
OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=\\localhost\TestExcel.xlsx'', [Sheet1$])')
at rmlinkedserver
April 7, 2011 at 5:09 am

This was removed by the editor as SPAM
April 7, 2011 at 6:46 am

This was removed by the editor as SPAM
April 7, 2011 at 8:16 am

This was removed by the editor as SPAM
April 8, 2011 at 2:21 am

This was removed by the editor as SPAM
April 11, 2011 at 1:34 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

raztos SSC Rookie Points: 28 More actions · Answer 1

stewartc-708166 (4/7/2011)
Does the linked server user have sufficient priviliges to execute the openrowset command on the remote server?

I have the same problem. How can I find it if there are sufficient privileges ?

Thx

lindsaywang SSCrazy Points: 2569 More actions · Answer 2

Thanks for your post stewartc:)

Actually, I can use same user run the query on that remote server directly. But if I run the query via "EXECUTE (query) At rmlinkedserver", it reported the error described in this thread. did I missed any step to check it?

Thanks

Lindsay

lindsaywang SSCrazy Points: 2569 More actions · Answer 3

Hmmm... RPC and RPC OUT have been set true.I think there are something wrong with the ole db provider. Because other linked server can work fine....I have set the ole db provider as 1 for AllowInProcess. Don't know what's wrong with it.....

Thanks

Linday

jmonk 57451 SSC Enthusiast Points: 131 More actions · Answer 4

This may also be related to a Kerberos setting issue. Had a similar problem recently where the SQL Agent was able to run this in a job but user was unable to run it from SSMS. However, I do not know enough about the Kerberos settings to be of any real assistance...just want to point out a possible avenue to resolution.

lindsaywang SSCrazy Points: 2569 More actions · Answer 5

Thanks for your support stewartc-708166.

Actually, the Distributed Queries has been set to be true both on my work server and remote server before I run the query.

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

Reconfigure

lindsaywang SSCrazy Points: 2569 More actions · Answer 6

Thanks a lot stewartc-708166. You are so kindly 🙂

Thanks

Lindsay