question

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