Re: [FreeTDS][SQL Server]Procedure has no parameters and arguments were supplied., {42000, NativeErr = 8146}

On Mon, Dec 12, 2011 at 04:05:12PM +0100, Hans Christiansen wrote:
> A maintenence task executed nightly has worked well with freeTDS for 
> many months/years now (on Oracle 10/hsodbc/freetds). Upon upgrade to 
> Oracle 11g and dg4odbc I receive the following message after ~ 1 minutes 
> execution.
...
> SQL> exec weekly_sales.refresh_job_info_cache_week(sysdate-7);
> /error -28500
> text: ORA-28500: connection from ORACLE to a non-Oracle system returned this
> message:/
> [FreeTDS][SQL Server]Procedure  has no parameters and arguments were
> supplied. {42000,NativeErr = 8146}
...
> 15:11:41.158760 16157 (query.c:1573):tds_submit_execute()
> 15:11:41.158766 16157 (util.c:119):Changing query state from IDLE to 
> QUERYING
> 15:11:41.158772 16157 (mem.c:519):tds_free_all_results()
> 15:11:41.158779 16157 (query.c:1267):tds_put_data_info putting status
> 15:11:41.158786 16157 (query.c:1362):tds_put_data: colsize = 0
> 15:11:41.158792 16157 (query.c:1391):tds_put_data: not null param 
> varint_size = 1
> 15:11:41.158798 16157 (query.c:1450):swapping numeric data...
> 15:11:41.158811 16157 (util.c:119):Changing query state from QUERYING to 
> PENDING
> 15:11:41.158818 16157 (net.c:673):Sending packet
> 0000 03 01 00 34 00 00 01 00-0a 00 73 00 70 00 5f 00 |...4.... ..s.p._.|
> 0010 65 00 78 00 65 00 63 00-75 00 74 00 65 00 00 00 |e.x.e.c. u.t.e...|
> 0020 00 00 26 04 04 01 00 00-00 00 00 6a 04 06 00 04 |..&..... ...j....|
> 0030 01 9a 31 02            -                        |..1.|

http://msdn.microsoft.com/en-us/library/ff848746.aspx

You may have snipped off too much of what came earlier in the log.   
The sp_execute procedure takes a handle returned by sp_prepare.  To understand
the interaction, we'd need to see everything between sp_prepare and sp_execute. 
 
Even then, it won't be easy.  The log may will show what's happening, not why. 
It may expose each ODBC call, and show what Oracle is passing to FreeTDS.  If we
determine something is missing or done incorrectly, thus explaining the error
returned by SQL Server, we'll still be left with the question of why Oracle is
doing it that way.  

>From the message, "Procedure  has no parameters and arguments were supplied", it
would appear the procedure is prepared and executed, but no name was provided
and no parameters were bound.  

HTH.  :-/

--jkl