8 Replies Latest reply: Jan 25, 2013 9:38 AM by 666153 RSS

    Error converting SQL Server DDL and views to Oracle

    666153
      I am trying to convert SQL Serve 2008 on windows to Oracle 11g using SQL Developer 3.2.20.09. I am using the jtds-1.2.7 drivers to connect to SQL Server.

      I have made several attempts to run the conversion and am getting the tables, indexes, and triggers converted with no issue. However, my Procedures and views are not coming across. All of them are giving similar errors as:
      "Failed To Convert Stored Procedure get_compvision > esp3.dbo.get_compvision:unexpected end of subtree: Line 0 Column 0"

      When I log into the source SQL Server environment using SQL Developer, I get an empty SQL Editor window with "null" when I try to {right-click -> "Open"} on the Procedure.

      Logging into SQL Server Administrator as the same user I can see the source code, so the issue appears to be related to SQL Developer and not my user privleges. I am a dbo alias in SQL Server.

      In addition, if I click on a view, I can see the data and metadata, but not the SQL that defines the view. I see tabs for column, data, triggers, and details.

      In addition, if I go to the "capture" for my Migration project and drill down to procedures, I can see them listed, but each and every procedure gives me a "/* DDL not accesible */" error. I get teh same results for views, triggers and indexes.

      Can someone help me? I have been googling for days!

      Edited by: user4714217 on Jan 24, 2013 12:54 PM
        • 1. Re: Error converting SQL Server DDL and views to Oracle
          Gary Graham-Oracle
          Hi,

          Have you seen our supplementary documentation on how to increase you odds of success for SQL Server migrations?
          http://docs.oracle.com/cd/E35137_01/doc.32/e18462/trig_stored_proc.htm#BABGCJBJ

          Searching this forum, I am able to find only one reply to a "Failed to convert" question:
          Re: Unknown Object:unexpected AST node: EXEC+PARAM_LIST:Line -1 Column -1

          and a couple of others regarding the "unexpected end of subtree":
          Re: ExtStringTemplate Warning while Converting a SQLServer DB to Oracle 10g
          SQL Server migration object renamin

          I will ping Dermot on this. In case your situation is something new and unexpected, it might help if you could post some samples of the procedure / view DDL that fail.

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: Error converting SQL Server DDL and views to Oracle
            666153
            SELECT v.vnd_c, CASE v.use_parent_addr_flg WHEN 0 THEN a.str_1 ELSE pa.str_1 END AS str_1,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.str_2 ELSE pa.str_2 END AS str_2,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.str_3 ELSE pa.str_3 END AS str_3,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.city ELSE pa.city END AS city,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.state ELSE pa.state END AS state,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.zipcode ELSE pa.zipcode END AS zipcode,
            CAST((CASE v.use_parent_addr_flg WHEN 0 THEN (CASE a.foreign_flg WHEN 1 THEN 'Y' ELSE 'N' END)
            ELSE (CASE pa.foreign_flg WHEN 1 THEN 'Y' ELSE 'N' END) END) AS char(1)) AS foreign_flg,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.country ELSE pa.country END AS country,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.cre_d ELSE pa.cre_d END AS cre_d,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.mod_d ELSE pa.mod_d END AS mod_d,
            CASE v.use_parent_addr_flg WHEN 0 THEN a.mod_by ELSE pa.mod_by END AS mod_by
            FROM dbo.om_vendor AS v LEFT OUTER JOIN
            dbo.om_address AS a ON a.vnd_id = v.vnd_id AND a.addr_c = 1 INNER JOIN
            dbo.om_owner AS o ON v.owr_id = o.owr_id INNER JOIN
            dbo.om_address AS pa INNER JOIN
            dbo.primary_vendors AS pv ON pa.vnd_id = pv.vnd_id ON o.part_id = pv.part_id
            WHERE (pa.addr_c = 1)




            WHICH TRANSLATES TO



            SELECT v.vnd_c ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.str_1
            ELSE pa.str_1
            END str_1 ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.str_2
            ELSE pa.str_2
            END str_2 ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.str_3
            ELSE pa.str_3
            END str_3 ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.city
            ELSE pa.city
            END city ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.STATE
            ELSE pa.STATE
            END STATE ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.zipcode
            ELSE pa.zipcode
            END zipcode ,
            (CASE v.use_parent_addr_flg
            WHEN 0 THEN (CASE a.foreign_flg
            WHEN 1 THEN 'Y'
            ELSE 'N'
            END)
            ELSE (CASE pa.foreign_flg
            WHEN 1 THEN 'Y'
            ELSE 'N'
            END)
            END) foreign_flg ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.country
            ELSE pa.country
            END country ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.cre_d
            ELSE pa.cre_d
            END cre_d ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.mod_d
            ELSE pa.mod_d
            END mod_d ,
            CASE v.use_parent_addr_flg
            WHEN 0 THEN a.mod_by
            ELSE pa.mod_by
            END mod_by

            FROM om_vendor v
            LEFT JOIN om_address a
            ON a.vnd_id = v.vnd_id
            AND a.addr_c = 1
            JOIN om_owner o
            ON v.owr_id = o.owr_id
            JOIN om_address pa
            JOIN primary_vendors pv
            ON pa.vnd_id = pv.vnd_id
            ON o.part_id = pv.part_id
            WHERE ( pa.addr_c = 1 );

            Edited by: user4714217 on Jan 24, 2013 1:43 PM
            • 3. Re: Error converting SQL Server DDL and views to Oracle
              666153
              The above code was cut from the admin tool on SQL Server, copied to the "scratch editor" and translated with no errors.

              I think the issue is that I cannot get the code using SQL Developer, not the translation itself. I copied a procedure over as well, and it translated succesfully also.

              I found the links you supplied but they did not answer my question.

              Edited by: user4714217 on Jan 24, 2013 1:41 PM
              • 4. Re: Error converting SQL Server DDL and views to Oracle
                Gary Graham-Oracle
                Hi,

                Well, perhaps the underlying issue has something to do with privileges or (at least) the way SQL Developer either uses or does not use them. Searching for "DDL not accessible" I found the following, but it seems some private emailing occurred so we don't know how that poster's issue got resolved:
                Re: SQL Server 2005 Migration Fails part of the way through

                Let's hope Dermot can get back to you this week.

                -Gary-
                • 5. Re: Error converting SQL Server DDL and views to Oracle
                  Dermot Oneill-Oracle
                  Hi,

                  Just to test the connections permissions.

                  1) Open your SQL Server connection. Right Click on the database you want to migrate and choose "Select Default Database"
                  2) Open a worksheet for your SQL Server connection.
                  3) Run the following query (run statement, for the grid result)
                  select o.name, c.encrypted,c.compressed,c.text from syscomments c,sysobjects o where c.id =o.id

                  If your connection has enough privileges and the text is not compressed or encrypted, then you should see the procedure/view definitions in plain text in the result window.
                  This should give us a clue as to the problem.

                  Just two things that may help
                  A) Use JTDS version 1.2 . This is an older version. But it is the version we use here and what we recommend.
                  B) SQL Developer supports a second way of capturing your database. Offline Capture. If there is some problem with online (standard) capture, then this mechanism may help you capture your database.

                  Regards,
                  Dermot
                  SQL Developer Team
                  • 6. Re: Error converting SQL Server DDL and views to Oracle
                    666153
                    OK, I did as you proposed and I get no rows back. I logged into the admin screen on the host server and executed the same query and I did get rows back, and in clear text. It appears to not be my privilege but some interface with my ID and SQL Developer?

                    I will prusue this, but do you have any suggestions?
                    • 7. Re: Error converting SQL Server DDL and views to Oracle
                      666153
                      There is one difference. When I login using the SQL Server GUI, I am using domain based login. When I login using SQL Developer, I have to use password, so it appears they are not identical. I will investigate this further.

                      Edited by: user4714217 on Jan 25, 2013 9:08 AM
                      • 8. Re: Error converting SQL Server DDL and views to Oracle
                        666153
                        O...M...G

                        It appears that my SQL Server DBA did nto set me up correctly or consistently. He created a new local login and it had no privilege. When I logged in directly, I was using a domain based login, and was a sysadmin. I have just verified this. I am going to run this again and see if it works. I anticipate it will.

                        I had assumed my DBA knew what he was doing when he set me up! I'll let you know if I am OK.