Solved

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

Posted on 2012-12-28
9
1,791 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 143

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 143

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A brief introduction to what I consider to be the best editor for PowerShell.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Viewers will learn how the fundamental information of how to create a table.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 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