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


Post New Web Links

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

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
 
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


View Complete Post


More Related Resource Links

Excel 2007 Spreadsheet - need to import it to SQL Server 2005

  

Trying to import spreadsheet from Excel 2007 to a table in SQL Server 2005, when I follow the steps for a linked server ,, I am able to create a linked server but because the instructions I am following are calling for a Microsoft 4.0 Jet something or another as "Provider" and I do not have that I am getting an error.. I have the a list of providers but the 4.0 Jet is not one of them...

 

Any help is greatly appreciated


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

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 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???

Unable to connect Excel 2003 from linked server in sql 2008 64 bit

  
Hi ALL,

I was unable to connect to excel 2003 with linked server created in sql server 2008 64 bit. This was working fine with sql server 2005 32 bit.

I know that there no 64 bit drivers for JET drivers, but i am looking for some alternative ways to do the same job.

I dont want to migrate to Excel 2007.

Thanks in advance for all your help.

Thanks,
Loknath

Linked server from SQL Server 2008 R2 to SQL Server 2005 not working

  

I'm trying to create a linked server from SQL Server r2 to SQL Server 2005 (both 64 bit Enterprise) using the following script

 EXEC master.dbo.sp_addlinkedserver
 @server = N'TestLS'
 ,@srvproduct=''
 ,@provider='SQLOLEDB.1'
 ,@datasrc=N'dw_prod'
 ,@provstr='Integrated Security=SSPI;';
 
 GO

SELECT
    *
 FROM
    [TestLS].[I_OLAP].[dbo].[FACT_AUDIT]
I verified that the Kerberos is working.

When I try to run a four part query as below it gives me the error

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI10" for linked server "TestLS" returned message "Invalid connection string attribute".


If I try to set up the linked server against localhost (i.e. SQL 2008 R2, it works just fine).


Thanks,
GB


GBM

Linked server issue on test box, post upgrade 2005 to 2008 R2

  

In an effort to do some testing prior to upgrading our environment to 2008 R2, I made a test instance on our Dev box.  2005 instance, copied as many things as I could think of from various other instances.  Made a basic linked server to our main cluster, had a repeating job to email me results of a query across that link every few hours.

 

Everything was working fine until the upgrade finished.  It completed at ~7pm.  The email at 6PM came through fine (while the upgrade was in progress), the email at 8pm didn't come through.  Checked various things (DB mail was still working, tested fine).  It couldn't access the data across the linked server.  I tried deleting that link and remaking it.  Errors out.  Tried running the same scripts we use to create our standard linked servers, error out.  The only ones that I can set up and function are links to other instances on the same box.

I've looked around at other fixes for this error message and none seem to make any difference.  Log in with Domain cred's, log in with the sa account, no difference.  I can connect from other instances & servers back to this one, just not outbound from this one.  And it worked prior to the upgrade I applied to it.  All other instances on the box are 2005 as well.

Here is the message I ge

Export to Excel 2007 Problem - SQL Server 2008

  

 

Hi! I did a quick test to dump data into an Excel spreadsheet. Everything worked fine, but when I created a job to do this for me and then run it, I get this error:

 

Date  10/8/2008 4:24:25 PM
Log  Job History (ExportClientInfoToSpreadsheet)

Step ID  1
Server  OHI0056
Job Name  ExportClientInfoToSpreadsheet
Step Name  CreateExcelClientExport
Duration  00:00:00
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
Executed as user: OHI0056\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Option "12.0;HDR=YES;" is not valid.  The command line parameters are invalid.  The step failed.

 

Here is the script of the job. Any ideas what is going on?

 

Thanks!

 

 

USE [msdb]

GO

