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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Linked Server using Microsoft.Jet.OLEDB.4.0 problem

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

Hi all,

I'm using following code to create linked server and then access data

EXEC sp_addlinkedserver N'XLS',

'Jet 4.0',




'Excel 5.0;'

EXEC sp_addlinkedsrvlogin N'XLS', FALSE, NULL, Admin, NULL


View Complete Post

More Related Resource Links

Cannot create instance of "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" on x64 Vista with x6

I am attempting to take data from an Excel 2007 spreadsheet and use OpenDataSource to throw the data into a SQL Server 2008 SP1 table under an x64 OS and x64 SQL Server.  I was able to get this to work under x86 versions of the OS and SQL by setting sp_MSset_oledb_prop with values of AllowInProcess and DynamicParameters to 1.  However, under x64 SQL 2008, when I set the AllowInProcess option to 1, I get the following error:  "OLEDB provider "Microsoft.ACE.OLEDB.12.0" cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."  If I turn this flag off, I receive the error shown above in the title.I saw suggestions about creating a .Net program and then forcing it to target an x86 processor, but this method did not work either.  I also saw suggestions about installing the 2007 Data Access drivers, and although I believe they already were installed, I installed them specifically, but it changed nothing.  I also tried remotely accessing the x64 system from an x86 environment, but it also changed nothing.Therefore, I cannot get the OpenDataSource method to work from Mgmt Studio or from an x86 program, so I would appreciate insights into how someone is supposed to be able to retrieve data from an Excel 2007 workbook in an x64 environment.  I find it hard to believe that with numerous servers running on x64 t

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

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Syntax error


Hi there.

I have been sitting hours and hours trying to solved this issue with absolutely no luck.

I have the following query:

Code Snippet

DECLARE @sqlScript1 VARCHAR(8000)

-- Import records fron source file (OPENROWSET)

SET @sqlScript1 = 'SELECT * INTO ##tmpOrders FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,

''Excel 8.0;Database=' + @fileName + ''', ''SELECT [Sale Time] AS SaleTime, Ref, [Item Title] AS ItemTitle, [Item Type] AS ItemType, [Item Id] AS ItemId,


Problem when installing Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint


I have configured the SharePoint Production Server(Standalone) with WS 2008 64 bit version, SQL Server 2005 with SP3 , Visual Studio 2008 and MOSS 2007(SP3). I have configured SSRS 2005 in SharePoint integration mode. Everything works fine till the last step. Now, i am trying to install Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies feature and failed. I am getting below error message.

Product: Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies -- You must first install SharePoint Server before installing SQL Server 2005 Reporting Services Add-in for SharePoint Technologies

Any help will be very appreciated!


Installing Microsoft.ACE.OLEDB.12.0 on a shared server--supported or not?

Here's the scenario: we have a Web application that needs to open an Excel 2007 file, read the sheet names and pass the sheet selected by a user to a SSIS package for processing on the DB server. During development, we discovered that the Microsoft Access Database engine (Microsoft.ACE.OLEDB.12.0) is required for our application to perform these tasks when running on a 64-bit server. Details: ASP.NET 4/VS 2010/SSIS on SQL 2008 R2; 32-bit environments have no issues; 64-bit requires installation of the provider. We followed this link to get the components: http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en We have installed the components and everything works great in our development and test environments. However, we must deploy to a 64-bit environment that is controlled by our customer's IT group, not by us. They are balking at installing the components because of the following statements that appear on the download page in the "Additional Information" section: "The Office System Drivers are only supported under certain scenarios, including: " Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files. To transfer data between supported file formats and a database repository such as SQL Server; in the

Linked server and sensitive to register name of table. Problem with UPDATE.

