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 |
|
chrisaldrich
Starting Member
3 Posts |
 Posted - 2008-07-24 : 18:33:15
|
| I have been trying to set up an Import from one SQL server to another. Each time I complete the DTS Import wizard it fails with [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_add_job' database 'msdb'I have tried everything that I can think of and done an exhaustive search for a solution. The source SQL database is hosted on a shared SQL server where you only have access to your own database (not the whole server), its SQL Server 2000 but I am not sure of the exactly version. The destination server is again SQL Server 2000 and its on my hosted server to which I have complete control. Its SQL server that came with SBS 2003. I mention this in case there is an issue with the version of SQL Server I am using.I did manage to set up an import a few weeks ago however its showing as having failed for a while. unfortunately I cant recreate the procedure I used to set it up.Can anyone help please? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-24 : 20:47:55
|
| You import data from user db? Tried to schedule the package as sql job? |
 |
|
|
chrisaldrich
Starting Member
3 Posts |
Posted - 2008-07-25 : 07:39:35
|
| Hi, thanks for replying. I am not sure what you mean by "user db". I am trying to import a database from one SQL server to another SQL server. These are tables holding web site data.I have been trying to use the DTS Import wizard using "Copy objects and data between SQL server databases", I may be wrong but this creates a SQL job? If you know of an alternative way of scheduling this import please can you let me know? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-26 : 18:50:31
|
| There are system dbs (master , msdb, model and tempdb) and user dbs (non-system dbs) in sql. Yes, you can build pachake with data import wizard, save it in sql server then run it as job in schedule. |
|
|
|
chrisaldrich
Starting Member
3 Posts |
Posted - 2008-08-11 : 07:16:03
|
| Hi, sorry for the lengthy delay, but yes this is a user db. |
|
|
|
|
|
|
Scheduled DTS Import Failure