Problem Reverse Engineering MS SQL Server

Discussion:

(too old to reply)

Jerry M

2007-04-06 16:48:45 UTC

When I try to Reverse Engineer any database on my MS SQL Server 2005 I get the following error. This occurs even on databases created by PowerDesigner.

Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s) could not be prepared.
SQLSTATE = 37000

This occurs when Reverse engineering starts up and when I select any database and select any of the following tabs:

Table, View (but it returns views), System Table, Trigger

It sounds like some things in my DBMS Resource for Microsoft SQL Server 2005 (sqlsv2k5.xdb) are causing an ill-formed statement to be passed to the engine. I tried pointing to the original, unmodified version of the .xdb that came with the installation and I still get this message. Any ideas how I might narrow down the problem and resolve it?

Jerry M

2007-04-06 17:14:50 UTC

Permalink

Here is more information on my problem. I turned on a trace in SQL Server and was able to trap the statement that failed the "prepare". Here it is:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'select
u.name,
o.name,
case (o.type) when ''S'' then ''SYSTEM TABLE'' else ''TABLE'' end,
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null, null) where name = ''MS_Description'')
from
sys.sysobjects o
join sys.schemas u on (u.schema_id = o.uid)
where
o.type in (''U'', ''S'')
and u.name = ''dbo''
order by 1, 2'
select @p1
The error message is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'u'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.
I am not that good at SQL to understand the syntax issue plus the fix needs to be in how this statement is being generated.

Thanks for any help

Jerry

"Jerry M" <***@retalix.com> wrote in message news:46166bdd$***@forums-1-dub...
When I try to Reverse Engineer any database on my MS SQL Server 2005 I get the following error. This occurs even on databases created by PowerDesigner.

Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s) could not be prepared.
SQLSTATE = 37000

This occurs when Reverse engineering starts up and when I select any database and select any of the following tabs:

Table, View (but it returns views), System Table, Trigger

It sounds like some things in my DBMS Resource for Microsoft SQL Server 2005 (sqlsv2k5.xdb) are causing an ill-formed statement to be passed to the engine. I tried pointing to the original, unmodified version of the .xdb that came with the installation and I still get this message. Any ideas how I might narrow down the problem and resolve it?

unknown

2007-04-08 05:18:33 UTC

Permalink

I don't have access to a SQL Server database, so I'm not going to be
able to help.
The XDB might have a poorly formed SQL statement. I've seen some
problems with DB2, and was able to fix them by changing the SQL directly
in the XDB. This might require more skill than I have.
Please contact Sybase tech support and log this case. They will be able
to help.
--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike[at]datamodel[dot]org (can you figure what to change?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

Post by Jerry M
Here is more information on my problem. I turned on a trace in SQL
Server and was able to trap the statement that failed the "prepare".
u.name,
o.name,
case (o.type) when ''S'' then ''SYSTEM TABLE'' else ''TABLE'' end,
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name,
null, null) where name = ''MS_Description'')
from
sys.sysobjects o
join sys.schemas u on (u.schema_id = o.uid)
where
o.type in (''U'', ''S'')
and u.name = ''dbo''
order by 1, 2'
The error message is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'u'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.
I am not that good at SQL to understand the syntax issue plus the fix
needs to be in how this statement is being generated.
Thanks for any help
Jerry
When I try to Reverse Engineer any database on my MS SQL Server 2005
I get the following error. This occurs even on databases created by
PowerDesigner.
Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s)
could not be prepared.
SQLSTATE = 37000
This occurs when Reverse engineering starts up and when I select any
Table, View (but it returns views), System Table, Trigger
It sounds like some things in my DBMS Resource for Microsoft SQL
Server 2005 (sqlsv2k5.xdb) are causing an ill-formed statement to be
passed to the engine. I tried pointing to the original, unmodified
version of the .xdb that came with the installation and I still get
this message. Any ideas how I might narrow down the problem and
resolve it?

Terry Voth

2007-04-09 23:53:18 UTC

Permalink

FWIW, I worked around the same problem using an older SQL Server
profile (IIRC, I had to go back to v7).

