0

I have a .sql file that was created using the MS SQL Management Studio Script Wizard. It contains the entire database schema including views, stored procedures, tables, indexes, etc.

I’m looking for a way to automate the process of creating the database with ColdFusion or Java or even .NET.

I’ve tried using CFQUERY but it throws all kinds of errors with the .sql file.

I’ve tried using ANT with sqljdbc4.jar but it also fails on the first line with a “[sql] Failed to execute: BEGIN TRAN” com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ''.

Micah
  • 1,221
  • 17
  • 42

3 Answers3

3

I highly doubt you can use the scripts generated by the wizard outside of an MS tool. The wizard makes copious use of GO which is not standard T-SQL. Also, commands like CREATE PROCEDURE need to be the first statement within the batch. To run the wizard scripts, you need to use an MS tool like sqlcmd.exe. With the correct permissions, you should be able to run sqlcmd.exe from <cfexecute>.

Leigh
  • 28,765
  • 10
  • 55
  • 103
  • Thanks Leigh. I discovered sqlcmd.exe shortly after posting the question. It works perfectly. – Micah Oct 24 '11 at 21:19
1

You should be able to execute all the SQL within a <cfquery assuming you have the appropriate permissions.

We generate all our databases like this with CREATES etc in a ColdFusion <cfquery

Do you have the appropriate permissions, what errors are you getting.

Dale Fraser
  • 4,623
  • 7
  • 39
  • 76
  • 1
    The wizards usually add client `GO` commands, which do not work within a cfquery. – Leigh Oct 21 '11 at 02:12
  • Aside from removing non-standard T-SQL ("GO" statements are pretty rampant), also check that the DSN settings allow for multiple statements to be run. I haven't setup an MSSQL DSN in a while, but this is how you do it for MySQL: https://img.skitch.com/20111021-d9m2f62wg6983wc5myt7qhnqji.png – Adam Tuttle Oct 21 '11 at 02:30
  • 1
    FYI: Unlike mySQL, multiple statements work out right of the box for MS SQL DSN's. – Leigh Oct 21 '11 at 03:02
0

If you're using ORM, you may set this.ormsettings.sqlscript to point to your .sql file in Application.cfc and it'll be run on ORMReload() when this.ormsettings.dbcreate is set to dropcreate.

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSED380324-6CBE-47cb-9E5E-26B66ACA9E81.html

Good luck

Henry
  • 32,689
  • 19
  • 120
  • 221