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'.Hi
The following will work!
File f.sql
:r F1.sql
GO
:r F2.sql
File F1.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
)
File F2.sql
CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
AS
BEGIN
IF @.@.ROWCOUNT = 0 RETURN
SET NOCOUNT ON
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
John
"Bevo" wrote:

> 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'.|||Thank you for the solution John, yet I was hoping there would be another way
.
This approach would require a seperate file for each trigger. Considering ou
r
database may have at least one trigger on each of our 100+ tables, this
results in alot of trigger files...
"John Bell" wrote:
> Hi
> The following will work!
> File f.sql
> :r F1.sql
> GO
> :r F2.sql
>
> File F1.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
> )
>
> File F2.sql
> CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> SET NOCOUNT ON
> 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
> John
>
> "Bevo" wrote:
>|||Hi
In general I would consider a trigger as a separate object to the table
anyhow, therefore they will be kept separately in version control. With
a decent file naming convention it is not difficult to know which
tables have triggers (something that you can not currently tell from
your files!), and the fact that are in different files make no
difference once you have a scripted build process.
John|||The file names provided were for demostrative purposes only.
Another limitation I have found is that osql will not allow nested :r files.
This is not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
This limits the source directory structure to just one tier, so one cannot
group functionality into tiered directories.
"John Bell" wrote:

> Hi
> In general I would consider a trigger as a separate object to the table
> anyhow, therefore they will be kept separately in version control. With
> a decent file naming convention it is not difficult to know which
> tables have triggers (something that you can not currently tell from
> your files!), and the fact that are in different files make no
> difference once you have a scripted build process.
> John
>|||Hi
I think that you may be trying to make this overly complicated.
http://tinyurl.com/5299q
John