But Mike is right; one of us needs to get this reported. <g> We aren't
the first to run into this.

Good luck,

Terry [TeamSybase] and Sequel the techno-kitten

Post by Jerry M
u.name,
o.name,
case (o.type) when ''S'' then ''SYSTEM TABLE'' else ''TABLE'' end,
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null, null) where name = ''MS_Description'')
from
sys.sysobjects o
join sys.schemas u on (u.schema_id = o.uid)
where
o.type in (''U'', ''S'')
and u.name = ''dbo''
order by 1, 2'
The error message is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'u'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.
I am not that good at SQL to understand the syntax issue plus the fix needs to be in how this statement is being generated.
Thanks for any help
Jerry
When I try to Reverse Engineer any database on my MS SQL Server 2005 I get the following error. This occurs even on databases created by PowerDesigner.
Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s) could not be prepared.
SQLSTATE = 37000
Table, View (but it returns views), System Table, Trigger
It sounds like some things in my DBMS Resource for Microsoft SQL Server 2005 (sqlsv2k5.xdb) are causing an ill-formed statement to be passed to the engine. I tried pointing to the original, unmodified version of the .xdb that came with the installation and I still get this message. Any ideas how I might narrow down the problem and resolve it?

*********************************
PowerBuilder for $95? No.
Personal use PB Enterprise *AND* PocketBuilder *AND* DW.NET *AND* Sybase IQ as
free benefits of an ISUG membership. See http://www.isug.com for details.
*** For a limited time, use promotion code MP20224C to get a $10 (USD) discount
on a complete new or renewal membership

*********************************
Click once a day to help the hungry
http://www.thehungersite.com
*********************************
Newsgroup User Manual
=====================
TeamSybase <> Sybase employee
Forums = Peer-to-peer
Forums <> Communication with Sybase
IsNull (AnswerTo (Posting)) can return TRUE
Forums.Moderated = TRUE, so behave or be deleted
*********************************

Sequel's Sandbox: http://www.techno-kitten.com
Home of PBL Peeper, a free PowerBuilder Developer's Toolkit.
Version 3.0.02 now available at the Sandbox
PB Futures updated Apr 24/2006
See the PB Troubleshooting & Migration Guides at the Sandbox
^ ^
o o
=*=

Jerry M

2007-04-10 22:18:05 UTC

Permalink

Thanks to those who tried to help. Here is the issue and solution for anyone else who has this problem. The statement:
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null, null) where name = ''MS_Description'')
is both bad and unnecessary. It is trying to do a "select ... where name =", but name is not a selected variable. The fn_ function does not accept qualified names (or apparently parameterized names). Basically I could not resolve the errors in this line. However, all it is doing is trying to pull a comment that could only exist in the database if it was created by a recent version of Power Designer and then only if you actually put a comment in the table. Then all you get is the comment. As I said, it is unnecessary to the Reverse Engineering process because you are not likely to be reverse engineering a database you created.

If you take the statement out (and remove the comma at the end of the case statement), reverse engineering starts to work. You can do this by going into Tools | Resources | DBMS and editing the MS SQL Server 2005 Properties. Under Scripts | Objects | Table | SqlListQuery you will find the script that generates the SQL below. Remove the text from the comma following the end of the case statement to the end of the next line and save it.

