Hi Stuart, I've used SQLExecDirect with UV before, we always allocate and prepare for each of the required parameter markers. Then call UV BASIC subroutines via SQLExecDirect.
I would hazard a guess that QueryTool is doing a SQLExec and Transact-SQL pass-through is doing a SQLExecDirect. The later which requires the parameters to be pre-declared using SQLPrepare in order for the query result set to be returned successfully. You could test this by calling "DATE" using both tools and see what happens. As it has no parameters - I suspect both queries should work. Whereas, anything requiring parameters is likely to fail where the tool is using SQLExecDirect. Finally, is your ZSELECT_CUST a BASIC subroutine? Is it globally catalogued? If not, try cataloguing it as *ZSELECT_CUST and then call it like CALL *ZSELECT_CUST(1), etc. Hope this helps... :) Regards, David -----Original Message----- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Boydell, Stuart Sent: Monday, 16 May 2011 5:48 PM To: U2 Users List Subject: [U2] UVSQL CALL with MSSQL Openquery against linked UV server. Hi, Just wondering if anyone has used the SQL CALL statement from MSSQL against a linked UV server and got it working. I have a linked server set up in MSSQL using UniOLEDB which works for most SQL queries. I now find I need to call a basic program to do some work to setup the result set I need. I have built and tested the program which does some updating and sets @hstmt using SQLExecDirect(). Using a simple third party OLEDB client (ADO Query Tool http://www.gpoulose.com/), the CALL to the program returns the expected row set. However, if I try the same query from within SSMS it bombs out with an error. Using ADO QueryTool: Works: CALL ZSELECT_CUST(1) Using MSSQL Management Studio: Works: select * from openquery(UVServer,'SELECT * FROM CUST SLIST QQQ') Doesn't work: select * from openquery(UVServer,'CALL ZSELECT_CUST(1)') And returns the error: Cannot get the column information from OLE DB provider "IBM.UniOLEDB" for linked server "UVServer". The other possibility might be running as a 4 part exec - which also isn't working for me. Exec UVServer...[ZSELECT_CUST(1)] Which returns Msg 7411, Level 16, State 1, Line 1 Server 'UVServer' is not configured for RPC. In the meanwhile I can work around the issue by SELECTING from an I type which runs the setup program... but that's not what I would like to do. Anyone have any experience with getting this type of thing going? Cheers, Stuart Boydell _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ************** IMPORTANT MESSAGE ***************************** This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. ************************************************************** _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
