DTS: Copy SQL Server object - why is doing this..
Hi,
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
Mike
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
Mike
All three error messages are do to having "Copy PRIMARY and FOREIGN keys" checked, without passing authentication credentials with enough permissions for that.
ASKER
'xxxxx' is the name of my target database on the hosted server.
If the error is due to the "Copy PRIMARY and FOREIGN keys" how come it lets me create and set them in enterprise manager? Is that slightly different? I appreciate I do log in to the server with enterprise manager.
If the error is due to the "Copy PRIMARY and FOREIGN keys" how come it lets me create and set them in enterprise manager? Is that slightly different? I appreciate I do log in to the server with enterprise manager.
When you schedule it, you aren't the user any more. The user that logs on the SQLagent is the user/password that is used....
ask DBA to help you
ASKER
Thanks for the suggestions I have turned off the "Copy PRIMARY and FOREIGN keys" and it still happens. Guess its something else..
Have you tried a straghtforward transform data task, as opposed to a copy object task?
This should be a more striaghtforward INSERT type statement.
This should be a more striaghtforward INSERT type statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think you are pointing me in the correct direction so I shall award points on this one.
If all you want to do is copy data, not the objects themseleves, you can do a simple transform task rather than a copy objects task.
You can pre-clude it with a truncate statement Execute SQL step if that is waht you need.