You have to do one more thing to fully get Reverse Engineering working. The SQL to get the column data was doing the same unnecessary thing and had exactly the same problem. Under Scripts | Objects | Column | SqlListQuery you will find the script that generates the SQL for extracting the Column information during the Reverse Engineering process. In this case, just find the line that starts:
(select convert(varchar(8000), value) from ::fn_listextendedproperty...
and remove that whole line. Because of the order of the returned values I was concerned that I would have a problem, but it worked for me. I think the rest of the stuff that was trying to be returned is not in most older database and not really needed to get the structure and data elements you are really looking for.

Anyhow these fixes allowed me to reverse engineer several older databases just fine. I will leave this to whoever wants if they care to submit this to Support to get a permanent fix. (Maybe someone will actually test it before release. Sorry for the sarcasm, but this clearly was never run after it was written.)

Jerry
"Jerry M" <***@retalix.com> wrote in message news:461671fa$***@forums-1-dub...
Here is more information on my problem. I turned on a trace in SQL Server and was able to trap the statement that failed the "prepare". Here it is:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'select
u.name,
o.name,
case (o.type) when ''S'' then ''SYSTEM TABLE'' else ''TABLE'' end,
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null, null) where name = ''MS_Description'')
from
sys.sysobjects o
join sys.schemas u on (u.schema_id = o.uid)
where
o.type in (''U'', ''S'')
and u.name = ''dbo''
order by 1, 2'
select @p1
The error message is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'u'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.
I am not that good at SQL to understand the syntax issue plus the fix needs to be in how this statement is being generated.

Thanks for any help

Jerry

"Jerry M" <***@retalix.com> wrote in message news:46166bdd$***@forums-1-dub...
When I try to Reverse Engineer any database on my MS SQL Server 2005 I get the following error. This occurs even on databases created by PowerDesigner.

Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s) could not be prepared.
SQLSTATE = 37000

This occurs when Reverse engineering starts up and when I select any database and select any of the following tabs:

Table, View (but it returns views), System Table, Trigger

It sounds like some things in my DBMS Resource for Microsoft SQL Server 2005 (sqlsv2k5.xdb) are causing an ill-formed statement to be passed to the engine. I tried pointing to the original, unmodified version of the .xdb that came with the installation and I still get this message. Any ideas how I might narrow down the problem and resolve it?

Marc

2007-04-11 09:05:49 UTC

Permalink

Hi,

Well... I use SQL Server 2005, and I did not see this error (and the query
you gave is successfully executed on my server, whatever the database I
select), and I'm glad to get the comment from the database I reverse in a
new model...

What is the version of your server ?

Marc.

"Jerry M" <***@retalix.com> wrote in message news:***@forums-1-dub...
Thanks to those who tried to help. Here is the issue and solution for
anyone else who has this problem. The statement:
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL,
''user'', u.name, ''table'', o.name, null, null) where name =
''MS_Description'')
is both bad and unnecessary. It is trying to do a "select ... where name
=", but name is not a selected variable. The fn_ function does not accept
qualified names (or apparently parameterized names). Basically I could not
resolve the errors in this line. However, all it is doing is trying to pull
a comment that could only exist in the database if it was created by a
recent version of Power Designer and then only if you actually put a comment
in the table. Then all you get is the comment. As I said, it is
unnecessary to the Reverse Engineering process because you are not likely to
be reverse engineering a database you created.

If you take the statement out (and remove the comma at the end of the case
statement), reverse engineering starts to work. You can do this by going
into Tools | Resources | DBMS and editing the MS SQL Server 2005 Properties.
Under Scripts | Objects | Table | SqlListQuery you will find the script that
generates the SQL below. Remove the text from the comma following the end
of the case statement to the end of the next line and save it.

You have to do one more thing to fully get Reverse Engineering working. The
SQL to get the column data was doing the same unnecessary thing and had
exactly the same problem. Under Scripts | Objects | Column | SqlListQuery
you will find the script that generates the SQL for extracting the Column
information during the Reverse Engineering process. In this case, just find
the line that starts:
(select convert(varchar(8000), value) from ::fn_listextendedproperty...
and remove that whole line. Because of the order of the returned values I
was concerned that I would have a problem, but it worked for me. I think
the rest of the stuff that was trying to be returned is not in most older
database and not really needed to get the structure and data elements you
are really looking for.

Anyhow these fixes allowed me to reverse engineer several older databases
just fine. I will leave this to whoever wants if they care to submit this
to Support to get a permanent fix. (Maybe someone will actually test it
before release. Sorry for the sarcasm, but this clearly was never run after
it was written.)

Jerry
"Jerry M" <***@retalix.com> wrote in message news:461671fa$***@forums-1-dub...
Here is more information on my problem. I turned on a trace in SQL Server
and was able to trap the statement that failed the "prepare". Here it is:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'select
u.name,
o.name,
case (o.type) when ''S'' then ''SYSTEM TABLE'' else ''TABLE'' end,
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null,
null) where name = ''MS_Description'')
from
sys.sysobjects o
join sys.schemas u on (u.schema_id = o.uid)
where
o.type in (''U'', ''S'')
and u.name = ''dbo''
order by 1, 2'
select @p1
The error message is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'u'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.
I am not that good at SQL to understand the syntax issue plus the fix needs
to be in how this statement is being generated.

