osql using :r and GO

I have a script that calls other scripts using :r. I am using osql to run th
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.As you have noticed, the GO cannot be included in the :r script, because the
osql tool does not try to examine or parse the contents of the file, but
sends it directly to SQL Server for parsing, and the SQL Server doesn't like
GO.
Normally, GO is caught by osql itself, and never makes it to the back end.
Do you have to do this from within an osql session? A file with embedded GO
can be specified as a command line parameter to osql.
If the scripts have to call other scripts, can those other scripts be
editted at all? Instead of having a script with :r, maybe the script could
call xp_cmdshell which would call osql with the embedded script as a
parameter.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>I have a script that calls other scripts using :r. I am using osql to run
>the
> script. I am getting an error that seems to indicate GO is not allowed in
> a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||It seems :r files cannot be tiered, is this not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
"Kalen Delaney" wrote:

> As you have noticed, the GO cannot be included in the :r script, because t
he
> osql tool does not try to examine or parse the contents of the file, but
> sends it directly to SQL Server for parsing, and the SQL Server doesn't li
ke
> GO.
> Normally, GO is caught by osql itself, and never makes it to the back end.
> Do you have to do this from within an osql session? A file with embedded G
O
> can be specified as a command line parameter to osql.
> If the scripts have to call other scripts, can those other scripts be
> editted at all? Instead of having a script with :r, maybe the script could
> call xp_cmdshell which would call osql with the embedded script as a
> parameter.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>
>|||So use xp_cmdshell and don't use :r at all.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:170F0DC1-B0B1-4183-8540-34509092778D@.microsoft.com...
> It seems :r files cannot be tiered, is this not allowed:
> F1.sql
> :r F2.sql
> F2.sql
> :r F2_1.sql
>
> "Kalen Delaney" wrote:
>