May 29, 2006 at 2:20 am
Hi all,
I got to maintain an application designed by somebody else. The DB for the application is to be replicated everyday. Its designer created DTS packages to to perform the replication from an SQL Server to an other.
I noticed one of the packages fails with the following error:
Step Error Source: Microsoft SQL-DMO
Step Error Description:[SQL-DMO]The Bulk Copy execution failed.
Step Error code: 80045707
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131
The help file does not give me more details: SQLDMO_E_BCPEXECFAILED0x5707Bulk copy operation failed. Returned by the ExportData or ImportData method.
The package has a unique activity "Copy SQL Server objects": logs into DB A (1st machine) and copies a set of tables into DB B (2nd machine), it has the following options:
** Create destination object: Drop destination objects, include extended properties
** Copy data: replace existing data.
Do you have an idea on what might cause the above error ?
thx in advance,
/A
PS: I am not a DBA. I have a side question: why did he use DTS to replicate the DB ? isn't the replication service of sql server sufficient ?
May 30, 2006 at 2:08 am
More often than not, errors are down to security. Check that the account that is trying to drop and create objects has the necessary permissions. Remember that if the job is running as a scheduled job, the account performing the actioon is the sql server agent account.
I also noticed that ** Create destination object: Drop destination objects is out of step. Shouldn't it be the other way around.
Also, make sure that you have logging turned on in the package. That way, you should see more detail in the log as to what is going wrong.
In regards to why dts was used, I'd say it was for convenience rather than functionality. Replication involves more work and is generally used for real-time synchronisation. If the data only needs to be in synch once a day, then replication is overkill.
May 30, 2006 at 3:00 am
Thanks Jonathan for your answer. My comments below:
More often than not, errors are down to security. Check that the account that is trying to drop and create objects has the necessary permissions. Remember that if the job is running as a scheduled job, the account performing the actioon is the sql server agent account.
=> I'll check this. But, if it was a security issue, wouldn't the error be systematic ? In my case, the DTS packages succeed some times. Could it be related to volume ? how do i tune the bulkcopy for volume ?
I also noticed that ** Create destination object: Drop destination objects is out of step. Shouldn't it be the other way around.
=> Sorry I did not get your point, how is it out of step? I stated the options in the copy activity inside the DTS package.
Also, make sure that you have logging turned on in the package. That way, you should see more detail in the log as to what is going wrong.
=> I did this, that's how I got a little more details on the error. The error log I posted first is a package log.
In regards to why dts was used, I'd say it was for convenience rather than functionality. Replication involves more work and is generally used for real-time synchronisation. If the data only needs to be in synch once a day, then replication is overkill.
=> Thx, i'll leave it as is.
May 30, 2006 at 7:16 am
Does this situation apply in your case?
May 31, 2006 at 7:18 am
Actually, no, there aren't any BLOBs in the copied tables.
Our SysAdmin tells me sql server 2k sp3a might fix the issue but I still haven't my answer as to what causes this.
June 28, 2006 at 12:31 pm
I have almost the same exact problem. Package didn't start failing until recently and as far as I know, not much has changed (server settings and security have not changed). I have the same exact error code and description. Still debugging.
My guess is that there might be some interruption going at the time the package runs. This could be anything from someone rebooting a server or restarting services, or more likely a network outage - which in turn could be from network hardware cycling (or other problem), or ISP service going down (in my case I'm pulling the tables over the internet from a hosted server and it partially transfers the tables before erroring out). Not sure a service pack could help.
Did you ever resolve this?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply