Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
SridharYes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridhar

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
Sridhar
Yes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridhar

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
SridharYes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridharsql

copying users between roles

I would like to copy all users currently from RoleA into RoleB. I know there
must be a nice way to script this. Any ideas?Robert
There is no a "nice" script to move users from one Role to another at least
I am not aware.
You will have to deal with system tables that hold the info about users but
it is not recommended.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:66EA482B-0FD9-49A9-B049-6CADCA5B5BD1@.microsoft.com...
>I would like to copy all users currently from RoleA into RoleB. I know
>there
> must be a nice way to script this. Any ideas?|||Hi Robert,
This may help u......a nice script to move all users from roleA to roleB
create table #users (
UserName sysname,
GroupName sysname,
LoginName sysname,
DefDBName sysname,
UserID smallint,
SID smallint
)
insert #users
exec('sp_helpuser')
select identity(int,1,1) as idn, * into #usersInRoleA from #users where
GroupName like 'roleA'
declare @.i as int,
@.maxusers as int,
@.username as sysname
select @.maxusers = max(idn) from #usersInRoleA
set @.i = 1
while (@.i <= @.maxusers)
begin
select @.username = username from #usersInRoleA where idn = @.i
exec sp_addrolemember 'roleB',@.username
exec sp_droprolemember 'roleA',@.username
set @.i = @.i + 1
end
drop table #usersInRoleA
drop table #users
enjoy and keep going...
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?|||Hi Robert
Maybe something like:
CREATE TABLE #Rolemembers ( DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) )
INSERT INTO #Rolemembers ( DbRole, MemberName, MemberSID )
EXEC sp_helprolemember 'ISDLOAD_ROLE'
DECLARE @.member sysname
DECLARE Member_Cursor CURSOR FOR SELECT MemberName FROM #Rolemembers
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor INTO @.member
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_executesql N'EXEC sp_addrolemember @.rolename= ''ROLEB'' ,
@.membername = @.membernm' ,
N'@.membernm sysname',
@.membernm = @.member
FETCH NEXT FROM Member_Cursor INTO @.member
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
DROP TABLE #Rolemembers
John
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?

Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure an
d
the contents. Is this possible and how can I go about it.
--
Beaversuse import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure
and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:
>

Copying Tables Across Servers

I'm trying to copy data from a remote server connected thru enterprise
manager. I' assuming there shld be a means to script the table structure and
the contents. Is this possible and how can I go about it.
Beavers
use import/export wizard
"Beavers" wrote:

> I'm trying to copy data from a remote server connected thru enterprise
> manager. I' assuming there shld be a means to script the table structure and
> the contents. Is this possible and how can I go about it.
> --
> Beavers
>
|||I had problems with that because of the server names. Nevertheless I have
managed to copy with the age old copy and paste function.
Thanks!
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use import/export wizard
>
> "Beavers" wrote:

copying table data from 1 dbase to another

