dbaSQL
asked on
Powershell / SMO -- scripting out SQL database ddl -- non dbo schema problem
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\SchemaExtra ct.ps1:97 char:20
+ $scrp.Script <<<< ($db.Tables["$Object"])
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes t
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
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\SchemaExtra
+ $scrp.Script <<<< ($db.Tables["$Object"])
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes
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
ASKER
Thank you, angeliii. Will try it asap, and let you know.
ASKER
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\SchemaExtra ct.ps1:96 char:14
+ $scrp.Script <<<< ($db.Tables["$Schema.$Obje ct"])
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes t
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.
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\SchemaExtra
+ $scrp.Script <<<< ($db.Tables["$Schema.$Obje
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes
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.
ASKER
any ideas?
ASKER
angeliii, or any other Expert suggestions on the powershell script?
ASKER
Really becoming fairly pressing. Does anyone have any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you very much, angeliii. i will get back to you with status.
ASKER
very helpful, angeliii. the dbo and other schema objects are handled properly. thank you very much.
$scrp.Script($db.Tables["$
by this line
$scrp.Script($db.Tables["$