Hi All. I try to work with table with "sensitive to register" name through Linked Server (MSSQL 2005/2008) and get the problem with UPDATE statement. Reason: MSSQL generates UPDATE statement with "un-quoted" table name. With SELECT/INSERT/UPDATE - no any problems. ----- Linked Database Information: 1. Firebird 2.5 2. OLEDB Provider: IBProvider v3 3. Database dialect: 3 Metadata: CREATE GENERATOR "GEN_ID_TableWithMixName1"; CREATE TABLE "TableWithMixName1" ( TEST_ID INTEGER NOT NULL, "Col" VARCHAR(100), DUMMY_COL INTEGER, CONSTRAINT "PK_TableWithMixName1" PRIMARY KEY (TEST_ID) ); CREATE TRIGGER "BI_TableWithMixName1_TEST_ID" FOR "TableWithMixName1" BEFORE INSERT AS BEGIN IF(NEW."TEST_ID" IS NULL)THEN NEW."TEST_ID" =GEN_ID("GEN_ID_TableWithMixName1",1); END; ------- MSSQL Test 1. MSSQL: select * from IBP_TEST_FB25_D3_V3...TableWithMixName1; IBProvider: Command_Execute   SELECT "Tbl1002"."TEST_ID" "Col1004",         "Tbl1002"."Col" "Col1005",         "Tbl1002"."DUMMY_COL" "Col1006"   FROM "TableWithMixName1" "Tbl1002" No Problem ------- MSSQL Test 2. MSSQL: delete from IBP_TEST_FB25_D3_V3...Tabl

Problem trying to attach database file to Microsoft SQL Server Management Studio

I was trying to attach a database to SQL Server MGT Studio and got an exception error.  The message stated that the database file I was trying to attach could not be opened because it was version 661.  The SQL Server shows to support version 655 and earlier.  It states that a downgrade path is not supported. Question:  Is there some download ; fix; I can make to SQL Server 2008 that will allow me to attach the version 661????  I am not technical at all.  Please speak slowly.  :)  Thanks

Linked Server. DBTYPE_DBTIME2. Problem with Precision and Scale

Hi I added to my OLEDB provider (for IB/FB) the support of DBTYPE_DBTIME2 (from MSSQL Native Client) FB/IB supports a data type "TIME" with four digit in fraction of second. By other words: "hh:mm:ss.ffff" So, I define Precision=13, Scale=4 When I try to work with this DBTIME2-column through linked server, MSSQL 2008 returns the error: Msg 7356, Level 16, State 1, Line 7 OLE DB "LCPI.IBProvider.3" "IBP_TEST_FB25_v3" . "COL_TYPE_TIME"  .... "LENGTH" at compile time: 5, at execute time: 4. Ok. I change the Precision to 16 and Scale to 7 (as in SQLNCLI) With this settings, MSSQL 2008 can to work (read/write) with my DBTIME2-column. I tried to use new column flags [DBCOLUMNFLAGS_SS_ISVARIABLESCALE]. With (16/7) - no problem. With (13/4) - I get the same error. Question: How to define my DBTIME2-column with required precision and scale (13/4)? --- I tried to explore how SQLNCLI works with the time(4) column (precision:13, scale:4) But did not found anything unexpected, except DBCOLUMNFLAGS_SS_ISVARIABLESCALE

Problem when adding Linked Server...

Hi @all,I try to add a new Linked Server to my DB in Microsoft SQL Server Management Studio Express...The following error is returned (SERVER2 is the Linked Server to add): There is no remote user 'sa' mapped to local user '(null)' from the remote server 'SERVER2'. (.Net SqlClient Data Provider)I entered the user sa and the correct password for SERVER2 but it doesen't work...When I connect to SERVER2 in Management Studio with the same infos it works fine...So please help...

Linked Server Problem

