Getting unique table using PowerShell and SQL SMO

When accessing tables property of database in SMO, tables are indexed using name (excluding schema)
This is problematic when using different schemas.
To access a table using unique name use the following code


$instance = $args[0] [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)
$db=$srv.Databases["ExampleDatabase"]
$table = $db.Tables["ExampleTable"] # will get dbo schema, or random? 
$table = $db.Tables | Where-Object {$_.Schema -eq "dbo" -and $_.Name -eq "ExampleTable"} #will get your unique table

Comments

Post a Comment

Popular posts from this blog

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

How to fail sql server agent job if powershell script fails

Image
Problem 1 I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:  A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed. Problem 2 So instead I created a Operating System (CmdExec) step with calling pow...
Read more

Cause for Parameter is incorrect 0x80070057 error in ssis

Image
Possible cause for the cryptic error The package failed to load due to error  0x80070057 "The p arameter is incorrect.". This occurs when CPackage::LoadFrommXML fails.  ...when opening or executing and SSIS package In my case i created the ssis package using biml. And accidentally created a duplicate precedence constraint to another task twice. Removing the duplicate constraint and re-generating the package solved it.
Read more