We’re moving our forums to a new platform.

You can still browse and search existing topics, but you can no longer create new topics or post replies.

For questions, technical issues, or feature requests, please don’t hesitate to contact our support team at support@advancedinstaller.com.

Thank you for your understanding!

Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

SQLCMD custom action problems

Ive been fighting with a custom action for more than a week and am really in need of some guidance. I'm trying to run a LaunchFileFromProperty custom action to install an SQL database from a backup. Here's what the custom action's command looks like:

Code: Select all

"[RESULT_PROPERTY_SQLCMD_SEARCH]" -E -S "[SERVER_PROP]" -o "[TempFolder]sqllog.txt" -i "[AI_RESTORECRMGRSCRIPTED]" -v CRMGR_BACKUP="[AI_CRM.BAK]" PROG_FILES="[ProgramFilesFolder]"
The RestoreCRMgrScripted.sql file looks like this:

Code: Select all

RESTORE DATABASE [CRMgr] FROM  DISK = N'$(CRMGR_BACKUP)' WITH  FILE = 1,  MOVE N'RS3' TO N'$(PROG_FILES)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\CRMgr.mdf',  MOVE N'RS3_log' TO N'$(PROG_FILES)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\CRMgr_1.ldf',  NOUNLOAD,  STATS = 10
GO
If I run the custom action's command from a CMD prompt, and hard code all of the Property values, it works fine:

Code: Select all

sqlcmd -E -S %COMPUTERNAME%\SQLExpress -o C:\temp\sqllog.txt -i C:\Temp\RestoreCRMgrScripted.sql -v CRMGR_BACKUP="C:\Temp\CRMgr.bak" PROG_FILES="C:\Program Files"
I've been launching the installer from an elevated CMD prompt so I can log everything like this:

Code: Select all

"Demo Setup Project.exe" /L*V installation.log
When I peruse the installation.log file to see what the various property values are, I find these lines early on and all in the same section of the log file:
MSI (c) (8C:80) [16:07:34:479]: PROPERTY CHANGE: Adding RESULT_PROPERTY_SQLCMD_SEARCH property. Its value is 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe'.
MSI (c) (8C:80) [16:07:34:479]: PROPERTY CHANGE: Adding SERVER_PROP property. Its value is 'TEST-W2008SP2-3\SQLEXPRESS'.
MSI (c) (8C:80) [16:07:34:479]: PROPERTY CHANGE: Adding AI_RESTORECRMGRSCRIPTED property. Its value is 'C:\Users\mday\AppData\Local\Temp\RestoreCRMgrScripted.sql'.
MSI (c) (8C:80) [16:07:34:479]: PROPERTY CHANGE: Adding AI_CRM.BAK property. Its value is 'C:\Users\mday\AppData\Local\Temp\CRM.bak'.

I noticed that the TempFolder property is conspicuously missing from that section and isn't listed until much further down the log, so I tried the same command without the -o "[TempFolder]sqllog.txt" and it still didn't work.

The remaining properties on the Custom Action Properties page are as follows:
Execution Properties: Synchronous execution, ignore return code
Execution Options: Immediate execution
Scheduling options: Always execute
Execution stage condition: Install

We're running AI 9.6.1 on Windows 7.

Thanks in advance.
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: SQLCMD custom action problems

Hello and welcome to Advanced Installer forums,

Thank you for your interest in Advanced Installer.

Please keep in mind that in the "Command" field, from "Custom Action Properties" right pane, you should set only the command line parameters (i.e. -E -S "[SERVER_PROP]" ...) without the path of your exe file (RESULT_PROPERTY_SQLCMD_SEARCH - if this property contain the path of your exe file). The related RESULT_PROPERTY_SQLCMD_SEARCH property should be set in the "Property" field.

Also, can you run the related custom action as deferred with no impersonation and tell us if the behavior still persist? If so, can you attach a sample project which reproduce the behavior?

Let us know if this helped.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Hey Daniel:

Thanks for your quick reply. I just tried your recommended fix and it looks like it's real close now. The script did indeed run, but from the error message I received, I think that it's running after the temporary files have been deleted. Here are the contents of the sqllog.txt file:

Code: Select all

Msg 3201, Level 16, State 2, Server TEST-W2008R2-2\SQLEXPRESS, Line 1
Cannot open backup device 'C:\Users\mday\AppData\Local\Temp\CRM.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server TEST-W2008R2-2\SQLEXPRESS, Line 1
RESTORE DATABASE is terminating abnormally.
Thoughts?

Thanks again.
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: SQLCMD custom action problems

Hello,

I'm not sure why you are encountering this behavior. Please keep in mind that temporary files are copied on the target machine after the "Wizard Dialogs Stage -> Paths Resolution -> CostFinalize" standard action and removed after "Install Execution Stage -> Finish Execution -> InstallFinalize" standard action.

Can you please make sure that the SQL service account you are using has read and write permissions to the related folder location? Also, please take a look on the following links, which may help you:
- SQL 2005 backup: Operating system error5(access is denied) or Unable to export database
- Cannot open backup device. Operating System error 5
- Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.)

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Thanks, Daniel.

I got it working by creating my own folder under the Target Computer -> Windows Volume -> Temporary folder in the Files and Folders resource. Then I put my DB backup and SQL script in there and gave Everyone rights to the new subfolder. That's all it took.

I appreciate your help!
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: SQLCMD custom action problems

You're welcome. I'm glad you got this working.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Hi Daniel:

Well, I thought it was solved. Turns out that it only works sometimes, and always works if I delete the application (but not the support apps) and run it a second time. I found out that the first time I run it, the rights I gave to the TemporaryFolder don't always get applied, but they do if I run it again. Without those rights, I get the Access Denied error I reported earlier, and the restore of the database backup fails.

Any ideas?

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

Re: SQLCMD custom action problems

Hello Michael,

There are some well known permission issues with Windows Installer. Here's Filesystem permissions do not flow to sub folders thread which debates this.

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Thanks, Mihai!

This worked for me:

Code: Select all

cacls "[TempFolder]CRMgr" /t /e /c /p Everyone:f
I appreciate your assistance.
mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: SQLCMD custom action problems

You're welcome Michael, glad to help.
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Well good, Mihai, 'cause I got more. :D

Creating the database from a backup after installing SQLExpress 2008 works great, but now I'm looking at installing when the user already has one of the SQL 2008 versions installed. One of my test VMs has that situation, but the SQLBrowse service isn't enabled or running on it. I already found out how to enable the service from a command prompt:

Code: Select all

sc config SQLBrowser start= auto
and also how to start the service from a command prompt:

Code: Select all

net start sqlbrowser
So I created two LaunchFileAsAdmin custom actions. One for enabling the service that looks like this:

Code: Select all

/RunAsAdmin sc config SQLBrowser start= auto
and the other to start the service:

Code: Select all

/RunAsAdmin net start sqlbrowser
and hooked them up to the SQLConnectionDlg as InitEvents, in the order shown above, but that configuration doesn't appear to be working. The SQLBrowse service never starts.
The rest of the settings (for both custom actions) are:

Code: Select all

Synchronous execution, check return code
Immediate execution
Always execute
Thoughts?

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

Re: SQLCMD custom action problems

Hello Michael,

You should try to execute those command lines for the service outside the installer first and make sure they work as expected.

Also, please keep in mind the "Service Name" must match the declared "Service Name:" field of your service (case sensitive).
For example this custom action action has lower case letters for the service as opposed to the first:

Code: Select all

/RunAsAdmin net start sqlbrowser
All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Hi Mihai:

Everything DOES work as expected when run from a CMD prompt, and your example is identical to the one I included in my post. I think it might be a timing issue and that the installer is continuing before the service is finished being enabled and started. Would you please reread the details in my post before this one and tell me if there is a way to have it wait until the service has been enabled and started?

Thakn you.
mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: SQLCMD custom action problems

Hello Michael,
your example is identical to the one I included in my post
That is your custom action configuration which I copied from your last post. My point was to use the correct service name. Your custom actions refer to different service names (sqlbrowser vs SQLBrowser).

You can also try setting the custom action with a deferred execution option and see the outcome.

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Michael D
Posts: 11
Joined: Mon Oct 29, 2012 9:42 pm

Re: SQLCMD custom action problems

Hi Mihai:

My apologies for being so dense. Service names are case sensitive? OK, I'll change the name and give it a whirl.

Thanks.

Return to “Building Installers”