Fix by Cloning table struction
in [Programming SQL]
|
Prev: Calculate Differences in Closing Balances
Next: Running Total in a View, SP or Table Valued Function
From: news.microsoft.com on 11 Sep 2009 17:38 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 11 Sep 2009 17:53 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 11 Sep 2009 18:05 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 11 Sep 2009 21:50 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 15 Sep 2009 16:45
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 >> > > |