'[DB-SIG] mxODBC.ProgrammingError: ('37000', 8501,'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       python-db-sig
Subject:    [DB-SIG] mxODBC.ProgrammingError: ('37000', 8501,
From:       joe () goldthwaites ! com (Joe Goldthwaite)
Date:       2004-11-04 23:14:55
Message-ID: 002d01c4c2bb$b1c42070$7264a8c0 () jgoldamd
[Download RAW message or body]

Hi Mark,

It's nice to know how to create a connection without creating a DSN first.
That will come in handy I'm sure. I made the change both with and without
the SA password but it didn't fix the problem.  I've been working with my
SQL expert.  He also thinks it has somthing to do with permissions. He's had
me editing the registry and changing how the SQL service runs but so far,
nothing has worked.

I'm going to give up on it for now.  I can use the OleDB connection on the
section that has the problem and address it later if I switch to Linux.
They want some additional enhancements finished next week and I'm out of
time.  (One guy's bonus depends on me finishing them - or so he says).

Thanks again!

Joe Goldthwaite



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


Joe Goldthwaite wrote:
> 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.

There may be a subtle difference between the ADO connection
and the one you are using with mxODBC: in the ADO connection
you are creating a per connection data source on the fly whereas
with mxODBC you use the predefined data source from the
ODBC manager.

There may be permission problems or different setups
involved due to this (the ODBC manager connection being
setup statically in the ODBC manager rather than created
on the fly).

If the ADO connection works, you should be able to create
a similar connection with mxODBC using the DriverConnect API:

ODBCConnection = ODBC.Windows.DriverConnect(
    "driver={SQL Server};server=JGOLDAMD;database=EISLocal")
# you may need to add: ";PWD=password;UID=userid" to the connection
# string

>    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.

It's always interesting to learn new things. Esp. with
ODBC you can never learn enough (which helps in building
good products ;-).

> Joe Goldthwaite
>
> -----Original Message-----
> From: M.-A. Lemburg [mailto:mal@egenix.com]
> Sent: Thursday, November 04, 2004 1:28 AM
> To: joe@goldthwaites.com
> Cc: db-sig@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 ! ::::

--
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 ! ::::

[prev in list] [next in list] [prev in thread] [next in thread]
Configure | About | News | Add a list | Sponsored by KoreLogic