eurotronik
Posts: 17
Joined: Tue Jan 10, 2012 1:01 pm

How to run CMDSQL for SQL scripts

I have few sql scripts which have to be run during install. But for first script, which also creates database I have to run CMDSQL from MSSQL 2008R2.

If I pre-create database and run all other scripts which dont need CMDSQL, everything works perfectly.

So, how to run CMDSQL before running first scirpt?

I found an answer on forum but I dont understand it perfectly:
Ionut wrote:Hi Kiran,

First, you need to create a File Search in the Search page for "sqlcmd.exe" and name it for instance SQLCMD. Right-click on the search and select "Add Location -> Folder", specifying the following parameters:

- Path : [ProgramFilesFolder]Microsoft SQL Server
- Depth : 5

If you are using SQL Server 2000/MSDE, search for "osql.exe" instead of "sqlcmd.exe". Use the "Test Search" toolbar button in order to make sure the Search is configured correctly.

In the Custom Actions page, create a new "EXE with Working Dir" Custom Action under Install, with the following parameters:

- Working Dir: (any)
- Full Path:

Code: Select all

"[SQLCMD]" -S .\InstanceName -U user -P password -i "[#RestoreScript.sql]"
- Execution Properties: Synchronous execution, ignore return code
- Execution Options: Deferred with no impersonation
- Execution Condition: (Not Installed) AND SQLCMD

Replace SQLCMD with the Property you have used to name your Search and instead of "RestoreScript.sql" select the SQL script file from your package (use the "Edit" button at the right of the "Full Path" field).

Hope this helps.

Regards,
Ionut
Thank you for answer. :?
eurotronik
Posts: 17
Joined: Tue Jan 10, 2012 1:01 pm

Re: How to run CMDSQL for SQL scripts

I also found this, but I dont know how that

Code: Select all

sqlcmd -E -i restoredb.sql -v path="[MY_DIR]"
works?

Do I have to put sql file in App folder and run that cmd code from Costum actions?

Code: Select all

sqlcmd -E -i NurseLog.Database.sql -v path="[APPDIR]"
cosmin wrote:Hi,
I'm aware that it seems only possible to perform a database restore by executing a script using 'sqlcmd.exe' and not directly with Advanced Installer (has this changed since?).
Please note that Advanced Installer allows you to restore databases by running SQL scripts. However, the MS SQL Server usually hang in the restoring state (this is a MS SQL Server problem). Therefore, if you are using MS SQL server it is recommended to use "sqlcmd.exe" (you can also try using the SQL Scripts page).
Problem is a script will need hard coded paths to the database backup file and MDF restore paths.
The sqlcmd.exe utility supports the -v parameter which allows you to define variables. These variables can be set to installer properties and used inside the script. Here is a sample command line:

Code: Select all

sqlcmd -E -i restoredb.sql -v path="[MY_DIR]"
where "MY_DIR is a property which contains the path you need.

Regards,
Cosmin
mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: How to run CMDSQL for SQL scripts

Hello,
Do I have to put sql file in App folder and run that cmd code from Costum actions?
That's correct, as Ionut mentioned in the example from the first post on this thread:
Replace SQLCMD with the Property you have used to name your Search and instead of "RestoreScript.sql" select the SQL script file from your package (use the "Edit" button at the right of the "Full Path" field).
Let's refer to Ionut's example. Can you please specify what part was unclear when you tried to implement the functionality step by step into your project?

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
eurotronik
Posts: 17
Joined: Tue Jan 10, 2012 1:01 pm

Re: How to run CMDSQL for SQL scripts

Now I have sql file (database.sql) in APPDIR.

So I create a costum action and put this details in. Database file is in application folder.

Image

But this is not working... I dont have user and password of SQL server because SQL management studio is installed as prerequisite and used with windows auth.

Will this script run before other scripts which are in SQL scripts?

Must SQLCMD.exe be in APPDIR too?
mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: How to run CMDSQL for SQL scripts

Hello,
Will this script run before other scripts which are in SQL scripts?
To execute a custom action before the SQL scripts are installed, you must schedule it before InstallODBC standard action in the InstallExecuteSequence:
http://www.advancedinstaller.com/user-g ... tions.html
Must SQLCMD.exe be in APPDIR too?
Cosmin gave only a usage sample command line. Ionut described how to search for SQLCMD on the target machine and reference the result in the custom action.

To implement the functionality in your project you should follow Ionut's example and then construct the appropriate command line by reading this article:
http://msdn.microsoft.com/en-us/library/ms162773.aspx

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”