SQL error pop-ups - DB exists, still errors.

[MNSwiftOne]

Installshield MSI project - added Connections only via Sql Scripts pane - are these causing errors - If I delete them, my use of these 'empty' connection strings fails... These errors show whether the database is present or not present - need help setting up connections or finding another way to use SQLRTInitialize2() and other SQL built-ins like SQLRTGetServers2(..).

The two pop-ups during installation (or uninstall) both say:
"Error 27502. Could not connect to Microsoft SQL Server".[DBNETLIB][ConnectionOpen(Connect().]SQL Server does not exist or access denied.(17)"

This occurs with the SQL 2005 named instance installed and running - default Windows Authenication was used - it was installed using the same user login...

What I am trying to do:
This is a Installshield MSI Project with many, many script lines far above this SQL Stuff... the rest works... just these errors I cannot get to go away.

I need to detect if a new SQL 2005 "MYPROD" named instance is present, if not detect if MSDE db for our product is there and back it up if present - then install 2005 named instance if not there and if prior MSDE was backed up - restore it otherwise install new db...

Am I am just not understanding how to use these IS 2008 calls?
I added two SQL connections under SQL Scripts - one for MSDE and one for SQL2005. Should I somehow use the machine name and machine name\MYPROD instance name in the script itself instead?

There is NO prompt for user to enter login information - the databases are ALWAYS, and must be during this install - on the same system (local), and Windows Authentication was used with prior products (MSDE) and the new product (2005).

Please - can someone provide a better way - that will not show error if dBs do not exist - or do exist as this error occurs...?
There has to be an easier way to detect installed dBs???

Thanks,
Todd

My current code:

// flag to indicate if SQL Server 2005 -WITH- an instance of MYPROD was found 
on NETWORK
BOOL bTQ2005Present;
// flag to indicate if found on THIS system
BOOL bTDHERE;
// flag to indicate if found on THIS system
BOOL bMYPRODTD2005HERE;
// flag to indicate if a ProdData database was found   
BOOL bProdDataPresent;
// flage to indicate if a Backup was made of existing data - to restore!
BOOL bProdDataBackedUp;
STRING szTDBackupPath;
         
function OnBegin()    
	HWND hMain;        
	STRING currentVersionInfo;
	 
	STRING szUserName, szPassword;         
	STRING szConnection, svName, szUser, szDB, szThisServer, sItem; 
	
	STRING szServer, szOldServer, szNewServer, sMisc;
	BOOL bOldServerFound, bNewServerFound;
	LIST allSERVERs; 
	LIST allDBs; 
	number i, nCount, nServerCount, nResult, nType, nvSize, dResult, sResult;
begin

// saw this suggestion in IS forum post - not sure if needed
MsiSetProperty( ISMSI_HANDLE, "IS_SQLSERVER_DO_NOT_USE_REG", "1" );

SQLRTInitialize2();

bProdDataPresent = FALSE;    // tells if product dB was found
bProdDataBackedUp = FALSE; // tells if dB was backed up
szTDBackupPath = ""; 
bTQ2005Present = FALSE;      // tells if 2005 named instance is present
bTDHERE = FALSE;                // tells if MSDE product dB found
bMYPRODTD2005HERE = FALSE; // tells if 2005 product dB found

// TRUE—Return only local database servers.
allSERVERs = SQLRTGetServers2 ( "MSTRSQLConnection", TRUE ); nServerCount = ListCount ( allSERVERs );
nResult = ListGetFirstString (allSERVERs, szServer); 
while (nResult != END_OF_LIST)  
    // for each possible SQL Server found - check:
    if (szServer % "(local)") then     	// this system
        // ONLY evaluate Local / this server
        if (StrFind ( szServer , "MYPROD" ) < 0) then
            // named instance MYPROD not found
            if (bTDHERE = FALSE) then
                 // ProdData not found yet on MSDE Server
		   	
               SQLRTGetConnectionInfo( "MSTRSQLConnection", szThisServer, szDB, szUser, szPassword);
                 allDBs = SQLRTGetDatabases ( "MSTRSQLConnection", szThisServer , TRUE , szUser , szPassword );
		    	              	
                 dResult = ListGetFirstString (allDBs, sItem); 
                 while (bTD2005HERE = FALSE && dResult != END_OF_LIST)
                     if ("ProdData" = sItem) then
                          // found ProdData on Server
                          bTDHERE = TRUE;
                          // set general found flag	
                          bProdDataPresent = TRUE; 
                     endif; 
                     dResult = ListGetNextString (allDBs, sItem);    	          
                 endwhile;                 
            endif;	// end local non-MSDE analyze
            ListDestroy (allDBs); 
        else     	
            // MYPROD named instance Found   		
                 if (bTQ2005Present = FALSE) then 
                     // found SQL 2005 with MYPROD Instance
                     bTQ2005Present = TRUE; // MYPROD Instance found		
                     // check for ProdData
                     SQLRTGetConnectionInfo( "MYPRODSQLConnection", szThisServer, szDB, szUser, szPassword);
                     allDBs = SQLRTGetDatabases ( "MYPRODSQLConnection", szThisServer , TRUE , szUser , szPassword );
	    		              	
                     dResult = ListGetFirstString (allDBs, sItem); 
                     while (bMYPRODTD2005HERE = FALSE && sResult != END_OF_LIST)
                         if ("ProdData" = sItem) then
                             // found ProdData on Server
                             bMYPRODTD2005HERE = TRUE;
                             if bProdDataPresent = FALSE then
                                 // set general found flag	
                                 bProdDataPresent = TRUE;   
                             endif;
                         endif; 
                         sResult = ListGetNextString (allDBs, sItem); 
                     endwhile;
                 endif;   // end MYPROD analyze
             endif;   // end MYPROD found 
          endif;   // end local
    	
          // get next server
          nResult = ListGetNextString (allSERVERs, szServer); 
     endwhile;
     ListDestroy (allSERVERs); 
}

Here is the complete algorithm sequence in detail to give you an idea of how these flags are used:
OnBefore function:
First detect if SQL 2005 named instance MYPROD exists
- If present set flag for use later.
- If not present - Detect if a MSDE dB is installed already
If it is - back it up - (works)

OnFirstUIBefore function:
- If MSDE dB was present and SQL2005 not present
backup MSDE dB via osql call (works)
- If SQL2005 db present - continue, nothing to do
else Install SQL 2005 named instance (works)

OnFirstUIAfter function:
- If dB was backed up - Restore it to SQL 2005 named instance
- else If SQL2005 dB not already present - Create DB (works)

This format seems to work when briefly tested - just need to get rid of the SQL errors.

[hidenori]

The following code is trying to get database names available on the server specified for the "MSTRSQLConnection" connection in the SQL Scripts view:

SQLRTGetConnectionInfo( "MSTRSQLConnection", szThisServer, szDB, szUser, szPassword);
allDBs = SQLRTGetDatabases ( "MSTRSQLConnection", szThisServer , TRUE , szUser , szPassword );

It looks like that you specify no server names for the connection. Therefore, you received the connection error. In order to solve it, you need to set a vaild server name to szThisServer before calling the SQLRTGetDatabases() function.

Hope that helps.