EM SQL Script generation BUG?

I'm using Enterprise Manager to generate a SQL script from a database, the
intent being that I can use that script to populate a new "empty" database
instance, duplicating the schema of the source database. My source database
has a table whose name contains a space; e.g., [tblFoo Bar].
There is a relationship between this table and another and so its name is
incorporated in the constraint name; e.g., [FK ...tblFoo Bar...]. The
problem is that the EM generated DROP statement doesn't use brackets to
delimit the object name; thus an error is thrown when the generated script
is executed in OA. Note that brackets *are* used in the generated script at
the point where the constraint is (re)created.
This appears to be a bug in the script generator. Surely the generator
should generate valid SQL!!
Is this a known problem? Is there a fix?
Thanks in advance,
Bill
Oops, forgot to mention SQL Server 2000 SP3
"Bill Cohagan" <bill@.teraXNOSPAMXquest.com> wrote in message
news:%23GkQs4DjEHA.556@.tk2msftngp13.phx.gbl...
> I'm using Enterprise Manager to generate a SQL script from a database, the
> intent being that I can use that script to populate a new "empty" database
> instance, duplicating the schema of the source database. My source
database
> has a table whose name contains a space; e.g., [tblFoo Bar].
> There is a relationship between this table and another and so its name is
> incorporated in the constraint name; e.g., [FK ...tblFoo Bar...]. The
> problem is that the EM generated DROP statement doesn't use brackets to
> delimit the object name; thus an error is thrown when the generated script
> is executed in OA. Note that brackets *are* used in the generated script
at
> the point where the constraint is (re)created.
> This appears to be a bug in the script generator. Surely the generator
> should generate valid SQL!!
> Is this a known problem? Is there a fix?
> Thanks in advance,
> Bill
>
|||Hi Bill,
Thanks for sharing your pains with us!
Based on my scope, I noticed there is an known issue like this, however, I
need more information to confirm it. Would you please so kind as to provide
more information about this?
1. What's the owner of your 2 tables? All are [dbo]?
2. Could you show me an example for me how to make this issue reporduce?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||The answer to #1 is yes. I'll try to get some code to you to repro the
problem, but it'll be a couple of days.
Bill
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:6MqG4RkjEHA.2860@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Thanks for sharing your pains with us!
> Based on my scope, I noticed there is an known issue like this, however, I
> need more information to confirm it. Would you please so kind as to
> provide
> more information about this?
> 1. What's the owner of your 2 tables? All are [dbo]?
> 2. Could you show me an example for me how to make this issue reporduce?
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Cheng
Following is a SQL generated script for a simple database containing two
tables, [Table A] and [Table B] and a one to many relationship between A and
B. Note the first SQL statement containing the DROP CONSTRAINT. The
constraint name should be enclosed in [...] but is not.
If you'd like to recreated the database, wrap this name in [ ], then
execute the entire script to populate an empty database with the two tables
and relationship. Finally do a script generation and you should see the same
DROP CONSTRAINT name problem. Let me know if you have any questions.
==========================================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Table B_Table A]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table B] DROP CONSTRAINT FK_Table B_Table A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table
A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table A]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table
B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table B]
GO
CREATE TABLE [dbo].[Table A] (
[AID] [uniqueidentifier] NOT NULL ,
[SomeText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table B] (
[BID] [uniqueidentifier] NOT NULL ,
[AID] [uniqueidentifier] NOT NULL ,
[SomeInfo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table A] ADD
CONSTRAINT [PK_Table A] PRIMARY KEY CLUSTERED
(
[AID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table B] ADD
CONSTRAINT [PK_Table B] PRIMARY KEY CLUSTERED
(
[BID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table B] ADD
CONSTRAINT [FK_Table B_Table A] FOREIGN KEY
(
[AID]
) REFERENCES [dbo].[Table A] (
[AID]
)
GO
==========================================
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:6MqG4RkjEHA.2860@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Thanks for sharing your pains with us!
> Based on my scope, I noticed there is an known issue like this, however, I
> need more information to confirm it. Would you please so kind as to
> provide
> more information about this?
> 1. What's the owner of your 2 tables? All are [dbo]?
> 2. Could you show me an example for me how to make this issue reporduce?
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
Thanks for your posting to let me reproduce it!
Admittedly, it is an known issue for us. There is no public document
released for this issue. Bad news from SQL Server development Team is they
won't fix this issue for SQL Server 2000 as the fix will induce rewrite of
SQL Server Enterprise Manager.
The only workaround for this is not using space in constraint name or add
the brackets manually. I am sincerely sorry for the inconvenience you may
meet from this issue.
Thanks again for your kindest providing repo scenario for us and your
corpoerations.
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for looking into this; though I can hardly imagine fixing the problem
would require a "rewrite of SQL Server Enterprise Manager" -- since the tool
already does the right thing when *creating* the constraint; i.e., the
failure is only when DROPping the old constraint.
Unfortunately in my case I have no control over the database schema as I'm
supplying a tool to generate code using the MS generated SQL code as input.
I suppose I can just tell my customers that SQL Server 2000 simply does not
support blanks in table names -- at least if you want to generate scripts,
but I doubt their reaction will be pleasant.
I would appreciate it if you'd get another reading from the Enterprise
Manager folks because I really think this fix is probably pretty simple. The
only reason I can imagine it to be a "rewrite" would be if Microsoft has
misplaced the source code!
Also, until/unless this gets fixed it'd save everyone time were it
documented in the KB.
Regards,
Bill
PS - Perhaps I should have targeted my product at Oracle! ;-)
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:tTueemxmEHA.752@.cpmsftngxa06.phx.gbl...
> Hi Bill,
> Thanks for your posting to let me reproduce it!
> Admittedly, it is an known issue for us. There is no public document
> released for this issue. Bad news from SQL Server development Team is they
> won't fix this issue for SQL Server 2000 as the fix will induce rewrite of
> SQL Server Enterprise Manager.
> The only workaround for this is not using space in constraint name or add
> the brackets manually. I am sincerely sorry for the inconvenience you may
> meet from this issue.
> Thanks again for your kindest providing repo scenario for us and your
> corpoerations.
> If you have any questions or concerns, don't hesitate to let me know. We
> are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
I understood that this issue may make a big business impact for you. If so,
I would like to suggest you ask for a direct assistance from a Microsoft
Support Professional through Microsoft Product Support Services. They may
find a better workaround for you. You can contact Microsoft Product Support
directly to discuss additional support options you may have available, by
contacting us at 1-(800)936-5800 or by choosing one of the options listed
at http://support.microsoft.com/default...=sz;en-us;top.
I am afraid the development of SQL Server Enterprise Manager might be
easier to think than to do. As I had said, the workaround for this is not
using space in constraint name or add the brackets manually. I am sincerely
sorry again for the inconvenience you may meet from this issue.
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||OK. Thanks for working with me on this problem.
Regards,
Bill
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:B9wHeN8mEHA.752@.cpmsftngxa06.phx.gbl...
> Hi Bill,
> I understood that this issue may make a big business impact for you. If
so,
> I would like to suggest you ask for a direct assistance from a Microsoft
> Support Professional through Microsoft Product Support Services. They may
> find a better workaround for you. You can contact Microsoft Product
Support
> directly to discuss additional support options you may have available, by
> contacting us at 1-(800)936-5800 or by choosing one of the options listed
> at http://support.microsoft.com/default...=sz;en-us;top.
> I am afraid the development of SQL Server Enterprise Manager might be
> easier to think than to do. As I had said, the workaround for this is not
> using space in constraint name or add the brackets manually. I am
sincerely
> sorry again for the inconvenience you may meet from this issue.
> Thank you for your patience and cooperation.
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>