Valid SQL query fails when executed from VB Script

Question

I have SQL query that successfully runs in the SQL management studio, but when I'm running the same query from VB script it fails with error:

Microsoft OLE DB Provider for SQL Server: Incorrect syntax near the keyword 'ALTER'.

My SQL query is:

SET XACT_ABORT ON;
BEGIN TRANSACTION VersionBuild
BEGIN TRANSACTION
GO
CREATE TABLE dbo.TimeTemplate
    (
    id int NOT NULL IDENTITY (1, 1),
    startTime datetime NOT NULL,
    endTime datetime NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.TimeTemplate ADD CONSTRAINT
    PK_TimeTemplate PRIMARY KEY CLUSTERED 
    (
    id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

INSERT INTO [dbo].[TimeTemplate] ([startTime],[endTime])
VALUES ('2013-03-15 00:00:00.000','2013-03-15 23:59:00.000')

INSERT INTO [dbo].[Form] ([name] , [description], [dateCreated], [fileName])
VALUES ('TimeTemplates.aspx' , '' , getdate(), 'TimeTemplates.aspx')

;

If @@Error <> 0 BEGIN
ROLLBACK TRANSACTION VersionBuild
END ELSE 
BEGIN
UPDATE Version SET version = 74;
COMMIT TRANSACTION VersionBuild
END

Lynn Crumbling · Accepted Answer · 2013-03-18 17:22:48Z

1

From MSDN:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Also from the same article:

Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.

Try removing your GO statements and break the script up into multiple commands.

answered Mar 18, 2013 at 17:22

Lynn Crumbling

13.4k8 gold badges61 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lynn Crumbling Over a year ago

Additionally, if you're planning on keeping your error check, you may need to return the error as a query result, and handle the actual if statement down inside of vbscript. I don't think you can use if statements like this in vbs. Another alternative would be to make this entire thing into its own stored procedure, and just execute one command calling the sp from vbscript.

Collectives™ on Stack Overflow

Valid SQL query fails when executed from VB Script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related