Saving all triggers to a script

Hi,
Is there a simple way to save all triggers/ and or SP's to a Create script
in the same whay that you can save an individual trig/SP by right clicking it?
Thanks for any ideas on this is advance
Cheers
AntAnt
When you click on Generate Scripts menu , you have Options tab up to the
window. Click on it and check Script Triggers option
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
> Hi,
> Is there a simple way to save all triggers/ and or SP's to a Create script
> in the same whay that you can save an individual trig/SP by right clicking
> it?
> Thanks for any ideas on this is advance
> Cheers
> Ant|||Hi Uri,
Thanks for your help though when I tried it, it simply created a script full
of exec dbOptions. Is this what I should be expecting?
I was hoping for a sql file filled with create trigger statements.
In Enterprise Manager, I went to Tools> Generate Scripts>
Checked thew Generate Create for each objecte & unchecked the Drop;
Security Scripting Options = Script Database (Had to check this/ didn't want
to)
Table Scripting Options = Script Triggers
But it didn't seem to work
Any ideas on this?
Cheers
Ant
"Uri Dimant" wrote:
> Ant
> When you click on Generate Scripts menu , you have Options tab up to the
> window. Click on it and check Script Triggers option
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
> > Hi,
> >
> > Is there a simple way to save all triggers/ and or SP's to a Create script
> > in the same whay that you can save an individual trig/SP by right clicking
> > it?
> >
> > Thanks for any ideas on this is advance
> >
> > Cheers
> > Ant
>
>|||Ant
What version are you using? In SQL Server 2005 (with SP2 I think) you are be
able to script the objects per file
SQL Server 2000 , you can use SQL DMO object library
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath,,SQLDMOScript2_NoCollation
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script intOptions, StrFilePath
End If
Next
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:32A3B041-7CC3-44F9-803E-AB805E8356BC@.microsoft.com...
> Hi Uri,
> Thanks for your help though when I tried it, it simply created a script
> full
> of exec dbOptions. Is this what I should be expecting?
> I was hoping for a sql file filled with create trigger statements.
>
> In Enterprise Manager, I went to Tools> Generate Scripts>
> Checked thew Generate Create for each objecte & unchecked the Drop;
>
> Security Scripting Options = Script Database (Had to check this/ didn't
> want
> to)
> Table Scripting Options = Script Triggers
>
> But it didn't seem to work
> Any ideas on this?
> Cheers
> Ant
> "Uri Dimant" wrote:
>> Ant
>> When you click on Generate Scripts menu , you have Options tab up to the
>> window. Click on it and check Script Triggers option
>>
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
>> > Hi,
>> >
>> > Is there a simple way to save all triggers/ and or SP's to a Create
>> > script
>> > in the same whay that you can save an individual trig/SP by right
>> > clicking
>> > it?
>> >
>> > Thanks for any ideas on this is advance
>> >
>> > Cheers
>> > Ant
>>|||HI Uri,
Thank you, this will be applied to SQL2000 so it looks like the DMO object
library is the go.
Thanks very much for your help on this. Much appreciated
Ant
"Uri Dimant" wrote:
> Ant
> What version are you using? In SQL Server 2005 (with SP2 I think) you are be
> able to script the objects per file
>
> SQL Server 2000 , you can use SQL DMO object library
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
>
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Tables and Triggers, ignoring system
> ' tables and system generated triggers
> For Each genObj In db.Tables
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath,,SQLDMOScript2_NoCollation
> For Each objTrigger In genObj.Triggers
> If objTrigger.SystemObject = False Then
> objTrigger.Script intOptions, StrFilePath
> End If
> Next
> End If
> Next
>
> MsgBox "Finished generating SQL scripts."
> End Sub
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:32A3B041-7CC3-44F9-803E-AB805E8356BC@.microsoft.com...
> > Hi Uri,
> >
> > Thanks for your help though when I tried it, it simply created a script
> > full
> > of exec dbOptions. Is this what I should be expecting?
> >
> > I was hoping for a sql file filled with create trigger statements.
> >
> >
> > In Enterprise Manager, I went to Tools> Generate Scripts>
> > Checked thew Generate Create for each objecte & unchecked the Drop;
> >
> >
> > Security Scripting Options = Script Database (Had to check this/ didn't
> > want
> > to)
> > Table Scripting Options = Script Triggers
> >
> >
> > But it didn't seem to work
> >
> > Any ideas on this?
> >
> > Cheers
> >
> > Ant
> >
> > "Uri Dimant" wrote:
> >
> >> Ant
> >> When you click on Generate Scripts menu , you have Options tab up to the
> >> window. Click on it and check Script Triggers option
> >>
> >>
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
> >> > Hi,
> >> >
> >> > Is there a simple way to save all triggers/ and or SP's to a Create
> >> > script
> >> > in the same whay that you can save an individual trig/SP by right
> >> > clicking
> >> > it?
> >> >
> >> > Thanks for any ideas on this is advance
> >> >
> >> > Cheers
> >> > Ant
> >>
> >>
> >>
>
>|||Another option is SMO. I would stay away from DMO as it's deprecated.
Here's a console app that uses SMO that will do this for you:
http://www.codeplex.com/scriptdb
On Sep 3, 1:54 am, Ant <A...@.discussions.microsoft.com> wrote:
> HI Uri,
> Thank you, this will be applied to SQL2000 so it looks like the DMO object
> library is the go.
> Thanks very much for your help on this. Much appreciated
> Ant
> "Uri Dimant" wrote:
> > Ant
> > What version are you using? InSQLServer 2005 (with SP2 I think) you are be
> > able toscriptthe objects per file
> >SQLServer 2000 , you can useSQLDMO object library
> > Sub ScriptDB(strLogin As String, strPwd As String, _
> > strDataBase As String, StrFilePath As String)
> > DimsqlAs Object
> > Dim db As Object
> > Dim objTrigger As Object
> > Dim intOptions As Long
> > Dim genObj
> > Setsql= CreateObject("SQLDMO.SQLServer")
> > Set db = CreateObject("SQLDMO.Database")
> > Set objTrigger = CreateObject("SQLDMO.Trigger")
> > Const sDrops As Integer = 1
> > Const sIncludeHeaders As Long = 131072
> > Const sDefault As Integer = 4
> > Const sAppendToFile As Integer = 256
> > Const sBindings As Integer = 128
> > Const SQLDMOScript2_NoCollation As Long = 8388608
> > ' Set scripting options. Because you need to specify multiple behaviors
> > ' for the ScriptType argument, you use "Or" to combine these.
> > intOptions = sDrops Or sIncludeHeaders Or _
> > sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> > ' Connect to local server
> > sql.Connect "(local)", strLogin, strPwd
> > Set db =sql.Databases(strDataBase, "dbo")
> > 'ScriptTables and Triggers, ignoring system
> > ' tables and system generated triggers
> > For Each genObj In db.Tables
> > If genObj.SystemObject = False Then
> > genObj.ScriptintOptions, StrFilePath,,SQLDMOScript2_NoCollation
> > For Each objTrigger In genObj.Triggers
> > If objTrigger.SystemObject = False Then
> > objTrigger.ScriptintOptions, StrFilePath
> > End If
> > Next
> > End If
> > Next
> > MsgBox "Finished generatingSQLscripts."
> > End Sub
> > "Ant" <A...@.discussions.microsoft.com> wrote in message
> >news:32A3B041-7CC3-44F9-803E-AB805E8356BC@.microsoft.com...
> > > Hi Uri,
> > > Thanks for your help though when I tried it, it simply created ascript
> > > full
> > > of exec dbOptions. Is this what I should be expecting?
> > > I was hoping for asqlfile filled with create trigger statements.
> > > In Enterprise Manager, I went to Tools>GenerateScripts>
> > > Checked thewGenerateCreate for each objecte & unchecked the Drop;
> > > Security Scripting Options =ScriptDatabase (Had to check this/ didn't
> > > want
> > > to)
> > > Table Scripting Options =ScriptTriggers
> > > But it didn't seem to work
> > > Any ideas on this?
> > > Cheers
> > > Ant
> > > "Uri Dimant" wrote:
> > >> Ant
> > >> When you click onGenerateScripts menu , you have Options tab up to the
> > >> window. Click on it and checkScriptTriggers option
> > >> "Ant" <A...@.discussions.microsoft.com> wrote in message
> > >>news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
> > >> > Hi,
> > >> > Is there a simple way to save all triggers/ and or SP's to a Create
> > >> >script
> > >> > in the same whay that you can save an individual trig/SP by right
> > >> > clicking
> > >> > it?
> > >> > Thanks for any ideas on this is advance
> > >> > Cheers
> > >> > Ant|||Its SQL Server 2000 you cannot use SMO
"Jesse" <google@.jessehersch.fastmail.fm> wrote in message
news:1188941936.583258.128870@.57g2000hsv.googlegroups.com...
> Another option is SMO. I would stay away from DMO as it's deprecated.
> Here's a console app that uses SMO that will do this for you:
> http://www.codeplex.com/scriptdb
> On Sep 3, 1:54 am, Ant <A...@.discussions.microsoft.com> wrote:
>> HI Uri,
>> Thank you, this will be applied to SQL2000 so it looks like the DMO
>> object
>> library is the go.
>> Thanks very much for your help on this. Much appreciated
>> Ant
>> "Uri Dimant" wrote:
>> > Ant
>> > What version are you using? InSQLServer 2005 (with SP2 I think) you are
>> > be
>> > able toscriptthe objects per file
>> >SQLServer 2000 , you can useSQLDMO object library
>> > Sub ScriptDB(strLogin As String, strPwd As String, _
>> > strDataBase As String, StrFilePath As String)
>> > DimsqlAs Object
>> > Dim db As Object
>> > Dim objTrigger As Object
>> > Dim intOptions As Long
>> > Dim genObj
>> > Setsql= CreateObject("SQLDMO.SQLServer")
>> > Set db = CreateObject("SQLDMO.Database")
>> > Set objTrigger = CreateObject("SQLDMO.Trigger")
>> > Const sDrops As Integer = 1
>> > Const sIncludeHeaders As Long = 131072
>> > Const sDefault As Integer = 4
>> > Const sAppendToFile As Integer = 256
>> > Const sBindings As Integer = 128
>> > Const SQLDMOScript2_NoCollation As Long = 8388608
>> > ' Set scripting options. Because you need to specify multiple
>> > behaviors
>> > ' for the ScriptType argument, you use "Or" to combine these.
>> > intOptions = sDrops Or sIncludeHeaders Or _
>> > sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
>> > ' Connect to local server
>> > sql.Connect "(local)", strLogin, strPwd
>> > Set db =sql.Databases(strDataBase, "dbo")
>> > 'ScriptTables and Triggers, ignoring system
>> > ' tables and system generated triggers
>> > For Each genObj In db.Tables
>> > If genObj.SystemObject = False Then
>> > genObj.ScriptintOptions,
>> > StrFilePath,,SQLDMOScript2_NoCollation
>> > For Each objTrigger In genObj.Triggers
>> > If objTrigger.SystemObject = False Then
>> > objTrigger.ScriptintOptions, StrFilePath
>> > End If
>> > Next
>> > End If
>> > Next
>> > MsgBox "Finished generatingSQLscripts."
>> > End Sub
>> > "Ant" <A...@.discussions.microsoft.com> wrote in message
>> >news:32A3B041-7CC3-44F9-803E-AB805E8356BC@.microsoft.com...
>> > > Hi Uri,
>> > > Thanks for your help though when I tried it, it simply created
>> > > ascript
>> > > full
>> > > of exec dbOptions. Is this what I should be expecting?
>> > > I was hoping for asqlfile filled with create trigger statements.
>> > > In Enterprise Manager, I went to Tools>GenerateScripts>
>> > > Checked thewGenerateCreate for each objecte & unchecked the Drop;
>> > > Security Scripting Options =ScriptDatabase (Had to check this/ didn't
>> > > want
>> > > to)
>> > > Table Scripting Options =ScriptTriggers
>> > > But it didn't seem to work
>> > > Any ideas on this?
>> > > Cheers
>> > > Ant
>> > > "Uri Dimant" wrote:
>> > >> Ant
>> > >> When you click onGenerateScripts menu , you have Options tab up to
>> > >> the
>> > >> window. Click on it and checkScriptTriggers option
>> > >> "Ant" <A...@.discussions.microsoft.com> wrote in message
>> > >>news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
>> > >> > Hi,
>> > >> > Is there a simple way to save all triggers/ and or SP's to a
>> > >> > Create
>> > >> >script
>> > >> > in the same whay that you can save an individual trig/SP by right
>> > >> > clicking
>> > >> > it?
>> > >> > Thanks for any ideas on this is advance
>> > >> > Cheers
>> > >> > Ant
>|||Here, in this KB, Microsoft says SMO is compatible with 7.0, 2000 and 2005
Quote:
Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, and
SQL Server 2005, you can also use SMO for configuration of SQL Server 2000.
Source: http://support.microsoft.com/default.aspx/kb/306397
--
Ekrem Önsoy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eRLP9n57HHA.396@.TK2MSFTNGP06.phx.gbl...
> Its SQL Server 2000 you cannot use SMO
> "Jesse" <google@.jessehersch.fastmail.fm> wrote in message
> news:1188941936.583258.128870@.57g2000hsv.googlegroups.com...
>> Another option is SMO. I would stay away from DMO as it's deprecated.
>> Here's a console app that uses SMO that will do this for you:
>> http://www.codeplex.com/scriptdb
>> On Sep 3, 1:54 am, Ant <A...@.discussions.microsoft.com> wrote:
>> HI Uri,
>> Thank you, this will be applied to SQL2000 so it looks like the DMO
>> object
>> library is the go.
>> Thanks very much for your help on this. Much appreciated
>> Ant
>> "Uri Dimant" wrote:
>> > Ant
>> > What version are you using? InSQLServer 2005 (with SP2 I think) you
>> > are be
>> > able toscriptthe objects per file
>> >SQLServer 2000 , you can useSQLDMO object library
>> > Sub ScriptDB(strLogin As String, strPwd As String, _
>> > strDataBase As String, StrFilePath As String)
>> > DimsqlAs Object
>> > Dim db As Object
>> > Dim objTrigger As Object
>> > Dim intOptions As Long
>> > Dim genObj
>> > Setsql= CreateObject("SQLDMO.SQLServer")
>> > Set db = CreateObject("SQLDMO.Database")
>> > Set objTrigger = CreateObject("SQLDMO.Trigger")
>> > Const sDrops As Integer = 1
>> > Const sIncludeHeaders As Long = 131072
>> > Const sDefault As Integer = 4
>> > Const sAppendToFile As Integer = 256
>> > Const sBindings As Integer = 128
>> > Const SQLDMOScript2_NoCollation As Long = 8388608
>> > ' Set scripting options. Because you need to specify multiple
>> > behaviors
>> > ' for the ScriptType argument, you use "Or" to combine these.
>> > intOptions = sDrops Or sIncludeHeaders Or _
>> > sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
>> > ' Connect to local server
>> > sql.Connect "(local)", strLogin, strPwd
>> > Set db =sql.Databases(strDataBase, "dbo")
>> > 'ScriptTables and Triggers, ignoring system
>> > ' tables and system generated triggers
>> > For Each genObj In db.Tables
>> > If genObj.SystemObject = False Then
>> > genObj.ScriptintOptions,
>> > StrFilePath,,SQLDMOScript2_NoCollation
>> > For Each objTrigger In genObj.Triggers
>> > If objTrigger.SystemObject = False Then
>> > objTrigger.ScriptintOptions, StrFilePath
>> > End If
>> > Next
>> > End If
>> > Next
>> > MsgBox "Finished generatingSQLscripts."
>> > End Sub
>> > "Ant" <A...@.discussions.microsoft.com> wrote in message
>> >news:32A3B041-7CC3-44F9-803E-AB805E8356BC@.microsoft.com...
>> > > Hi Uri,
>> > > Thanks for your help though when I tried it, it simply created
>> > > ascript
>> > > full
>> > > of exec dbOptions. Is this what I should be expecting?
>> > > I was hoping for asqlfile filled with create trigger statements.
>> > > In Enterprise Manager, I went to Tools>GenerateScripts>
>> > > Checked thewGenerateCreate for each objecte & unchecked the Drop;
>> > > Security Scripting Options =ScriptDatabase (Had to check this/
>> > > didn't
>> > > want
>> > > to)
>> > > Table Scripting Options =ScriptTriggers
>> > > But it didn't seem to work
>> > > Any ideas on this?
>> > > Cheers
>> > > Ant
>> > > "Uri Dimant" wrote:
>> > >> Ant
>> > >> When you click onGenerateScripts menu , you have Options tab up to
>> > >> the
>> > >> window. Click on it and checkScriptTriggers option
>> > >> "Ant" <A...@.discussions.microsoft.com> wrote in message
>> > >>news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
>> > >> > Hi,
>> > >> > Is there a simple way to save all triggers/ and or SP's to a
>> > >> > Create
>> > >> >script
>> > >> > in the same whay that you can save an individual trig/SP by right
>> > >> > clicking
>> > >> > it?
>> > >> > Thanks for any ideas on this is advance
>> > >> > Cheers
>> > >> > Ant
>>
>|||Hi
I meant you can use SMO only in .NET technologies. Sorry , my mistake
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:5A8AEF56-3DFA-4D2B-BB71-97EB9C4D288B@.microsoft.com...
> Here, in this KB, Microsoft says SMO is compatible with 7.0, 2000 and 2005
> Quote:
> Because SMO is compatible with SQL Server version 7.0, SQL Server 2000,
> and SQL Server 2005, you can also use SMO for configuration of SQL Server
> 2000.
> Source: http://support.microsoft.com/default.aspx/kb/306397
> --
> Ekrem Önsoy
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eRLP9n57HHA.396@.TK2MSFTNGP06.phx.gbl...
>> Its SQL Server 2000 you cannot use SMO
>> "Jesse" <google@.jessehersch.fastmail.fm> wrote in message
>> news:1188941936.583258.128870@.57g2000hsv.googlegroups.com...
>> Another option is SMO. I would stay away from DMO as it's deprecated.
>> Here's a console app that uses SMO that will do this for you:
>> http://www.codeplex.com/scriptdb
>> On Sep 3, 1:54 am, Ant <A...@.discussions.microsoft.com> wrote:
>> HI Uri,
>> Thank you, this will be applied to SQL2000 so it looks like the DMO
>> object
>> library is the go.
>> Thanks very much for your help on this. Much appreciated
>> Ant
>> "Uri Dimant" wrote:
>> > Ant
>> > What version are you using? InSQLServer 2005 (with SP2 I think) you
>> > are be
>> > able toscriptthe objects per file
>> >SQLServer 2000 , you can useSQLDMO object library
>> > Sub ScriptDB(strLogin As String, strPwd As String, _
>> > strDataBase As String, StrFilePath As String)
>> > DimsqlAs Object
>> > Dim db As Object
>> > Dim objTrigger As Object
>> > Dim intOptions As Long
>> > Dim genObj
>> > Setsql= CreateObject("SQLDMO.SQLServer")
>> > Set db = CreateObject("SQLDMO.Database")
>> > Set objTrigger = CreateObject("SQLDMO.Trigger")
>> > Const sDrops As Integer = 1
>> > Const sIncludeHeaders As Long = 131072
>> > Const sDefault As Integer = 4
>> > Const sAppendToFile As Integer = 256
>> > Const sBindings As Integer = 128
>> > Const SQLDMOScript2_NoCollation As Long = 8388608
>> > ' Set scripting options. Because you need to specify multiple
>> > behaviors
>> > ' for the ScriptType argument, you use "Or" to combine these.
>> > intOptions = sDrops Or sIncludeHeaders Or _
>> > sDefault Or sAppendToFile Or sBindings Or
>> > SQLDMOScript2_NoCollation
>> > ' Connect to local server
>> > sql.Connect "(local)", strLogin, strPwd
>> > Set db =sql.Databases(strDataBase, "dbo")
>> > 'ScriptTables and Triggers, ignoring system
>> > ' tables and system generated triggers
>> > For Each genObj In db.Tables
>> > If genObj.SystemObject = False Then
>> > genObj.ScriptintOptions,
>> > StrFilePath,,SQLDMOScript2_NoCollation
>> > For Each objTrigger In genObj.Triggers
>> > If objTrigger.SystemObject = False Then
>> > objTrigger.ScriptintOptions, StrFilePath
>> > End If
>> > Next
>> > End If
>> > Next
>> > MsgBox "Finished generatingSQLscripts."
>> > End Sub
>> > "Ant" <A...@.discussions.microsoft.com> wrote in message
>> >news:32A3B041-7CC3-44F9-803E-AB805E8356BC@.microsoft.com...
>> > > Hi Uri,
>> > > Thanks for your help though when I tried it, it simply created
>> > > ascript
>> > > full
>> > > of exec dbOptions. Is this what I should be expecting?
>> > > I was hoping for asqlfile filled with create trigger statements.
>> > > In Enterprise Manager, I went to Tools>GenerateScripts>
>> > > Checked thewGenerateCreate for each objecte & unchecked the Drop;
>> > > Security Scripting Options =ScriptDatabase (Had to check this/
>> > > didn't
>> > > want
>> > > to)
>> > > Table Scripting Options =ScriptTriggers
>> > > But it didn't seem to work
>> > > Any ideas on this?
>> > > Cheers
>> > > Ant
>> > > "Uri Dimant" wrote:
>> > >> Ant
>> > >> When you click onGenerateScripts menu , you have Options tab up to
>> > >> the
>> > >> window. Click on it and checkScriptTriggers option
>> > >> "Ant" <A...@.discussions.microsoft.com> wrote in message
>> > >>news:E0983BC0-796A-4801-9CE4-014AFED0A715@.microsoft.com...
>> > >> > Hi,
>> > >> > Is there a simple way to save all triggers/ and or SP's to a
>> > >> > Create
>> > >> >script
>> > >> > in the same whay that you can save an individual trig/SP by
>> > >> > right
>> > >> > clicking
>> > >> > it?
>> > >> > Thanks for any ideas on this is advance
>> > >> > Cheers
>> > >> > Ant
>>
>>
>

