MDAC SQL Server Script does not appear to run

[inbiz]

I am using installAware 9 R2 (Moya.sp3.070910) evaluation
The target platform is Windows 7 Home 64bit
We are installing Server Express 2005 SP3 (x64) runtime as part of the installation.
The IA samples and plugins checksql2005_x64 have been utilised to allow the user to locate the instance.


The problem occurs in line:

Code: Select all

Microsoft SQL Server Script : Connect to database TestDB on instance $SERVERNAME$\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)

I know that the code is being reached as the database "create database if absent" is created the associated script does not run.
there are no errors in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG relating to the script nor any evidence of it running.

the SQL script is:

Code: Select all

USE [master]
GO
sp_grantlogin 'BUILTIN\Users'
GO
CREATE DATABASE MyApp ON
( FILENAME = N'c:\Program Files (x86)\MyApp\Data\MyApp.mdf' ),
( FILENAME = N'c:\Program Files (x86)\MyApp\Data\MyApp_log.LDF' )
FOR ATTACH ;
GO

I have tried with and and without variable paths in the script.

Post installation I can run a manual script from the command prompt containing the same SQL code and the attach works perfectly.

Secondly, to work around the first issue I have attempted to include a run program line:
Run Program sqlcmd -S .\MYINSTANCE -U SA -P $Strong$Apa$$word -iattach_sql.sql
This also does not appear to run. I have enabled logging in [HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer] with "Logging"= voicewarmupx but my log does not report the failure or success of this line.
Should I expect feedback in the log?

You assistance would be appreciated so we can settle the version of IA required and settle our purchase.

Thanks,

Nathan.

[inbiz]

Okay... here is the real problem..
The reason the script is failing is that the database files to attach are not in the folder at the time the script is executed.
I had turned off the abort if error option hoping to get some error reporting from SQL.
But it appears to simply bomb out without reporting (unless you can direct me to where better logging can be found)

How can I either delay the execution of the line:
Microsoft SQL Server Script : Connect to database test on instance $SERVERNAME$\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)

or: bring forward the copy of the database.mdf files?

I have modelled a testapp on the IA SQL Connection example which has only been modified to include my attach script and copy the files.

Code: Select all

Comment: SQL Server Connection Example with <<< 2 ADDED LINES
Comment: =============================
Install Files C:\Program Files\TestApp\Data\*.* to $TARGETDIR$\Data, include subfolders   <<<-- ADDED 
Apply Install       <<<-- ADDED 

[DEFINE REGION: Initialize Variables]
Comment: Declare variables used by script plug-ins and the wizard dialogs
Set Variable SELECTEDSERVER to 
Set Variable AVAILABLESERVERS to 
Set Variable SQLUSER to 
Set Variable SQLPASSWORD to 
Set Variable SQLTEST to 
Set Variable WINDOWS to TRUE
Set Variable SERVER to FALSE
<<Code Folding Region>>
 
[DEFINE REGION: Detect SQL Server Instances]
Comment: Display progress dialog (detection may take a few moments)
Display Dialog: sqlscan, use as progress dialog (non-modal)
 
Comment: Use MS SQL Instances plug-in to detect instances
Detect MS SQL Instances : get result into variable AVAILABLESERVERS
Comment: The list of servers returned from the plug-in is stored in a single variable, seperated by new line characters
 
Comment: If the Cancel button was clicked during progress, exit immediately
if Variable ABORT Equals TRUE
  Terminate Installation
end
 
Comment: Trim the last new line character out of the list of servers
Parse String $AVAILABLESERVERS$ into Variables AVAILABLESERVERS and  (Split at last occurrence of pattern)
Comment: Obtain the first server from the list of servers to initialize default server selection
Parse String $AVAILABLESERVERS$ into Variables SELECTEDSERVER and  (Split at first occurrence of pattern)
 
Comment: Hide the progress dialog
Hide Dialog
<<Code Folding Region>>
 
[DEFINE REGION: Show SQL Connection Dialog]
Comment: Define loop for selecting a SQL Server
label: Select SQL Server
 
Comment: Display list of servers and optional connection fields
Display Dialog: sqlinstance, wait for dialog to return (modal)
 
if Variable WIZARD Equals CANCEL
  Comment: Immediately exit if cancelled
  Terminate Installation
end
<<Code Folding Region>>
 
[DEFINE REGION: Test SQL Connection]
Comment: Display connection progress dialog (testing the connection can take a few moments)
Display Dialog: sqlwait, use as progress dialog (non-modal)
 
Comment: Obtain the SQL Server machine name and instance name
Parse String $SELECTEDSERVER$ into Variables SERVERNAME and INSTANCENAME (Split at first occurrence of pattern)
if Variable INSTANCENAME Equals 
  Comment: An empty instance name indicates the default instance
  Set Variable INSTANCENAME to SQLEXPRESS
end
 
Comment: If Windows Authentication has been selected, set an empty user name
if Variable WINDOWS Equals TRUE
  Comment: An empty user name instructs the MS SQL Server plug-in to use Windows authentication
  Set Variable SQLUSER to 
  Comment: A non-empty user name instructs the MS SQL Server plug-in to use Server authentication
end
 
Comment: Test the connection
Microsoft SQL Server Script : Connect to database TestApp on instance $SERVERNAME$\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)
if Variable ABORT Equals TRUE
  Comment: Exit immediately if cancelled
  Terminate Installation
end
 
Comment: Testing complete
if Variable SQLTEST Equals SUCCESS
  MessageBox: Success!, Connection test was successful.
  Hide Dialog
  else
  MessageBox: Error!, Connection failed. Would you like to try again?
  Hide Dialog
  if Variable WIZARD Equals RETRY
    Comment: Test failed, and retry desired; loop back to the connection dialog
    GoTo Label: Select SQL Server
  end
end
<<Code Folding Region>>

Thanks,

Nathan.

[mills]

Perhaps you could write a program or dll that will either just pause for a predetermined amount of time or check the status of the files/database and when the conditions are met, returns a value (return 0 when conditions are met, -1 after a timeout)...

[inbiz]

Thanks Mills for your reply.
I am close to a solution -

What I have now done is create two lines of:
Microsoft SQL Server Script : Connect to database test on instance $SERVERNAME$\$INSTANCENAME$ as user $SQLUSER$ (get result into variable SQLTEST)
The first is used for the SQLTEST to ensure the connection is fine and has no body in the script.
I think the problem running the script in the body at this point is that the files are not on the target as yet.
At the very end of the installer, the second call contains the script in the body.
This works and fires the script and even reports errors if encountered in the execution.. so all good from an installer perspective.
This is all fine if the folder is fixed - but in reality the user can install anywhere...
I am now applying variables in the filename for the body eg: '$TARGETDIR$\Data\MyDB.mdf' these are however translating to:
‘C:\Program Files (x86)\\MyApp\Data\MyDB.mdf' on the target. (NOTE THE DOUBLE BACK SLASH AFTER (x86))
Which causes problems when the ODBC SQL driver address the path...

[inbiz]

Fixed this double back slash with $PROGRAMFILES$$TITLE$ instead of $TARGETDIR$
Script is now running and dynamic.

Thanks!

[mills]

Glad I could give you a little push in the right direction!