Skip to Content.
Sympa Menu

freetds - Re: [freetds] ANSI settings and stored procedures

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] ANSI settings and stored procedures
  • Date: Thu, 22 Mar 2007 19:20:13 -0400

Sherrill, Justin wrote:
>
> I've been trying to insert to a table on a SQL Server 2000 system that
> would kick off a stored procedure; I've been getting back an error each
> time I try:
>
> > Msg 7405, Level 16, State 1
> > Server 'CCDEVSQL01', Procedure 'proc_insert_update_meter_signa', Line
> 59
> > Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be
> > set for the connection. This ensures consistent query semantics.
> Enable these options > and then reissue your query.
> > bsqldb: Msg 20018, Level 5
> > General SQL Server error: Check messages from the SQL Server.
>
> Doing some searching, it appears to be a settings issue from recent
> versions of FreeTDS:
>
> http://www.arcknowledge.com/gmane.comp.db.tds.freetds/2004-09/msg00037.h
> tml

Well, if 2 1/2 years and 3 versions ago is "recent", can I buy Manhattan
for $24? ;-)

> 1> set ansi_nulls on
> 2> go
> 1> set ansi_warnings on
> 2> go

That's the right approach. Changing the login packet would be a hack.

> 1> INSERT INTO ne_import_log (type, created_by) VALUES (3, 'test')
> 2> go
> Msg 7312, Level 0, State 1, Server CCDEVSQL01, Line 56
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> Msg 7300, Level 0, State 1, Server CCDEVSQL01, Line 56
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Msg 7391, Level 16, State 1, Server CCDEVSQL01, Line 56
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.

I don't know why you're getting these messages. Does the same query
succeed using Microsoft's client tools? Because I'm inclined to think it
has nothing to do with the connection properties.

The only distinction SQL Server makes regarding connection properties
(which are one way to affect ANSI defaults) is whether the login packet
has the "I'm an ODBC client" bit turned on. FreeTDS leaves it turned on
unless you're making a db-lib connection. That means that any other
connection tool -- tsql, ODBC, ct-lib -- claims to be an ODBC client and
gets ANSI defaults (by default).

Microsoft's own tools then adjust the properties after connecting. Look
at the differences in the properties shown by osql and Query Analyzer,
both ODBC clients. But I doubt those properties are preventing your
remote procedure call from working.

HTH.

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page