OK, finally figured this one out. I needed to set the batch="false"
attribute on the <sql> task. SQL Management Studio would generate a
script like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MyView] WITH SCHEMABINDING
AS
etc etc
By unbatching the script, the two SETs are called, then the CREATE VIEW,
which seems to insist on being the first statement to be executed.
Strangely, neither osql.exe nor sqlcmd.exe seemed too bothered about
this.
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David
Keaveny
Sent: Tuesday, 12 February 2008 1:52 PM
To: nant-users@lists.sourceforge.net
Subject: Re: [NAnt-users] Scripting SQL tables and views
I tried swapping the <sql> task for running osql.exe from <exec>, so my
build file looks like this:
<foreach item="Line"
in="D:\SVN\Classifieds\dev-jan-08\sql\Espresso\Scripts\DatabaseManifest.
txt" property="scriptname">
<exec program="osql.exe" commandline="-E -H localhost -d MyDatabase
-i Scripts\${scriptname}" failonerror="true" verbose="true" />
</foreach>
So the problem isn't with the scripts per se, it's how <sql> interacts
with them. I'll play around with a few more settings and see what
happens.
________________________________
From: David Keaveny
Sent: Monday, 11 February 2008 5:15 PM
To: nant-users@lists.sourceforge.net
Subject: Scripting SQL tables and views
I'm trying to automate the building of a database, and to that end, I've
used SQL Server Management Studio to script all the
tables/views/functions/procedures in my database, one script file per
object; I have a text file which lists the objects in the order they
need to be created in (to preserve dependencies - I wrote a little tool
that uses SQL SMO to work out dependencies, which is pretty neat). I
then use NAnt's <foreach> task to run through the file, and execute each
script using NAntContrib's <sql> task. So far, so good.
The problem comes when the <foreach> gets to the end of the list of
tables - as soon as it hits the views, the first view will generate the
following error:
Error while executing SQL statement.
'CREATE VIEW' must be the first statement in a query batch.
If I run the script file from the SQL Server Management Studio it runs
just fine. If I run that script using osql.exe, it also runs fine. I
haven't yet tried just using osql.exe called from <exec>, as I'd rather
use the built-in <sql> task for the sake of readability.
My build file looks like this:
<foreach item="Line" in="Scripts\DatabaseManifest.txt"
property="scriptname">
<sql connstring="Sql.Connection" source="Scripts\${scriptname}"
verbose="true" delimiter="GO" delimstyle="Line" print="true" />
</foreach>
Can anyone spot anything obviously wrong with my picture?
Regards,
David Keaveny
Senior Software Developer, Enterprise Applications, Fairfax Digital
________________________________
The information contained in this e-mail message and any accompanying
files is or may be confidential. If you are not the intended recipient,
any use, dissemination, reliance, forwarding, printing or copying of
this e-mail or any attached files is unauthorised. This e-mail is
subject to copyright. No part of it should be reproduced, adapted or
communicated without the written consent of the copyright owner. If you
have received this e-mail in error please advise the sender immediately
by return e-mail or telephone and delete all copies. Fairfax does not
guarantee the accuracy or completeness of any information contained in
this e-mail or attached files. Internet communications are not secure,
therefore Fairfax does not accept legal responsibility for the contents
of this message or attached files.
________________________________
The information contained in this e-mail message and any accompanying files is
or may be confidential. If you are not the intended recipient, any use,
dissemination, reliance, forwarding, printing or copying of this e-mail or any
attached files is unauthorised. This e-mail is subject to copyright. No part of
it should be reproduced, adapted or communicated without the written consent of
the copyright owner. If you have received this e-mail in error please advise
the sender immediately by return e-mail or telephone and delete all copies.
Fairfax does not guarantee the accuracy or completeness of any information
contained in this e-mail or attached files. Internet communications are not
secure, therefore Fairfax does not accept legal responsibility for the contents
of this message or attached files.
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
NAnt-users mailing list
NAnt-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nant-users