HI just had a question on this. I was able to copy the table using the
script, create in query analizer but am not quite sure how to copy the data.
I tried the (script object to window as Select) for the source data, and then
switched to the destination dbase and table and selected script object to new
window insert. For the insert I get the error though, Incorrect syntax near
'<'.,line 3, also not quite sure if this is the correct method to use.
this is the insert code created automatically that does not compile correctly.
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
VALUES(<Arrive_Depart_ID,int,>,
<Arrive_Depart_VC,varchar(50),>)
--
Paul G
Software engineer.If the table is already on the OTHER database try
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.|||You can use DTS to move tables and data between servers.
Since you already have the table created you could use DTS to move the data
OR
you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
in to the other server
OR
you could use a linked server or openrowset to select (and insert) the data
something like this (on the server that you are trying to populate)
INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
AnotherColumnName...)
SELECT A.ColumnName, A.AnotherColumnName...
FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
/*this next step is not needed if the destination table is truly empty*/
WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)
> --
> Paul G
> Software engineer.|||Several methods would work, in your example you are missing the SELECT
statement (see BOL INSERT statement for examples). If the column names are
the same on source and target, your SQL should look something like:
INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
SELECT * FROM SourceTable
Steve
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> HI just had a question on this. I was able to copy the table using the
> script, create in query analizer but am not quite sure how to copy the
data.
> I tried the (script object to window as Select) for the source data, and
then
> switched to the destination dbase and table and selected script object to
new
> window insert. For the insert I get the error though, Incorrect syntax
near
> '<'.,line 3, also not quite sure if this is the correct method to use.
> this is the insert code created automatically that does not compile
correctly.
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> VALUES(<Arrive_Depart_ID,int,>,
> <Arrive_Depart_VC,varchar(50),>)|||Hi thanks for the response, tried this,-DMLinter is the destination dbase
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
but get the error,
Cannot insert explicit value for identity column in table
'DML$Arrive_Depart_T' when IDENTITY_INSERT is set to OFF.
just wondering if you know how to set identity_insert to on?
"Wayne Snyder" wrote:
> If the table is already on the OTHER database try
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> ([Arrive_Depart_ID], [Arrive_Depart_VC])
> Select [Arrive_Depart_ID], [Arrive_Depart_VC] from origtableinlocaldatabase
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
> > --
> > Paul G
> > Software engineer.
>
>|||The following seemed to work, both dbases are on the same server.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
Have not played around with DTS yet but seems pretty useful.
thanks.
"Keith Kratochvil" wrote:
> You can use DTS to move tables and data between servers.
> Since you already have the table created you could use DTS to move the data
> OR
> you could BCP / BULK COPY the data out of one server and BCP or BULK COPY it
> in to the other server
> OR
> you could use a linked server or openrowset to select (and insert) the data
> something like this (on the server that you are trying to populate)
> INSERT INTO TheDatabase.TheOwner.TheTable (ColumnName,
> AnotherColumnName...)
> SELECT A.ColumnName, A.AnotherColumnName...
> FROM LinkedServerName.TheDatabase.TheOwner.TheTable A
> /*this next step is not needed if the destination table is truly empty*/
> WHERE NOT EXISTS (SELECT * FROM TheDatabase.TheOwner.TheTable B WHERE
> A.ThePrimaryKeyColumn = B.ThePrimaryKayColumn)
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
> > --
> > Paul G
> > Software engineer.
>|||This below seems to work, had to set the Identity_insert to on.
SET IDENTITY_INSERT [DMLinter].[dbo].DML$Arrive_Depart_T ON
--IDENTITY_INSERT = ON
INSERT INTO [DMLinter].[dbo].[DML$Arrive_Depart_T]
([Arrive_Depart_ID], [Arrive_Depart_VC])
Select [Arrive_Depart_ID], [Arrive_Depart_VC] from
[DML].[dbo].[DML$Arrive_Depart_T]
"Steve Thompson" wrote:
> Several methods would work, in your example you are missing the SELECT
> statement (see BOL INSERT statement for examples). If the column names are
> the same on source and target, your SQL should look something like:
> INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> SELECT * FROM SourceTable
> Steve
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:5F1EBBC2-3897-4935-B004-54687848335E@.microsoft.com...
> > HI just had a question on this. I was able to copy the table using the
> > script, create in query analizer but am not quite sure how to copy the
> data.
> > I tried the (script object to window as Select) for the source data, and
> then
> > switched to the destination dbase and table and selected script object to
> new
> > window insert. For the insert I get the error though, Incorrect syntax
> near
> > '<'.,line 3, also not quite sure if this is the correct method to use.
> > this is the insert code created automatically that does not compile
> correctly.
> > INSERT INTO [DML].[dbo].[DML$Arrive_Depart_T]
> > ([Arrive_Depart_ID], [Arrive_Depart_VC])
> > VALUES(<Arrive_Depart_ID,int,>,
> > <Arrive_Depart_VC,varchar(50),>)
>
>

copying selective tables with SMO(VB)

Hi ,

I’m using following VB script (SMO) to copy tables between instances and it works fine. At the moment

xfr.CopyAllTables = True

but I would like to be able to be selective (list of table) that would be transferred.

Dim svr_connection As ServerConnection = New ServerConnection()

svr_connection.ServerInstance = "TECH-2L56H2J\sql2k5"

