I have found very strange problem - it seems that if you
create a table in the Enterprise Manager (for example, in
my case the table name is MyTbl) and accept default
primary key name (which in my case is PK_MyTbl and I like
it), when you try to generate a script for this table, the
script will omit the primary key name at all and the table
will be copied to another server with absolutely different
primary key name (something very cryptic like
PK_MyTbl_987645.).

I have about 500 tables and 1000 stored procedures and in
some procedures I had to use hints with direct reference
to the indexes names - how can I generate a script with
PROPER index names?

It also seems that the scripts for objects owned by an
application role are also generated with errors - SQL
Server tries to use SETUSER command, which does not work
for application roles.

Has anybody seen these bugs?

Can you detail the steps you went through to create the scripts?
What version SQL Server?
What Service Pack level?
Can you show us what the script that is incorrect looks like?

Where does DTS come into this?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"att" <Alex.Tchepak (AT) hudson (DOT) com> wrote

Ok - here are some details:

I have a table called frstx and if I open this table in
the Design Mode in the Enterprise manager, I can clearly
see that this table has a primary key called PK_frstx.

But when I try to generate a SQL script for it (selecting
option "Generate Primary keys, etc ...", I receive the
following script (I left only part related to PK creation):

.....
ALTER TABLE [dbo].[frstx] ADD
CONSTRAINT [DF__frstx__syscreate__63057124]
DEFAULT (getdate()) FOR [syscreated],
CONSTRAINT [DF__frstx__syscreato__63F9955D]
DEFAULT (0) FOR [syscreator],
CONSTRAINT [DF__frstx__sysmodifi__64EDB996]
DEFAULT (getdate()) FOR [sysmodified],
CONSTRAINT [DF__frstx__sysmodifi__65E1DDCF]
DEFAULT (0) FOR [sysmodifier],
CONSTRAINT [DF__frstx__sysguid__66D60208] DEFAULT
(newid()) FOR [sysguid],
PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
.....

As you can see - there is no primary key name in the
script and when I run it on another SQL Server, it
generates default name for it, but since I have references
to PK_frstx in the stored procedures, they just stop
working.

Interesting thing - if in the Design Mode I rename
PK_frstx to, for example, PK_frstx_temp, save the table
and rename the PK back to PK_frstx, everything starts
working properly and the script now will use proper PK
name:

ALTER TABLE [dbo].[frstx] ADD
CONSTRAINT [DF__frstx__syscreate__63057124]
DEFAULT (getdate()) FOR [syscreated],
CONSTRAINT [DF__frstx__syscreato__63F9955D]
DEFAULT (0) FOR [syscreator],
CONSTRAINT [DF__frstx__sysmodifi__64EDB996]
DEFAULT (getdate()) FOR [sysmodified],
CONSTRAINT [DF__frstx__sysmodifi__65E1DDCF]
DEFAULT (0) FOR [sysmodifier],
CONSTRAINT [DF__frstx__sysguid__66D60208] DEFAULT
(newid()) FOR [sysguid],
CONSTRAINT [PK_frstx] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

DTS comes here because all I need to do is to export my
tables and data onto different SQL Server and it does not
matter what I do, the primary keys are exported with names
like PK_frstx_067435078545...

Why?

So you are generating a PK Constraint for a field titled ID

PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO


Unless you specify a constraint name then SQL Server generates one.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"att" <Alex.Tchepak (AT) hudson (DOT) com> wrote

it becomes even more interesting - for some tables scripts
use proper PK names but for some - not.

Something wrong with the database? DBCC could not find any
errors at all.

Can it be that some PK names are marked as supplied by
default by SQL Server and therefore they are excluded from
the script?

I am using SQL Server 2000 (SP3)

I have a table, which ALREADY HAS a primary key called
PK_frstx, but when I generate a SQL script for this table,
I receive the following result:

... CONSTRAINT PRIMARY KEY NONCLUSTERED ..

and I can NOT understand why this script has empty space
instead of existing and properly named primary key. The
result that I expected, was:

... CONSTRAINT [PK_frstx] PRIMARY KEY NONCLUSTERED ..

So, I repeat, I have a properly named PK for the table,
but SQL Server refuses to include its name into sql
scripts generated for this table.

I did ask you earlier to provide a script amongst other things so that there
was no confusion.

I have a table like this

CREATE TABLE MyTable(ColPK int CONSTRAINT pk_frstx PRimary Key)

colPK is a column Name
pk_frstx is the constraint name

Here is what EM generated

CREATE TABLE [dbo].[MyTable] (
[ColPK] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [pk_frstx] PRIMARY KEY CLUSTERED
(
[ColPK]
) ON [PRIMARY]
GO

Where do you differ?


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"att" <Alex.Tchepak (AT) hudson (DOT) com> wrote

In my case generated script looks like:

CREATE TABLE [dbo].[MyTable] (
[ColPK] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT PRIMARY KEY CLUSTERED
(
[ColPK]
) ON [PRIMARY]
GO

I think I explained it several times - in my case there is
NO primary key name in the generated sqcript (i.e. just a
space between words "CONSTRAINT" and "PRIMARY"). I repeat -
the table has properly named primary key, but generated
script creates no-named primary key.

One more interesting detail - all primary keys that have
this problem have status=36 in the corresponding rows in
the sysobjects table - I do not have a clue what it means.

I checked several other databases on other servers and
could not find any single row in sysobjects with status =
36.

OK

The reason you may have "Mentioned" things before and nobody has read them
is becuse you keep changing the subject of the posts.

I created my table in QA.
I did Right click on the table | All Tasks | Generate SQL Script

My Status column in sysobjects for my CONSTRAINT name is 16

SELECT *
FROM sysobjects
WHERE (name = 'pk_frstx')

SQL Server Magazine I believe has run articles on the status column and its
meanings.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"att" <Alex.Tchepak (AT) hudson (DOT) com> wrote

It seems that simple select statement

select * from sysobjects where (xtype='PK') and ((status &
4) <> 0)


gives me full list of all "problem" primary keys, the only
solution that I could find was to run my own script that
drops and rebuilds all these primary keys. Rebuilt primary
keys have status either 16 or 32.

But it is still not clear why this problem happened at all.