DTS: Copy SQL Server object - why is doing this..
Posted on 2004-08-12
I have a local SQL database and I want to update any modified tables to my hosted SQL database. I can't use replication as I don't have the sysadmin permissions (I totally understand that). So I have come up with the idea of using a DTS package to Copy the tables across. I have set a task up in the COPY SQL SERVER OBJECTS options. I have selected my source, my destination and my things to copy. In the copy tab I have unticked everything EXCEPT the "Copy Data -> Replace Existing Data" (I want to do this) and in the "SELECT OBJECTS" I have only ticked the tables I want to copy. In the "OPTIONS" I have unticked everything except the "Copy PRIMARY and FOREIGN keys".
So when I run it it works fine but then fails and reports the following :
[Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'xxxxxx'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statment failed
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed
Okay, the data goes across fine and I get the update - but only when i run it manually! If I schedule it no data gets transferred. So my problem is why is it trying to ALTER DATABASE? What options is using in sp_dboption? Why is it altering my database - surely it is just dropping and creating tables for which I have permissions to do!? And if this is never going to work how can I transfer tables easily between servers?
Any help greatly appreciated