Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
QueenKirsty
Starting Member
18 Posts |
 Posted - 2010-08-31 : 12:52:52
|
| Setup:1. I have a SQL Server 2008 db (called VantageTrans) with a linked server (a Progress db called VANTAGE - the back end of our financial system). 2. I have a stored procedure (spUpdateTblForFile) on VantageTrans (the SQL Server db) that pulls data out of the tables in VANTAGE (the linked server) and dumps the manipulated data into tables in VantageTrans. Issue:The stored procedure runs fine on when run directly on the server db (VantageTrans) but will not run when called from VBA. I have tried calling it from Access (.accdb and .adp) and from Excel using the ADODB.Connection / ADODB.Command functions but it just hangs. Initially it produced a timeout error but I set the connection string and command timeouts to 0 and that removed the error. I can run stored procedures successfully from VBA that only use the VantageTrans data tables so I am confident that the VBA code is correct. PLEASE can anyone suggest what the issue with the linked server might be? I wondered if it might be a permissions thing when trying to connect to the linked server but I don't know how I can check his out.All help would be gratefully appreciated! :-) |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-31 : 13:18:10
|
| what security context are u using for the linked server? sounds a lot like the user(s) from the Access and Excel apps, don't have permission. also, are u using windows authentication or sql authentication in the connection string?I don't know about VANTAGE, but if it runs on a Windows server, and you're using windows authentication, you'll need to allow delegation for SQL Server service account in Active Directory and register the SPN for SQL Service |
 |
|
|
|
|
|
run sp using data from linked Server via VBA