February 9, 2006 at 2:26 am
Hi,
I have a DTS package that is scheduled to run each night. The job basically copies the data from one sql server to another sql server, It has been failing since the application and database upgrade however the job was set up before the upgrade and working OK. In the error log I get this error
Package Steps execution information:
Step 'Copy SQL Server Objects' failed
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42S02)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'GL_DISTRIBUTION'.
Step Error code: 800400D0
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131
Ive checked the package but Ive noticed that there is no object called 'GL_DISTRIBUTION' in either the source or destination databases and is not in the DTS package….
Anyone possibly recommend a way to trouble shoot this please……?
February 9, 2006 at 7:16 am
Possibly the upgrade removed some tables (or other objects). If the copy was set up before something was removed I guess it would fail. If the job is a 'Copy SQL Server Objects' task go to the copy tab, uncheck 'Copy all objects' if it is checked, and click the cick the select objects button to see what is being copied.
I would save the package under a new name before altering it.
February 9, 2006 at 10:10 am
I agree with allen, do a restore from your upgraded db to the backup.This will accomplish two thing. One its a good time to test your restore. Two you will now have duplicate database, then retry your dts.
GOOD LUCK
February 10, 2006 at 2:22 am
February 10, 2006 at 3:38 am
Hi - anything in the windows event log? If you can run it interactively you can double click on the error messsage for more info.
You can switch on package logging - right mouse the background of the package designer -> Package Properties -> Logging -> Select 'Log Package Execution' you might also specify an error file. Package execution is logged in the SQL logs directory with a fileaname like sysdtspackagelog. Steps are recorded in msdb..sysdtssteplog.
Does the account running the package (SQL Agent?) have sufficient permissions. If you are doing stuff between servers I think it needs to be a domain account.
February 10, 2006 at 7:25 am
February 10, 2006 at 7:31 am
Sorry if I was not clear - does the account that the SQL Agent runs under have permissions. Right mouse agent in EM -> properties -> general tab?
February 10, 2006 at 9:39 am
You didn't answer Allen's question about whether you can execute the DTS package manually. From the message you posted, I think the problem is in the package, not the job.
Open the Copy SQL Server Objects task in the package, again as Allen suggested, and see what objects are being copied.
Greg
Greg
February 13, 2006 at 3:14 am
Hi
The package executes automatically and I have checked all objects to copy..however still get the message
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'GL_DISTRIBUTION'
I checked both source and destination databases but there is no object called
'GL_DISTRIBUTION'......
any suggestions please???
February 13, 2006 at 8:17 am
When I can't find the error it's usually because I'm looking in the wrong place.
Is 'GL_DISTRIBUTION' owned by other than dbo?
Does it copy any objects?
Is it a dependant object and copy dependants is checked?
If you script out the whole DB is there a 'GL_DISTRIBUTION'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply