Re: bug in ADOX.catalog with SQL Server 7.0 ?

From: Val Mazur (group51a_at_hotmail.com)
Date: 03/06/04 

Date: Fri, 5 Mar 2004 23:08:33 -0500

Hi Steve,

Actually ADOX was designed for using with Access and does not support
everything if you work with SQL Server. There is a lot of functionality,
which is not supported in ADOX when you work with SQL Server. Best way is to
use SQL DMO library, which is similar to ADOX, but designed specifically for
SQL Server 

-- 
Val Mazur
Microsoft MVP
"steve" <stevetallard@yahoo.fr> wrote in message 
news:edffa387.0403050746.3b2919dc@posting.google.com...
> I' m facing a strange problem using ADOX.Catalog on SQL Server 8.0.194
> When querying a view using ADOX objects, I loose the original types of
> some fields.
>
> Here is a code snippet ( in python  ):
>
> def display_type_catalog(v_sTableName, v_sCatalog ):
>    print "===============> With ADOX.Catalog
> <========================="
>    conn = open_connection( v_sCatalog )
>    cat = win32com.client.Dispatch( r"ADOX.Catalog")
>    cat.ActiveConnection = conn
>    tabs = cat.Tables
>    t = tabs[v_sTableName]
>    if t.Name == v_sTableName:
>        for colum in t.Columns:
>            print colum.Name , " ===> " ,
> TypeInfo.alltypesinfo[colum.Type].m_adotype
>    print
>    print
>    return
>
> def display_type_recordset(v_sTableName, v_sCatalog ):
>    print "===============> With ADODB.Recordset
> <========================="
>    conn = open_connection( v_sCatalog )
>    rs = win32com.client.Dispatch( r"ADODB.Recordset")
>    l_sQuery = "Select * from " + v_sTableName
>    rs.Open(str(l_sQuery) , conn, 1,3)
>    for colum in rs.Fields:
>            print colum.Name , " ===> " ,
> TypeInfo.alltypesinfo[colum.Type].m_adotype
>    print
>    print
>    return
>
>
> def open_connection(v_sCatalog  ):
>    conn = win32com.client.Dispatch(r'ADODB.Connection')
>    DSN = "Provider=SQLOLEDB;Data Source=LEDIAG;Initial
> Catalog=%s;User ID=sa;Pwd=nissan" % ( v_sCatalog )
>    try:
>        conn.Open( DSN )
>        return conn
>    except :
>        raise SQLServerError("Impossible de se connecter à la base
> avec le DSN:" + DSN )
>
>
> def main( v_sTableName , v_sCatalog ):
>    display_type_catalog(v_sTableName, v_sCatalog )
>    display_type_recordset( v_sTableName, v_sCatalog )
>
> try:
>   tablename   =  sys.argv[1]
>   catalog = sys.argv[2]
> except:
>   print sys.argv[0] , " <TABLE/VIEW> "  , "<CATALOG>"
>
> main( tablename , catalog )
>
>
> Running that code on a local view,I obtain :
>
> ===============> With ADOX.Catalog <=========================
> SCE_CODE  ===>  adInteger
> SCE_NAME  ===>  adVarWChar
> SCE_IS_DOMAIN_SCE  ===>  adBoolean
> SCE_TYPE  ===>  adUnsignedTinyInt
> SCE_DEFAULT_TOOL_TARGET  ===>  adInteger
> CALC_PRINCIPAL  ===>  adInteger
> CALC_SECONDAIRE  ===>  adInteger
> UTI_NOM  ===>  adVarChar
> VER_NUMBER  ===>  adDouble
> VER_FILING_DATE  ===>  adDBTimeStamp
> VER_COMMENTARY  ===>  adLongVarChar
> VER_EN_COURS  ===>  adBoolean
>
>
> ===============> With ADODB.Recordset <=========================
> SCE_CODE  ===>  adInteger
> SCE_NAME  ===>  adVarWChar
> SCE_IS_DOMAIN_SCE  ===>  adBoolean
> SCE_TYPE  ===>  adUnsignedTinyInt
> SCE_DEFAULT_TOOL_TARGET  ===>  adInteger
> CALC_PRINCIPAL  ===>  adInteger
> CALC_SECONDAIRE  ===>  adInteger
> UTI_NOM  ===>  adVarChar
> VER_NUMBER  ===>  adDouble
> VER_FILING_DATE  ===>  adDBTimeStamp
> VER_COMMENTARY  ===>  adVarWChar
> VER_EN_COURS  ===>  adBoolean
>
>
>
> where type of field VER_COMMENTARY is different between ADO  and ADOX.
>
> field VER_COMMENTARY  is defined as nvarchar( 255) both in original
> table and final view ...
>
>
> Anyone has faced this problem before ?  Anyone has a workaround ?
>
>
> Thanks for your help
>
> Stephane
>
> Please CC to : stevetallard at yahoo.fr

Relevant Pages

  • Re: Getting FK->PK relationships
    ... As far as I know, unlike SQL Server, Access does not support the ... ADOX is the object model of Microsoft Office Acces. ... Microsoft Online Community Support ... Delighting our customers is our #1 priority. ...
    (microsoft.public.data.odbc)
  • RE: Getting FK->PK relationships
    ... As far as I know, unlike SQL Server, Access does not support the ... INFORMATION_SCHEMA views that return both sides of all FOREIGN KEY ... ADOX is the object model of Microsoft Office Acces. ... Microsoft Online Community Support ...
    (microsoft.public.data.odbc)
  • Re: bug in ADOX.catalog with SQL Server 7.0 ?
    ... > Actually ADOX was designed for using with Access and does not support ... > which is not supported in ADOX when you work with SQL Server. ... - Launch the query against SQL Server using ADODB.RecordSet object ...
    (microsoft.public.data.ado)
  • Re: default constraint
    ... But the end result is the same - the combination of ADOX and the ... > SQL Server developers were not, in my opinion, the main target of ADOX. ... SQL DMO would be great - but it doesn't work with Jet at all. ... > GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.vb.general.discussion)
  • Re: default constraint
    ... But the end result is the same - the combination of ADOX and the ... > SQL Server developers were not, in my opinion, the main target of ADOX. ... SQL DMO would be great - but it doesn't work with Jet at all. ... > GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.sqlserver.programming)
Loading