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

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:
>