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'.