| Author |
SMO not scripting procedures with owner?
|
|
|
|
I am trying to script stored procedures to a file (using SMO from a VB
program); I can't seem to get the file to be written qualified with the
owner; is there an easy way to do this? I had the SchemaQualify option
set, but it doesn't seem to be helping. Any pointers?
| |
| Andrea Montanari 2006-10-24, 6:31 pm |
| hi,
Jayme wrote:
> I am trying to script stored procedures to a file (using SMO from a VB
> program); I can't seem to get the file to be written qualified with
> the owner; is there an easy way to do this? I had the SchemaQualify
> option set, but it doesn't seem to be helping. Any pointers?
Dim ScriptOptions As New Microsoft.SqlServer.Management.Smo.ScriptingOptions
ScriptOptions.PrimaryObject = True
ScriptOptions.SchemaQualify = True
this works for me..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply
| |
|
| here is the snippet of code; is it not writing the owner name because I
am writing line by line? Is there a better way?
For Each udf In smoDatabase. UserDefinedFunctions
smoObjects = New Urn(0) {}
smoObjects(0) = udf.Urn
sc = scrpDatabase.Script(smoObjects)
If udf.IsSystemObject = False Then
For Each st In sc
writeSpLine.Write(st)
writeSpLine.Write(vbCrLf & "GO" & vbCrLf)
Next
End If
'writeSpLine.WriteLine("GO")
Next
| |
| Andrea Montanari 2006-10-24, 6:32 pm |
| hi,
Jayme wrote:
> here is the snippet of code; is it not writing the owner name because
> I am writing line by line? Is there a better way?
>
> For Each udf In smoDatabase. UserDefinedFunctions
>
> smoObjects = New Urn(0) {}
> smoObjects(0) = udf.Urn
> sc = scrpDatabase.Script(smoObjects)
try providing the 2 parameter,
scriptingOptions As Microsoft.SqlServer.Management.Smo.ScriptingOptions
, with the
ScriptOptions.PrimaryObject = True
ScriptOptions.SchemaQualify = True
set
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply
| |
|
| I still can't get it to work correctly..... what is missing?
--------------------------------------------------
Dim srvSMO As Server
Dim sc As StringCollection
Dim st As String
Dim smoDatabase As Database
Dim scrpDatabase As Scripter
Dim sp As StoredProcedure
Dim smoObjects(1) As Urn
Dim dbName As String
Dim writeSpLine As StreamWriter = New
StreamWriter("c:\SQLScript.txt")
Dim udf As UserDefinedFunction
Dim view As View
Dim strEntireFile As String
Dim ScriptOptions As
Microsoft.SqlServer.Management.Smo.ScriptingOptions
Me.Cursor = Cursors.WaitCursor
dbName = Me.txtDatabaseName.Text
srvSMO = New Server
'Reference database.
'smoDatabase = srvSMO.Databases("DirectorIT")
'(Form1.txtDatabaseName.Text)
smoDatabase = srvSMO.Databases(dbName)
'Define a Scripter object and set the required scripting
options.
scrpDatabase = New Scripter(srvSMO)
ScriptOptions = New ScriptingOptions
ScriptOptions.PrimaryObject = True
ScriptOptions.SchemaQualify = True
' Test
For Each udf In smoDatabase. UserDefinedFunctions
smoObjects = New Urn(0) {}
smoObjects(0) = udf.Urn
sc = scrpDatabase.Script(smoObjects)
If udf.IsSystemObject = False Then
For Each st In sc
writeSpLine.Write(st)
writeSpLine.Write(vbCrLf & "GO" & vbCrLf)
Next
End If
'writeSpLine.WriteLine("GO")
Next
| |
| Andrea Montanari 2006-10-24, 6:32 pm |
| hi,
Jayme wrote:
> I still can't get it to work correctly..... what is missing?
>
I tested your code using dbName = "AdventureWorks"... the (here abridged)
result is correct as expected..
<c:\SQLScript.txt>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].& #91;ufnGetAccounting
EndDate]()
RETURNS [datetime]
AS
BEGIN
RETURN DATEADD(millisecond,
-2, CONVERT(datetime, '20040701', 112));
END;
-- abridged
</c:\SQLScript.txt>
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply
| |
|
| It scripts the table/object correctly if the object I try to script was
prefixed with the owner name on build. ex: Create table test.test
but if I created the table without the owner ex: create table test
then the owner name is not scripted.
do you have these results?
Andrea Montanari wrote:
> hi,
>
> Jayme wrote:
>
> I tested your code using dbName = "AdventureWorks"... the (here abridged)
> result is correct as expected..
> <c:\SQLScript.txt>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE FUNCTION [dbo].& #91;ufnGetAccounting
EndDate]()
> RETURNS [datetime]
> AS
> BEGIN
> RETURN DATEADD(millisecond,
-2, CONVERT(datetime, '20040701', 112));
> END;
> -- abridged
> </c:\SQLScript.txt>
>
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> --------- remove DMO to reply
| |
|
| Once I added this option
ScriptOptions. EnforceScriptingOpti
ons = True
the schema name was scripted with the object. Thanks for all of your
help!
Jayme wrote:[color=darkred
]
> It scripts the table/object correctly if the object I try to script was
> prefixed with the owner name on build. ex: Create table test.test
>
> but if I created the table without the owner ex: create table test
> then the owner name is not scripted.
>
> do you have these results?
>
>
> Andrea Montanari wrote:
|
|
|
|