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 -
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.
A bit of an update as I've had no replies so far...
SP1 for the SQL Server 2008 Express Edition was just installed.
I am now only trying to use this driver:
I have noted that I can create a Linked Server that if I right click it from SSMS and then select 'Test Connection' it returns 'Succeeded'
However.... when trying to expand the Linked server in the gui to see Tables or Views I get the following:
TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
As before any help would be greatly appreciated.
One further point to note here is when the error dialog pops upin SSMS there is a 'show technical details' button. Clicking this button reveals more detail about what went wrong and it includes this:
Error Number: 7399
Line Number: 41
I have looked at the sp_tables_ex procedure but I'm kind of stuck here.
To clarify how I managed to get past the 7303 error - it seems that if I select the properties of the driver (under Providers), if the 'Allo Inprocess' is ticked I get the 7303 error but if it's cleared I do not and instead I get the 7399 error when I try to view the tables.
I've also tried changing the SQL Server user account credentials to various different accounts to no avail.
Thankyou for the suggestion but this is still not working.
I have moved the Access mdb file as suggested and my sp_addlinkedserver command looks like this:
"TEST","MsAccess2k","Microsoft.Jet.OLEDB.4.0","C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\odbc.mdb"
The linked server is created succesfully but is unusable as either the error 7303 occurs if 'Allow Inprocess' is checked against the Jet provider or 7399 occurs if 'Allow Inprocess' is checked.
I am can Link a server to another SQL Server instance on another server and drill into it up to the DB but the tables of the DB are blocked.
I will poke around and research. If I find anything out, will let you folks know. Really don't think it is a permissions issue.
Had the same issue (first 7303 but" allow inprocess" solves it , then 7399). This works for me:
error 7399:"This problem occurs because the login account does not have full access to the temporary folder of the SQL Server startup account. The linked server query runs in the context of the login account. If you run a linked server query, SQL Server tries to create a temporary file data source name (DSN) in the temporary folder of the SQL Server startup account." - from http://support.microsoft.com/kb/814398
I thin its cause I installed server withoit option "Run as admin", or didnt choose account for server in instalation process.