Solved

Powershell / SMO -- scripting out SQL database ddl -- non dbo schema problem

Posted on 2012-12-28
9
1,778 Views
Last Modified: 2013-01-15
Trying to traverse all objects in the SQL Server database, scripting the object creation to .sql file, to be stored within a source code repository.  

We are using Powershell calling SMO to create the DDL files.   Basically, it works well for all dbo schema objects.  Any other user defined schemas are not being processed correctly.  

The Powershell script is attached.  At line 21 you can see where we tried to handle the schema with this:    $Object=$Schema + "." + $Object

 
EXAMPLES:

This works:      
./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "dbo" "TableA"

This works, but the object is qualified with ‘dbo’, instead of ‘OtherSchema’.  ‘TableA’ exists within both schemas:
./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "OtherSchema" "TableA"  

This fails, presumably because ‘TableB’ is only in ‘OtherSchema’:
./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "OtherSchema" "TableB"
 
We have tried unsuccessfully to pass in the schema name.  When attempting to do so, this is the failure:
 
PS C:\mssql\tools> ./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "dbo" "TableA"
Directory: C:\temp\DatabaseName
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        12/21/2012   2:46 PM            Tables
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At C:\mssql\tools\SchemaExtract.ps1:97 char:20
+        $scrp.Script <<<< ($db.Tables["$Object"])
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest



We did this several years back with SMO -- no problems at all.  But, we had only the 'dbo' schema at that time.  Is anyone able to advise?
SchemaExtract.txt
0
Comment
Question by:dbaSQL
  • 7
  • 2
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726874
what if you do replace this line:
$scrp.Script($db.Tables["$Object"])
by this line
$scrp.Script($db.Tables["$Schema.$Object"])
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38728007
Thank you, angeliii.  Will try it asap, and let you know.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38728113
this is the error we get, angeliii:

PS C:\mssql\tools> ./schemaextract.ps1 "c:\temp" "SERVERNAME" "CTS" "Table" "
OtherSchema" "TableB"
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At C:\mssql\tools\SchemaExtract.ps1:96 char:14
+     $scrp.Script <<<< ($db.Tables["$Schema.$Object"])
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest


in this case, Table2 is there only in OtherSchema, not dbo.
If the table exists under 2 schemas, dbo and otherwise, the script works without error, but is only finding the dbo object.
If the object only exist in a schema other than dbo, the script errors out.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 17

Author Comment

by:dbaSQL
ID: 38732513
any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38737322
angeliii, or any other Expert suggestions on the powershell script?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38754521
Really becoming fairly pressing.  Does anyone have any suggestions?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38756273
this is what worked for me:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$SQL_SERVER"
$db=$s.Databases["$Database"]

$tbls = $db.Tables | where { $_.Name -eq "Object" }
$tbl = $tbls | where { $_.Schema -eq "$Schema" }

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.FileName = "c:\temp\test.sql"
$scrp.Options.ToFileOnly = $True

$scrp.Script($tbl)

Open in new window


you can use the "where" code easily to limit the objects array to be scripted to what you need ... this may help to simplify the work.

apart from that, the only thing that made the code "fail" is when the output folder was "write protected" ...

hope this helps
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38756389
thank you very much, angeliii.  i will get back to you with status.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 38779925
very helpful, angeliii.  the dbo and other schema objects are handled properly.  thank you very much.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Synchronize a new Active Directory domain with an existing Office 365 tenant
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question