SaveCheckpoints=True affects Recordset

I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:

Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable

adp.Fill(dt, Dts.Variables("CountryTable").Value)

It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.

Note: I have SP1 installed.

That's weird for sure. I'll need to investigate.

When we are writing checkpoints, we also write out the value of variables to the checkpoint file - so that we can start the package again in the correct state. I can imagine an error when serializing an object variable could cause some checkpoint funkiness, but as I say, it needs more investigation.

Donald

|||

Donald,

I thought that Object variables don't get written out (unless the behaviour has changed in SP1).

-Jamie

|||

They're not and its documented in BOL now.

I hade the same issue, that my package worked up until I added checkpoints at which point it wouldn't run properly. Thought I had raised a bug but haven't

Save time in Script Component which is almost forwarding

Hi,
I have to add the fields coming from the source AS IS but, I need to add current date as a column to it. So, What I do is to add an Script Component and add each and every output column in that along with defining their types and writing an "assignments" script.

Is there any possibility for me to save time in the scenarios where I am almost passing on the information to next level in the data flow ?

Any input regarding this will sincerely be appreciated.

FahadWhy can't you use a derived column transformation with getdate() as an expression? Using a derived column, you can add a new date column to your data flow.|||Thanks, I appreciate it.

Save change script

Using SQL Server Enterprise Manager I can change field type,
press button Save change script and get script for change this column.
My question is:
HOW Enterprise Manager get this script ? Used DLL call / stored procedure or
something else?
Is any way to get the same script in my code (VB) ?
Best regards.
AlexanderEnterprise Manager uses SQL-DMO to generate scripts, although to generate
change scripts like this there is some logic build in Enterprise Manager as
well.
See the "methods-SQL-DMO, Script" subject in Books Online for a start on how
to script SQL Server objects.
Jacco Schalkwijk
SQL Server MVP
"Alexander" <a@.a.com> wrote in message
news:e87dEwO5DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Using SQL Server Enterprise Manager I can change field type,
> press button Save change script and get script for change this column.
> My question is:
> HOW Enterprise Manager get this script ? Used DLL call / stored procedure

