Solved

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

Posted on 2012-12-28
9
1,798 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

752 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