Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerShell function to turn a SQL Server JDBC connection string into a set of SQL Compare connection args for command line calls.
# Function to create a string of SQL Compare switches from a SQL Server JDBC connection string
# $sourcetarget should be a 1 or 2 to determine whether the string should be the source or target db
function getSqlCompareArgs ($connectionsString, $sourcetarget){
$sSwitch = "/s$sourcetarget" + ": "
$dbSwitch = "/db$sourcetarget" + ": "
$uSwitch = "/u$sourcetarget" + ": "
$pSwitch = "/p$sourcetarget" + ": "
$s = ""
$db = ""
$u = ""
$p = ""
$compareArgs = ""
# Grabbing server name
$s = ((($connectionsString -split ";")[0]) -split "/")[2]
# Grabbing all properties from connection string and saving to Hash Table
$dbProperties = @{}
for ($i = 1; $i -lt ($connectionsString -split ";").length ; $i++){
$dbProperties.Add(((($connectionsString -split ";")[$i]) -split "=")[0], ((($connectionsString -split ";")[$i]) -split "=")[1])
}
# Checking properties for further connection details
if ($dbProperties.ContainsKey("serverName")){
$s = $dbProperties.Get_Item("serverName")
}
if ($dbProperties.ContainsKey("server")){
$s = $dbProperties.Get_Item("server")
}
if ($dbProperties.ContainsKey("instanceName")){
$s = "$s\$dbProperties.Get_Item('instanceName')"
}
if ($dbProperties.ContainsKey("portNumber")){
$s = $s + ":$dbProperties.Get_Item('portNumber')"
}
if ($dbProperties.ContainsKey("port")){
$s = $s + ":$dbProperties.Get_Item('port')"
}
if ($dbProperties.ContainsKey("database")){
$db = $dbProperties.Get_Item("database")
}
if ($dbProperties.ContainsKey("databaseName")){
$db = $dbProperties.Get_Item("databaseName")
}
if ($dbProperties.ContainsKey("password")){
$p = $dbProperties.Get_Item("password")
}
if ($dbProperties.ContainsKey("userName")){
$u = $dbProperties.Get_Item("userName")
}
if ($dbProperties.ContainsKey("user")){
$u = $dbProperties.Get_Item("user")
}
# Creating compare args string
$compareArgs = @(
" $sSwitch" + "$s ",
" $dbSwitch" + "$db ",
" $uSwitch" + "$u ",
" $pSwitch" + "$p "
)
# If integrated security compare args should not include password and username
if ($dbProperties.ContainsKey("integratedSecurity")){
$compareArgs = @(
" $sSwitch" + "$s ",
" $dbSwitch" + "$db "
)
}
return $compareArgs
}
@Alex-Yates
Copy link
Author

Alex-Yates commented Nov 5, 2015

CreateSqlCompareArgsFromJdbcString

PowerShell function to turn a SQL Server JDBC connection string into a set of SQL Compare connection args for command line calls.

I created this PS function to solve a problem that it turns out I didn't need to solve. As a result I'm not going to use it. I've partially tested it and it seems to work OK but I make no promises.

Since it took a fair amount of time to create I didn't want to throw it away so I'm saving it here for anyone else that could benefit from it.

It converts a connection string in the format defined here: https://msdn.microsoft.com/en-us/library/ms378428(v=sql.110).aspx

To an array of SQL Compare args in the format:

@(" /s1:" + "Server\Instance " + " /db1:" + "Database " + " /u1:" + "Username " + " /p1:" + "Password ")

The $servertarget variable should either be a 1 or 2 to determine whether these args are for a source or target DB.

It works for all the examples listed on the MS docs page listed above.

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment