I have created a user role in my SQL 7 database. I would like to copy this
role to 8 other servers. I created the role using Enterprise Manager. Can
you advise how to query this out to SQL Script so that I could just run the
script on the other servers to create the role and then assign users as
required.In Enterprise Manager you can script database and select option to script
roles and permissions also. Then find in the generated script related T-SQL
by role name (starting from sp_addrole and below).
Martin Rakhmanov
"dtatham" <dtatham@.discussions.microsoft.com> wrote in message
news:79CC672A-B46A-4F2C-9CC1-F7DAE8772DB8@.microsoft.com...
>I have created a user role in my SQL 7 database. I would like to copy this
> role to 8 other servers. I created the role using Enterprise Manager.
> Can
> you advise how to query this out to SQL Script so that I could just run
> the
> script on the other servers to create the role and then assign users as
> required.
>
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Script to copy permissions for all objects given to a user or a role
How would I, using a sql script, copy permissions assigned to a user
or a role in one or more databases to another user or a role in their
respective databases?
or a role in one or more databases to another user or a role in their
respective databases?
Help appreciatedgudia97@.yahoo.com (gudia) wrote in message news:<2130f7ff.0407071438.275b76f3@.posting.google.com>...
> How would I, using a sql script, copy permissions assigned to a user
> or a role in one or more databases to another user or a role in their
> respective databases?
> Help appreciated
First of all, I would avoid granting permissions to users, since this
becomes difficult to manage - if you always use roles, then things are
much easier. Even if you have only one user in a role today, at least
you won't have any extra work when you need to add a second one. Also,
you should keep a permissions script for each role, so you know which
permissions are correct, and you can run it for multiple roles.
To solve your issue right now, you could add one role to another, if
that's appropriate. If not, then you can reverse-engineer a
permissions script using a query like this:
select
case protecttype
when 205 then 'grant '
when 206 then 'revoke '
end +
case action
when 26 then ' references '
when 193 then ' select '
when 195 then ' insert '
when 196 then ' delete '
when 197 then ' update '
when 224 then ' execute '
end +
' on ' + object_name(id) + ' to TargetRole '
+ case when protecttype = 204 then ' with grant option' else '' end
from
sysprotects
where
uid = user_id('SourceRole')
See sysprotects in Books Online for more information. Note that this
query doesn't handle SELECT or UPDATE permissions on explicit columns,
but only where the permissions are on the whole table. It also does
not handle statement permissions (CREATE TABLE etc.), but you can
easily write a similar query for that.
Simon|||Simon:
Thanks. This will help as a starting point. I do use roles as oppossed
to users for permissioning.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
script to copy logins and users with all permissions
Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.Inna,
>
- Show quoted text -
Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?|||On Jan 30, 3:26 pm, "Inna" <med...@.hotmail.comwrote:
>
>
>
>
>
>
>
Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?
AlterEgo's point is - let Enterprise manager generate the sql for you.
It will give you a starting point and you can change the code to be
specific for your application.
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.Inna,
If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.
-- Bill
"Inna" <mednyk@.hotmail.comwrote in message
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...
Quote:
Originally Posted by
Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.
>
Quote:
Originally Posted by
Inna,
>
If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.
>
-- Bill
>
"Inna" <med...@.hotmail.comwrote in message
>
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...
>
>
>
Quote:
Originally Posted by
Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.- Hide quoted text -
>
- Show quoted text -
Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?|||On Jan 30, 3:26 pm, "Inna" <med...@.hotmail.comwrote:
Quote:
Originally Posted by
On Jan 25, 7:04 pm, "AlterEgo" <altereg...@.dslextreme.comwrote:
>
>
>
Quote:
Originally Posted by
Inna,
>
Quote:
Originally Posted by
If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.
>
Quote:
Originally Posted by
-- Bill
>
Quote:
Originally Posted by
"Inna" <med...@.hotmail.comwrote in message
>
Quote:
Originally Posted by
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.- Hide quoted text -
>
Quote:
Originally Posted by
- Show quoted text -
>
Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?
AlterEgo's point is - let Enterprise manager generate the sql for you.
It will give you a starting point and you can change the code to be
specific for your application.
Script to copy entire table
I'm a newbie to script writing. I'm trying to write a script to copy all
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.
Example:
Copy entire table called 'Customers' in the 'Data01' database to
table 'Customers' in the 'Data02' database
I want to overwrite all data in the destination table.
Thanks"sqlnewbie" <sqlnewbie@.yahoo.com> wrote in message
news:k4PYb.27374$vs5.13502@.newssvr25.news.prodigy. com...
> I'm a newbie to script writing. I'm trying to write a script to copy all
> data from a table to the same table in a 2nd database. Both databases are
> on the same server and are identical in design. I can do this with DTS
but
> wanted a script I could email to a user to run in Query Analyzer.
> Example:
> Copy entire table called 'Customers' in the 'Data01' database to
> table 'Customers' in the 'Data02' database
> I want to overwrite all data in the destination table.
> Thanks
/* Replace all data in the destination table */
use Data02
go
truncate table dbo.Customers
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers
/* Insert only data which isn't already there */
use Data02
go
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCol = c2.PrimaryKeyCol)
Note that TRUNCATE TABLE requires certain permissions (see Books Online),
and won't work if the table is referenced by foreign keys. In this case, you
can use "DELETE FROM dbo.Customers".
I would be careful about sending scripts to users, as they often seem to run
them in the wrong place at the wrong time - moving data should really be a
DBA's task (although I appreciate that not everyone has a DBA available).
You may want to back up the database first, just in case.
Simonsql
Friday, March 9, 2012
Script for copying data
Frinds,
How can i have a script to:
1- Disable all Foreign Keys and Constraints
2- Copy all data
3- Enable all Foreign Keys and Constraints
Actually, i need the commando to enable and disable a Foreign Key.
Thanks
Leandro L S
Vitória-ES
Brazilor just use ALTER TABLE <tablename> NOCHECK CONSTRAINT ALL if you wan tto
get them all.
Note that this only disables Foreign Keys and Check constraint and not
Primary keys or unique constraints
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eTitIcjbDHA.1280@.tk2msftngp13.phx.gbl...
> Leandro,
> --To disable a Foreign key constraint
> ALTER TABLE <tablename> NOCHECK CONSTRAINT <constraintname>
> --To enable it
> ALTER TABLE <tablename> CHECK CONSTRAINT <constraintname>
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:%23rlB%23MjbDHA.1204@.TK2MSFTNGP12.phx.gbl...
> > Frinds,
> >
> > How can i have a script to:
> >
> > 1- Disable all Foreign Keys and Constraints
> > 2- Copy all data
> > 3- Enable all Foreign Keys and Constraints
> >
> > Actually, i need the commando to enable and disable a Foreign Key.
> >
> > Thanks
> > Leandro L S
> > Vitória-ES
> > Brazil
> >
> >
>
How can i have a script to:
1- Disable all Foreign Keys and Constraints
2- Copy all data
3- Enable all Foreign Keys and Constraints
Actually, i need the commando to enable and disable a Foreign Key.
Thanks
Leandro L S
Vitória-ES
Brazilor just use ALTER TABLE <tablename> NOCHECK CONSTRAINT ALL if you wan tto
get them all.
Note that this only disables Foreign Keys and Check constraint and not
Primary keys or unique constraints
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eTitIcjbDHA.1280@.tk2msftngp13.phx.gbl...
> Leandro,
> --To disable a Foreign key constraint
> ALTER TABLE <tablename> NOCHECK CONSTRAINT <constraintname>
> --To enable it
> ALTER TABLE <tablename> CHECK CONSTRAINT <constraintname>
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:%23rlB%23MjbDHA.1204@.TK2MSFTNGP12.phx.gbl...
> > Frinds,
> >
> > How can i have a script to:
> >
> > 1- Disable all Foreign Keys and Constraints
> > 2- Copy all data
> > 3- Enable all Foreign Keys and Constraints
> >
> > Actually, i need the commando to enable and disable a Foreign Key.
> >
> > Thanks
> > Leandro L S
> > Vitória-ES
> > Brazil
> >
> >
>
Script component: copy a row into a variable
HI, is it possible to copy a row from the Input0_ProcessInputRow into another variable of a synchronous script component. I have a script component that has three outputs. What I would like to do is the following:
At first iteration, copy entire row into a variable.
For the other rows, is business key cols is different from the previous row (the one copied at previous iteration) or type 2 columns :
1-direct previous row (from the variable) to second.output
2- direct current row to the first output
3- copy the current row into the variable
If the business key cols are the same and type 2 cols are the same:
1-Direct row to the third output
My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).
The reason I use a script component is that the lookups are not updated dynamically, the rows I need to process may not exist in the target table and once inserted, they are not retreived subsequently by the buffer.
Thank you,
Cco
ccote wrote:
My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).
You should not use the buffer that was passed to you outside of the ProcessInputRow call - the data flow engine manages the buffer lifetime, and by trying to use it outside of this call you are breaking the rules.
Try copying the columns to another data structure, e.g. an array.
|||HI, thank you for your reply. I knew I could copy the row's column to another structure (array, list, variables) but I was wondering If I could copy the whole row into a kind of "row" structure and be able to later direct it to an output. It would have been great since as I said, some of my tables have 150+ columns. I guess, the only way I can manage this is to actually add type1 and type 2 columns to my output and accumulate previous row type1 and type2 attributes into variables. So when the business key changes, I would copy variables into current row and have a conditionnal split that further filter out if any action have to be taken.
I could also use asynchrounous script but there I need to recreate all my 150+ table structure into output buffers. Actually, the real problem is that the lookup transform cache is not updated as new rows are added to the target table. That is why I need to do some custom code like this.
Thank's again,
Ccote
Wednesday, March 7, 2012
script all MSDB jobs in TSQL
I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/...790/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/...790/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to scrip
t
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
script all MSDB jobs in TSQL
I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/Article/ArticleID/38790/38790.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name ='xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. ThanksYou can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/Article/ArticleID/38790/38790.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name ='xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
script all MSDB jobs in TSQL
I know of a way to script all SQL Agent jobs using EM. But i want to script
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. Thanks
You can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/A...90/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
|||try attached
rename to .vbs
usage CSCRIPT ScriptAllJobs.vbs <Server> <FileName> <owner>
Server is Source Server where you want to script jobs from
FileName is output file for the script
owner is the new owner of the jobs. either do NULL (which will give make the
owner the user who runs the create script) or 'somelogin' ie the NT Login
that should be the owner ie 'Domainx\Jobowner' - with the quotes.
hope that makes sense
I had to do a bit of hacking cos a) the owner is hard coded and b) the
servername is hard coded (ie replace this with @.@.ServerName). Not too happy
about this, I think I might have missed a DMO Option somewhere
ai
Andy.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
begin 666 ScriptAllJobs.txt
M)R!38W)I<'1!;&Q*;V)S+G-Q;" -"B<@.4V-R:7!T<R!A;&P@.:F]B<R!O;B!A
M(&=I=F5N(%-13"!397)V97(@.86YD(')E<&QA8V5S('1H92!H87)D8V]D960@.
M<F5F97)E;F-E<R!T;R!A;&QO=R!T;R!B92 -"B<@.8V]P:65D('1O(&%N;W1H
M97(@.<V5R=F5R#0HG($%N9'D@.0F%L;" R-"\X+S(P,#0)=C$N, E"87-E(%9E
M<G-I;VX-"@.T*3W!T:6]N($5X<&QI8VET#0H)( T*)R!344PM1$U/(&-O;G-T
M86YT<PT*0V]N<W0@.4U%,1$U/4V-R:7!T7T1R;W!S(#T@.,0T*#0I#;VYS="!3
M44Q$34]38W)I<'1?4')I;6%R>4]B:F5C=" ](#0@.#0I#;VYS="!344Q$34]3
M8W)I<'1?5&]&:6QE3VYL>2 ](#8T#0I#;VYS="!344Q$34]38W)I<'1?07!P
M96YD=&]&:6QE(#T@.,C4V#0H-"D-O;G-T(%-13$1-3U-C<FEP=#)?3&]G:6Y3
M240@./2 X,3DR#0H-"E!U8FQI8R!38W)I<'1/8FIE8W1/<'1I;VYS#0I38W)I
M<'1/8FIE8W1/<'1I;VYS(#T@.("!344Q$34]38W)I<'1?4')I;6%R>4]B:F5C
M=" K(%\-"@.D)"5-13$1-3U-C<FEP=%]$<F]P<R -"@.T*1&EM(&]!<F=S(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" ))R!!<F=U;65N=',-"D1I;2!7<VA3:&5L
M; D)"0DG(&9O<B!7:6YD;W=S(%-C<FEP=&EN9R!(;W-T#0I$:6T@.4V5R=F5R
M3F%M92 @.(" @.(" @.(" @.(" @.(" @.(" @.( DG('1H92!397)V97(-"D1I;2!&
M:6QE;F%M90D)"0DG($9I;&5N86UE('1O('=R:71E('1O( T*1&EM(%-13%-T
M<FEN9PT*1&EM($YE=T]W;F5R3F%M92 -"@.T*)R!#<F5A=&4@.4VAE;&P@.;V)J
M96-T#0IS970@.5W-H4VAE;&P@./2!#<F5A=&5/8FIE8W0@.*")7<V-R:7!T+E-H
M96QL(BD-"@.T*)R!087)A;65T97)S#0IS970@.;T%R9W,@./2!7<V-R:7!T+D%R
M9W5M96YT<PT*#0HG(&-H96-K(&-O<G)E8W0@.;G5M8F5R(&]F(&%R9W,-"FEF
M("AO07)G<RY#;W5N=" \/B S*2!4:&5N#0H)5W-C<FEP="Y%8VAO(")5<V%G
M92 Z(&-S8W)I<'0@.4V-R:7!T06QL2F]B<RYV8G,@./'-E<G9E<CX@./$9I;&5.
M86UE/B \3F5W3W=N97(^(@.T*"5=S8W)I<'0N16-H;R B:2YE+B @..B!C<V-R
M:7!T(%-C<FEP=$%L;$IO8G,N=F)S(%!23T1397)V97(@.2F]B<RYS<6P@.<V$B
M#0H)5W-C<FEP="Y1=6ET(" @.( T*16QS90T*"2<@.;VL@.<V\@.87-S:6=N('!A
M<F%M<PT*"5-E<G9E<DYA;64@.(" @.(" ](&]!<F=S*# I#0H)1FEL94YA;64)
M/2!O07)G<R@.Q*0T*"4YE=T]W;F5R3F%M90D](&]!<F=S*#(I#0I%;F0@.268-
M"@.T*)R!-86EN(%-C<FEP= T*4V-R:7!T06QL2F]B<R!397)V97).86UE+"!&
M:6QE3F%M90T*#0HG($5N9"!O9B!-86EN#0H-"E-U8B!38W)I<'1!;&Q*;V)S
M("A397)V97).86UE+"!&:6QE3F%M92D-"@.E$:6T@.;T1A=&%B87-E(" @.(" @.
M(" @.(" @.(" @.(" @.("<@.=&AE('1A<F=E="!D871A8F%S92!T;R!U<V4-"@.E$
M:6T@.4V-R:7!T4W1R:6YG(" @.(" @.(" @.(" @.(" @.("<@.<F5T=7)N<R!I;G1O
M(&AE<F4@.=')A;G-A8W0@.<W%L(&9O<B!O8FIE8W0-"@.E$:6T@.4U%,3W5T<'5T
M1FEL90D))R!F:6QE('1H92!S=&]R960@.<')O8V5D=7)E(&ES('=R:71T96X@.
M=&\@.#0H)1&EM(&]397)V97(-"@.E$:6T@.9G,-"@.T*"41I;2!F<T]U='!U=$9I
M;&4-"@.T*"41I;2!*;V(-"@.E$:6T@.2F]B4W1R:6YG#0H)1&EM($1R;W!%>&ES
M=&EN9TIO8E-T<FEN9PT*#0H))R!C<F5A=&4@.4U%,(%-E<G9E<B!O8FIE8W0-
M"@.E3970@.;U-E<G9E<B ]($-R96%T94]B:F5C="@.B4U%,1$U/+E-13%-E<G9E
M<B(I#0H-"@.DG(&%L=V%Y<R!U<V4@.82!T<G5S=&5D(&-O;FYE8W1I;VX@.86YD
M(&-O;FYE8W0-"@.EO4V5R=F5R+DQO9VEN4V5C=7)E(#T@.5')U90T*"6]397)V
M97(N0V]N;F5C="!397)V97).86UE("<@.=')U<W1E9"!C;VYN96-T:6]N#0H-
M"@.DG(%)O;&P@.=&AR;W5G:"!E86-H(&IO8B!A;F0@.9V5N97)A=&4@.=&AE(%-1
M3"P@.:&%C:R!O=70@.:&%R9&-O9&5D('-T=69F(&%N9"!A<'!E;F0@.=&\@.4V-R
M:7!T4W1R:6YG('9A<FEA8FQE#0H)1F]R($5A8V@.@.2F]B(&EN(&]397)V97(N
M2F]B4V5R=F5R+DIO8G,-"@.D))R!$<F]P('1H92!J;V(@.:68@.:70@.97AI<W1S
M+B!3:&]U;&0@.8F4@.82!$34\@.;W!T:6]N(&9O<B!T:&ES(#\_/S\_#0H)"41R
M;W!%>&ES=&EN9TIO8E-T<FEN9R ](")"14=)3B!44D%.4T%#5$E/3B(@.*R!V
M8D-23$8-"@.D)1')O<$5X:7-T:6YG2F]B4W1R:6YG(#T@.1')O<$5X:7-T:6YG
M2F]B4W1R:6YG("L@.(DE&($5825-44R H4T5,14-4("H@.1E)/32!M<V1B+F1B
M;RYS>7-J;V)S(%=(15)%(&YA;64@./2 G(B K($IO8BY.86UE("L@.(B<I(B K
M('9B0U),1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I
M;F=*;V)3=')I;F<@.*R!V8E1!0B K(")%6$5#(&US9&(N9&)O+G-P7V1E;&5T
M95]J;V(@.0&IO8E]N86UE(#T@.)R(@.*R!*;V(N3F%M92 K("(G(B K('9B0U),
M1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I;F=*;V)3
M=')I;F<@.*R B1T\B("L@.=F)#4DQ�H)"4IO8E-T<FEN9R ]($IO8BY38W)I
M<'0@.*%-13$1-3U-C<FEP=%]0<FEM87)Y3V)J96-T*0T*"0E*;V)3=')I;F<@.
M/2!215!,04-%*$IO8E-T<FEN9RP@.(F)E9VEN('1R86YS86-T:6]N(BP@.1')O
M<$5X:7-T:6YG2F]B4W1R:6YG*0T*"0D-"@.D))R!R97!L86-E('1H92!O=VYE
M<B!W:71H($Y53$P@.<V\@.=&AA="!I="!C<F5A=&5S(&IO8B!A< R!T:&4@.=7-E
M<B!E>&5C=71I;F<@.=&AE('-C<FEP= T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!O=VYE<E]L;V=I;E]N86UE(BP@.
M3F5W3W=N97).86UE("D-"@.D))R!297!L86-E(%-E<G9E<FYA;64@.=7-E9"!I
M;B!S<%]!9&1?:F]B7U-E<G9E<B!T;R!B92!T:&4@.8W5R<F5N="!S97)V97(L
M(&]T:&5R=VES92!I="!W:6QL('5S92!T:&4@.<V5R=F5R;F%M92!S8 W)I<'0@.
M9G)O;2!I9B!L;V-A;"!I;G-T86YC90T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!S97)V97)?;F%M92(L(") 0%-%
M4E9%4DY!344B*0T*#0H)"2<@.061D('1O('1H92!E>&ES=&EN9 R!3=')I;F<-
M"@.D)4V-R:7!T4W1R:6YG(#T@.4V-R:7!T4W1R:6YG("L@.=F)#4DQ&("L@.2F]B
M4W1R:6YG#0H)"5=38W)I<'0N16-H;R B5W)I=&EN9R!J;V(@.(B K($IO8BY.
M86UE("L@.(B!T;R B("L@.1FEL94YA;64-"@.E.97AT( T*#0H))R!.;W<@.9FEN
M86QL>2!W<FET92!T:&4@.9FEL90T*"7-E="!F<R ]($-R96%T94]B:F5C="@.B
M4V-R:7!T:6YG+D9I;&53>7-T96U/8FIE8W0B*0T*"5-E="!F<T]U='!U=$9I
M;&4@./2!F<RY#<F5A=&5497AT1FEL92A&:6QE3F%M92P@.5')U92D-"@.EF<T]U
M='!U=$9I;&4N5W)I=&5,:6YE(%-C<FEP=%-T<FEN9PT*"69S3W5T<'5T1FEL
M92Y#;&]S90T*16YD(%-U8@.T*#0HG(%-E87)C:"!F;W(@.82!3=')I;F<@.=&AA
M="!H87,@.0'-E<G9E<B ](&XG9&]O9F5R)R!T>7!E('-Y;G1A>"!A;F0@.<F5T
M=7)N(&XG9&]O9F5R)R L('=H97)E(%-E87)C:%-T<FEN9R!I<R! <V5R=F5R
M(&EN('1H:7,@.8V%S92X-"B<@.4V\@.=V4@.8V%N(')E<&QA8V4@.:&%R9&-O9&5D
M(')E9F5R;F-E<RX@.#0I&=6YC=&EO;B!297!L86-E2&%R9$-O9&5D4F5F("A3
M44Q3=')I;F<L(%-E87)C:%-T<FEN9RP@.4F5P;&%C95-T<FEN9RD-"@.E$:6T@.
M3F5W4W1R:6YG#0H)1&EM(%-E87)C:%-T<FEN9U!O<PT*"41I;2!396-O;F11
M=6]T95!O<PT*#0H)1&EM($QO9VEN3F%M90T*"41I;2!344Q3=')I; F=,96X-
M"@.T*"5-13%-T<FEN9TQE;B ]($QE;BA344Q3=')I;F<I#0H-"@.DG(&=E="!T
M:&4@.;&]C871I;VX@.;V8@.=&AE(&]W;F5R7VQO9VEN7VYA;64@.8VAA<@.T*"5-E
M87)C:%-T<FEN9U!O<R ]($EN4W1R*#$L(%-13%-T<FEN9RP@.4V5A<F-H4W1R
M:6YG+" P*0T*#0H))R!G970@.82!3=')I;F<@.+"!S=&%R=&EN9R!W:71H( $XG
M;&]G:6X@.;F%M92<-"@.E,;V=I;DYA;64@./2!-:60H4U%,4W1R:6YG+"!396%R
M8VA3=')I;F=0;W,@.*R!,14XH4V5A<F-H4W1R:6YG*2 K(#,L(%-13%-T<FEN
M9TQE;B M(%-E87)C:%-T<FEN9U!O<RD-"@.D-"@.E396-O;F11=6]T95!O<R ]
M($EN4W1R*#,L($QO9VEN3F%M92P@.(B<B+" P*0D)#0H)3&]G:6Y.86UE(#T@.
M36ED*$QO9VEN3F%M92P@.,2P@.4V5C;VYD475O=&50;W,I#0H)# 0H))R!74V-R
M:7!T+D5C:&\@.3&]G:6Y.86UE#0H)4F5P;&%C94AA<F1#;V1E9%)E9B ](%)E
M<&QA8V4H4U%,4W1R:6YG+"!,;V=I;DYA;64L(%)E<&QA8V53= ')I;F<I#0I%
6;F0@.1G5N8W1I;VX@.#0H-"@.T*#0H-"@.``
`
end
|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
all my jobs on a daily basis and copy that script file to another server . I
know how i can do the daily part of it which would be scheduling it again
but whats the TSQL or command I need to run within the job step .. Thanks
You can use Distributed Management Objects (DMO), not T-SQL. Check the
article at
http://www.winnetmag.com/SQLServer/A...90/38790.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||You can set up a job that uses an ActiveX script type job step containing
the following
Set oSQL = CreateObject("SQLDMO.SQLServer")
oSQL.Name = "(local)"
oSQL.LoginSecure = True
oSQL.LoginTimeout = 10
oSQL.Connect
Set oJobs = oSQL.JobServer.Jobs
oJobs.Script 4,"c:\jobs.sql",2048
oSQL.DisConnect
Set oSQL = Nothing
This will script all your jobs to c:\jobs.sql. For a named instance, change
the oSQL.Name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
>I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server .
> I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
|||one thing to watch out for is that the job owner (@.owner_login_name) will be
scripted out too. If login doesn't exist or in a different domain / env this
may cause you problems.
Could parse the Scripted text (can use the Script object just to return text
as opposed to file) and do a replace operation on the @.owner_login_name =
'xx' and replace with @.owner_login_name = null, so that sp_add_job will
create the job with owner of the user executing the script.
Then write the corrected text to File.
cheers,
Andy
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
|||try attached
rename to .vbs
usage CSCRIPT ScriptAllJobs.vbs <Server> <FileName> <owner>
Server is Source Server where you want to script jobs from
FileName is output file for the script
owner is the new owner of the jobs. either do NULL (which will give make the
owner the user who runs the create script) or 'somelogin' ie the NT Login
that should be the owner ie 'Domainx\Jobowner' - with the quotes.
hope that makes sense
I had to do a bit of hacking cos a) the owner is hard coded and b) the
servername is hard coded (ie replace this with @.@.ServerName). Not too happy
about this, I think I might have missed a DMO Option somewhere
ai
Andy.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uEV1iN8iEHA.3988@.tk2msftngp13.phx.gbl...
> I know of a way to script all SQL Agent jobs using EM. But i want to
script
> all my jobs on a daily basis and copy that script file to another server .
I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
begin 666 ScriptAllJobs.txt
M)R!38W)I<'1!;&Q*;V)S+G-Q;" -"B<@.4V-R:7!T<R!A;&P@.:F]B<R!O;B!A
M(&=I=F5N(%-13"!397)V97(@.86YD(')E<&QA8V5S('1H92!H87)D8V]D960@.
M<F5F97)E;F-E<R!T;R!A;&QO=R!T;R!B92 -"B<@.8V]P:65D('1O(&%N;W1H
M97(@.<V5R=F5R#0HG($%N9'D@.0F%L;" R-"\X+S(P,#0)=C$N, E"87-E(%9E
M<G-I;VX-"@.T*3W!T:6]N($5X<&QI8VET#0H)( T*)R!344PM1$U/(&-O;G-T
M86YT<PT*0V]N<W0@.4U%,1$U/4V-R:7!T7T1R;W!S(#T@.,0T*#0I#;VYS="!3
M44Q$34]38W)I<'1?4')I;6%R>4]B:F5C=" ](#0@.#0I#;VYS="!344Q$34]3
M8W)I<'1?5&]&:6QE3VYL>2 ](#8T#0I#;VYS="!344Q$34]38W)I<'1?07!P
M96YD=&]&:6QE(#T@.,C4V#0H-"D-O;G-T(%-13$1-3U-C<FEP=#)?3&]G:6Y3
M240@./2 X,3DR#0H-"E!U8FQI8R!38W)I<'1/8FIE8W1/<'1I;VYS#0I38W)I
M<'1/8FIE8W1/<'1I;VYS(#T@.("!344Q$34]38W)I<'1?4')I;6%R>4]B:F5C
M=" K(%\-"@.D)"5-13$1-3U-C<FEP=%]$<F]P<R -"@.T*1&EM(&]!<F=S(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" ))R!!<F=U;65N=',-"D1I;2!7<VA3:&5L
M; D)"0DG(&9O<B!7:6YD;W=S(%-C<FEP=&EN9R!(;W-T#0I$:6T@.4V5R=F5R
M3F%M92 @.(" @.(" @.(" @.(" @.(" @.(" @.( DG('1H92!397)V97(-"D1I;2!&
M:6QE;F%M90D)"0DG($9I;&5N86UE('1O('=R:71E('1O( T*1&EM(%-13%-T
M<FEN9PT*1&EM($YE=T]W;F5R3F%M92 -"@.T*)R!#<F5A=&4@.4VAE;&P@.;V)J
M96-T#0IS970@.5W-H4VAE;&P@./2!#<F5A=&5/8FIE8W0@.*")7<V-R:7!T+E-H
M96QL(BD-"@.T*)R!087)A;65T97)S#0IS970@.;T%R9W,@./2!7<V-R:7!T+D%R
M9W5M96YT<PT*#0HG(&-H96-K(&-O<G)E8W0@.;G5M8F5R(&]F(&%R9W,-"FEF
M("AO07)G<RY#;W5N=" \/B S*2!4:&5N#0H)5W-C<FEP="Y%8VAO(")5<V%G
M92 Z(&-S8W)I<'0@.4V-R:7!T06QL2F]B<RYV8G,@./'-E<G9E<CX@./$9I;&5.
M86UE/B \3F5W3W=N97(^(@.T*"5=S8W)I<'0N16-H;R B:2YE+B @..B!C<V-R
M:7!T(%-C<FEP=$%L;$IO8G,N=F)S(%!23T1397)V97(@.2F]B<RYS<6P@.<V$B
M#0H)5W-C<FEP="Y1=6ET(" @.( T*16QS90T*"2<@.;VL@.<V\@.87-S:6=N('!A
M<F%M<PT*"5-E<G9E<DYA;64@.(" @.(" ](&]!<F=S*# I#0H)1FEL94YA;64)
M/2!O07)G<R@.Q*0T*"4YE=T]W;F5R3F%M90D](&]!<F=S*#(I#0I%;F0@.268-
M"@.T*)R!-86EN(%-C<FEP= T*4V-R:7!T06QL2F]B<R!397)V97).86UE+"!&
M:6QE3F%M90T*#0HG($5N9"!O9B!-86EN#0H-"E-U8B!38W)I<'1!;&Q*;V)S
M("A397)V97).86UE+"!&:6QE3F%M92D-"@.E$:6T@.;T1A=&%B87-E(" @.(" @.
M(" @.(" @.(" @.(" @.("<@.=&AE('1A<F=E="!D871A8F%S92!T;R!U<V4-"@.E$
M:6T@.4V-R:7!T4W1R:6YG(" @.(" @.(" @.(" @.(" @.("<@.<F5T=7)N<R!I;G1O
M(&AE<F4@.=')A;G-A8W0@.<W%L(&9O<B!O8FIE8W0-"@.E$:6T@.4U%,3W5T<'5T
M1FEL90D))R!F:6QE('1H92!S=&]R960@.<')O8V5D=7)E(&ES('=R:71T96X@.
M=&\@.#0H)1&EM(&]397)V97(-"@.E$:6T@.9G,-"@.T*"41I;2!F<T]U='!U=$9I
M;&4-"@.T*"41I;2!*;V(-"@.E$:6T@.2F]B4W1R:6YG#0H)1&EM($1R;W!%>&ES
M=&EN9TIO8E-T<FEN9PT*#0H))R!C<F5A=&4@.4U%,(%-E<G9E<B!O8FIE8W0-
M"@.E3970@.;U-E<G9E<B ]($-R96%T94]B:F5C="@.B4U%,1$U/+E-13%-E<G9E
M<B(I#0H-"@.DG(&%L=V%Y<R!U<V4@.82!T<G5S=&5D(&-O;FYE8W1I;VX@.86YD
M(&-O;FYE8W0-"@.EO4V5R=F5R+DQO9VEN4V5C=7)E(#T@.5')U90T*"6]397)V
M97(N0V]N;F5C="!397)V97).86UE("<@.=')U<W1E9"!C;VYN96-T:6]N#0H-
M"@.DG(%)O;&P@.=&AR;W5G:"!E86-H(&IO8B!A;F0@.9V5N97)A=&4@.=&AE(%-1
M3"P@.:&%C:R!O=70@.:&%R9&-O9&5D('-T=69F(&%N9"!A<'!E;F0@.=&\@.4V-R
M:7!T4W1R:6YG('9A<FEA8FQE#0H)1F]R($5A8V@.@.2F]B(&EN(&]397)V97(N
M2F]B4V5R=F5R+DIO8G,-"@.D))R!$<F]P('1H92!J;V(@.:68@.:70@.97AI<W1S
M+B!3:&]U;&0@.8F4@.82!$34\@.;W!T:6]N(&9O<B!T:&ES(#\_/S\_#0H)"41R
M;W!%>&ES=&EN9TIO8E-T<FEN9R ](")"14=)3B!44D%.4T%#5$E/3B(@.*R!V
M8D-23$8-"@.D)1')O<$5X:7-T:6YG2F]B4W1R:6YG(#T@.1')O<$5X:7-T:6YG
M2F]B4W1R:6YG("L@.(DE&($5825-44R H4T5,14-4("H@.1E)/32!M<V1B+F1B
M;RYS>7-J;V)S(%=(15)%(&YA;64@./2 G(B K($IO8BY.86UE("L@.(B<I(B K
M('9B0U),1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I
M;F=*;V)3=')I;F<@.*R!V8E1!0B K(")%6$5#(&US9&(N9&)O+G-P7V1E;&5T
M95]J;V(@.0&IO8E]N86UE(#T@.)R(@.*R!*;V(N3F%M92 K("(G(B K('9B0U),
M1@.T*"0E$<F]P17AI<W1I;F=*;V)3=')I;F<@./2!$<F]P17AI<W1I;F=*;V)3
M=')I;F<@.*R B1T\B("L@.=F)#4DQ�H)"4IO8E-T<FEN9R ]($IO8BY38W)I
M<'0@.*%-13$1-3U-C<FEP=%]0<FEM87)Y3V)J96-T*0T*"0E*;V)3=')I;F<@.
M/2!215!,04-%*$IO8E-T<FEN9RP@.(F)E9VEN('1R86YS86-T:6]N(BP@.1')O
M<$5X:7-T:6YG2F]B4W1R:6YG*0T*"0D-"@.D))R!R97!L86-E('1H92!O=VYE
M<B!W:71H($Y53$P@.<V\@.=&AA="!I="!C<F5A=&5S(&IO8B!A< R!T:&4@.=7-E
M<B!E>&5C=71I;F<@.=&AE('-C<FEP= T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!O=VYE<E]L;V=I;E]N86UE(BP@.
M3F5W3W=N97).86UE("D-"@.D))R!297!L86-E(%-E<G9E<FYA;64@.=7-E9"!I
M;B!S<%]!9&1?:F]B7U-E<G9E<B!T;R!B92!T:&4@.8W5R<F5N="!S97)V97(L
M(&]T:&5R=VES92!I="!W:6QL('5S92!T:&4@.<V5R=F5R;F%M92!S8 W)I<'0@.
M9G)O;2!I9B!L;V-A;"!I;G-T86YC90T*"0E*;V)3=')I;F<@./2!297!L86-E
M2&%R9$-O9&5D4F5F*$IO8E-T<FEN9RP@.(D!S97)V97)?;F%M92(L(") 0%-%
M4E9%4DY!344B*0T*#0H)"2<@.061D('1O('1H92!E>&ES=&EN9 R!3=')I;F<-
M"@.D)4V-R:7!T4W1R:6YG(#T@.4V-R:7!T4W1R:6YG("L@.=F)#4DQ&("L@.2F]B
M4W1R:6YG#0H)"5=38W)I<'0N16-H;R B5W)I=&EN9R!J;V(@.(B K($IO8BY.
M86UE("L@.(B!T;R B("L@.1FEL94YA;64-"@.E.97AT( T*#0H))R!.;W<@.9FEN
M86QL>2!W<FET92!T:&4@.9FEL90T*"7-E="!F<R ]($-R96%T94]B:F5C="@.B
M4V-R:7!T:6YG+D9I;&53>7-T96U/8FIE8W0B*0T*"5-E="!F<T]U='!U=$9I
M;&4@./2!F<RY#<F5A=&5497AT1FEL92A&:6QE3F%M92P@.5')U92D-"@.EF<T]U
M='!U=$9I;&4N5W)I=&5,:6YE(%-C<FEP=%-T<FEN9PT*"69S3W5T<'5T1FEL
M92Y#;&]S90T*16YD(%-U8@.T*#0HG(%-E87)C:"!F;W(@.82!3=')I;F<@.=&AA
M="!H87,@.0'-E<G9E<B ](&XG9&]O9F5R)R!T>7!E('-Y;G1A>"!A;F0@.<F5T
M=7)N(&XG9&]O9F5R)R L('=H97)E(%-E87)C:%-T<FEN9R!I<R! <V5R=F5R
M(&EN('1H:7,@.8V%S92X-"B<@.4V\@.=V4@.8V%N(')E<&QA8V4@.:&%R9&-O9&5D
M(')E9F5R;F-E<RX@.#0I&=6YC=&EO;B!297!L86-E2&%R9$-O9&5D4F5F("A3
M44Q3=')I;F<L(%-E87)C:%-T<FEN9RP@.4F5P;&%C95-T<FEN9RD-"@.E$:6T@.
M3F5W4W1R:6YG#0H)1&EM(%-E87)C:%-T<FEN9U!O<PT*"41I;2!396-O;F11
M=6]T95!O<PT*#0H)1&EM($QO9VEN3F%M90T*"41I;2!344Q3=')I; F=,96X-
M"@.T*"5-13%-T<FEN9TQE;B ]($QE;BA344Q3=')I;F<I#0H-"@.DG(&=E="!T
M:&4@.;&]C871I;VX@.;V8@.=&AE(&]W;F5R7VQO9VEN7VYA;64@.8VAA<@.T*"5-E
M87)C:%-T<FEN9U!O<R ]($EN4W1R*#$L(%-13%-T<FEN9RP@.4V5A<F-H4W1R
M:6YG+" P*0T*#0H))R!G970@.82!3=')I;F<@.+"!S=&%R=&EN9R!W:71H( $XG
M;&]G:6X@.;F%M92<-"@.E,;V=I;DYA;64@./2!-:60H4U%,4W1R:6YG+"!396%R
M8VA3=')I;F=0;W,@.*R!,14XH4V5A<F-H4W1R:6YG*2 K(#,L(%-13%-T<FEN
M9TQE;B M(%-E87)C:%-T<FEN9U!O<RD-"@.D-"@.E396-O;F11=6]T95!O<R ]
M($EN4W1R*#,L($QO9VEN3F%M92P@.(B<B+" P*0D)#0H)3&]G:6Y.86UE(#T@.
M36ED*$QO9VEN3F%M92P@.,2P@.4V5C;VYD475O=&50;W,I#0H)# 0H))R!74V-R
M:7!T+D5C:&\@.3&]G:6Y.86UE#0H)4F5P;&%C94AA<F1#;V1E9%)E9B ](%)E
M<&QA8V4H4U%,4W1R:6YG+"!,;V=I;DYA;64L(%)E<&QA8V53= ')I;F<I#0I%
6;F0@.1G5N8W1I;VX@.#0H-"@.T*#0H-"@.``
`
end
|||... or use the DTS Transfer Jobs task !
"Hassan" wrote:
> I know of a way to script all SQL Agent jobs using EM. But i want to script
> all my jobs on a daily basis and copy that script file to another server . I
> know how i can do the daily part of it which would be scheduling it again
> but whats the TSQL or command I need to run within the job step .. Thanks
>
>
Subscribe to:
Posts (Atom)