Update SP Definition reading from file and run dynamic SQL, deal with EOL

Question

Hi I need to create new definitions for SP having their source in external physical files (imported from other TFS site, have 1000+ of them). I thought I solved this problem on my first post.
But now I see another problem if you read my file into single line I got an error 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. I need bring end of of line into my @sql
The only solution I see now read all lines into table with multiple rows and then concatenate them ? which is quite complex. Is there any way to deal with \n' in more compact way ? The code below produce error. GO should be on separate line alone. My global task to read 100+ files and run code inside. And I don't have any access to that source DB they came from, just need to deal with flat files. Don't have any option to do Right Click on database, etc..

I think probably it's wrong way at all, I can bring those files into TFS/SQL project and do something out of TFS, probably should be be some way to apply new codes...(?)

CREATE TABLE #imp (Col varchar(max))
BULK INSERT  #imp
FROM '//TFSNetwork/log/Install/sp_Test02.sql'
  WITH (ROWTERMINATOR = '\nzzzzz')      ---<< ?????
select top 1 @Sql = Col from #imp
 EXEC (@sql);

---  This is content of my sample file sp_Test02
USE MAGDA_Test
GO
SET ANSI_NULLS ON
GO
/*
Description:
*/
CREATE PROCEDURE [dbo].[sp_Test]   
AS  
BEGIN  

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;
    SELECT GETDATE() AS TS
END

Actual result: Msg 111, Level 15, State 1, Line 1
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Expected Result:  SP Created OK


--I suspect that SSMS/ doing some own stuff on line breaks, b'z this in this test we still have single line
DECLARE @sql NVARCHAR(MAX)   = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
SELECT @sql  --- 1 line
Print  @sql  --- 2 lineS

Answer 1

Use sqlcmd you can compose multiple entries for all files in .bat file and run in single click. This way it works fine with all CR/LF. Just get enough permissions for your db. Below is the contents of simple .bat file.Can use universal CREATE OR ALTER PROCEDURE inside your .sql

 @echo off
sqlcmd -S dbServ10 -U UserAcct -P UserPsw -i "//TFSNetwork/log/Install/sp_Test01.sql"
 sqlcmd -S dbServ10 -U UserAcct -P UserPsw -i "//TFSNetwork/log/Install/sp_Test02.sql"
...
...
 sqlcmd -S dbServ10 -U UserAcct -P UserPsw -i "//TFSNetwork/log/Install/sp_Test100.sql"