[DB-SIG] mxODBC.ProgrammingError: ('37000', 8501, "[Microsoft][ODBC SQL Server Driver][SQL Server]MSDTC on server 'xxxxx' is unavailable.", 4612)

Joe Goldthwaite joe at goldthwaites.com
Thu Nov 4 21:53:37 CET 2004


Hi Mark,

I appreciate the reply.  I went through the links you sent but was unable to
find anything that would help.  I don't think the problem is in the DTC
configuration on the server because everything on the server end works.  I
can create views with linked tables and run the view from the SQL Query
Analyzer and get the results.  When I try to run the same view from mxODBC,
I get the above error.  If I use the win32 extensions to create an OleDB
connection, I can run the query through that and it works fine.  The only
difference is the mxODBC connection.

Here's a sample that illustrates;

   #Build my SQL statement with the linked table reference.  I'm using
OPENDATASOURCE instead of the linked table syntax
   #although both have the same problem

   sql = """
   SELECT DISTINCT LTRIM(RTRIM(DRKY))
   FROM
      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=JDEENT;User ID=sa;Password=frayed'
         ).JDE_PRODUCTION.PRODCTL.F0005
   WHERE RTRIM(LTRIM(DRSY)) = '00'  AND RTRIM(LTRIM(DRRT)) = '07' AND
RTRIM(LTRIM(DRKY)) <> ''
   """

   #use WIN32 to create an ODBC object
   from win32com.client import DispatchEx
   OleDBConnection = DispatchEx("ADODB.Connection")
   OleDBConnection.Open('driver={SQL
Server};server=JGOLDAMD;database=EISLocal')
   OleDBRecordset = DispatchEx("ADODB.Recordset")

   #Open the recordset and print the first row
   OleDBRecordset.Open(sql, OleDBConnection, 0)
   if not OleDBRecordset.EOF:
      for x in range(0, OleDBRecordset.Fields.Count):
	   print OleDBRecordset.Fields(x).Value


   #Now we're going to use mxODBC to execute the same SQL statement
   from mx import ODBC
   ODBCConnection = ODBC.Windows.connect("EISLocal")      #Create the
connection
   csr = ODBCConnection.cursor()                          #Create the cursor
object
   csr.execute(sql)                                       #Excute the above
SQL statement - this blows up.
   #It never gets here.

   allRecords = csr.fetchall()
   print allRecords

It seems like if the problem was in the server configuration, it would not
work with the SQL Analyzer or with the OLE DB object.  The only difference
is the connection.  I'm not saying that the problem is in the ODBC stuff.
In fact, it doesn't make any sense to me that linked tables would depend in
any way on the type of connection used.  It seems like the remote tables
would be running at a much lower level.


I can work around the problems by using the ODBC connection when I'm
rebuilding my reporting database.  I hate to do that because it ties me to
using Windows as my host.  I was planning to eventually convert the server
to Linux.

Let me know if you have any other ideas.  Again, I thank you for taking the
time to reply.  I really do appreciate it.

Joe Goldthwaite

-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com]
Sent: Thursday, November 04, 2004 1:28 AM
To: joe at goldthwaites.com
Cc: db-sig at python.org
Subject: Re: [DB-SIG] mxODBC.ProgrammingError: ('37000', 8501,
"[Microsoft][ODBC SQL Server Driver][SQL Server]MSDTC on server 'xxxxx'
is unavailable.", 4612)


Copying the error message here:

[Microsoft][ODBC SQL Server Driver][SQL Server]MSDTC on	server 'xxxxx' is
unavailable.", 4612)

Joe Goldthwaite wrote:
> I suspect I'm not going to get a happy answer on this one.  I ran into it
> during the final testing of some major structural changes in my
application.
> I'm using SQL server as my database.  I had originally built some data
> tables for reporting and was storing them in the main database used by our
> JDE application.  Due to some additional requirements for other reporting
> databases, I pulled the tables out and put them in their own database on a
> separate server.  I then used Microsoft SQL servers ability to link tables
> to access the raw data from the JDE database and pull the data into the
> reporting database.
>
> To do this, I specify the fully qualified table name in my stored
procedure.
> For example, to reference the F0901 table in the JDE_PRODUCTION database
on
> our JDEENT server, I have to reference it as
> JDEENT.JDE_PRODUCTION.PRODDTA.F0901.  JDEENT is the server name,
> JDE_PRODUCTION is the database name and PRODDTA is the table owner.

Note that there's a limit on the length of column names in
SQL: mxODBC uses a 37 character limit which is what ANSI SQL
defines. I don't that's a problem here, though, since you'd get
a warning from the database in case of truncations.

> If I execute the stored procedure or one of the individual queries from
> within Microsoft's SQL Query Analyzer, it works fine.  When I try to
execute
> the exact same SQL statement from the cursor.execute function, I get the
> above MSDTC error.  MSDTC stands for Microsoft Distributed Transaction
> Coordinator.
>
> I suspect that the MS Query Analyzer is doing something different that
> allows it to reference linked tables that the mxODBC code does not.  I'm
> hoping that someone knows about some setting or something that enables the
> use of linked tables in mxODBC.  I'm not optimistic though.  It's a bummer
> that I ran into this at the END of all my changes...

This is likely not an mxODBC problem, but related to the
configuration of DTS and your database cluster. Here are some
pointers:

  * "MSDTC Is Unavailable" Error When a Stand-Alone Instance of SQL Server
Is in a Cluster Environment
	http://support.microsoft.com/kb/822473

  * Transacted Commerce Server Pipeline Failure
	http://support.microsoft.com/kb/248403/
	(see resolution steps 1 - 9)

  * Troubleshooting Guides for SQL Server
	http://www.microsoft.com/resources/documentation/sql/7/all/reskit/en-us/par
t7/sqc09.mspx
	http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/
part4/c1261.mspx

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 04 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::



More information about the DB-SIG mailing list