Packaging and distributing saved scripts

[markphsd]

Earlier this month i had asked how to script changes to a SQL Server 2000 database. I was told to set the changes to be saved to a text file.

Now i have all these script files from the changes. I thought I could run the SQL in the scripts using ADO with VB, but I get an error.

For the distributions the datbases's names, user, server and password are going to be different however, I would like to automate this distribution of updates using the files.

this is the error
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'COMMIT'.

thanks

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[gmmastros]

Are there [!]GO[/!]'s within the script. If so, this is what is causing the problem. Now, you can't just remove the go's because they are important. What you can do it so split the script and execute each part seperately.

Like this...

Dim AllScripts as String
Dim arTemp() As String
Dim i As Long

AllScripts = 'Whatever is in the script file'

arTemp = Split(AllScripts, vbCrLf & "GO" & vbCrLf,,vbTextCompare)
For i = lBound(arTemp) To uBound(arTemp)
  ADOConnection.Execute(arTemp(i))
Next

Erase arTemp

This is just psuedo code, but hopefully you get the idea. Let me know if there is anything you don't understand.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markphsd]

I tried that, however it doesn't do anything.

this is one complete script: i'm wondering if i should split out the Create Table.

/* Saturday, March 03, 2007 11:26:40 PM User: Server: (local) Database: CCS Application: MS SQLEM - Data Tools*/BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.tblFolders
(
FolderID int NOT NULL IDENTITY (1, 1),
FolderName nvarchar(50) NULL,
FolderType int NULL,
FolderImage int NULL,
FolderOrder int NULL
) ON [PRIMARY]
GO
COMMIT





Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.