'svr_connection.ConnectionString = "Data Source=TECH-907WL2J;Initial Catalog=CustomerDM_Control;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

dest_svr_connection.ServerInstance = "TECH-2L56H2J\sql2k5_2"

'dest_svr_connection.ConnectionString = "Data Source=TECH-907WL2J;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("adventureworks")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "adventureworkscopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

xfr = New Transfer(db)

xfr.CopyAllTables = True

'xfr.ObjectList.Add("Shaunt")

xfr.DestinationDatabase = "adventureworkscopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2"

'xfr.DestinationLoginSecure = True

xfr.CopySchema = True

xfr.CopyData = True

xfr.CopySchema = True

xfr.Options.WithDependencies = True

'Script the transfer. Alternatively perform immediate data transfer with TransferData method.

'xfr.ScriptTransfer()

xfr.TransferData()

can you give me any idea?

Thanks

Moving thread...

Copying of database

I tried to write a batch script which copies a database by making full
backup, then copying the backup file to another location and restoring it.
The backup schedule of the my database is this:
full backup on sunday at 23 h stored in file1
differential backup every day except sunday at 23h stored in file2
Now i'm not sure what will happen with my differential scheduled backups
when i delete the temporary backup file that is made only for copying during
the w. It's stored in another file (let's call it file3). I see that SQL
Server remembers every full backup, is there a way to tell it not to write
to backup history tables?
I'm thinking of another ways of copying database. One is to detach the
database or to stop the server but i think it's not good. Another way is DTS
package. Can you tell me if it can do the trick? Is there another way?
Thank you in advance
Georgi PeshterskiWays of copying a database:
1. Certainly backup and restore is an option, but not the first I would take
.
2. Detach, copy and attach also works, but it is kludgy.
3. Snapshot replication works well if you desire a snapshot to copy at any
one time (ie, the update is the entire database and instant update of data i
s
not important).
4. Transaction replication works well if you need real time updates
5. DTS works, as well, but can end up with unnecessary locking on the
original database while you are migrating. You have some control over this,
of course, but it gets more advanced.
I would aim for replication or DTS long before using a backup/restore or
detach/copy/attach scenario.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"George Peshterski" wrote:

> I tried to write a batch script which copies a database by making full
> backup, then copying the backup file to another location and restoring it.
> The backup schedule of the my database is this:
> full backup on sunday at 23 h stored in file1
> differential backup every day except sunday at 23h stored in file2
> Now i'm not sure what will happen with my differential scheduled backup
s
> when i delete the temporary backup file that is made only for copying duri
ng
> the w. It's stored in another file (let's call it file3). I see that SQ
L
> Server remembers every full backup, is there a way to tell it not to write
> to backup history tables?
> I'm thinking of another ways of copying database. One is to detach the
> database or to stop the server but i think it's not good. Another way is D
TS
> package. Can you tell me if it can do the trick? Is there another way?
> Thank you in advance
> Georgi Peshterski
>
>|||Thank you, i'm working to do it with DTS
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:F34C25E5-AD13-4255-B1E0-38CBD3481C5F@.microsoft.com...
> Ways of copying a database:
> 1. Certainly backup and restore is an option, but not the first I would
take.
> 2. Detach, copy and attach also works, but it is kludgy.
> 3. Snapshot replication works well if you desire a snapshot to copy at any
> one time (ie, the update is the entire database and instant update of data
is
> not important).
> 4. Transaction replication works well if you need real time updates
> 5. DTS works, as well, but can end up with unnecessary locking on the
> original database while you are migrating. You have some control over
this,
> of course, but it gets more advanced.
> I would aim for replication or DTS long before using a backup/restore or
> detach/copy/attach scenario.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "George Peshterski" wrote:
>
full
it.
backups
during
SQL
write
the
DTS

Copying jobs to a new server.