We migrated our production SQL Server 2000 database to a new machine and all seems to be working well except for our linked server on our SQL Server 2005 instance that points to our SQL Server 2000 database that was just moved.  We get the following error when we try to query a table on the 2000 database.  I have read this article in the Books online but that does not seem to apply to us ( http://msdn2.microsoft.com/en-us/library/ms175496.aspx ). OLE DB provider "SQLNCLI" for linked server "SQLPRD-DBS" returned message "Communication link failure". Msg 233, Level 16, State 1, Line 0 Named Pipes Provider: No process is on the other end of the pipe. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'sa1'.   Anyone have any ideas? Thanks!!!

Microsoft SQL Server 2008 Enterprise Installation Problem


Good evening!

I downloaded Microsoft SQL Server 2008 Enterprise Edition (x86 and x64) from MSDNAA (i study computer science) and also  Windows 7 Professional (x86). I installed Windows 7 and i tried to install SQL Server but i receive the "Microsoft .NET Framework 3.5 installation has failed. SQL Server 2008 Setup requires .NET Framework 3.5 to be installed." error. I tried installing .NET Framework but it doesn't work because it's installed by default with Windows 7 (i checked the "Turn Windows features on or off" page and the "Microsoft .NET framework 3.5.1" checkbox is checked). I understand that this means .NET framework 3.5 SP1 is also installed so i shouldn't have any errors.

Can anyone help me with this installation problem?

Thank you!

Microsoft SQL Server 2005 Uninstall remove problem


In trying to install the 2010 Switched On Schoolhouse Home Edition it tries to install the SQL server then comes up with error message that it fails.


Tech support walked me through trying to delete all the SQL titles manually and I came to the last and only application of Microsoft SQL Server 2005 with a log file "80" that will NOT delete.


The IT told me it was corrupt in the registry.


Is there ANY way to delete this??? The new installation has a newer version but I think it is the same one.


Thanks, Ricky

Not able to see microsoft oledb provider for oracle in sql server 2005 providers



can any one help me, environment window server 2003  64 bit and sql server 2005 sp3 64 bit and i have installed oracle client  10g 32 bit. problem is, I am not able to see the provider name  while creating linked server.

Problem with "Error 0xD59 when trying to install the Microsoft SQL Server Express edition Service Pa

Hi Folks,

I've been jumping from site to site trying to find a solution for this and still no luck.  I have tried this update many times but it always fails.  I have the results of the installation log here if it helps.  I am on HP hardware using XP.  Any help is most appreciated.  Thanks for reading!

Time: 11/30/2009 06:25:09.718
KB Number: KB955706
OS Version: Microsoft Windows XP Professional Service Pack 3 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 3
Package Version: 4035
Command-line parameters specified:
Cluster Installation: No

Prerequisites Check & Status
SQLSupport: Passed

Products Detected                         Language  Level  Patch Level       Platform  Edition
Express Database Services (MSSMLBIZ)      ENU       SP2    2005.090.3042.00  x86       EXPRESS

Products Disqualified & Reason

Another Oracle linked server problem


I have trouble making a linked server from a SQL Server 2008 R2 64bit to an Oracle database.  I have a linked servers on a few other PCs working fine to this same Oracle server.

On this particular one, I configured the following and Command Test connection shows that it is working fine:


But when I tried to create the linked server as shown below, it fails.

The error is: 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "CARE".
OLE DB provider "OraOLEDB.Oracle" for linked server "CARE" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

I can't find what other parameters I could adjust.  How should I go about solving this problem.  Thanks.

Very strange Linked Server problem



I've got a server setup with SQL2008R2. It connects (linked server) to another SQL server which runs SQL2005. The link works fine in management studio, and it also works ok in code (where I use it to query the data). But after a few minutes of inactivity, suddenly the link begins to fail in code (SQLException; System.Data.SqlClient.SqlException: SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. ). I then open up management studio, click "Test connection"; management studio says it's ok. Running the code again, and it suddenly works fine for a few minutes, untill the same problem appears.

Seems like a bug, but is there a solution for this problem?

MCTS in Web Application Development in .NET 2.0

Very-NewBie Linked Server Problem


I'm having a problem selecting from an Excel linked server...


1. Create a new simple Excel file at C:\DeleteMe.xls and add a couple column headers and a row of data. e.g.



ColA | ColB |


123  | 456  |


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