Solved

SQL Server error

Posted on 2014-01-17
6
564 Views
Last Modified: 2014-01-23
Hi EE,

Could you please help on below error that we are getting in our end and cannot  access DB Server. I am not very familiar with this error. Please post me how to troubleshoot  this issue how we prevent from this in future( Can we setup any monitoring ?)  . Thanks and much appreciates.
Regards
J
=================================================
"In a nutshell, we are trying to use Linked Server to combine data from the SQL server and an Access file (no password on the Access file) but can't get the Linked Server to work. We tried to create the Linked using both Server Management Studio and T-SQL with the same result.
 
At the creation stage (sp_addlinkedserver) no errors are returned. However, all statements trying to use the linked server return some kind of error. The procedure sp_testlinkedserver is generating the error code 7303 but we are unable to resolve the issue."
===================================================
0
Comment
Question by:Jey_P
  • 3
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 90 total points
ID: 39788921
They are trying to create a linked server.

The error they're getting says that for some reason they can't initialize that data source.  Either they're creating the linked server wrong or additional permissions are needed.

http://technet.microsoft.com/en-us/library/aa226395(v=sql.80).aspx
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 270 total points
ID: 39792700
I am going to go out on a limb and suggest the linked server is from a 64-bit SQL Server using JET to connect to MS Access.  Is that correct?  If so, you should know there is no 64-bit JET driver.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39793795
Anthony: actually, since Office 2010 there is one, see Microsoft Access Database Engine 2010 Redistributable

But you might indeed be on to something!  For a 64-bit SQL they'd indeed need to install the 64-bit driver and not 32-bit.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 270 total points
ID: 39795074
since Office 2010 there is one
I suspect you will find that is not a JET driver, but rather an ACE driver.  My point was they cannot use a JET connection from a SQL Server 64 bit O/S.

But feel free to let me know if I am wrong.
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 90 total points
ID: 39796322
I suspect you will find that is not a JET driver, but rather an ACE driver.  My point was they cannot use a JET connection from a SQL Server 64 bit O/S.

Well, ACE actually is the new JET.  It's backward-compatible and can thus be used to create the linked server from a 64-bit SQL.

With version 2007 onwards, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. This engine was fully backward-compatible with previous versions of the Jet engine, so it read and wrote (.mdb) files from earlier Access versions.

The engine in Microsoft Access 2010 discontinued support for Access 1.0, Access 2.0, Lotus 1-2-3 and Paradox files. A 64-bit version of the ACE Driver/Provider was introduced, which in essence provides a 64-bit version of Jet.

The engine in Microsoft Access 2013 discontinued support for Access 95, Access 97 and xBase files.

From a data access technology standpoint, Jet is considered a deprecated technology by Microsoft, but Microsoft continues to support ACE as part of Microsoft Access.
Ref Microsoft Jet Database Engine [Wikipedia]

The above means that care should be taken on which ACE driver to install: if it's an oldes Access version (95/97) then it's not a good idea to install the latest (2013) ACE driver, but ACE 2010 should work.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 270 total points
ID: 39797723
Certainly ACE can be used where JET used to be used previously, but they are not the same connection string, that is the point I was making.  In other words yes, they may be able to use ACE instead of JET however, they will have to change their linked server.

I trust this is clear now.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now