title: Calling Named Query in NHibernate and passing parameters to Sybase Server
body: <p>I am trying to call a named query using NHibernate and am receiving errors upon execution.
  This only happens when connecting to Sybase server and works fine on Microsoft SQL Server.</p>
  
  <p>We are using Sybase ASE 15 and NHibernate 3.3.1.4000.</p>
  
  <p>We do not have access to a Sybase Profiler. My guess is that NHibernate is not translating the query properly when sending to Sybase.</p>
  
  <p>Here is where the parameters are being passed:</p>
  
  <pre><code>NHibernate.IQuery query;
  
  query = NHibernateDataContext.GetNamedQuery("UPDATE_INVESTOR_TARGET");
  
  query.SetParameter("pTargetId", target.Id.TargetId &lt; 1 ? null :(int?) target.Id.TargetId, NHibernate.NHibernateUtil.Int32);
  query.SetParameter("pInvestorId", target.InvestorId, NHibernate.NHibernateUtil.String);
  query.SetParameter("pReleaseStatus", new TargetReleaseStatusEnumerationStringType().GetValue(target.ReleaseStatus), NHibernate.NHibernateUtil.String);
  query.SetParameter("pEventId", target.Id.EventId, NHibernate.NHibernateUtil.Int32);
  query.SetParameter("pAccountName", target.InvestorName.Value, NHibernate.NHibernateUtil.String);
  query.SetParameter("pLastChangedBy", lastChangedBy, NHibernate.NHibernateUtil.String);
  
  target.Id.TargetId = (int)query.UniqueResult();
  
  trans.CommitChanges();
  </code></pre>
  
  <p>Here is the NHibernate mapping:</p>
  
  <pre><code>&lt;sql-query name="UPDATE_INVESTOR_TARGET" cacheable="false" &gt;
      &lt;return-scalar column="Returned Value" type="int" /&gt;
      EXECUTE sUDI_COMPS
      @pSelectReturnYN = 'Y',
      @pAccountNo = :pInvestorId,
      @pReleaseStatus = :pReleaseStatus,
      @pAccountName = :pAccountName,
      @pLastChangedBy = :pLastChangedBy,
      @pDealNo = :pEventId,
      @pCompsNo = :pTargetId
  &lt;/sql-query&gt;
  </code></pre>
  
  <p>The error is received when executing query.UniqueResult().</p>
  
  <p>This is the error:</p>
  
  <blockquote>
    <p>could not execute query [ EXECUTE sUDI_COMPS      @pSelectReturnYN = 'Y',         @pAccountNo = @p0,      @pReleaseStatus = @p1,      @pAccountName =
    @p2,      @pLastChangedBy = @p3,      @pDealNo = @p4,         @pCompsNo = @p5 ]<br>
    Name:pTargetId - Value:1  Name:pInvestorId - Value:1 
    Name:pReleaseStatus - Value:R  Name:pEventId - Value:1 
    Name:pAccountName - Value:45cda18d-f4da-4342-909b-491918fb4032 
    Name:pLastChangedBy - Value:jmacri [SQL: EXECUTE sUDI_COMPS
          @pSelectReturnYN = 'Y',         @pAccountNo = @p0,      @pReleaseStatus =
    @p1,      @pAccountName = @p2,        @pLastChangedBy = @p3,      @pDealNo = @p4,
          @pCompsNo = @p5]</p>
  </blockquote>
  
  <p>And the Inner Exception:</p>
  
  <blockquote>
    <p>{"Must declare variable '@p0'.\n"}</p>
  </blockquote>
  
  <p>It is obvious that the variables are not being declared by NHibernate.</p>
  
  <p>Has anybody experienced this before that may know why this is happening?</p>
  
  <p>Is this an NHibernate issue or a configuration/setup issue?</p>
  
  <p>Any help is greatly appreciated.</p>
  
  <p>Thanks,
  Joe</p>
  
  <p>UPDATE:</p>
  
  <p>Here are the configuration settings. Note that the Microsoft SQL settings are commented out. The Microsoft SQL settings work when connecting to a Microsoft SQL database. The Sybase settings do not work when connecting to a Sybase SQL server.</p>
  
  <pre><code> &lt;hibernate-configuration xmlns="urn:nhibernate-configuration-2.2"&gt;
      &lt;session-factory name="ICM"&gt;
  
        &lt;!-- MS SQL 2008 Server --&gt;
        &lt;!--&lt;property name="connection.driver_class"&gt;
          Dealogic.DataAccess.Driver.DealogicSqlClientDriver,Dealogic.DataAccess
        &lt;/property&gt;
        &lt;property name="dialect"&gt;
          NHibernate.Dialect.MsSql2008Dialect
        &lt;/property&gt;
        &lt;property name="connection.connection_string_name"&gt;default&lt;/property&gt;--&gt;
  
        &lt;!-- Sybase ASE 15 Server --&gt;
        &lt;property name="connection.driver_class"&gt;
            Dealogic.DataAccess.Driver.DealogicSybaseAseClientDriver,Dealogic.DataAccess
        &lt;/property&gt;
        &lt;property name="dialect"&gt;
            Dealogic.DataAccess.Dialect.ExtendedSybaseAse15Dialect,Dealogic.DataAccess
        &lt;/property&gt;
        &lt;property name="connection.connection_string_name"&gt;default&lt;/property&gt;
  
        &lt;property name="adonet.batch_size"&gt;10&lt;/property&gt;
        &lt;property name="show_sql"&gt;false&lt;/property&gt;
        &lt;property name="format_sql"&gt;true&lt;/property&gt;
        &lt;property name="generate_statistics"&gt;false&lt;/property&gt;
        &lt;property name="command_timeout"&gt;60&lt;/property&gt;
        &lt;property name="query.substitutions"&gt;true 1, false 0, yes 'Y', no 'N'&lt;/property&gt;
      &lt;/session-factory&gt;
    &lt;/hibernate-configuration&gt;
  </code></pre>