Problem running script in VS2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem running script in VS2005

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2006-03-27 : 09:10:39

I have a script prepared which works. I now want to execute this script in a Visual Basic (.Net 2)application (Visual Studio 2005).

My script is:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Models]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#Models]
GO

CREATE TABLE [dbo].[#Models] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Model] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

DECLARE @TableName varchar(40), @FileType int
DECLARE tables_cursor CURSOR FOR

SELECT [table name] FROM [system files]
WHERE [file type] = 0 or [file type] = 1 or [file type] = 2 or [file type] = 7 or [file type] = 08

OPEN tables_cursor

FETCH NEXT FROM tables_cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

exec('INSERT INTO [dbo].[#Models] select DISTINCT model from ' +@TableName)
FETCH NEXT FROM tables_cursor
INTO @TableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Models]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Models]
GO

CREATE TABLE [dbo].[Models] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Model] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Models] select DISTINCT model from [dbo].[#Models]
GO

My code is:

Dim sqlConnection1 As New SqlClient.SqlConnection()
Dim cmd As New SqlClient.SqlCommand
Dim rowsAffected As Integer

sqlConnection1.ConnectionString = My.Settings.ConnectionString

cmd.CommandText = LoadScriptFile("LoadDistinctModels.sql")
cmd.CommandType = CommandType.Text
cmd.Connection = sqlConnection1

sqlConnection1.Open()

rowsAffected = cmd.ExecuteNonQuery()

sqlConnection1.Close()

Basic scripts like "Select * from pubs" work. What need I do to get my full script to work?

Declan

-dw

liffey
Yak Posting Veteran

58 Posts

Posted - 2006-03-27 : 11:07:21

Problem Solved.

I removed all GO commands from the scripts and everything worked just fine.
dw

-dw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources