Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pete Brown
Starting Member
1 Post |
 Posted - 2002-09-12 : 10:02:56
|
| We have a development server that is used by our developers each of which utilise their own database e'g 'Fred' to isolate them from any other developer. Once a system has been developed/tested we need to move all the associated DTS jobs/SqlAgent jobs to the production datawarehouse server that has a single database name of DW and have encountered the following:-DTS Jobs can be transferred by using 'save as' from the dts designer and saving the job to the new server, however, all the data transformation tasks still carry a database name pre-fix of 'FRED' that does not exist on the new server therefore the job will not run. Is there any way to overcome this problem without having to edit all of the task steps to reflect the new server's database name ?How to we copy SQL Agent jobs from one server to another? (we cannot find an option or method to do this) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-12 : 10:26:03
|
| I thought about producing an article on this if it hasn't already been produced.You can include a startup step in the package and execute something like: Dim intCount For intCount = 1 To objPackage.Connections.Count If objPackage.Connections(intCount).ProviderID = "SQLOLEDB.1" Then objPackage.Connections(intCount).DataSource = strServer objPackage.Connections(intCount).Catalog = strDatabase End If Next the server and database names can be set as a global variable in the package.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-09-12 : 19:26:12
|
| To answer your last question about SQL Agent Jobs, I just had EM generate scripts for mine and then ran the scripts in the new server. |
|
|
|
|
|
|
Moving developed systems bteween servers