Getting started using SAP BODS (Business Object Data Services). My testing playground are my 2 local SQL servers, the main SQL Server 2008 R2, and its stepbrother, SQLEXPRESS.
The jobs run on SAP don't run locally from my repository, but from a central network server.
Didn't realize that at first; had to learn the hard way.
Couldn't figure out for the life of me why executing jobs worked fine connecting with the my main SQL Server 2008 R2, which doesn't depend on a named instance, while the Express server could never be found.
Even after realizing, upon combing through the job's error output, that these jobs were not being locally executed, it didn't dawn on me right away that the Named Instance nature of the express server was the real issue.
I'm kinda dense that way sometimes.
Anyhoo, after combing back and forth like an ocean tide over all the configuration and security settings of the express server and my PC, looking for the magic bullet or best suspect to arrest, I finally received the epiphany.
The remote server executing the jobs only knew my local machine's name, which by default pointed to the main SQL server without needing a named instance. Adding the express's name to that was the equivalent of speaking in tongues to the job server, with it being an atheist.
No named instance, fine, let's get to it and work.
Named instance, "like, DUH??? Which way do I go George, which way do I go...?"
Supposedly there's always got to be one, but when Googling tech issues, you're lucky to even find one real solution, or even a mere clue to one.
Turned out, all I needed to do was turn the SQL Server Browser service on, along with making SQLEXPRESS's TCP/IP enabled (in SQL Server Configuration Manager under the server's protocols), which I'd already done further back in the process of futility.
Bang, boom! Named instance found! Job complete.
One last little thing, in express's TCP/IP protocol settings, I had to make sure what was listed under IPAll for dynamic ports, was set as THEE port for it, because the normal default SQL port, 1433, was already taken by 2008 R2.
I just cut the port number from Dynamic ports and pasted into 'TCP Port'.