---

or

quote:

---

> something else?
> Is any way to get the same script in my code (VB) ?
> Best regards.
> Alexander
>

---

|||Thank you Jacco
Using SQL DMO I can generate script for existin object(s) in database.
But I'm need way to generate script for CHANGE table (like Enterprise
Manager)
Maybe there are examples generate scripts like this in VB ?
Brgrds
Alexander Feduleev
ARL Consulting
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> /
: news:uBetE6O5DHA.2412@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Enterprise Manager uses SQL-DMO to generate scripts, although to generate
> change scripts like this there is some logic build in Enterprise Manager

---

as

quote:

---

> well.
> See the "methods-SQL-DMO, Script" subject in Books Online for a start on

---

how

quote:

---

> to script SQL Server objects.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Alexander" <a@.a.com> wrote in message
> news:e87dEwO5DHA.2480@.TK2MSFTNGP10.phx.gbl...
procedure[QUOTE]
> or
>

---

|||I doubt that you can generate ALTER TABLE scripts reliably with SQL-DMO,
when you have two existsing tables. Enterprise Manager in most cases, except
really simple ones, uses a script that creates a new table, copies the data
from the old table to the new table, drops the old table and then renames
the new table.
You can have a look at a third party product like SQLCompare by
www.red-gate.com , which compares databases and has a toolkit that exposes
their APIs.
Jacco Schalkwijk
SQL Server MVP
"Alexander" <a@.a.com> wrote in message
news:OTBG$IP5DHA.2136@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Thank you Jacco
> Using SQL DMO I can generate script for existin object(s) in database.
> But I'm need way to generate script for CHANGE table (like Enterprise
> Manager)
> Maybe there are examples generate scripts like this in VB ?
>
> --
> Brgrds
> Alexander Feduleev
> ARL Consulting
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> /

