I get this error when running the below powershell script; connecting to a remote server. The error occurs on during the "Extract Table List" step:
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL
Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow r
emote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
If I run the same script while connecting to a local instance, it works fine. Any ideas why?
$dbName = "TestDB"
$dbInstance = "TestRemoteServer\" + $dbname
$expFolder = "C:\mssql\export\"
$uid = 'TESTUSER'
$pwd = 'testpass'
$tablequery = "SELECT name from sys.tables"
$connectionTemplate = "Data Source={0};User ID = $uid; Password = $pwd;;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $dbInstance, $dbName)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
Write-Host "Connection Strings"
Write-Host "Connecting to Server"
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
Write-Host "Connected to Server"
Write-Host "Extract Table List"
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
