SQL Server 2005 Parsing Problem

General discussions about code engineering

Moderator: Moderators

SQL Server 2005 Parsing Problem

Postby jgemmill » Thu Oct 14, 2010 11:53 am

I'm new to MD and enjoy it very much, thanks!

I'm trying to reverse a SQL Server 2005 DB that makes heavy use of stored procedures. It seems that MD cannot parse stored procedures for SQL Server ("Parsing failed (<My Documents>\StoredProcs.sql) Encountered "CREATE PROCEDURE" at line 28, column 1."), although I do see a <<procedure>> stereotype for Oracle. Are there plans to add this for SQL Server? If not, I'm not sure how to add the functionality myself, but there appear to be facilities to do this in MD. Can anyone point me in the right direction?

Although the tables in the DB come through fine (from a direct database connection), there are numerous other parsing errors when attempting to import the stored procedures from a DDL file (they are ignored when reversing directly from the DB), such as:

From the line:
SET ANSI_NULLS ON
Parsing failed (<My Documents>\StoredProcs.sql) Encountered "SET ANSI_NULLS" at line 4, column 1.

From the line:
SET QUOTED_IDENTIFIER ON
Parsing failed (<My Documents>\StoredProcs.sql) Encountered "SET QUOTED_IDENTIFIER" at line 6, column 1.

From the line (part of CREATE TABLE command):
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
Parsing failed (<My Documents>\StoredProcs.sql) Encountered "=" at line 20, column 50.
(indicating the STATISTICS_NORECOMPUTE token is not recognized; all the rest on that line also do not parse, but it works as ")WITH (PAD_INDEX = OFF) ON [PRIMARY]")

I can comment out the offending elements (a lot of them...) but it would be really nice to have a complete parsing with no errors.

Thanks for any help you can provide!

-=> J <=-
jgemmill
Forum Newbie
Forum Newbie
 
Posts: 3
Posts Rating:0
Joined: Thu Oct 14, 2010 11:05 am

Re: SQL Server 2005 Parsing Problem

Postby TomasJkn » Mon Oct 18, 2010 2:54 am

Hello J,

I'm new to MD and enjoy it very much, thanks!

I'm trying to reverse a SQL Server 2005 DB that makes heavy use of stored procedures. It seems that MD cannot parse stored procedures for SQL Server ("Parsing failed (<My Documents>\StoredProcs.sql) Encountered "CREATE PROCEDURE" at line 28, column 1."), although I do see a <<procedure>> stereotype for Oracle. Are there plans to add this for SQL Server? If not, I'm not sure how to add the functionality myself, but there appear to be facilities to do this in MD. Can anyone point me in the right direction?


Unfortunately stored procedures are not (yet) supported in non-Oracle flavors. Also unfortunately,
there are no OpenAPI calls, allowing to extend/modify the code engineering set behavior (there are only calls
for building CE sets, triggering reverse or generation, but no calls for changing how reverse is performed).

Currently database engineering in MD is undergoing large restructuring and rework process (it was neglected a bit).
Instead of the two separate profiles - GenericDDL (for standard SQL and all non-Oracle flavors) and OracleDDL,
we will have one large profile for DB, supporting all the concepts in the SQL standard, plus small profiles for each
DB flavor (just Oracle at first, but other flavors should follow soon).

The first reworked release (planned for ~May2011) will have only DB modeling reworked, script generation and reverse
will remain at the current level (shortage of dev resources, as usual :|). But the generation and reverse should follow
promptly. The priority list now is : standard SQL first, then 3 "biggies" - Oracle (mostly covered already), MS SQL Server, and IBM DB2,
then 2 open source leaders - PostgreSQL and MySQL, then everything else.

Although the tables in the DB come through fine (from a direct database connection), there are numerous other parsing errors when attempting to import the stored procedures from a DDL file (they are ignored when reversing directly from the DB), such as:

From the line:
SET ANSI_NULLS ON
Parsing failed (<My Documents>\StoredProcs.sql) Encountered "SET ANSI_NULLS" at line 4, column 1.

From the line:
SET QUOTED_IDENTIFIER ON
Parsing failed (<My Documents>\StoredProcs.sql) Encountered "SET QUOTED_IDENTIFIER" at line 6, column 1.

From the line (part of CREATE TABLE command):
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
Parsing failed (<My Documents>\StoredProcs.sql) Encountered "=" at line 20, column 50.
(indicating the STATISTICS_NORECOMPUTE token is not recognized; all the rest on that line also do not parse, but it works as ")WITH (PAD_INDEX = OFF) ON [PRIMARY]")

I can comment out the offending elements (a lot of them...) but it would be really nice to have a complete parsing with no errors.


If the parser chokes on some script constructs, this needs to be fixed. We have a policy that script reversing code
should be able to cleanly skip the constructs it does not understand,

I will post bugs to our buglist about these situations you've mentioned and we will try to promptly fix them
(hopefully in the upcoming Noverber2010 release). Can you post fragments from your script, illustrating the problems? -
I will attach them to bug descriptions.

Thank you.
Tomas Juknevicius
Senior Systems Analyst
No Magic Europe
TomasJkn
Customer Support
Customer Support
 
Posts: 63
Posts Rating:6
Joined: Mon May 10, 2010 9:24 am

Re: SQL Server 2005 Parsing Problem

Postby jgemmill » Mon Oct 18, 2010 10:54 am

I posted snippets (above) from the script with the exact lines that caused the errors, but below is a more complete chunk of the script from the same section detailed above (the very beginning of the script). This script comes from SQL Server 2005 using the SQL Scripts wizard, exporting the entire DB with stored procedures. Note that if I connect to the DB directly there are no parsing errors -- the errors only occur when I use Reverse from files.

Code: Select all
USE [PED_Stage]
GO
/****** Object:  Table [dbo].[Bibli_Editor]    Script Date: 10/14/2010 13:27:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bibli_Editor](
   [Bibli_Editor_ID] [numeric](30, 0) IDENTITY(1,1) NOT NULL,
   [Bibli_ID] [numeric](30, 0) NOT NULL,
   [Editor_LastName] [varchar](150) NULL,
   [Editor_Initial] [varchar](50) NULL,
   [SortOrder] [tinyint] NULL,
   [Date_Modified] [smalldatetime] NULL,
CONSTRAINT [PK__Bibli_Editor__5B0E7E4A] PRIMARY KEY CLUSTERED
(
   [Bibli_Editor_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[sp_dAuthor_By_BibliID]    Script Date: 10/14/2010 13:25:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_dAuthor_By_BibliID]
   @Bibli_ID   int
AS

   Begin Transaction

   Delete Bibli_Author
   Where Bibli_ID = @Bibli_ID

   IF @@Error = 0
   BEGIN
        Commit Transaction
        Return 0
   END
   Else
   BEGIN
        Rollback Transaction
        Return -1
   End
GO


Thanks!

-=> J <=-
jgemmill
Forum Newbie
Forum Newbie
 
Posts: 3
Posts Rating:0
Joined: Thu Oct 14, 2010 11:05 am

Re: SQL Server 2005 Parsing Problem

Postby TomasJkn » Wed Oct 20, 2010 9:13 am

Hello J,

thanks for this additional info. I will update bug descriptions with this additional information.
Tomas Juknevicius
Senior Systems Analyst
No Magic Europe
TomasJkn
Customer Support
Customer Support
 
Posts: 63
Posts Rating:6
Joined: Mon May 10, 2010 9:24 am


Return to Code engineering

Who is online

Users browsing this forum: No registered users and 0 guests