---

quote:

---

> : news:uBetE6O5DHA.2412@.TK2MSFTNGP09.phx.gbl...
generate[QUOTE]
> as
> how
column.[QUOTE]
> procedure
>

---

|||Hello Jacco
Unfortunaly ALTER TABLE and Column.AlterDataType not work with text/ntext
fields.

quote:

---

> Enterprise Manager uses a script that creates a new table, copies the data
> from the old table to the new table, drops the old table and then renames
> the new table.

---

I see, but there are situations when this column has reference to another
table and need change it all.
Enterpise Manager correct build script for this situations (add recreation
referenced tables also)
I think easy way is try to use this functionality of Enterprise Manager
SQLCompare is fine but i'm not need compare two databases but need change
some fields in exist DB.
Any new Ideas
Best Regards
Alexander
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> /
: news:ORDNGSP5DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I doubt that you can generate ALTER TABLE scripts reliably with SQL-DMO,
> when you have two existsing tables. Enterprise Manager in most cases,

---

except

quote:

---

> really simple ones, uses a script that creates a new table, copies the

---

data

quote:

---

> from the old table to the new table, drops the old table and then renames
> the new table.
> You can have a look at a third party product like SQLCompare by
> www.red-gate.com , which compares databases and has a toolkit that exposes
> their APIs.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Alexander" <a@.a.com> wrote in message
> news:OTBG$IP5DHA.2136@.TK2MSFTNGP12.phx.gbl...
>
> generate
Manager[QUOTE]
on[QUOTE]
> column.
>

