Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DTS: Copy SQL Server object - why is doing this..

Posted on 2004-08-12
9
Medium Priority
?
536 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:hydev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11785770
If you have "Copy PRIMARY and FOREIGN keys" checked then it is trying to modify the database, but I don;t think that is what is creating the problem. Is 'xxxxxx' the source or the target database?
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.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11785784
All three error messages are do to having "Copy PRIMARY and FOREIGN keys" checked, without passing authentication credentials with enough permissions for that.
0
 

Author Comment

by:hydev
ID: 11786853
'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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 34

Expert Comment

by:arbert
ID: 11788458
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....
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 11797541
ask DBA to help you
0
 

Author Comment

by:hydev
ID: 11811160
Thanks for the suggestions I have turned off the "Copy PRIMARY and FOREIGN keys" and it still happens.  Guess its something else..
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11811770
Have you tried a straghtforward transform data task, as opposed to a copy object task?
This should be a more striaghtforward INSERT type statement.
0
 
LVL 34

Accepted Solution

by:
arbert earned 1500 total points
ID: 11812101
You said  "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? "

So, like I said above, if it indeed does work correctly when you run it, but fails when you schedule it, it's a permissions problem...
0
 

Author Comment

by:hydev
ID: 11840322
I think you are pointing me in the correct direction so I shall award points on this one.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question