From: news.microsoft.com on
We had a database on an SQL 2005 box. We lost access to that server so we
needed to downgrade it to our SQL 2000. We used the MS Database Publishing
Wizard to extract the database and bring it into SQL 2000.

It worked well except the Default Values for the fields did not come over.
Our problem is most of the int fields are set to not allow nulls, but no set
as either 0,1, or -1 for the default value and some string fields are not
set to '' for the default. Naturally this causes errors when the program
tried to add records and takes for granted the fields will just go to their
defaults.

I reinstalled the app on another SQL 2000 server which installed a fresh DB
so I can see how the app created the DB struct. Is it possible for me to
the existing tables with the default values from the new blank tables
through a script?




From: Eric Isaacs on
You can script the changes to the tables by altering the tables and
adding new default constraints to the columns.

If you still have a 2005 box with that db on it (a dev license for
instance) you can use the Information_Schema.Columns view to see the
default values (in 2005 and 2000.) That might help you determine what
needs to be done.

SELECT table_catalog, table_schema, table_name, column_name,
Column_default FROM information_schema.columns WHERE Column_default IS
NOT NULL

You might also check the differences between the information_schema
views between the two dbs to see if there are other differences.

-Eric Isaacs

From: Erland Sommarskog on
news.microsoft.com (none(a)here.com) writes:
> I reinstalled the app on another SQL 2000 server which installed a fresh
> DB so I can see how the app created the DB struct. Is it possible for
> me to the existing tables with the default values from the new blank
> tables through a script?

Beside the fact that you should of course put all your database code
under version control, your best bet is to go hold of SQL Compare from
Red Gate. For a one-off the evaluation period of 14 days should do, but
may find that you love it.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: m on
A competitive product is offered by xSQL. It is also effective for most
problems

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9C84100D2AFEYazorman(a)127.0.0.1...
> news.microsoft.com (none(a)here.com) writes:
>> I reinstalled the app on another SQL 2000 server which installed a fresh
>> DB so I can see how the app created the DB struct. Is it possible for
>> me to the existing tables with the default values from the new blank
>> tables through a script?
>
> Beside the fact that you should of course put all your database code
> under version control, your best bet is to go hold of SQL Compare from
> Red Gate. For a one-off the evaluation period of 14 days should do, but
> may find that you love it.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

From: news.microsoft.com on
Thanks, I think I will give SQL compare a try from both.
"m" <m(a)b.c> wrote in message news:eWn2mt0MKHA.3672(a)TK2MSFTNGP02.phx.gbl...
>A competitive product is offered by xSQL. It is also effective for most
>problems
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9C84100D2AFEYazorman(a)127.0.0.1...
>> news.microsoft.com (none(a)here.com) writes:
>>> I reinstalled the app on another SQL 2000 server which installed a fresh
>>> DB so I can see how the app created the DB struct. Is it possible for
>>> me to the existing tables with the default values from the new blank
>>> tables through a script?
>>
>> Beside the fact that you should of course put all your database code
>> under version control, your best bet is to go hold of SQL Compare from
>> Red Gate. For a one-off the evaluation period of 14 days should do, but
>> may find that you love it.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
>