.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Linked Server to access Excel 2007

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
Hi I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0. Apparently the query requires the sql account to have SYSADMIN privileges. Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method. Following is my code. Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1' Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL go SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]') Exec sp_dropserver 'AB2','droplogins' Now i get the following error Error.15247-User does not have permission to perform this action If I execute the query from Query Analyzer it works fine, but fails when I execute it using Windows App and encapsulate code in Stored Proc. My Excel file, Database and Windows Application run on separate machines. i have provided the following privileges GRANT ALTER ANY LOGIN TO sqlaccount GRANT ALTER ANY LINKED SERVER TO sqlaccount EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE The DisAllowAdHocProcess in Registry has value 0 Please let me know what additional permissions should i set to get it working???


View Complete Post


More Related Resource Links

Linked Server to access Excel 2007

  

Hi

I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.


Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\202.46.215.35\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
go
SELECT * INTO [CPCAB2.xlsx] FROM OPENQUERY([AB2] ,'SELECT * FROM [Sheet1$]')
Exec sp_dropserver 'AB2','droplogins'


Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges

GRANT ALTER ANY LOGIN TO sqlaccount
GRANT ALTER ANY LINKED SERVER TO sqlaccount


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE

The DisAllowAdHocProcess in

How to make linked server to Access 2007 db on 64-bit server

  

It seems that Microsoft created ____ for a lot of people trying to migrate applications to 64-bit platforms that use an Access database.  My situation:  need to create linked server in SQL Server Express 2008 R2 to an Access 2007 database.  The provider "Microsoft.ACE.OLEDB.12.0 " might work but is not listed as one of the available providers in the linked servers properties dialog.  I have office 2007 installed on the machine, so I can't install the 2010 access database engine.

Can I somehow make the Microsoft.ACE.OLEDB.12.0 provider avialable on my system in this circumstance?


Cannot update Excel 2007 spreadsheet as linked server within SQL 2005 or SQL 2008 via ADO

  
Greetings!

I am having difficulty updating an Excel worksheet via the ACE.OLEDB.12.0
provider.

I have a worksheet defined as a linked server in SQL Server via this
provider, and all attempts to update the lone worksheet in this file as a
linked server results in the following:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linked_excel"
returned message "Bookmark is invalid.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider
"Microsoft.ACE.OLEDB.12.0" for linked server "linked_excel".

The query:
update linked_excel...sheet1$ set error_col='hithere' where
)='G'

However, when I try to perform precisely the same update against the same
source via openrowset, it works, to-wit:

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel
12.0;HDR=yes;Database=f:\path_to_file\filename.xlsx','select * from
[sheet1$]')
set error_col='hithere'
where
='G'

SELECT's performed against either version work properly.

The linked server behavior is consistent across SQL 2005 and 2008
installations.

I am concerned that this problem is an artifact of an OLEDB provider update that purposely disabled update b

Trying to add a linked server - SQL Server 2008 Express Edition to Access 2007

  
OS: Vista SP1
SQL Server 2008 Express Edition installed locally
Office 2007 installed locally

I'm trying to add a linked server to a named instance of the SQL Server 2008 that links to an Access 2007 database.

I've spent a couple of hours trying this now - many, many different ways.

Always seem to end up back at Error 7303 - http://msdn.microsoft.com/en-us/library/aa226395(SQL.80).aspx

I've tried all versions of Access database file 2000, 2002-2003, 2007
I've tried to use different drivers -

Microsoft.Jet.OLEDB.4.0
ODBC
Office 12 for Access driver

I've tried to use the upsizing wizard from Access - no good, cannot get it to connect to the SQL Server 2008 Express Edition instance

Whichever way I try it I end up with this message/error:

the upsizing wizard only works with microsoft sql server versions 6.50 sp5 or higher

I've read: http://support.microsoft.com/default.aspx/kb/838594 and followed it - same error.

I really want this to just work. Like it should already.

Any suggestions appreciated.

Matt Haddon

Linked Server From SQL 2008 R2 (32 or 64 bit) TO Access 2007 mdb

  

I have what I thought was a simple task.  Create an OLEDB connection to an Access2007 MDB data file within SQL Server 2008 R2.  Here's my environment:

    Windows 7 64-bit OS
    SQL Server 2008 R2 Standard 64-bit
    Office 2010 32-bit (installed 32-bit because all legacy data needed to be ported)

My first attempt was within SQL Server's Management Studio by navigating to Server Objects - Linked Servers and right-click selecting New Linked Server...
First indication of some trouble began when I didn't see an ODBC Provider for my Access database.  After trying a few options, I turned to Google expecting a quick resolution.  Here is the beginning of 5 days of going round in circles!!!

Eventually I found some links that led me to potential solutions:
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/589ca193-3541-4c5e-965c-4c515d6b476b
http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/c371fb35-6bdd-42e6-b88e-d1c0003d23f6
http://social.msdn.microso

Interacting with the Excel Web Services API for SharePoint Server 2007

  
Get a quick start with the Excel Web Services API, which enables interaction with published Excel 2007 workbooks in SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance.

Administrator and Developer Guide to Code Access Security in SharePoint Server 2007

  
Explore configuration options, get best practices for managing CAS in SharePoint environments, and walk through a complex CAS scenario.

