Building an MSSQL database with ColdFusion or Java from an ".sql" file?

Question

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 ''.

Answer 1

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

Answer 2

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.

Answer 3

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