/****** Object: Job [ExportClientInfoT

Issue with linked server 2008 (and R2) not an issue in 2005

  

Hi,

I have been using SQL 2005 successfully to connect to tables in a 10g Oracle data warehouse. I have been using the 11g client.

I have tried several times to get this working in SQL Server 2008 (and R2) and have tried the 10g and 11g clients...

I do a query like Select top 100 * from DWD..DW.IC_TRAN_PND and I get the error: 

"Invalid data for type "numeric".

 

I have read about the reasons for this but is there a way to make it work like it does in SQL server 2005?


Upgrade from SQL Server 2005 to SQL Server 2008 from SharePoint 2007 SSRS 2005 integrated mode

  

I"m attempting to upgrade from SQL Server 2005 to SQL Server 2008 R2 x64 Enterprise on a server running Windows Server 2003 x64 Enterprise, SharePoint 2007 Enterprise, and SQL Server 2005 and SSRS 2005 Enterprise running in integrated mode.

I installed the Microsoft SQL Server 2008 SP2 Reporting Services Add-in for Microsoft SharePoint Technologies from here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=35c3a13f-4b5e-4d4a-86e4-07ee307b9638&displaylang=en.

When attempting to install Report Builder 2.0, I get the error "Reporting Services Add-in for SharePoint products and technologies is not installed."

When I try to run a report (existing 2005 reports already deployed), I get the error: "Operation is not valid due to the current state of the object." The log has repeating ReportingServicesService.exe errors: #20015: Cannot open "Resources.en-US.resx": no such file or folder, and: Localized resource for token 'ManageDatasetCaching_Desc' could not be found for file with path: "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Template\Features\ReportServer\ItemCustomActions\ecbExtension.xml" among other things.

I've tried rec

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

How can Install Office 2007 on Windows server 2008 R2 64 bit machine in WSS 3.0

  
I have  64 bit machine  and Windows server 2008 R2 has installed. i have successfully install WSS 3.0  , but  when i tried to install  office 2007 ,  one  error  has  come  "OS is not compatible "  i thought  it was asking  for 64 bit office  2007   and i go through the  google and R&d find no 64 bit office is available ,  i have used   excel .dll in my custom code  so my problem is that   how can  install office 2007  on 64 bot OS 2008 r2  machine .  if anyone can help   me  , please let me know . thanks in advance

Importing Excel 2007 spreadsheet into WSS 3.0 -- Error Message

  

Hi,

I'm trying to import (Custom Lists >> Import Spreadsheet) into WSS 3.0 and I'm getting the following message: 

Refers to the _layouts

You are not authorized to view this page.  You might not have permissions to view this direcotyr or page using the credentials you supplied. [More stuff here.]

Http ERror 403 - Forbidden

Is this just a permissions problem or is there some other underlying issue?  Should you be able to upload an Excel spreadsheet (with links) into a Custom List?

Thanks!


Thanks! Patti N.

VS Studio WorkFlow Failed on Start in MOSS 2007 ON Windows Server 2008 64 Bit

  

I have created a sequential sharepoint workflow in WSP Builder in my Sharepoint Server and Deployed it.

But the workflows says Failed on start and retrying ans stops there forever says couldn't start the workflow.

I repaired .NET Framework 3.5sp1 installed

 tried this

 1. I tried again, in C Prompt:  Lodctr "c:\Windows\Microsoft.Net\Framework\v3.0\Windows Workflow Foundation\perfcounters.ini"  it says initiation file cannot be found

2   i again tried this C prompt Lodctr /R "c:\Windows\Microsoft.Net\Framework\v3.0\Windows Workflow Foundation\perfcounters.ini"

it says reloaded  from the system restored

i am still getting the same error.

When i manually go to my  C:\Windows\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation i can see a folder for SQL that's it nothing more than that..

I dont know what is happening i am trying it from 2 days and still no solution.

any ideas would be real great help for me.

My system is 64 bit , Window server 2008, MOSS SP2, Visual Studio 2008 Version 9.0.30729.

.Net Framework 3.5 sp1

and WSP builder the one from Codeplex for 64 bit machines


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.

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.
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