How can i "script table as" at sql server 2005 to sql server 2000 compatibility????

Question

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005  and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object:  Table [dbo].[TBL_TEST]    Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [KOLON1] [nchar](10) NULL,
    [KOLON2] [nchar](10) NULL,
 CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [KOLON1] [nchar](10) NULL,
    [KOLON2] [nchar](10) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
    CONSTRAINT [PK_TBL_TEST] PRIMARY KEY  CLUSTERED
    (
        [ID]
    )  ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility????

Answer 1

 

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

Answer 2

thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..

Answer 3

You might try a free app I wrote called scriptdb.  It will script out all objects in your database, with a separate file for each.  It's useful for getting all your objects into source control if they aren't already.  The source code is freely available.  get it here:

http://www.elsasoft.org/tools.htm

hope it helps!

Answer 4

I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.

Answer 5

I had a similar situation,

 

here is my SOLUTION.

 

If you are useing Microsoft SQL Server Management Studio Express

 

A.  Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts...  ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

 

the script will work in SQL 2000

 

 

Answer 6

Its very simple

see following post

http://dev30.com/post/2008/11/18/How-to-Generate-Sqlserver-2000-compatible-script-from-sqlserver-2005-database.aspx

Answer 7

kuvve wrote:

How can i "script table as" at sql server 2005 to sql server 2000 compatibility????

 

I tried the method suggested in the posts above (Script Wizard, SQL 2000 output).  It failed for HumanResources.JobCandidate due the XML column in the table.

 

With all fairness, going from SQL 2000 to SQL 2005 is the way to go. Going the other way is a dubious undertaking.

Answer 8

See if you want to Use Sql2005 Features in database then generate script for 2000 it will not work. You have to get those features fixed which are provided by sqlserver 2005 such as varchar(max) ,CTE , XML column, .net Sps  etc

Or if you have intentions to  work in sqlserver 2005 then will move to 2000 in production environment  you should set compatibility level to 80 while development 

Answer 9

Kashif Akram wrote:

Or if you have intentions to  work in sqlserver 2005 then will move to 2000 in production environment  you should set compatibility level to 80 while development

 

Kashif,

 

80 will not force you to SQL Server 2000 code. You can still create tables with XML column for example.  BOL 2008: "New functionality might work under older compatibility levels, but SET options might require adjustments. For example, using the xml data type under compatibility level 80 requires appropriate ANSI SET options. "

 

Best Practices in this case to use SQL Server 2000 platform to support 2000 customers and 2005/2008 for 2005/2008 customers.

Answer 10

 

Definitely it will not force you or you can say validate features or enhancements provided by upgraded server but will let you use old or obsolete features or language set. So it is better to set compatibility level if you planning to downgrade after production environment

Answer 11

@WickedMonkey - This worked great!  Thanks for making me look like a hero. :)

I should post this clarification on the steps... perhaps you had a different version of SSMS, but in 2008 R2, your step E (where you select Script for Server Version) is now in a different place.

Now it's in the "Set Scripting Options" section under that "Advanced" button.