Thanks for any help

Jerry

"Jerry M" <***@retalix.com> wrote in message news:46166bdd$***@forums-1-dub...
When I try to Reverse Engineer any database on my MS SQL Server 2005 I get
the following error. This occurs even on databases created by
PowerDesigner.

Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s) could not be
prepared.
SQLSTATE = 37000

This occurs when Reverse engineering starts up and when I select any
database and select any of the following tabs:

Table, View (but it returns views), System Table, Trigger

It sounds like some things in my DBMS Resource for Microsoft SQL Server 2005
(sqlsv2k5.xdb) are causing an ill-formed statement to be passed to the
engine. I tried pointing to the original, unmodified version of the .xdb
that came with the installation and I still get this message. Any ideas how
I might narrow down the problem and resolve it?

Jerry M

2007-04-11 22:58:50 UTC

Permalink

Sorry for the disparaging remarks. I was frustrated having to debug to that
level. My MS SQL Server 2005 is Version 9.00.3042.00. I am amazed that the
statement I documented worked for you. After receiving your comment I went
back and tried again to execute the SQL I documented. When I tried to run
is as a SQL Server query directly in the Management Studio, I had to change
the line

select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null,
null) where name = ''MS_Description''

No matter what I did with the u.name and o.name unless I converted them to
double single-quote strings where u.name was exactly dbo, I got an error. I
could change o.name to anything including NULL. I tried casting the names
to unqualified variables in several ways and nothing works. I was surprised
that it did work without selecting "name" as I previously stated. It was
good to learn that you can use columns in your where clause that you are not
selecting. I guess it could be a configuration setting that does not allow
u.name and o.name to work in this context, but I am at a loss as to what
that might be.

BTW, I agree that it would be nice to get the comments if they are
available. Documenting the reverse engineered model is good. However,
actually getting reverse engineering to run is the minimum requirement and
from the thread I am not the only one having a problem with this.

Jerry