We are moving SQL Server (2000) to a bigger dedicated server.
Is there a faster way to move all the jobs than creating a script for each
one and then running that script in query analyzer?
TIA
Mike
There is a "Transfer Jobs" Task in DTS That will do that for you.
Greg Jackson
PDX, Oregon
|||Hi,
There are 2 more options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"Michael Beck" <Mikeb46NoSpam@.pacbell.net> wrote in message
news:e9ySexfaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> We are moving SQL Server (2000) to a bigger dedicated server.
> Is there a faster way to move all the jobs than creating a script for each
> one and then running that script in query analyzer?
> TIA
> Mike
>

Copying jobs to a new server.

We are moving SQL Server (2000) to a bigger dedicated server.
Is there a faster way to move all the jobs than creating a script for each
one and then running that script in query analyzer?
TIA
MikeThere is a "Transfer Jobs" Task in DTS That will do that for you.
Greg Jackson
PDX, Oregon|||Hi,
There are 2 more options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"Michael Beck" <Mikeb46NoSpam@.pacbell.net> wrote in message
news:e9ySexfaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> We are moving SQL Server (2000) to a bigger dedicated server.
> Is there a faster way to move all the jobs than creating a script for each
> one and then running that script in query analyzer?
> TIA
> Mike
>

Copying jobs to a new server.

We are moving SQL Server (2000) to a bigger dedicated server.
Is there a faster way to move all the jobs than creating a script for each
one and then running that script in query analyzer?
TIA
MikeThere is a "Transfer Jobs" Task in DTS That will do that for you.
Greg Jackson
PDX, Oregon|||Hi,
There are 2 more options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"Michael Beck" <Mikeb46NoSpam@.pacbell.net> wrote in message
news:e9ySexfaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> We are moving SQL Server (2000) to a bigger dedicated server.
> Is there a faster way to move all the jobs than creating a script for each
> one and then running that script in query analyzer?
> TIA
> Mike
>

Copying encrypted objects ?

Hi,

I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.

Any help appreciated.

James.

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose.|||I tried this out in DTC and although the procedure seems to run successfully, the function still does not appear in the destination database. I take it from this that you cannot copy encrypted functions using DTC unless there is a certain way of doing it?|||Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server.|||

I thought this would be the case.

thank you for your help :)

Copying encrypted objects ?

Hi,

I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another? I don't really care to know the contents of the function.

Any help appreciated.

James.

I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose.|||I tried this out in DTC and although the procedure seems to run successfully, the function still does not appear in the destination database. I take it from this that you cannot copy encrypted functions using DTC unless there is a certain way of doing it?|||Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server.|||

I thought this would be the case.

thank you for your help :)

Copying DTS Packages to new server

Is there any way/an easy way to copy dts packages from one server to another? I cant seem to find a way to script them, is there any other way other than recreating them on the new server?
Regards JimPackages are saved in msdb..sysdtspackages - you just need to copy the relevant rows from that table.
Note that sysdtspackages is undocumented so this may not work in the future.

If you prefer this is an SP to load all packages and save them to files - just change the save to save to a server. This will lose the graphics as there is no context for it and no way at present to load the stream.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

Create procedure s_SavePackages
@.Path varchar(128)
as
/*

*/

set nocount on

declare @.objPackage int
declare @.PackageName varchar(128)
declare @.rc int
declare @.ServerName varchar(128)
declare @.FileName varchar(128)
declare @.FilePath varchar(128)
declare @.cmd varchar(2000)

select @.ServerName = @.@.ServerName ,
@.FilePath = @.Path

if right(@.Path,1) <> '\'
begin
select @.Path = @.Path + '\'
end

-- create output directory - will fail if already exists but ...
select @.cmd = 'mkdir ' + @.FilePath
exec master..xp_cmdshell @.cmd

create table #packages (PackageName varchar(128))
insert #packages
(PackageName)
select distinct name
from msdb..sysdtspackages

