Is there any way (possibly cheap) to access Microsoft Sql Server from z/OS mainframe (COBOL programs) and have the result in 3270 terminal emulation?
I'm aware that 3270 is a pretty old system, but in bank CED, is still very popular.
It depends on what you are actually trying to do. My reading of your question is that you want to have a mainframe based process access an SQL Server database, and then do something with the result, probably involving a 3270 terminal.
If you can use Unix System Services, you can compile a TDS library like FreeTDS and then use a C program to do what you want with the result. If you want to get more complex, you can run the connection from the native z/OS environment by compiling the code with IBM C, SAS C or Dignus C/C++. I can recommend Dignus and I have used it to build code that interacts with other languages on z/OS. The Dignus headers and runtime library have (from memory) some FreeBSD lineage which helps to simplify porting.
Using this approach you can get a load module that you can call from some other part of your system to do the work, you can link the code with other parts of your system, or you can just submit a job and get the output.
If you want to use Java, you can use something like jTDS and write Java code to do what you need. I haven't used Java on z/OS, so I can't offer specific advice there, but I have used jTDS on other platforms and I've been happy with the result.
Update:
You can export a C function as an entry point to a load module and then call that from Cobol. The C/C++ implementation needs to deal with Cobol data structures; they are well defined and predictable so that isn't a problem. Depending on how flexible you need things to be, you could compile the query into the C code and just have a function which executed a predefined query and had an interface for retrieving the result, or you could have something more complex where the query was provided from the Cobol program.
I have used this approach to provide API functions to Adabas/Natural developers and it worked well. The Dignus compiler has a mechanism for callers to provide a handle to a runtime library so that you can manage the lifetime of the C runtime environment from the calling program.
For a C/C++ developer, that should be fairly straightforward. If your developers are all Cobol developers, things could be a bit more tricky.
The gateway approach is possible, and I'm sure there are gateway products around, but I can't recommend one. I have seen crappy ones that I wouldn't recommend, but that doesn't mean that there isn't a good one somewhere.
For completeness, I'll mention the possibility of implementing the TDS protocol in Cobol. Sounds like cruel and usual punishment, though.