Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
migrating spatial data to SQL Server 2008 R2

Answered migrating spatial data to SQL Server 2008 R2

  • Montag, 14. Mai 2012 17:55
     
     
    Anmelden
    0
    hi guys,

    I'm trying to migrate some tables from Oracle to Sql, but facing some problems to! even following the tutorial from Microsoft, some errors still persist, as described below:

    Example of Microsoft:

    - Suppose we have an Oracle table defined as:

    GEOINFO CREATE TABLE (id NUMBER (10) NOT NULL, geo MDSYS.SDO_GEOMETRY);

    - Its counterpart SQL Server will be:

    GEOINFO CREATE TABLE (id NUMERIC (10) NOT NULL, geo geography);

    - In this case, the INSERT statement will Following Correctly copy the spatial data.

    INSERT INTO GEOINFO (id, geo)
    SELECT id, geography :: STGeomFromText (CAST (the geo nvarchar (max)), srid)
    FROM OPENQUERY (ORACLE_LS,
    'SELECT id, SDO_UTIL.TO_WKTGEOMETRY (g.geo) geo, srid g.geo.sdo_srid
    FROM GEOINFO g ')

    even following this tutorial, I can not succeed, see the following error:


    OLE DB provider "MSDAORA" for linked server "ssp26" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
    OLE DB provider "MSDAORA" for linked server "ssp26" returned message "Data type is not supported.".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT id, SDO_UTIL.TO_WKTGEOMETRY (g.geo) geo, srid g.geo.sdo_srid
    FROM scott.geoinfo g "for execution against OLE DB provider" MSDAORA "for linked server" ssp26. "


    someone could help me?

    tks,

    Kleber Rafael


    Kleber Rafael

     

Alle Antworten

  • Montag, 14. Mai 2012 19:33
    Moderator
     
     
    Anmelden
    0

    Hello,

    SSMA 5.1 does not support migration of spatial data type from Oracle to SQL Server as stated on the following article:

    http://technet.microsoft.com/en-us/magazine/hh334645.aspx

    Based on the following post SSMA 5.2 still does not support migration of spatial data.

    http://blogs.msdn.com/b/ssma/archive/2012/01/31/microsoft-sql-server-migration-assistant-ssma-5-2-is-now-available.aspx

    Try migrating that data to sql_variant data type.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

     
  • Montag, 14. Mai 2012 20:03
     
     
    Anmelden
    0

    Hello Alberto,

    I'm trying to migrate using linked server, and that is what is showing error!

    tks,

    Kleber Rafael.

    Kleber Rafael

     
  • Dienstag, 15. Mai 2012 03:12
    Moderator
     
     
    Anmelden
    0

    Hello,

    On the following document you will read this: “Oracle Spatial types are not recognized by existing OLE DB, ADO.NET or ODBC providers”.

    http://download.microsoft.com/download%2f7%2fC%2f2%2f7C20B070-BFF8-44B4-BD7D-1B03DF50F924%2fMigrateOracletoSQLServer2008.docx

    On the same document you will also read:

    “The proposed solution is based on the fact that both Oracle Spatial and SQL Server 2008 support conversion to WKT format. Next, we are assuming that the source SDO_GEOMETRY column is mapped to SQL Server column of the geography type. Before transferring the data, we should create a SQL Server linked server pointing at the source Oracle instance. To perform the migration, we need to convert the source column value into WKT format, which makes it a plain text, and insert the result into the target geography column using OPENQUERY statement.

    Example:

    Suppose we have an Oracle table defined as:

    CREATE TABLE geoinfo (id NUMBER(10) NOT NULL, geo MDSYS.SDO_GEOMETRY);

    Its SQL Server counterpart will be:

    CREATE TABLE geoinfo (id NUMERIC(10) NOT NULL, geo geography);

    In this case, the following INSERT statement will correctly copy the spatial data.

    INSERT INTO geoinfo (id, geo)

    SELECT id, geography::STGeomFromText(CAST(geo as nvarchar(max)), srid)

    FROM OPENQUERY(ORACLE_LS,

    ’SELECT id, SDO_UTIL.TO_WKTGEOMETRY(g.geo) geo, g.geo.sdo_srid srid

                FROM geoinfo g’)

    Here ORACLE_LS is the name of linked server referencing the source Oracle instance. The Oracle function TO_WKTGEOMETRY returns a Well Known Text representation of the Spatial geometry object. The spatial reference ID (srid) is necessary to define the way the WKT string is interpreted by SQL Server.”

      
     
    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

     
  • Dienstag, 15. Mai 2012 13:42
     
     
    Anmelden
    0

    hello Alberto!

    tks for the help...

    look at the error that shows:

    OLE DB provider "MSDAORA" for linked server "ssp26" returned message "ORA-00942: table or view does not exist
    ".
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT id, SDO_UTIL.TO_WKTGEOMETRY(g.geo) geo, g.geo.sdo_srid srid
                FROM geoinfo g" for execution against OLE DB provider "MSDAORA" for linked server "ssp26". 

    Kleber Rafael

     
  • Dienstag, 15. Mai 2012 14:37
    Moderator
     
     Beantwortet
    Anmelden
    0

    Hello,

    You need to use “Microsoft OLEDB provider for Oracle” to create the ODBC DSN used for the linked server.

    http://www.sqlcoffee.com/Troubleshooting091.htm

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com