question

rsheik avatar image
rsheik asked

Exception calling EnumScript with 1 arguement(s) . Failed to connect to server

Hi I am Running Powershell script which scripts all the SQL Objects into separate file for each object. this is my sample code.. Function powershell_main { # defining parameters [CmdletBinding()] param( [Parameter(Mandatory=$True)] [string[]]$server, [Parameter(Mandatory=$True)] [string[]]$user, [Parameter(Mandatory=$True)] [string[]]$password, [Parameter(Mandatory=$True)] [string[]]$databases, [Parameter(Mandatory=$True)] [string[]]$output_path, [Parameter(Mandatory=$True)] [string[]]$DBSchema ) BEGIN { Write-Host "Process started at :" (Get-Date) } PROCESS { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server #This sets the connection to mixed-mode authentication $srv.ConnectionContext.LoginSecure=$false; #This sets the login name $srv.ConnectionContext.set_Login($user); #This sets the password $srv.ConnectionContext.set_Password($password) try { write-host "Testing Connection with server:" $server # if we don't have connection this will throw exception write-host $srv.Databases } catch { write-host "Caught an exception:" write-host "Exception Type: $($_.Exception.GetType().FullName)" write-host "Exception Message: $($_.Exception.Message)" Write-Host "Server not found or Invalid credentials " Exit } # here onwards total process runs for each db # Iterate on each database # creating dictionary for holding result of process for each db # so that we can print dictionary as a table # $db_result = [ordered]@{} # $db_result = @{} $db_result = "Database `t`t` Result" foreach ($db in $databases) { try { $success_fail = "Failed" $database = $db write-host "Connected to the server: " $server write-host "Checking whether database" $database "Exists or not" # get all databases and check whether our database is existing in the list $ck_Database = $srv.Databases | where {$_.Name -eq $database} if ($ck_Database -eq $null) { write-host "Database " $database "does not exists" continue } write-host "Database" $database " Exists..." write-host "Checking whether Schema" $dbSchema "Exists or not" # get all Schemas and check whether our Schema is existing in the list $ck_Schema = $ck_Database.Schemas | where {$_.Name -match $dbSchema} if ($null -eq $ck_Schema) { write-host "Schema: " $dbSchema "does not exists for given credentials" continue } write-host "Schema: " $dbSchema " exists" write-host "Generating Scripts for " $database # setting folder structure $schema = $dbSchema $table_path = "$output_path\"+$schema+"\"+$database+"\Table\" $storedProcs_path = "$output_path\"+$schema+"\"+$database+"\StoredProcedure\" $views_path = "$output_path\"+$schema+"\"+$database+"\View\" $udfs_path = "$output_path\"+$schema+"\"+$database+"\UserDefinedFunction\" $textCatalog_path = "$output_path\"+$schema+"\"+$database+"\FullTextCatalog\" $udtts_path = "$output_path\"+$schema+"\"+$database+"\UserDefinedTableTypes\" $db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") $tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table") $scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server) # Get the database and table objects $db = $srv.Databases[$database] #$Just_Check = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $storedProcs = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $views = $db.Views | Where-object { $_.schema -eq $schema } $udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $catlog = $db.FullTextCatalogs $udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema } # Set scripter options to ensure only data is scripted $scripter.Options.ScriptSchema = $true; $scripter.Options.ScriptData = $false; #Exclude GOs after every line $scripter.Options.NoCommandTerminator = $false; $scripter.Options.ToFileOnly = $true $scripter.Options.AllowSystemObjects = $false $scripter.Options.Permissions = $true $scripter.Options.DriAllConstraints = $true $scripter.Options.SchemaQualify = $true $scripter.Options.AnsiFile = $true $scripter.Options.SchemaQualifyForeignKeysReferences = $true $scripter.Options.Indexes = $true $scripter.Options.DriIndexes = $true $scripter.Options.DriClustered = $true $scripter.Options.DriNonClustered = $true $scripter.Options.NonClusteredIndexes = $true $scripter.Options.ClusteredIndexes = $true $scripter.Options.FullTextIndexes = $true $scripter.Options.EnforceScriptingOptions = $true function CopyObjectsToFiles($objects, $outDir) { if (-not (Test-Path $outDir)) { [System.IO.Directory]::CreateDirectory($outDir) } foreach ($o in $objects) { if ($o -ne $null) { $schemaPrefix = "" if ($o.Schema -ne $null -and $o.Schema -ne "") { $schemaPrefix = $o.Schema + "." } $scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql" Write-Host "Writing " $scripter.Options.FileName $scripter.EnumScript($o) } } } # Output the scripts CopyObjectsToFiles $tbl $table_path CopyObjectsToFiles $storedProcs $storedProcs_path CopyObjectsToFiles $views $views_path CopyObjectsToFiles $catlog $textCatalog_path CopyObjectsToFiles $udtts $udtts_path CopyObjectsToFiles $udfs $udfs_path $success_fail = "Success" } catch { write-host "Caught an exception:" write-host "Exception Type: $($_.Exception.GetType().FullName)" write-host "Exception Message: $($_.Exception.Message)" write-host "Exception Occurred while generating scripts for " $database continue } finally { # inserting column name and lines $db_result = $db_result + "`n"+$database+"`t`t"+$success_fail } } } END { Write-Host "`nPrinting overall result for section ." Write-Host $db_result Write-Host "Process ended at :" (Get-Date) } } This is working well on my win7 system perfectly. (SqlServer 2008 R2 is there in same system) But when Iam trying to run this script on win2008R2 server (Have SQLserver 2008 R2) it is throwing error (screen shot) ![alt text][1] Please suggest where I have to look in . (User Name and password are hidden for security ) Thanks. [1]: /storage/temp/2346-222.jpg
powershellsqlserver 2008r2smo
222.jpg (184.2 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
I suspect you don't have the correct permissions to write the file from the login that is running the script. Validate which login is doing the work and ensure that it has the permissions needed to write that file.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.