question

POOJAPP avatar image
POOJAPP asked

Incorrect syntax near 'GO'

I have a requirement where I have a deployment script for
sql server.

This is saved to a file test.sql

It is like this:

alter table [DB1].[dbo].[Table1] --drop column Col4
add Col4 varchar(100)
GO
insert into [DB1].[dbo].[Table1] values ('sdfs','fdsfew','sdrtyr','rtyrtyr');
GO

Now, I am trying to access the content of this file through a stored procedure and execute the script.

Below is the content for the stored procedure:

alter PROCEDURE dbo.usp_ExecuteScriptOnAllDB(
 
@in_filePath VarChar(260)=null,-- UNC paths seem to work fine here.
 
@out_contents nVarChar(1000)=null
)
AS
BEGIN
set @in_filePath ='C:\mssql\test.sql'
DECLARE @hr Int
DECLARE @o_fso Int, @o_file Int, @sz_contents VarChar(1000)
DECLARE @noErr Bit, @errMethod VarChar(255)
DECLARE @src VarChar(255), @desc VarChar(255)
SET @noErr = 1
EXEC @hr =sp_OACreate'Scripting.FileSystemObject', @o_fso OUT
IF @hr = 0
BEGIN
EXEC @hr =sp_OAMethod@o_fso,'OpenTextFile', @o_file OUT, @in_filePath, 1
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OAMethod: OpenTextFile'
SET
@noErr = 0
END
END
IF @hr = 0
BEGIN
EXEC @hr =sp_OAMethod@o_file,'ReadAll', @sz_contents OUT
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OAMethod: ReadAll'
SET
@noErr = 0
END
END
IF @hr = 0
BEGIN
EXEC @hr =sp_OADestroy@o_file
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OADestroy: File Object'
SET
@noErr = 0
END
END
IF @hr = 0
BEGIN
EXEC @hr =sp_OADestroy@o_fso
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OADestroy: FSO Object'
SET
@noErr = 0
END
END
SET @out_contents = @sz_contents
print @out_contents
SETNOCOUNTON;
EXECUTEsp_executesql@out_contents
END
GO

I am able to read and print the contents of the file.

But somehow it it not executing the script.

It says

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near 'GO'.

stored-proceduressql serversp_executesql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI avatar image WRBI commented ·

When you added the code to the site did it remove spaces?

EXECUTEsp_executesql@out_contents

Shoudn't this be

EXECUTE sp_executesql @out_contents

If it's just this site that's accidentally removed space then us know. If not, that's going to be a problem!

0 Likes 0 ·

1 Answer

·
POOJAPP avatar image
POOJAPP answered

Ya. While adding the code, it has removed spaces.

The code parses correctly when I do it in SQL.

The code got converted on its own while submitting the content to this site.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.