Publishing Excel 2007 Workbooks to SharePoint Server 2007 (Visual How To)

  
Watch the video and explore code as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Sample: Publishing Excel 2007 Workbooks to SharePoint Server 2007

  
Explore the code in this visual how-to article as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Linked server to access 2010

  
I keep getting an error when setting up a linked server in sql 2008 R2 x64 to a microsoft access database file (mdb or accdb file).  (The problem occurs on computers with either the 32-bit or 64-bit version of ms office installed).  What are the proper connection string settings?  (This is after right clicking linked servers in ssms and selecting "add new linked server")  The below settings don't work: Provider (from dropdown): Microsoft Office 12.0 Access Database ENgine OLE DB Provider Product name: Access Data source: C:\foopath\foo.mdb  --also doesn't work for foo.accdb Provider string: Microsoft.ACE.OLEDB.12.0 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYLINKEDSERVER" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303) I got some exotic errors when following the directions in Books Online (under sp_addlinkedserver). For example: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apa

Problem connecting to SQL Server 2008 R2 Express from Access 2007

  
I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?

Server drafts location not being saved Excel 2007

  
Hi, I have problem with "Server drafts location" not being remembered. And my "Server drafts location on this computer" button is not even checked but I still get the message: "The server drafts location you entered for offline editing is not valid or you do not have permission to access that location. Please specify a location on your local computer" Even if I change and choose a place for this it renders this message as soon as I for example "Adjusted List". Every thing I change in "Excel Options" renders the above message. I have full control over the "Application data" folder as well. Please see if you can help out since this is really giving me trouble in my daily work. I have Microsoft Windows XP professional sp 3 and Office 2007 latest sp. Thanks in advance! Mari

Problem with ODBC from MS Access 2007 to MS Sql Server 2008

  
Hi experts, I need some help with ODBC Connection Settings from MS Access 2007 to Sql Server 2008. Here is the Scenario, I have created a Database(StagingDB) with 5 tables on SQlServer 2008 and I have  Developed an MS Access 2007 Front End using Linked tables and ODBC to Insert/ Update data into these 5 tables. This is just working fine in my Dev database. ODBC Connection Settings are pointing to my DevServer. All I wanted to do now is, I want to point the ODBC Settings to the same database on my Test Server and then to Production Server. I have Changed the Server name in the ODBC File to point it to my Test Server and If I try to Insert  some data into test server its stil going into the database on Dev server. Is this the right way to do. Please help me with this. thanks In Advance..

Linked Server To MS Access using Jet Returns "unspecified error"

  
I'm have SQL SERVER 2008 on Win2008 Server. I have several linked servers to Oracle (odbc) and have just created Linked Servers to MS access 2003 databases on network shares. I can 'test connection' and select data from the linked ms access servers using 4 part naming no problem. As soon as I do this, the Oracle db links stop working. I reboot the server, test connection to Oracle dbs , all ok there, run test connection to ms access dbs and receive the following;Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink"".OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MYAccessLink" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) Reboot the server, test connection to ms access db's, all ok, then test connection to oracle, and receive the following;A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) (Microsoft SQL Server, Error: 109) Reboot server, perform test again, toggling the order and I get the same results.Have tried opening ms access via openrowset and "execute ('select count(*) from SomeTable') at MYAccessLink" to rule out test connection (sp_testlinkedserver) doing something to crash the links. I have checke

Is it possible to access an Analysis Server with Excel via http?

  
Hi, in our scenario we want to give another company access to out analysis server via web, their front end should be Excel. So first of all I made a setup like descriped here http://technet.microsoft.com/de-de/library/cc917711(en-us).aspx (but I have really no idea how to test it in an easy way because using MDX sample application ends up every time in "The connection either timed out or was lost.") But apart of that, is it possible to access an analysis server with Excel via http? I did not found any possibility to use a http-adresse in Excel as server path. Any ideas? Thanks and regards Peter  

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access

  
Hi,I am importing Excel 2007 file into SqlServer 2005 (Sqlexpress). The office 2007 is not installed in the machine. I have downloaded and installed "AccessDatabaseEngine". When I execute following statement SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\File1.xlsx', 'SELECT * FROM [Sheet1$]');The following error is comming:Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.Msg 7350, Level 16, State 2, Line 1Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".Can any one suggest what should I do to resolve?Thanks,Parmeshthee. Learning .Net 3.5

Upsizing Database from Access 2007 to SQL Server Express 2008

  
I have recently downloaded and installed SQL Server Express 2008 on to my Windows XP SP3 machine, I have a large MS Access 2007 database (.accdb) I wish to export out to SQL Server. I am using the 'upsizing wizard' that is found on the 'Database Tools' tab of Access 2007. Clicking on the 'Database Tools' tab and then on the 'SQL Server' button starts the Upsizing wizard. On the first page of which I select the 'Create new database' option, the next page asks 'What SQL Server would you like to use for this database?' it already has '(local)' entered into the text box, so I leave that unchanged, I tick the trusted connection box and name the new SQL Server database and then click 'Next'. But I get the following error (it appears in a Windows information message box):

Title: Microsoft SQL Server Login
Message Text:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(Connect()).
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

PS. I have successfully used the upsizing wizard to export the same Access database into SQL Server Express 2005 on another machine.

What am I doing wrong this time?

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend