SQL Server Connection Example

[agencysoft]

Howdy,

I'm looking at the SQL Server Connection example and I need to modify it a bit. I want AVAILABLESERVERS to ONLY return all SQL instances that contain the letters 'APRO' in them (my instance name). Using Parse String I can get the first or last instance, but without a while loop I'm not sure how to get them if there are more than 1 instance on the network.

Also, how can I get the machine name of the local machine if I want it to be the server? I need to update a UDL file with 'machine name\\instance' if the server is installed locally and I don't see a global variable to do this.

Thanks for looking.

Mitch McInelly

[MichaelNesmith]

1) Use Label and GoTo.
2) Use Get System Settings.

[agencysoft]

Thank you for that, it works beautifully now.

There is one more thing, however. When I run the Sql Connection example it sometimes drops the instance name off the end of the server name - so it will just say 'ServerName' instead of 'ServerName\\InstanceName'. It works sometimes and not others, even on the same machine.

Have you ever had anyone post a similar problem to this?

Much thanks
Mitch McInelly

[MichaelNesmith]

What do you mean exactly when you say "it drops"? Does the dialog lose data, or does the script corrupt the data captured in the dialog? What exactly happens?

[agencysoft]

The dialog that displays the instance names will sometimes show 'COMPUTERNAME\\INSTANCENAME' in the dropdown list - but will usually show only 'COMPUTERNAME'. My instance is called 'APRO' so it is not the standard 'SQLEXPRESS' instance.

[Raj]

Hi,
I'm looking at the SQL Server Connection example and I need to modify it a bit. I want AVAILABLESERVERS to ONLY return all SQL instances that contain the letters 'KeyExpress' in them (my instance name). I have read above solution. In that it says about using 1) Lable and GO to label 2) Get System setting. But I did get it so please give some detail solution for it.
The script in example is as per attached file, So where should I make the changes so I can get only instance name "KeyExpress"

File Attached:

server.jpg

[agencysoft]

Hi Raj,

This is what I did. I was looking for an instance named 'APRO'. You could easily modify this to just return the SERVERNAME only. Also, because I couldn't determine if I was just getting the servername, or the servername plus the instance name, this script actually tests each instance that either is just the servername, or ends in APRO, for the database that I'm looking for.

[DEFINE REGION: Detect SQL Server Instances]
label: Seek Servers
Display Dialog: sqlscan, use as progress dialog (non-modal)
Set Variable PROGRESSTEXT to Scanning Network for SQL Servers...

Detect MS SQL Instances : get result into variable FOUNDSERVERS
if Variable ABORT Equals TRUE
Terminate Installation
end
Set Variable AVAILABLESERVERS to
Set Variable PART1 to
Set Variable PART2 to
Set Variable CURRLINE to
Set Variable RESTLINES to
Set Variable SQLTEST to
Set Variable COUNT to 0

label: Redo
if Variable FOUNDSERVERS Contains (Ignore Case) $NEWLINE$
Parse String $FOUNDSERVERS$ into Variables CURRLINE and RESTLINES (Split at first occurrence of pattern)

Parse String $CURRLINE$ into Variables PART1 and PART2 (Split at first occurrence of pattern)
if Variable PART2 Equals
Set Variable PART2 to APRO
end

if Variable PART2 Contains (Ignore Case) APRO
Set Variable PROGRESSTEXT to Testing Server $PART1$ for AgencyPro
Microsoft SQL Server Script : Connect to database 001 on instance $PART1$\\$PART2$ as user sup (get result into variable SQLTEST)
if Variable ABORT Equals TRUE
Set Variable PROGRESSTEXT to User Cancelled!
Terminate Installation
end

if Variable SQLTEST Equals SUCCESS
Set Variable PROGRESSTEXT to Testing Server $PART1$ for AgencyPro - Success!
if Variable COUNT Equals 0
Set Variable AVAILABLESERVERS to $AVAILABLESERVERS$$PART1$\\APRO
else
Set Variable AVAILABLESERVERS to $AVAILABLESERVERS$$NEWLINE$$PART1$\\APRO
end
Set Variable COUNT to 1
else
Set Variable PROGRESSTEXT to Testing Server $PART1$ for AgencyPro - Failed
end
end


Set Variable FOUNDSERVERS to $RESTLINES$

if Variable SELECTEDSERVER Equals
Set Variable SELECTEDSERVER to $AVAILABLESERVERS$
end
GoTo Label: Redo

end

Hide Dialog
END REGION