Creating tables with SQL Scripts

In sql server 2003 I would create tables with generated sql scripts.
This way I could easily create duplicate dev environments.

Now in SQL 2005 Dev edition I try to run my scripts to create tables.
I create a db [MSS].
They run successfully but there is no table created.

(example script)USE [MSS]
GO
/****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KataNames](
[Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SequenceId] [int] IDENTITY(1,1) NOT NULL,
[Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?

Thanks

Kim AllbritainDid you 'Refresh' the table list in SSMS?|||Right click your "Databases" node and choose "Refresh"|||Of course I refreshed the db list. I even restarted the app and looked again. No table, but successful completion of the script every time.

Is it possible to back up a sql 200 db on one machine and restore it to 2005 sql on another?|||

KAllbritain wrote:

Of course I refreshed the db list. I even

restarted the app and looked again. No table, but successful completion

of the script every time.

Is it possible to back up a sql 200 db on one machine and restore it to 2005 sql on another?

There isnt much point saying "Of course I did" we diddnt know that you did "yes I tried that" would have been much nicer

Ok, are you using an application to send these scripts? Like Access or .NET

If so, the application or the user account you are using to fire the scripts may not have the appropriate permissions to do so.

Alternatively, do you mean you fired the script in the Query Analayzer of SQL Server 2005 and still do not see the tables?

Do you have serveral SQL Servers on your network?

AFAIK there is no SQL Server 2003, and with regards to porting a 2000 to 2005 yes its possible, there is some documentation here; http://www.microsoft.com/technet/prodtechnol/sql/themes/default.mspx

take it slow, let us fully understand your situation so that we can resolve it = )|||I appreciate your response.

It was sql2000 dev version using 2003 .Net

on sql2005 dev:

The scripts are loaded into the query analyzer and run from there
while connected to the proper db on my local machine.
There are no other servers presently connected.

The script indicates the proper db as well so How can it report
successful without actually performing the create table?|||

If the purpose of the script is to CREATE an object, it is not successful until after it creates that object. If you want to verify syntax, you could, in SSMS, click on the green 'checkmark' icon on the toolbar, or [Query], [Parse] on the menu, or {ctrl}{F5}.

If you want to verify that it actually works, you could wrap it in a TRANSACTION and ROLLBACK when completed.

Or run it in a 'Test' database first.

|||Thanks

The scripts purpose is create a table. It works in sql2000 fine.
The message provided after executing the script is that it was "Successful", therefore I would expect the object (table) to be created.

I am cloning a small db in a new environment, change from 2000 to 2005.
The syntax is fine.

My problem is that if the syntax is fine, the script works in sql2000,
and SSMS says it worked, why doesn't it?

What else can I do?

I could build the tables by hand but why?|||OK I solved this. Just in case anyone else reads this thread.

The table was created by my scripts alright, just not in the db I thought I was in.

I was looking at db "Library" and it was highlighted, but when I opened the Query screen and connected it defaulted to the (Master)
db, so that's where The tables were.

Would be nice if the IDE for SQl2005 would default to the
db you were actually connected and working on instead of
re-connecting to something else.

But it worked ok.

Bye