I have recently completed upsizing a large Access 97 system from a Jet
backend to SQL Server 2000.
It uses linked tables and DAO exclusively.
It's working fine.
Sooner or later, the front end will need to go to A2003 or whatever,
which I asssume won't be too much of a problem.
Would there be any advantage converting to ADO, now, or when it goes to
A2003?
I can't see any justification at the moment.
Terry BellHi
There probably isn't any significant reasons to do this if you are keeping
Access as the front end, although it should help reduce the impact of the
upgrade to 2003.
John
<dreadnought8@.hotmail.com> wrote in message
news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>I have recently completed upsizing a large Access 97 system from a Jet
> backend to SQL Server 2000.
> It uses linked tables and DAO exclusively.
> It's working fine.
> Sooner or later, the front end will need to go to A2003 or whatever,
> which I asssume won't be too much of a problem.
> Would there be any advantage converting to ADO, now, or when it goes to
> A2003?
> I can't see any justification at the moment.
> Terry Bell
>|||BTW
You may want to post to an access newsgroup as they are more likely to have
indepth experience of this sort of conversion.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzQgLjKdFHA.1244@.TK2MSFTNGP10.phx.gbl...
> Hi
> There probably isn't any significant reasons to do this if you are keeping
> Access as the front end, although it should help reduce the impact of the
> upgrade to 2003.
> John
> <dreadnought8@.hotmail.com> wrote in message
> news:1119167899.670346.155260@.g47g2000cwa.googlegroups.com...
>>I have recently completed upsizing a large Access 97 system from a Jet
>> backend to SQL Server 2000.
>> It uses linked tables and DAO exclusively.
>> It's working fine.
>> Sooner or later, the front end will need to go to A2003 or whatever,
>> which I asssume won't be too much of a problem.
>> Would there be any advantage converting to ADO, now, or when it goes to
>> A2003?
>> I can't see any justification at the moment.
>> Terry Bell
>|||Yes thanks - I posted to this newsgroup in error
Terry|||Hi
DAO and RDO are considered obsolete by Microsoft.
http://msdn.microsoft.com/data/mdac/techinfo/default.aspx?pull=/library/en-us/dnmdac/html/data_mdacroadmap.asp
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dreadnought8@.hotmail.com> wrote in message
news:1119185731.468657.15010@.g49g2000cwa.googlegroups.com...
> Yes thanks - I posted to this newsgroup in error
> Terry
>
2012年2月25日星期六
2012年2月23日星期四
Any idea why OpenRowSet to open Excel file doesn''t work well in SQL 2005?
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
Any idea?
this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,
Any help?
|||any suggestion?
help please
|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.
I am using the buildin/administrators to connect to SQL server
|||I get following error while trying to query ACCESS data, any clues?
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
|||Ashish,
I'm getting the same error you did. Did you ever resolve the problem?
In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.
But if I log in with my domain account from another PC or server, I get this error in the report server:
An error has occurred during report processing.
Query execution failed for data set 'FinanceLinkedServer'.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".
My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.
I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.
Thanks!
|||I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:
My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.
|||Thanks, davery921. I tried that, but it didn't resolve the error.
Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?
|||First, to answer your question, the answer is yes, I've gotten it to work.
Next.... I need help too...
I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:
DECLARE @.RC int
DECLARE @.STARTDATE datetime
DECLARE @.ENDDATE datetime
-- TODO: Set parameter values here.
EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]
'07/01/2007','07/31/2007'
On my machine through SSMS, I get this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my test server through Remote desktop, to the test database, it runs just fine!
Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.
The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.
Any ideas?
Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
Any idea?
this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,
Any help?
|||any suggestion?
help please
|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.
I am using the buildin/administrators to connect to SQL server
|||I get following error while trying to query ACCESS data, any clues?
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
|||Ashish,
I'm getting the same error you did. Did you ever resolve the problem?
In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.
But if I log in with my domain account from another PC or server, I get this error in the report server:
An error has occurred during report processing.
Query execution failed for data set 'FinanceLinkedServer'.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".
My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.
I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.
Thanks!
|||I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:
My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.
|||
Thanks, davery921. I tried that, but it didn't resolve the error.
Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?
|||First, to answer your question, the answer is yes, I've gotten it to work.
Next.... I need help too...
I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:
DECLARE @.RC int
DECLARE @.STARTDATE datetime
DECLARE @.ENDDATE datetime
-- TODO: Set parameter values here.
EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]
'07/01/2007','07/31/2007'
On my machine through SSMS, I get this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my test server through Remote desktop, to the test database, it runs just fine!
Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.
The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.
Any ideas?
Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
Any idea?
this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,
Any help?
|||any suggestion?
help please
|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.
I am using the buildin/administrators to connect to SQL server
|||I get following error while trying to query ACCESS data, any clues?
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
|||Ashish,
I'm getting the same error you did. Did you ever resolve the problem?
In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.
But if I log in with my domain account from another PC or server, I get this error in the report server:
An error has occurred during report processing.
Query execution failed for data set 'FinanceLinkedServer'.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".
My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.
I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.
Thanks!
|||I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:
My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.
|||
Thanks, davery921. I tried that, but it didn't resolve the error.
Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?
|||First, to answer your question, the answer is yes, I've gotten it to work.
Next.... I need help too...
I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:
DECLARE @.RC int
DECLARE @.STARTDATE datetime
DECLARE @.ENDDATE datetime
-- TODO: Set parameter values here.
EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]
'07/01/2007','07/31/2007'
On my machine through SSMS, I get this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my test server through Remote desktop, to the test database, it runs just fine!
Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.
The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.
Any ideas?
Any idea why OpenRowSet to open Excel file doesn't work well in SQL 2005?
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\ablefiles\sitefiles\4000010\reibc\active.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
After I installed SP2. the query works on the SQL server, but still doesn't work when run it in management studio on my home machine, I connect by VPN. I get
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
Any idea?
this drives me crazy, it seems it doesn't matter where I run it, I just tried it in the studio on the SQL server itself, it gives me
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
it seems it can work sometime, but not the other time, everytime when I run it to handle data in Excel, it doesn't work, but later it can work,
Any help?
|||any suggestion?
help please
|||I've seen this exact error, but only when the path to the XLS file was wrong (SQL couldn't find the file). Suggest you try a simple path (eg. C:\TEMP\XLSFILE.XLS) first. And of course make sure you have privileges to read that folder.|||Something is not right, it didn't work and then started to work, after restart SQL server, it doesn't work again, even I copy file to C:\ of the SQL server, it gives me the some error, well before with the same account, it worked for me when the file is on remote server.
I am using the buildin/administrators to connect to SQL server
|||I get following error while trying to query ACCESS data, any clues?
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
|||Ashish,
I'm getting the same error you did. Did you ever resolve the problem?
In my case, I've built and deployed a SSRS 2005 report that's based on a view that connects to a linked server (an Access .MDB). I used my domain account to build and deploy the report, and if I try to view it locally (on the SSRS server -- using http://localhost/reports), it works perfectly without any errors.
But if I log in with my domain account from another PC or server, I get this error in the report server:
An error has occurred during report processing.
Query execution failed for data set 'FinanceLinkedServer'.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FINANCEDB".
My domain account has Full permissions on the SSRS server, the Access .MDB itself, and the folder in which it is stored.
I've also checked to make sure that the domain account under which the SQL Server and SSRS services are running also has Full permissions on the Access .MDB and it's container/folder.
Thanks!
|||I was having a similar issue, and thanks to Systernals FileMon I found that the sqlserver process tries to access the %tmp% directory of the profile of the account the process is running under as the Windows Authentication credentials of the user. Don't think I stated that very well, so I'll give my example:
My sqlserver was running under an account called 'DOMAIN\SqlServiceAct'. The %tmp% directory of this account was the 'C:\Winnt\Profiles\SqlServiceAct\Local Settings\Temp' folder. By changing the setting of this folder to allow both Read and Write permissions to the built-in Domain Users group, all is well.
|||Thanks, davery921. I tried that, but it didn't resolve the error.
Does anyone else have any ideas? Has anyone out there actually built a report based on a linked Access database and gotten it to work properly?
|||First, to answer your question, the answer is yes, I've gotten it to work.
Next.... I need help too...
I’m getting the strangest error. I have three SSMS clients, one on a PC, two on servers through remote desktop, the servers are both running SQL 2005. If I run this:
DECLARE @.RC int
DECLARE @.STARTDATE datetime
DECLARE @.ENDDATE datetime
-- TODO: Set parameter values here.
EXECUTE @.RC = [Reporting].[dbo].[uspPCChargeAudit3]
'07/01/2007','07/31/2007'
On my machine through SSMS, I get this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my production server (through Remote desktop), I get the same error when this machine is pointed to my Test database.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE" returned message "The Microsoft Jet database engine cannot open the file '\\SERVER_NAME_REMOVED_FOR_POST\Active-Charge\pccw.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure uspPCChargeAudit3, Line 30
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PCCHARGE".
If I run this on my test server through Remote desktop, to the test database, it runs just fine!
Now here is the kicker… If I run this through Remote desktop on my production server to the production DB, it works!!! If I run the same script point to my production database from my pc, it works.
The only difference is that my production machine that is pointed to the production database is interacting with Oracle 9i in the SP while my test database SP is interacting with Oracle 10g. They all point to the same access database on the same server, competing for the same resource. We are just testing 10g.
Any ideas?
订阅：
博文 (Atom)