Creating tables with SQL Scripts (SOLVED)

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

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

Creating stored procedures in sql server

I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it.

However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster".

Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didnt

Dim comm As New SqlCommand
Dim strsql As String
Dim strconn As String
strsql = "sp_Roster"
strconn = "server=Home; user=sa; pwd=juwar74; database=Book;"
With comm
.Connection = New SqlConnection(strconn)
.CommandText = strsql
.CommandType = CommandType.StoredProcedure
With .Parameters.Add("TeacherID", SqlDbType.Char)
.Value = "DawsMark@.aol.com"
End With
With .Parameters.Add("ClassID", SqlDbType.Int)
.Value = CInt(classid)
End With
With .Parameters.Add("sID", SqlDbType.Int)
.Value = ssID
End With
With .Parameters.Add("sLastName", SqlDbType.Char)
.Value = lastname
End With
With .Parameters.Add("sFirstName", SqlDbType.Char)
.Value = firstname
End With
With .Parameters.Add("sMiddleName", SqlDbType.Char)
.Value = middlename
End With
With .Parameters.Add("Student", SqlDbType.Char)
.Value = fullname
End With
With .Parameters.Add("Password", SqlDbType.Char)
.Value = password
End With
.Connection.Open()
.ExecuteNonQuery()
With comm.Connection
If .State = ConnectionState.Open Then
.Close()
End If
End With
End With

Here is my procedure that I created and that was saved as sp_Roster.sql in sql server

CREATE PROCEDURE sp_Roster

AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)
END
GO

Why isn't ado.net finding it in sql server. Is the sqlstr correct?

In order to save a stored procedure to the DB in SQL 2005 you need to execute this code instead of saving the text as a file. Just click the "Execute" button and you should be ok. Another thing however, is that you have not declared your variables. You will not be able to execute/save your stored procedure without this.

CREATEPROCEDURE sp_Roster

@.TeacherIDint,

@.ClassIDint,

@.sIDint,

@.sLastNamenvarchar(100),

@.sFirstNamenvarchar(100),

@.sMiddleNamenvarchar(50),

@.Studentnvarchar(100),

@.Passwordnvarchar(100)

AS

BEGIN

SETNOCOUNTON;

-- Insert statements for procedure here

INSERTINTO Roster(TeacherID, ClassID,sID, sLastName, sFirstName, sMiddleName, Student,Password)VALUES(@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)

END

GO

|||

Unfortunately, Sql Server doesn't work the same as Access in this way. Wehn you save a query in Sql Server, you are saving the query, but no the query to the database. The script has to be run to actually create your stored procedure. This can be done by pressing F5. However, your query itself needs some work as you have no input parameters to your stored procedure.

sql