select @.PackageName = ''
while @.PackageName < (select max(PackageName) from #packages)
begin
select @.PackageName = min(PackageName) from #packages where PackageName > @.PackageName

select @.FileName = @.FilePath + @.PackageName + '.dts'

exec @.rc = sp_OACreate 'DTS.Package', @.objPackage output
if @.rc <> 0
begin
raiserror('failed to create package rc = %d', 16, -1, @.rc)
return
end

exec @.rc = sp_OAMethod @.objPackage, 'LoadFromSQLServer' , null,
@.ServerName = @.ServerName, @.Flags = 256, @.PackageName = @.PackageName
if @.rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

-- delete old file
select @.cmd = 'del ' + @.FileName
exec master..xp_cmdshell @.cmd, no_output

exec @.rc = sp_OAMethod @.objPackage, 'SaveToStorageFile', null, @.FileName
if @.rc <> 0
begin
raiserror('failed to save package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

exec @.rc = sp_OADestroy @.objPackage
end
go

Copying detached databases over the network with T-SQL

I have a T-SQL script which deataches and attaches the database. Now here is what I want to do:
I

want to create a SQL Server Job and schedule it to run at a particular

time of the month to Detach all the databases on my local machine and

*Copy all the deatched databases over a network machine* and then

Attach those Database.

All this should be done in T-SQL. I

already have the T-SQLs for Attaching and Detaching. All I need is to

know the T-SQL (example) which will copy the *Detached Databases* from

mu local computer to my Network Computer. How do I achieve that?

Thanks

xp_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

xp_cmdshell 'copy c:\x.mdb y:\x.mdb'

where y: is a mapped network drive

|||You can invoke DOS command through sqlserver (e.g. "copy <source> <\\target>"). You want to take a look at xp_cmdshell in book online for details.|||

aside from using the sql jobs you can use

the "windows scheduler" to

1. detach the database from the source server using sqlcmd

2. use dos command to copy the database

3. use sqlcmd to attach the db to the destination server

I recommend this method over the other

cheers

|||Thank you guys for you replies. I think I may take the path of xp_cmdshell.

joeydj your suggestion looks valid, but I have a question. What is the full command(an example) to detach the database using sqlcmd? Also do I need to run this command on 'Command Prompt'.

Thanks
|||You can use sp_detach_db to detach a database. See BOL for syntax and examples. It is easier doing these type of operations outside the database. Note that in SQL Server 2005 xp_cmdshell is disabled my default on most SKUs & enabling it increases the security risk on the server. So don't use it unless you absolutely need to. Writing a batch file to do these operations is very trivial.|||

for sql2k you can use OSQL the SQLCMD 2005 counterpart

and here's the syntax

c:\ osql -USa -P -S(local)\sql2k -Q"sp_detach_db demodb" -X

where

-Usa --user Sa

-P -- password in my case blank

-S(local)\sql2k --is the server

-Q"sp_detach_db demodb" -- this is the query to detach and watchout for the quotes

-X exit OSQL

sqlcmd has the same syntax except that it handles blank password differently

c:\ SQLCMD -USa -Pmypaswd -S(local)\sql2k -Q"sp_detach_db demodb" -X

you cann use the -E switch for trusted connection

for more help type sqlcmd/? or osql/? on your command prompt

copying data and structure from one database to another

Hi all!

I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way? Can I use bcp and then use xp_cmdshell from within
my stored procedure? It's not clear to me from the documentation
whether bcp copies both structure and data or just data? Is there a
better way?

Thanks in advance for any help!
Karen[posted and mailed, posted and mailed]

(kjphipps_377@.hotmail.com) writes:
> I have an application that needs to copy the database structure from
> one database to another without using the "Generate SQL Script"
> function in Enterprise Manager. I'd like to do this from within a
> stored procedure. Can someone recommend the best approach for this?
> I've seen references to using SQL-DMO from a stored procedure using the
> sp_OA* procs in other postings to this group but was wondering if there
> was an easier way? Can I use bcp and then use xp_cmdshell from within
> my stored procedure? It's not clear to me from the documentation
> whether bcp copies both structure and data or just data? Is there a
> better way?

bcp copies only the data.

If you absolutely must copy table definitions and all from a stored
procedure, you are in for a painful exercise. I'd guess that DMO is
the way to go. You could read the system tables and construct SQL
from there, but that would be even more difficult. Particularly if
you need to take in regard that a stored procedure could extend over
more than 4000 characters.

But overall, I would recommend you to review the requirements. T-SQL
is simply not the right tool do this. If you absolutely must fire
a stored procedure, I would recommend writing a program in Perl,
VBscript or whatever, and call that program from xp_cmdshell. But it
goes without saying that it would be better to run this from the
application directly.

Also when running from an application, DMO may be the best pick. I
don't have any experience of DMO myself, so I don't know for sure
whether there is any built-in scripting facilities, but I would
expect there to be.

The general for creating database, is to keep code under source
control, and build the database from the version-controlled scripts.

To copy the data, bcp would still be necessary, but that's the easy
part of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

copying a table

Hi,o

I found an option to copy a table without having to script the table, now I can't find it. Is there an option to do this? When I selected the option, it didn't work. Any ideas? Also how do you turn off trace and statistics?

thx,

Kat

select * into newtable from old table

dbcc traceoff

CopyFile in DTS ActiveX Script Task

I am trying to copy a file and giving it a name with a date behind it. Belo
w
is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File Not
Found" message on Line 25 (I marked it below). Can someone see the problem
with my code'
Dim NYear
Dim NMonth
Dim NDate
NYear = Year(Date)
NMonth = Month(Date)
NDate = CStr(NYear) + CStr(NMonth)
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile =
" \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetailYYYYMM
"
sDestinationFile =
" \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetail" +
"_RunDate_" + NDate
oFSO.CopyFile sSourceFile, sDestinationFile '***(This is Line 25)***
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_SuccessHi
I assume you have tried outputting the file names in a MsgBox and validated
that they are correct? http://www.sqldts.com/default.aspx?292 has an example
of using the filesystem object to copy a file, and
http://www.sqldts.com/default.aspx?200 has an example of using the date as
part of a filename.
Make sure that the account that you are running this has permissions to the
share, it may be worth getting it working with a local drive first.
John
"atchleykl" wrote:

> I am trying to copy a file and giving it a name with a date behind it. Be
low
> is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File N
ot
> Found" message on Line 25 (I marked it below). Can someone see the proble
m
> with my code'
> Dim NYear
> Dim NMonth
> Dim NDate
> NYear = Year(Date)
> NMonth = Month(Date)
> NDate = CStr(NYear) + CStr(NMonth)
> Dim oFSO
> Dim sSourceFile
> Dim sDestinationFile
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> sSourceFile =
> " \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetailYYYY
MM"
> sDestinationFile =
> " \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetail" +
> "_RunDate_" + NDate
> oFSO.CopyFile sSourceFile, sDestinationFile '***(This is Line 25)***
> ' Clean Up
> Set oFSO = Nothing
> Main = DTSTaskExecResult_Success
>

Copy tables on remote servers

Hi all, I'm trying to create a script / stored proc that copies one table from a remote server to another table on a different remote server.

The approach I wanted to take was...

create a stored proc that can be called remotely

the stored proc tests to see if the table exists, if it does it is dropped, if not it is created

new table structure and data copied from remote server A to remote server B

...I feel that this should be quite easy, but I must admit I'm struggling. Any help would be really appreciated.

Cheers, Jon

Did you try to setup a linked server and then do a SELECT INTO?

How about using DTS instead?

|||

Thanks for your reply William.

I have tried SELECT INTO...

select * into remoteServer.intranetcms.dbo.woodford_bridge
from localServer.intranetcms.dbo.woodford_bridge

... but I get the error...

The object name 'remoteServer.intranetcms.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

...I am familiar with DTS but what I would like to do is create a stored proc that can be executed inside an on_click event from a web page.

- Jon

|||

In Ado.net, there is a pretty good method for doing this. The api name is SqlBulkCopy.

Let me know if you want to know more about the api.

Thanks

Bei

|||

Thanks Bei, unfortunately we're not using .Net 2 so, as I understand it, can't use SQLBulkCopy. Any other ideas?

- Jon

|||

Hi, maybe very late, but I ran into the same problem and solved this by doing this:

Ex:

SELECT t.Bilagnr

FROM [172.18.165.25\ASNV].[AS_14830].[dbo].[tblSalesDetails] as t WHERE Pkey > 1

Hope this helps!

- Per S.

|||

I'll recommend the use of DTS. then

create an SP that triggers the DTS thru xp_cmdshell using dtsrun.

call the SP from ASP.net