---

Save change script

Using SQL Server Enterprise Manager I can change field type,
press button Save change script and get script for change this column.
My question is:
HOW Enterprise Manager get this script ? Used DLL call / stored procedure or
something else?
Is any way to get the same script in my code (VB) ?
Best regards.
AlexanderEnterprise Manager uses SQL-DMO to generate scripts, although to generate
change scripts like this there is some logic build in Enterprise Manager as
well.
See the "methods-SQL-DMO, Script" subject in Books Online for a start on how
to script SQL Server objects.
Jacco Schalkwijk
SQL Server MVP
"Alexander" <a@.a.com> wrote in message
news:e87dEwO5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> Using SQL Server Enterprise Manager I can change field type,
> press button Save change script and get script for change this column.
> My question is:
> HOW Enterprise Manager get this script ? Used DLL call / stored procedure
or
> something else?
> Is any way to get the same script in my code (VB) ?
> Best regards.
> Alexander
>|||Thank you Jacco
Using SQL DMO I can generate script for existin object(s) in database.
But I'm need way to generate script for CHANGE table (like Enterprise
Manager)
Maybe there are examples generate scripts like this in VB ?
Brgrds
Alexander Feduleev
ARL Consulting
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:uBetE6O5DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Enterprise Manager uses SQL-DMO to generate scripts, although to generate
> change scripts like this there is some logic build in Enterprise Manager
as
> well.
> See the "methods-SQL-DMO, Script" subject in Books Online for a start on
how
> to script SQL Server objects.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Alexander" <a@.a.com> wrote in message
> news:e87dEwO5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > Using SQL Server Enterprise Manager I can change field type,
> > press button Save change script and get script for change this column.
> >
> > My question is:
> > HOW Enterprise Manager get this script ? Used DLL call / stored
procedure
> or
> > something else?
> > Is any way to get the same script in my code (VB) ?
> >
> > Best regards.
> > Alexander
> >
> >
>|||I doubt that you can generate ALTER TABLE scripts reliably with SQL-DMO,
when you have two existsing tables. Enterprise Manager in most cases, except
really simple ones, uses a script that creates a new table, copies the data
from the old table to the new table, drops the old table and then renames
the new table.
You can have a look at a third party product like SQLCompare by
www.red-gate.com , which compares databases and has a toolkit that exposes
their APIs.
--
Jacco Schalkwijk
SQL Server MVP
"Alexander" <a@.a.com> wrote in message
news:OTBG$IP5DHA.2136@.TK2MSFTNGP12.phx.gbl...
> Thank you Jacco
> Using SQL DMO I can generate script for existin object(s) in database.
> But I'm need way to generate script for CHANGE table (like Enterprise
> Manager)
> Maybe there are examples generate scripts like this in VB ?
>
> --
> Brgrds
> Alexander Feduleev
> ARL Consulting
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ
> ÓÌÅÄÕÀÝÅÅ: news:uBetE6O5DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > Enterprise Manager uses SQL-DMO to generate scripts, although to
generate
> > change scripts like this there is some logic build in Enterprise Manager
> as
> > well.
> >
> > See the "methods-SQL-DMO, Script" subject in Books Online for a start on
> how
> > to script SQL Server objects.
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Alexander" <a@.a.com> wrote in message
> > news:e87dEwO5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > > Using SQL Server Enterprise Manager I can change field type,
> > > press button Save change script and get script for change this
column.
> > >
> > > My question is:
> > > HOW Enterprise Manager get this script ? Used DLL call / stored
> procedure
> > or
> > > something else?
> > > Is any way to get the same script in my code (VB) ?
> > >
> > > Best regards.
> > > Alexander
> > >
> > >
> >
> >
>|||Hello Jacco
Unfortunaly ALTER TABLE and Column.AlterDataType not work with text/ntext
fields.
> Enterprise Manager uses a script that creates a new table, copies the data
> from the old table to the new table, drops the old table and then renames
> the new table.
I see, but there are situations when this column has reference to another
table and need change it all.
Enterpise Manager correct build script for this situations (add recreation
referenced tables also)
I think easy way is try to use this functionality of Enterprise Manager
SQLCompare is fine but i'm not need compare two databases but need change
some fields in exist DB.
Any new Ideas
Best Regards
Alexander
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: news:ORDNGSP5DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I doubt that you can generate ALTER TABLE scripts reliably with SQL-DMO,
> when you have two existsing tables. Enterprise Manager in most cases,
except
> really simple ones, uses a script that creates a new table, copies the
data
> from the old table to the new table, drops the old table and then renames
> the new table.
> You can have a look at a third party product like SQLCompare by
> www.red-gate.com , which compares databases and has a toolkit that exposes
> their APIs.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Alexander" <a@.a.com> wrote in message
> news:OTBG$IP5DHA.2136@.TK2MSFTNGP12.phx.gbl...
> > Thank you Jacco
> >
> > Using SQL DMO I can generate script for existin object(s) in database.
> > But I'm need way to generate script for CHANGE table (like Enterprise
> > Manager)
> >
> > Maybe there are examples generate scripts like this in VB ?
> >
> >
> > --
> > Brgrds
> > Alexander Feduleev
> > ARL Consulting
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
> ÎÏ×ÏÓÔÑÈ
> > ÓÌÅÄÕÀÝÅÅ: news:uBetE6O5DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > > Enterprise Manager uses SQL-DMO to generate scripts, although to
> generate
> > > change scripts like this there is some logic build in Enterprise
Manager
> > as
> > > well.
> > >
> > > See the "methods-SQL-DMO, Script" subject in Books Online for a start
on
> > how
> > > to script SQL Server objects.
> > >
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "Alexander" <a@.a.com> wrote in message
> > > news:e87dEwO5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > > > Using SQL Server Enterprise Manager I can change field type,
> > > > press button Save change script and get script for change this
> column.
> > > >
> > > > My question is:
> > > > HOW Enterprise Manager get this script ? Used DLL call / stored
> > procedure
> > > or
> > > > something else?
> > > > Is any way to get the same script in my code (VB) ?
> > > >
> > > > Best regards.
> > > > Alexander
> > > >
> > > >
> > >
> > >
> >
> >
>