Post by Marc
Hi,
Well... I use SQL Server 2005, and I did not see this error (and the query
you gave is successfully executed on my server, whatever the database I
select), and I'm glad to get the comment from the database I reverse in a
new model...
What is the version of your server ?
Marc.
Thanks to those who tried to help. Here is the issue and solution for
(select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL,
''user'', u.name, ''table'', o.name, null, null) where name =
''MS_Description'')
is both bad and unnecessary. It is trying to do a "select ... where name
=", but name is not a selected variable. The fn_ function does not accept
qualified names (or apparently parameterized names). Basically I could
not resolve the errors in this line. However, all it is doing is trying
to pull a comment that could only exist in the database if it was created
by a recent version of Power Designer and then only if you actually put a
comment in the table. Then all you get is the comment. As I said, it is
unnecessary to the Reverse Engineering process because you are not likely
to be reverse engineering a database you created.
If you take the statement out (and remove the comma at the end of the case
statement), reverse engineering starts to work. You can do this by going
into Tools | Resources | DBMS and editing the MS SQL Server 2005
Properties. Under Scripts | Objects | Table | SqlListQuery you will find
the script that generates the SQL below. Remove the text from the comma
following the end of the case statement to the end of the next line and
save it.
You have to do one more thing to fully get Reverse Engineering working.
The SQL to get the column data was doing the same unnecessary thing and
had exactly the same problem. Under Scripts | Objects | Column |
SqlListQuery you will find the script that generates the SQL for
extracting the Column information during the Reverse Engineering process.
(select convert(varchar(8000), value) from ::fn_listextendedproperty...
and remove that whole line. Because of the order of the returned values I
was concerned that I would have a problem, but it worked for me. I think
the rest of the stuff that was trying to be returned is not in most older
database and not really needed to get the structure and data elements you
are really looking for.
Anyhow these fixes allowed me to reverse engineer several older databases
just fine. I will leave this to whoever wants if they care to submit this
to Support to get a permanent fix. (Maybe someone will actually test it
before release. Sorry for the sarcasm, but this clearly was never run
after it was written.)
Jerry
Here is more information on my problem. I turned on a trace in SQL Server
u.name,
o.name,
case (o.type) when ''S'' then ''SYSTEM TABLE'' else ''TABLE'' end,
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, ''user'', u.name, ''table'', o.name, null,
null) where name = ''MS_Description'')
from
sys.sysobjects o
join sys.schemas u on (u.schema_id = o.uid)
where
o.type in (''U'', ''S'')
and u.name = ''dbo''
order by 1, 2'
The error message is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'u'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.
I am not that good at SQL to understand the syntax issue plus the fix
needs to be in how this statement is being generated.
Thanks for any help
Jerry
When I try to Reverse Engineer any database on my MS SQL Server 2005 I get
the following error. This occurs even on databases created by
PowerDesigner.
Unable to list the tables.
[Microsoft][ODBC SQL Server Driver]{SQL Server}Statement(s) could not be prepared.
SQLSTATE = 37000
This occurs when Reverse engineering starts up and when I select any
Table, View (but it returns views), System Table, Trigger
It sounds like some things in my DBMS Resource for Microsoft SQL Server
2005 (sqlsv2k5.xdb) are causing an ill-formed statement to be passed to
the engine. I tried pointing to the original, unmodified version of the
.xdb that came with the installation and I still get this message. Any
ideas how I might narrow down the problem and resolve it?

Jerry M

2007-04-13 00:16:29 UTC

Permalink

I figured it out. The issue is a problem of quotation marks. In the
original text for the SqlList Query for Tables, in the Scripts | Object |
Table, the line
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, null, null)
where name = 'MS_Description')
should read
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', 'u.name', 'table', 'o.name', null,
null) where name = 'MS_Description')
Note the single quotes around u.name and o.name.

For the SqlList Query for Tables, in the Scripts | Object | Column, the line
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column',
c.name) where name = 'MS_Description'),
should read
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', 'u.name', 'table', 'o.name',
'column', 'c.name') where name = 'MS_Description'),
Note there is an extra field that was unquoted compared to the Table entry.
I reverse engineered several database with this and it caused no errors. I
had nothing in an MS_Description extended property, but I assume if I had it
would have worked since it was clearly trying to return a value when I run
the statements in MS SQL Server Management Studio. Hope this helps others.

Jerry

unknown

2007-04-13 02:04:26 UTC

Permalink

This needs to get back to Sybase Engineering so they can fix it in the
shipping tool. Please visit the web site, go to the Support area, Case
Management, and post this as a Bug Report. This will get it on the
queue for Sybase.
--
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike[at]datamodel[dot]org (can you figure what to change?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

Post by Jerry M
I figured it out. The issue is a problem of quotation marks. In the
original text for the SqlList Query for Tables, in the Scripts | Object |
Table, the line
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, null, null)
where name = 'MS_Description')
should read
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', 'u.name', 'table', 'o.name', null,
null) where name = 'MS_Description')
Note the single quotes around u.name and o.name.
For the SqlList Query for Tables, in the Scripts | Object | Column, the line
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, 'column',
c.name) where name = 'MS_Description'),
should read
(select convert(varchar(8000), value) from
::fn_listextendedproperty(NULL, 'user', 'u.name', 'table', 'o.name',
'column', 'c.name') where name = 'MS_Description'),
Note there is an extra field that was unquoted compared to the Table entry.
I reverse engineered several database with this and it caused no errors. I
had nothing in an MS_Description extended property, but I assume if I had it
would have worked since it was clearly trying to return a value when I run
the statements in MS SQL Server Management Studio. Hope this helps others.
Jerry